What is SQL Server Wait Type? or How to get wait type info in sql server?

• wait_type :- Name of the wait type.
  waiting_tasks_count:-Number of waits on this wait type. This counter is incremented at the start of each wait.
• wait_time_ms:-Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
• max_wait_time_ms:- Maximum wait time on this wait type.
• signal_wait_time_ms:-Difference between the times that the waiting thread was signaled and when it started running.

In general there are three categories of waits that could affect any given request:

• Resource waits are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted. Resource waits are the ones you should focus on for troubleshooting the large majority of performance issues.
• External waits occur when SQL Server worker thread is waiting on an external process, such as extended stored procedure to be completed. External wait does not necessarily mean that the connection is idle; rather it might mean that SQL Server is executing an external code which it cannot control. Finally the queue waits occur if a worker thread is idle and is waiting for work to be assigned to it.
• Queue waits normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted. Normally you don’t have to worry about any performance degradation due to queue waits.

DMV sys.dm_os_wait_stats shows the time for waits that have completed. This DMV does not show current waits. If you want to see current wait type then you should use another system table sys.sysprocesses. Run below command:

Select * from sys.sysprocesses

The output describes you the wait time and wait type for each process id. So here you can get which process is pending since how much time and which type of wait is that. Once you have the sp id then you can run below command to get the exact code running behind that SP id:

DBCC Inputbuffer(SP ID)

You can also compare DMV (sys.dm_os_wait_stats) output to this sysprocesses table output and you can better analyze the exact issue.

Related Article:

• Understanding Resource Semaphore Wait Type
• What is ASYNC_IO_COMPLETION Wait Type?
• How to deal with BACKUPBUFFER wait type?

A SQL Server worker thread is not considered to be waiting if any of the following is true:

*      A resource becomes available.
*      A queue is nonempty.
*      An external process finishes.

Although the thread is no longer waiting, the thread does not have to start running immediately. This is because such a thread is first put on the queue of runnable workers and must wait for a quantum to run on the scheduler.

Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.

The contents of this dynamic management view can be reset by running the following command:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

This command resets all counters to 0.

I hope you like this article. You can follow our Facebook page and Twitter handle to get latest updates.