This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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