Hi,

Recently I started reading this book SQL wait stats joes 2 pros written by the famous author Pinal Dave, I found it very interesting and its a good for those who want to know more about  SQL server wait stats , and there are not many resources out there which explain these concepts

While reading it I found some interesting facts and I want to share them here

What are Wait stats?

When sql server executes a task , if for any reason if it needs to wait for the resources or if wait occurs when executing the task these waits are recorded by the SQL server, the recording of the wait in SQL server is known as a WAIT STAT

in order to understand the wait stat we need to understand the Query Execution Life Cycle which is very important

Now we know how the query execution takes place lets know about the waits which can take place during the cycle

Wait stats

All these waits are recorded in  a  dynamic management view called  “SYS.DM_OS_WAIT_STATS”

Like the above mentioned waited SQL Server logs 490 different types of waits at Sql Server level, and Server level waits which is great  way to know the current waits on the server

SYS.DM_OS_WAIT_STATS

Code:

SELECT *

FROM SYS.DM_OS_WAIT_STATS

WHERE WAIT_TIME_MS > 0

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’
,’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’)

ORDER BY WAIT_TIME_MS DESC

A modified version is below which provides more information

Above result is not considered as not so bad performace but can be better than that

the information in SYS.DM_WAIT_STATS are recorded from the time when database engine is started, we can refresh the view with out even restarting the server

with a simple DBCC command

DBCC SQLPERF(‘SYS.DM_OS_WAIT_STATS’,CLEAR)

CODE:

SELECT CAST(100.00 *SUM(SIGNAL_WAIT_TIME_MS)/SUM(WAIT_TIME_MS)

AS NUMERIC(20,4)) AS ‘% OF QUERIES WAITING FOR CPU’

,CAST(100.00 *SUM(WAIT_TIME_MS-SIGNAL_WAIT_TIME_MS)/SUM(WAIT_TIME_MS)

AS NUMERIC(20,6)) AS ‘% CPU SPENDING TIME RUNNING QUERIS OR SUSPENDED ‘

FROM SYS.DM_OS_WAIT_STATS

WHERE WAIT_TIME_MS > 0

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’
,’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’)

Thank you