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