What is SQL Server Waiting On?

Waiting is one of the hardest things for many people to do. 

For example, imagine that you are on your way to work in a car and you come upon a traffic jam.  Your once short commute is now taking much longer.  And the longer you wait the more frustrated you grow with your situation.

However, one thing that makes waiting a little easier is if you understand why you are waiting or what is making your task take so long.  Is there construction ahead?  Is it an accident?  Are you blocked by a train crossing?

Because once you know the reason, you could possibly take action to lessen your wait.  For example, maybe you can take another street to bypass the construction.

Similarly, users of SQL Server are like commuters in that they do not like to wait.  And the longer they wait, the more frustrated they become.  And as DBA’s, we probably have all experienced that phone call that “the server is running slow”.

How do we find out what is happening?

One method that many DBA’s use is analyzing the wait stats that SQL Server produces.  The key to analyzing this data is contingent on becoming familiar with the following dynamic management views (DMV):

Here are some basic queries that use these DMV’s:

  • Finding system waits

SELECT DISTINCT
wt.wait_type
FROM sys.dm_os_waiting_tasks AS wt
INNER JOIN sys.dm_exec_sessions AS s
ON wt.session_id = s.session_id
WHERE s.is_user_process = 0

  •   Finding the top waits since the last reset

SELECT
wait_type,
waiting_tasks_count,
wait_time_ms / 1000. AS wait_time_s,
wait_time_ms / waiting_tasks_count / 1000. AS average_wait_time_per_task_s,
max_wait_time_ms / 1000. AS max_wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS wait_percent
FROM sys.dm_os_wait_stats
WHERE
-- ignore system waits
wait_type NOT IN(
SELECT DISTINCT
wt.wait_type
FROM sys.dm_os_waiting_tasks AS wt
INNER JOIN sys.dm_exec_sessions AS s
ON wt.session_id = s.session_id
WHERE s.is_user_process = 0
)
-- ignore certain waits
AND wait_type NOT IN('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'
)
-- ignore waits that did not have any tasks
AND waiting_tasks_count <> 0
ORDER BY wait_percent DESC

  • Finding what a specific task is waiting on

SELECT
c.session_id,
wait_duration_ms,
wait_type
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_os_waiting_tasks wt
ON c.session_id = wt.session_id

How do we interpret the results?

Once the top waits on your system  or a specific task are found, you will have an idea of what is causing the waits (i.e. CPU pressure, blocking, IO, etc.).  However, this is just a starting point.  You will have to do further analysis by collecting more specific data using another tool, such as PerfMon or DMV related queries.  These tools with help you dig deeper into the issue at hand.  Finally, once you know what is causing your problem, you can take action to fix it.

The following references will be helpful with understanding waits:

SQL Server Community bloggers

 

Advertisements
This entry was posted in DBA and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s