-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment