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';
