In sql server 2005 and later version we can get this info from various DMVs.One of the most useful DMV is sys.dm_qs_wait_stats.This DMV gives you the detail report of all wait types and you can look into those wait types which are causing issue or waiting from lot much time.You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.Following columns will come in output when you run this DMV:
- 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 time 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.
sys.dm_os_wait_stats shows the time for waits that have completed. This dynamic management view does not show current waits.If you want to see current wait type then you should use another system table sys.sysprocesses.Run below cmd:
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 process id then you can run below cmd to get the codes behind that SP id:
DBCC inputbuffer(SP ID)
You can also compare DMV (sys.dm_os_wait_stats)output to this sysprocess table output and you can better analyze the exact issue.
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:
Copy
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
This command resets all counters to 0.
