waits.sql

-- Clear Wait Stats 
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or statistics clear  (Query 32) (Top Waits)
-- New SQL Server 2012-2014 specific version
-- QDS_PERSIST_TASK_MAIN_LOOP_SLEEP is new for SQL Server 2014
-- QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP is new for SQL Server 2014
-- WAIT_XTP_HOST_WAIT is new for SQL Server 2014
-- WAIT_XTP_OFFLINE_CKPT_NEW_LOG is new for SQL Server 2014
-- WAIT_XTP_CKPT_CLOSE is new for SQL Server 2014
-- PWAIT_ALL_COMPONENTS_INITIALIZED is new for SQL Server 2014

WITH Waits
AS (SELECT  wait_type
          , CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS [wait_time_s]
          , CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(12, 2)) AS [pct]
          , ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
     FROM   sys.dm_os_wait_stats WITH (NOLOCK)
     WHERE  wait_type NOT IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH'
                            , N'WAITFOR', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'BROKER_TO_FLUSH'
                            , N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTS_SCHEDULER_IDLE_WAIT'
                            , N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE', N'BROKER_EVENTHANDLER'
                            , N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
                            , N'SP_SERVER_DIAGNOSTICS_SLEEP', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
                            , N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'PWAIT_ALL_COMPONENTS_INITIALIZED'))
   , Running_Waits
AS (SELECT  W1.wait_type
          , wait_time_s
          , pct
          , SUM(pct) OVER (ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS [running_pct]
     FROM   Waits AS W1)
 SELECT     wait_type
          , wait_time_s
          , pct
          , running_pct
  FROM      Running_Waits
  WHERE     running_pct - pct <= 99
  ORDER BY  running_pct
OPTION (RECOMPILE);


-- The SQL Server Wait Type Repository
-- http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

-- Wait statistics, or please tell me where it hurts
-- http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

-- SQL Server 2005 Performance Tuning using the Waits and Queues
-- http://technet.microsoft.com/en-us/library/cc966413.aspx

-- sys.dm_os_wait_stats (Transact-SQL)
-- http://msdn.microsoft.com/en-us/library/ms179984(v=sql.120).aspx



-- Signal Waits for instance  (Query 33) (Signal Waits)
SELECT  CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [% Signal (CPU) Waits]
      , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [% Resource Waits]
 FROM   sys.dm_os_wait_stats WITH (NOLOCK)
OPTION (RECOMPILE);

-- Signal Waits above 15-20% is usually a sign of CPU pressure