sqlskills-logo-2015-white.png

SQL Server Diagnostic Information Queries Detailed, Day 14

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.