For Day 14 of this series, we start out with Query #33, which is Top Waits. This query retrieves information from the sys.dm_os_wait_stats dynamic management view about the cumulative wait statistics for the instance since the last time it was restarted (or the wait statistics were manually cleared). Query #33 is shown in Figure 1.
1: -- Clear Wait Stats with this command
2: -- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
3:
4: -- Isolate top waits for server instance since last restart or wait statistics clear (Query 33) (Top Waits)
5: WITH [Waits]
6: AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
7: (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
8: signal_wait_time_ms / 1000.0 AS [SignalS],
9: waiting_tasks_count AS [WaitCount],
10: 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
11: ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
12: FROM sys.dm_os_wait_stats WITH (NOLOCK)
13: WHERE [wait_type] NOT IN (
14: N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
15: N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
16: N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
17: N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
18: N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
19: N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
20: N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
21: N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
22: N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
23: N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
24: N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
25: N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
26: N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
27: N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS',
28: N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
29: N'QDS_ASYNC_QUEUE',
30: N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
31: N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
32: N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
33: N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
34: N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
35: N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
36: N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
37: N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
38: N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
39: AND waiting_tasks_count > 0)
40: SELECT
41: MAX (W1.wait_type) AS [WaitType],
42: CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
43: CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
44: CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
45: MAX (W1.WaitCount) AS [Wait Count],
46: CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
47: CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
48: CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
49: CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
50: FROM Waits AS W1
51: INNER JOIN Waits AS W2
52: ON W2.RowNum <= W1.RowNum
53: GROUP BY W1.RowNum
54: HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
55: OPTION (RECOMPILE);
56:
57: -- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure
58:
59: -- The SQL Server Wait Type Repository
60: -- http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx
61:
62: -- Wait statistics, or please tell me where it hurts
63: -- https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
64:
65: -- SQL Server 2005 Performance Tuning using the Waits and Queues
66: -- http://technet.microsoft.com/en-us/library/cc966413.aspx
67:
68: -- sys.dm_os_wait_stats (Transact-SQL)
69: -- http://msdn.microsoft.com/en-us/library/ms179984(v=sql.120).aspx
Figure 1: Query #33 Top Waits
This query is can be very useful when your instance has been experiencing performance problems. At the same time, I have seen many DBAs spend way too much time agonizing about their top wait statistics when they don’t need to. SQL Server will always be waiting on some type of resource (which is why I try to filter out what are generally considered to be benign wait types). If your instance is performing well, and nobody is complaining about performance, then you can relax a little bit.
Another issue with the results of this query is that there is a lot of bad advice on the internet about what certain wait types mean and what, if anything, you should do if you see them. This often leads to what Paul Randal calls “knee-jerk” performance tuning, where you see a certain wait type, and then immediately want to make some configuration change without doing any further investigation or putting any deeper thought into the matter.
After all of those cautions, this query can be very useful in pointing you in one direction or another to do deeper investigation, especially when your instance has been performing poorly. If you do make any configuration changes, or do something else that might affect performance (such as adding an index), then it is a good idea to clear the wait statistics so that the old cumulative wait statistics don’t obscure what is going on after the change.
Query #34 is Connection Counts by IP Address. This query retrieves information from the sys.dm_exec_sessions dynamic management view and the sys.dm_exec_connections dynamic management view about your current connection counts by IP address. Query #34 is shown in Figure 2.
1: -- Get a count of SQL connections by IP address (Query 34) (Connection Counts by IP Address)
2: SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name,
3: COUNT(ec.session_id) AS [connection count]
4: FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
5: INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
6: ON es.session_id = ec.session_id
7: GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name
8: ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);
9:
10: -- This helps you figure where your database load is coming from
11: -- and verifies connectivity from other machines
Figure 2: Query #34 Connection Counts by IP Address
This query helps you see the magnitude of your workload and judge whether it is in the normal range that you should be seeing. I think it is a good idea to have a baseline for how many connections your database server typically has from whatever other machines normally connect to it. This query can also help you confirm and troubleshoot connectivity issues from other machines. I can’t tell you how many times that people have claimed my SQL Server instance was down because they could not connect to it. In the vast majority of cases, they simply had an incorrect connection string or there was a blocked port on their machine that prevented the connection. Remember, the database is always guilty until proven innocent!