Statistik über aufgetretene Wait Types

Wenn dieser Artikel gepostet wird, befind ich mich schon im Urlaub und somit auf meiner Reise in den Kaukasus.
Aber ich habe schon vorab ein interessantes Skript vorbereitet:
Dieses Skript summiert die aufgetretenen Waits nach Typ auf und zeigt an, wie oft sie auftreten, wie lange gewartet wurde, stellt sie in prozentualer Relation dar und berechnet auch die durchschnittliche Wartezeit.
Anhand dieser Liste bekommt man oft einen Überblick, wo es am MS SQL Server kränkelt. Einen Artikel über die diversen Wait-Types, welche gut und welche böse sind, werde ich nach meiner Rückkehr verfassen.

Syntax:

WITH [Waits] AS
    (SELECT [wait_type]
           ,[wait_time_ms] / 1000.0 AS [WaitS]
           ,([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS]
           ,[signal_wait_time_ms] / 1000.0 AS [SignalS]
           ,[waiting_tasks_count] AS [WaitCount]
           ,100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage]
           , ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER'             
       ,N'BROKER_RECEIVE_WAITFOR'
       ,N'BROKER_TASK_STOP'               
       ,N'BROKER_TO_FLUSH'
       ,N'BROKER_TRANSMITTER'              
       ,N'CHECKPOINT_QUEUE'
       ,N'CHKPT'                           
       ,N'CLR_AUTO_EVENT'
       ,N'CLR_MANUAL_EVENT'                
       ,N'CLR_SEMAPHORE'
       ,N'DBMIRROR_DBM_EVENT'              
       ,N'DBMIRROR_EVENTS_QUEUE'
       ,N'DBMIRROR_WORKER_QUEUE'           
       ,N'DBMIRRORING_CMD'
       ,N'DIRTY_PAGE_POLL'                 
       ,N'DISPATCHER_QUEUE_SEMAPHORE'
       ,N'EXECSYNC'                        
       ,N'FSAGENT'
       ,N'FT_IFTS_SCHEDULER_IDLE_WAIT'     
       ,N'FT_IFTSHC_MUTEX'
       ,N'HADR_CLUSAPI_CALL'               
       ,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
       ,N'HADR_LOGCAPTURE_WAIT'            
       ,N'HADR_NOTIFICATION_DEQUEUE'
       ,N'HADR_TIMER_TASK'                 
       ,N'HADR_WORK_QUEUE'
       ,N'KSOURCE_WAKEUP'                  
       ,N'LAZYWRITER_SLEEP'
       ,N'LOGMGR_QUEUE'                    
       ,N'ONDEMAND_TASK_QUEUE'
       ,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
       ,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
       ,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
       ,N'REQUEST_FOR_DEADLOCK_SEARCH'     
       ,N'RESOURCE_QUEUE'
       ,N'SERVER_IDLE_CHECK'               
       ,N'SLEEP_BPOOL_FLUSH'
       ,N'SLEEP_DBSTARTUP'                 
       ,N'SLEEP_DCOMSTARTUP'
       ,N'SLEEP_MASTERDBREADY'             
       ,N'SLEEP_MASTERMDREADY'
       ,N'SLEEP_MASTERUPGRADED'            
       ,N'SLEEP_MSDBSTARTUP'
       ,N'SLEEP_SYSTEMTASK'                
       ,N'SLEEP_TASK'
       ,N'SLEEP_TEMPDBSTARTUP'             
       ,N'SNI_HTTP_ACCEPT'
       ,N'SP_SERVER_DIAGNOSTICS_SLEEP'     
       ,N'SQLTRACE_BUFFER_FLUSH'
       ,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
       ,N'SQLTRACE_WAIT_ENTRIES'           
       ,N'WAIT_FOR_RESULTS'
       ,N'WAITFOR'                         
       ,N'WAITFOR_TASKSHUTDOWN'
       ,N'WAIT_XTP_HOST_WAIT'              
       ,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
       ,N'WAIT_XTP_CKPT_CLOSE'             
       ,N'XE_DISPATCHER_JOIN'
       ,N'XE_DISPATCHER_WAIT'              
       ,N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
 )
SELECT
    MAX ([W1].[wait_type])          AS [WaitType],
    CAST (MAX ([W1].[WaitS])        AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS])    AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS])      AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount])          AS [WaitCount],
    CAST (MAX ([W1].[Percentage])                           AS DECIMAL (5,2))  AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount]))     AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount]))   AS DECIMAL (16,4)) AS [AvgSig_S]
 FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
   ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95
;

Langläufer suchen und warnen

Kurz vor meiner Urlaubspause (ja, auch ich darf mal) gibt es noch ein neues Skript.
Das unten angeführte Skript nehme ich gerne zur Laufzeitüberwachung von diversen Queries. Es passiert immer wieder, dass Queries, welche normalerweise nur einige Sekunden oder Minuten laufen, plötzlich von einem Tag auf den anderen schier unendlich laufen. 
Da natürlich viele Abfragen und Programme gleichzeitig Laufen, jedesmal zu einem unterschiedlichen Zeitpunkt, sollte man dies automatisiert überwachen.
In meinem Beispielskript habe ich zusätzlich eine Variable @Laufzeitgrenze eingebaut, welche wir derzeit via Control-M als Schwellwert mitgeben. Sollte die Abfrage ein Ergebnis zurückliefern wird automatisch ein Mail generiert, welches auf einen Langläufer hinweist. 
Danach erfolgt die übliche Analyse. Indizes, Statistiken, PlanHandle wegwerfen, Neustart.

Hat jemand Ideen zur Erweiterung oder Vereinfachung? Oder hat jemand einen komplett anderen Ansatz um dies zu bewerkstelligen? Freue mich über jede Rückmeldung

Syntax:

DECLARE @Laufzeitgrenze AS datetime
SET @Laufzeitgrenze = '00:05:00:000' -- 5 Minuten
-- SET @Laufzeitgrenze = '01:00:00:000' -- 1 Stunde

SELECT 'Schwellwert überschritten!!!' AS Warnung  
      ,[SID]   
      ,[STATUS]  
      ,[DOP]  
      ,[BLOCKED]  
      ,[DBNAME]  
      ,[PROGRAM_NAME]  
      ,CONVERT(CHAR(12),((CURRENT_TIMESTAMP)-start_time),14) AS [LAUFZEIT]
      ,[LOGINNAME]  
      ,[COMMAND]  
      ,SQLStatement  
      ,start_time  
      ,[PLAN_HANDLE]
FROM
  ( SELECT req.session_id AS [SID]  
          ,sqltext.TEXT AS [SQLStatement]  
          ,DB_NAME(req.database_id) AS [DBNAME]  
          ,req.status AS [STATUS]  
          ,mg.dop AS [DOP]
          ,p.blocked AS [BLOCKED]  
          ,req.command AS [COMMAND]  
          ,req.start_time AS start_time  
          ,p.loginame AS [LOGINNAME]  
          ,p.program_name AS [PROGRAM_NAME]  
          ,req.plan_handle AS [PLAN_HANDLE]
    FROM sys.dm_exec_requests req WITH (NOLOCK) 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) sqltext 
   CROSS APPLY sys.dm_exec_query_plan (plan_handle) eqp
   INNER JOIN sys.sysprocesses p WITH (NOLOCK) ON p.spid = req.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants mg WITH (NOLOCK) 
      ON req.session_id = mg.session_id
   WHERE req.session_id > 50
     AND req.session_id NOT IN (@@SPID)
     AND p.loginame <> ''
     AND (CURRENT_TIMESTAMP)-start_time > @Laufzeitgrenze) sub
ORDER BY start_time ASC

UpdateStatistic Skript

Eine weitere regelmäßige Tätigkeit ist es, die Statistiken aktuell zu halten.
Anbei ein kleines Skript, welches in der Erstversion bereits einige Steuerungsmöglichkeiten enthält. 

USE [MyDatabase]
GO

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

/*#####################################################################*/
/*############ DIESE WERTE VOR DER AUSFÜHRUNG ÄNDERN ! ################*/
/*#####################################################################*/
DECLARE @Resample             int    = 0;    

     -- 0|1 Schalter - wenn 1, erfolgt der StatUpd. mit Resample,
     -- und somit wie bei der letzten Durchführung
     -- wenn 0, wird nach Satzanz. gesteuert, wieviele gesampelt werden.
DECLARE @Exec                 int    = 0;    

     -- 0|1 Schalter - wenn 1, wird das SQL auch ausgeführt, 

     --0 ist nur für Analyse
DECLARE @Print                int    = 1;    

     -- 0|1 Schalter - wenn 1, wird das SQL zusätzlich ausgegeben
/*######################################################################*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SatzanzahlVeraendert int = 0;
DECLARE @Satzanzahl           int = 0;
DECLARE @SchemaName           nvarchar(130);
DECLARE @TableName            nvarchar(130);
DECLARE @IndexName            nvarchar(130);
DECLARE @Command              nvarchar(4000);

/*
   Für den Statistikupdate relevante Daten ermitteln
   Einschränkung auf Indizes, welche überhaupt veränderte Daten haben, 
   Microsoft interne Indizes werden ausgeschlossen
   Optional kann hier eingeschränkt werden, dass nur Indizes mit einer mind./max. Anzahl 
     von Sätzen berücksichtigt werden
*/

SELECT sSchem.name          AS SchemaName
  , sTab.name        AS TableName
  , sInd.name        AS IndexName
  , ssInd.rowcnt     AS Satzanzahl
  , ssInd.rowmodctr  AS SatzanzahlVeraendert
  INTO #IndizesFuerUpdateStatistic
  FROM sys.indexes AS sInd
 INNER JOIN sys.sysindexes AS ssInd 
    ON sInd.object_id     = ssInd.id
   AND sInd.name          = ssInd.name
 INNER JOIN sys.tables AS sTab 
    ON sTab.[object_id]   = sInd.[object_id]
 INNER JOIN sys.schemas AS sSchem 
    ON sSchem.[schema_id] = sTab.[schema_id]
 WHERE sTab.is_ms_shipped = 0 -- Only application indexes
   AND ssInd.rowcnt       > 100 -- Only indexes with at least 100 rows
   AND ssInd.rowmodctr    > 0 -- Only indexes with changed data
 
-- Cursor deklarieren und öffnen
DECLARE C_Indizes CURSOR FOR SELECT * FROM #IndizesFuerUpdateStatistic;
OPEN C_Indizes;

-- Schleife zum abarbeiten der Statistikupdates - hier wird auch gesteuert, 
-- wie die Statisticupdates durchgeführt werden.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM C_Indizes
INTO @SchemaName, @TableName, @IndexName, @Satzanzahl, @SatzanzahlVeraendert;
 BEGIN TRY
   IF @@FETCH_STATUS < 0 BREAK; -- wenn kein Satz mehr im Cursor, dann Schleife verlassen

   SET @Command = N'UPDATE STATISTICS '
                + QUOTENAME(@SchemaName) 
                   + N'.' 
                   + QUOTENAME(@TableName)
                   + N' ' 
                   + QUOTENAME(@IndexName) 
   IF @Resample = 1
   BEGIN
  SET @Command = @Command + N' WITH RESAMPLE';
      END
      ELSE IF @Resample = 0
      BEGIN
        SET @Command = @Command 
                   + N' WITH SAMPLE '
                   + CASE
                       WHEN @Satzanzahl < 100000     THEN N'100 PERCENT'
                       WHEN @Satzanzahl < 500000     THEN N'50 PERCENT'
                       WHEN @Satzanzahl < 10000000   THEN N'25 PERCENT'
                       WHEN @Satzanzahl < 30000000   THEN N'10 PERCENT'
                       WHEN @Satzanzahl < 50000000   THEN N'5 PERCENT'
                       WHEN @Satzanzahl < 100000000  THEN N'1 PERCENT'
                       ELSE N'500000 ROWS '
                     END
      END
--
      SET @Command = @Command + N' -- ' + CAST(@Satzanzahl AS VARCHAR(22)) + N' rows'
--
      IF @Print = 1
        PRINT N'Abzusetzendes Statement: ' +  @Command
--
      IF @Exec = 1
        EXEC (@Command)
--
 BEGIN TRAN
 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;


DROP TABLE #IndizesFuerUpdateStatistic

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

Procedure Statistiken

Eine sehr hilfreiche vom SQL Server zur Verfügung gestellte System-StoredProcedure heißt sys.dm_exec_procedure_stats
Mit ihr kann man sich in Windeseile einen Überblick über die Statistiken von Procedures verschaffen und einfach zeitintensive Prozeduren eruieren.
Die unten angeführte Query ermittelt die Top-10 der zeitaufwändigsten Durchführungen inklusive einiger wichtigen Attribute, wie die durchschnittliche Ausführungszeit und Anzahl der Durchführungen.
Eine Procedure, welche nicht nur zeitintensiv ist, sondern auch noch oft ausgeführt ist, sollte man sich auf jeden Fall genauer ansehen. Unter Umständen gibt es hier mit wenig Aufwand ein großes Optimierungspotential zu heben. 

-- Die Top 10 Abfragen mit dem längsten Zeitaufwand
SELECT TOP 10 
  d.object_id
 ,d.database_id
 ,OBJECT_NAME(object_id, database_id) 'procedure name'
 ,d.cached_time
 ,d.last_execution_time
 ,d.total_elapsed_time / 1000000 as GesamtzeitSek
 ,(d.total_elapsed_time/d.execution_count) / 1000000 AS DurchschzeitSek
 ,d.last_elapsed_time / 1000000 as LetzteDurchfZeitSek
 ,d.execution_count as AnzahlDurchfuehrungen
 ,d.sql_handle
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;