Maintenance Skript - Index Reorg/Rebuild, Update Statistics und Procedure Cache leeren

Bereits vor einigen Tagen habe ich einen Post über Index Rebuild und Reorg, sowie auch über Statistikupdates geschrieben und ein kleines Skript zur Verfügung gestellt.
Dieses Skript habe ich mittlerweile etwas erweitert. Es gibt mehr Einstellungs- und Steuermöglichkeiten.
Weiters wird kann nun automatisch nach einem Index-Reorg ein automatisches Statistikupdate durchgeführt werden, falls gewünscht.
Darüber hinaus kann auch der Procedure Cache der gesamten Datenbank geleert werden. Dies führt dazu, dass bei Neudurchführungen ein neuer Zugriffsplan vom Optimizer errechnet wird.

Zusätzlich gibt es auch die Möglichkeiten Statistiken auszugeben. Die Anzahl der durchzuführenden Arbeitsschritte wird errechnet, die Zeit je Schritt mitprotokolliert und daraus resultierend eine ungefähre Hochrechnung und somit Endzeit der Arbeitsschritte errechnet.
Achtung! Diese Zeit ist am Anfang der Skriptdurchführung sehr ungenau und wird erst im Laufe der Durchführung immer genauer, da kleine Indizes die Zielzeit natürlich nach unten drücken und wenn ein sehr großer Index dabei ist, entwickelt sich die Zieleit natürlich auch in die andere Richtung.

Am Skriptende wird auch eine kurze Zusammenfassung ausgegeben, welche ein Anhaltspunkt für weitere Durchführungen und der Einplanung von Wartungsfenstern sein kann.

USE [MyDatabase]
GO

/* 
 Mit diesem Skript erfolgt eine Indexpflege auf der oben angeführten (USE [abc]) Datenbank.
 Sofern nur ein bestimmtes Zeitfenster zur Verfügung steht, kann variablengesteuert (@reorgrebuiltlimit) ein Zeitlimit in Minuten mitgegeben werden
 Ist die gesamthafte Verarbeitung zu diesem Zeitpunkt noch nicht beendet, wird kein neuer Index-Reorg/Rebuild mehr gestartet.
 Jedoch wird der aktuelle Reorg/Rebuild noch abgeschlossen. Dies kann dazu führen, dass das Endlimit überschritten wird. Bitte berücksichtigen!

 Jeder Index-Reorg/Rebuild wird in einer eigenen Transaktion abgewickelt, dies hat den Grund, dass ansonsten das Transaction Log bei einem
 Reorg von sehr vielen Indizes stark anschwillt und darüber hinaus ein Rollback aller bereits reorganisierten Indizes nicht wünschenswert ist.
 Sprich: Im Fehlerfall wird nur ein Rollback auf den aktuell bearbeiteten Index durchgeführt

 Mittels Variablen können die zu wartenden Indizes eingegrenzt werden in Hinsicht auf Fragmentierung, Größe etc.
 Weiters kann eingestellt werden, wieviele Prozessoren verwendet werden sollen (MAXDOP)
 Darüberhinaus besteht die Möglichkeit, den ProcedureCache am Ende des Skripts für die gesamte DB zu leeren und zukünftige Zugriffe somit 
 zu einer Neuberechnung des Zugriffsplans zu zwingen

 Optional gibt es auch eine Forecast-Option (@avgruntime), welche nach einer mitgegebenen Anzahl von Arbeitsschritten
 jeweils die vorraussichtliche Endzeit der ganzen Verarbeitung hochrechnet. Achtung, hat natürlich einen kleinen Performancenachteil
 und wird erst im Laufe der Verarbeitung immer genauer.
*/

---------------------------------------------------
-- Produced By   : Stefan Perner 
-- URL           : www.sqlwithpleasure.blogspot.co.at
-- Author        : Stefan Perner 
-- Date          : 2015-07-06
-- Purpose       : Database Maintenance 
-- Modifications : no changes, as it is perfect
------------------------------------------------------------

/*##################################################################################################################*/
/*###################### DIESE WERTE VOR DER AUSFÜHRUNG ÄNDERN ! ###################################################*/
/*##################################################################################################################*/
DECLARE @page_count_minimum smallint  = 500;  -- nur Indizes größer diesem Pagewert werden berücksichtigt
DECLARE @fragmentation_minimum float  = 10.0; -- nur Indizes mit Fragmentierung größer dem Prozentwert werden berücksichtigt
DECLARE @reorgrebuiltlimit int        = 30;   -- unter diesem Schwellwert wird ein Reorg durchgeführt, darüber ein Rebuilt 
DECLARE @onlyanalyze int              = 1;    -- 0|1 Schalter - wenn 1, dann wird der Reorg/Rebuilt nicht durchgeführt, 
                                              --                jedoch die generierten Statements aufgelistet
DECLARE @maxruntimeminutes int        = 180;  -- maximales Wartungsfenster in Minuten
DECLARE @maxdopactive int             = 1;    -- 0|1 Schalter - wenn 1, dann wird MAXDOP bei Index Rebuild/Reorg verwendet
DECLARE @maxdopvalue int              = 4;    -- MaxDOP - Degree of Parallelism - Anzahl Prozessoren, welche verwendet werden  
DECLARE @avgruntime int               = 1;    -- 0|1 Schalter - wenn 1, dann wird eine hochgerechnete Endzeit ausgegeben.
DECLARE @avgruntimeevery int          = 10;    -- bestimmt, wie oft die Endzeit hochgerechnet wird 
                                              --                nur wirksam, wenn @avgruntime gesetzt ist
DECLARE @clearprocfordb int           = 1;    -- 0|1 Schalter - wenn 1, wird Proc Cache der DB am Ende des Skripts geleert!
/*##################################################################################################################*/

-- Ensure a USE  statement has been executed first.
SET NOCOUNT ON;
-- Cursor nicht durch Commit schließen lassen!
SET CURSOR_CLOSE_ON_COMMIT OFF;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @C_Indizes bigint;
DECLARE @frag float;
DECLARE @pagecount int;
DECLARE @command nvarchar(4000);
DECLARE @starttime DATETIME;
DECLARE @starttimeindex DATETIME;
DECLARE @workuntiltime DATETIME;
DECLARE @estimatedendtime DATETIME;
DECLARE @amountofindices int;
DECLARE @processingcounter int = 0;
DECLARE @reorgcounter int = 0;
DECLARE @rebuildcounter int = 0;
DECLARE @statcounter int = 0;
--
-- Startzeitpunkt und Abbruchzeitpunkt (wenn mit Arbeit nicht fertig) werden berechnet
SET @starttime = GETDATE();
PRINT N'Index Defragmentierungsjob gestartet um  ' + Convert(varchar(11),@starttime);
SET @workuntiltime = DATEADD(mi,@maxruntimeminutes,GETDATE());

-- Indizes anhand der obangeführten Einschränkungskritierien selektieren
SELECT object_id AS objectid,
       index_id AS indexid,
       partition_number AS partitionnum,
       avg_fragmentation_in_percent AS frag,
       page_count AS page_count
 INTO #work_to_do
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
  AND index_id > 0
  AND page_count > @page_count_minimum;

SELECT @amountofindices = COUNT(*) FROM #work_to_do;
PRINT N'In diesem Lauf werden vorraussichtlich ' + Convert(varchar(11),@amountofindices) +  N' Indizes verarbeitet';

PRINT N'Falls die Verarbeitung um ' +   CAST(CONVERT(datetime2, @workuntiltime, 126) AS nvarchar(30))  + N' noch nicht abgeschlossen ist, beendet sich das Skript von selbst!';
PRINT N' ';
PRINT N' ';

SET @starttimeindex = GETDATE();

-- 
DECLARE C_Indizes CURSOR FOR SELECT * FROM #work_to_do;
OPEN C_Indizes;

-- Alle oben als relevant eingestuften Indizes werden nun in einer Schleife abgearbeitet
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM C_Indizes
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;
 BEGIN TRY
 BEGIN TRAN
 SET @processingcounter = @processingcounter + 1;
 -- Exit, wenn alle gecachten Indizes abgearbeitet wurden
 IF @@FETCH_STATUS < 0 BREAK;
 -- wenn Ende des Wartungsfensters erreicht wurde, wird beendet 
 IF GETDATE() > @workuntiltime BREAK;

 -- Objekt- und Schemanamen ermitteln
 SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
 FROM sys.objects AS o
 JOIN sys.schemas as s ON s.schema_id = o.schema_id
 WHERE o.object_id = @objectid;

 -- Indexname ermitteln
 SELECT @indexname = QUOTENAME(name)
 FROM sys.indexes
 WHERE  object_id = @objectid AND index_id = @indexid;

 -- Partitionsanzahl ermitteln
 SELECT @partitioncount = count (*)
 FROM sys.partitions
 WHERE object_id = @objectid AND index_id = @indexid;

 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;

 -- Hier wird entschieden, ob ein REORG oder ein REBUILD eines Index vorgenommen wird 
 IF @frag > @reorgrebuiltlimit 
 BEGIN
  SET @command = @command + N' REBUILD';
  SET @rebuildcounter = @rebuildcounter + 1;
    END
 ELSE IF @frag <= @reorgrebuiltlimit 
 BEGIN
  SET @command = @command + N' REORGANIZE';
  SET @reorgcounter = @reorgcounter + 1;
    END

 IF @partitioncount > 1
 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

-- Parallelisierung - Falls gewünscht wird hier die Anzahl der Prozessoren, welche bei REBUILD verwendet werden, mitgegeben.
 IF @maxdopactive = 1 AND  @frag >= @reorgrebuiltlimit
    SET @command = @command + N' WITH (MAXDOP=' + CAST(@maxdopvalue AS nvarchar(10))  + N')'

 -- Protokollierung in Joblog ausgegeben.
 PRINT N'----------------------------------------------------------------------------';
 PRINT N'Arbeitsschritt ' + Convert(varchar(11),@processingcounter) + N' von ' + Convert(varchar(11),@amountofindices) + N' wird durchgeführt.'
 PRINT @command
 IF @frag > @reorgrebuiltlimit
  PRINT N'Rebuilding index ' + @indexname + ' on table ' + @objectname;
 ELSE IF @frag <= @reorgrebuiltlimit
  PRINT N'Reorganizing index ' + @indexname + ' on table ' + @objectname;
 PRINT N'  Fragmentation: ' + CAST(@frag AS varchar(15));
 PRINT N'  Page Count:    ' + CAST(@pagecount AS varchar(15));
 PRINT N'Start Index-Reorg/Rebuilt um : ' + convert(char(50), GETDATE(), 114) 

 -- Statement wird nun ausgeführt, wenn gewünscht
 IF @onlyanalyze = 0 EXEC (@command);
 PRINT N'Ende Index-Reorg/Rebuilt um  : ' + convert(char(50), GETDATE(), 114) 

 -- Wenn "nur" REORG durchgeführt wurde, dann werden nun auch noch die Statistiken upgedatet
 IF  @frag <= @reorgrebuiltlimit
 BEGIN
  SET @command = N'UPDATE STATISTICS ' + @schemaname + N'.' + @objectname
  PRINT N'Start Statistikupdate        : ' + convert(char(50), GETDATE(), 114) 
  IF @onlyanalyze = 0 EXEC (@command);
  SET @statcounter = @statcounter + 1;
  PRINT N'Ende Statistikupdate         : ' + convert(char(50), GETDATE(), 114) 
 END
 
 PRINT N' Arbeitsschritt ' + Convert(varchar(11),@processingcounter) + N' von ' + Convert(varchar(11),@amountofindices) + N' wird beendet.'

 -- wenn Hochrechnung der Endzeit erwünscht ist, wird sie hier ausgegben.
 IF @avgruntime = 1 AND @processingcounter % @avgruntimeevery = 0
 BEGIN
  PRINT N'Vorraussichtliches Ende des Skripts um: ' + 
        CAST(CONVERT(datetime2, DATEADD(millisecond,((DATEDIFF ( ms , @starttimeindex , GETDATE() ) / @processingcounter) * @amountofindices),@starttimeindex), 126) AS nvarchar(30))
 END

 PRINT N' ';

 COMMIT TRAN
 END TRY
 BEGIN CATCH
 ROLLBACK TRAN
 PRINT 'ERROR ENCOUNTERED'
 END CATCH
END; -- Ende der Schleife!

-- Cursor schließen und auch deallokieren
CLOSE C_Indizes;
DEALLOCATE C_Indizes;

-- temporäre Tabelle wieder entfernen
DROP TABLE #work_to_do;

--
-- Wenn gewünscht, wird noch der Procedure Cache geleert, damit bei Neudurchführungen ein neuer Zugriffsplan erstellt werden muss
IF @clearprocfordb = 1 AND @onlyanalyze = 0
BEGIN
 DECLARE @intDBID INT;
 SET @intDBID = (SELECT DB_ID() AS [Database ID]);
 DBCC FLUSHPROCINDB (@intDBID);
END
;

PRINT N' ';
PRINT N'----------------------------------------------------------------------------';
PRINT N'                 Zusammenfassung der Verarbeitung';
PRINT N'Index Defragmentierungsjob gestartet um  ' + convert(char(50), @starttime, 114);
PRINT N'Index Defragmentierungsjob beendet um    ' + convert(char(50), GETDATE(), 114);
PRINT N'Index REORGs                             ' + Convert(varchar(11),@reorgcounter);
PRINT N'Statistikupdates                         ' + Convert(varchar(11),@statcounter);
PRINT N'Index REBUILDs                           ' + Convert(varchar(11),@rebuildcounter);
PRINT N'Durchschn. Zeit je Index:                ' + Convert(varchar(11),(CAST(DATEDIFF ( millisecond , @starttimeindex , GETDATE() ) AS FLOAT) / @processingcounter) / 1000) + N' Sekunden';