The whitepaper SQL Server 2005 Waits and Queues describes a methodology called Waits and Queues. By using this methodology one can identify the best opportunities to improve performance, the so called “biggest bang for the buck”. Definitely recommended reading for anyone that is into performance tuning.
There are a few issues with this approach if not properly understood. I therefore recommend reading the whitepaper thoroughly before jumping to conclusions.
First of all, the DMV Sys.dm_os_wait_stats are aggregated across all session ids since the last restart of SQL Server or since the last time that the wait statistics were reset manually using DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR). So the output of this DMV will be hugely influenced by maintenance tasks such as index rebuilds, database backups, etc that often run during out-of-office hours. So it is important to reset the waitstats before the start of any workload you’re trying to analyze.
Secondly, not all waits have an impact on performance. For example the wait type SQLTRACE_BUFFER_FLUSH can be in the top of the list, but this simply the time the SQL Trace flush task spent sleeping between flushes. So don’t go and disable the default trace when you see this high in your list, as I have seen other people recommend! See http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx for a measure of the performance impact of the default trace.
A handy addition to a select on the wait stats, is the case-block below, which I extracted from one of the UDF’s of the SQL 2005 Peformance Dashboard Reports. Notice the conditions that result to a ‘Sleep’ Category.
select
case
when ws.wait_type like N’LCK_M_%’ then N’Lock’
when ws.wait_type like N’LATCH_%’ then N’Latch’
when ws.wait_type like N’PAGELATCH_%’ then N’Buffer Latch’
when ws.wait_type like N’PAGEIOLATCH_%’ then N’Buffer IO’
when ws.wait_type like N’RESOURCE_SEMAPHORE_%’ then N’Compilation’
when ws.wait_type = N’SOS_SCHEDULER_YIELD’ then N’Scheduler Yield’
when ws.wait_type in (N’LOGMGR’, N’LOGBUFFER’, N’LOGMGR_RESERVE_APPEND’, N’LOGMGR_FLUSH’, N’WRITELOG’) then N’Logging’
when ws.wait_type in (N’ASYNC_NETWORK_IO’, N’NET_WAITFOR_PACKET’) then N’Network IO’
when ws.wait_type in (N’CXPACKET’, N’EXCHANGE’) then N’Parallelism’
when ws.wait_type in (N’RESOURCE_SEMAPHORE’, N’CMEMTHREAD’, N’SOS_RESERVEDMEMBLOCKLIST’) then N’Memory’
when ws.wait_type like N’CLR_%’ or ws.wait_type like N’SQLCLR%’ then N’CLR’
when ws.wait_type like N’DBMIRROR%’ or ws.wait_type = N’MIRROR_SEND_MESSAGE’ then N’Mirroring’
when ws.wait_type like N’XACT%’ or ws.wait_type like N’DTC_%’ or ws.wait_type like N’TRAN_MARKLATCH_%’ or ws.wait_type like N’MSQL_XACT_%’ or ws.wait_type = N’TRANSACTION_MUTEX’ then N’Transaction’
when ws.wait_type like N’SLEEP_%’ or ws.wait_type in(N’LAZYWRITER_SLEEP’, N’SQLTRACE_BUFFER_FLUSH’, N’WAITFOR’, N’WAIT_FOR_RESULTS’) then N’Sleep’
else N’Other’
end as category,
ws.wait_type,
ws.waiting_tasks_count,
case when ws.waiting_tasks_count = 0 then 0 else ws.wait_time_ms / ws.waiting_tasks_count end as average_wait_time_ms,
ws.wait_time_ms as total_wait_time_ms,
convert(decimal(12,2), ws.wait_time_ms * 100.0 / sum(ws.wait_time_ms) over()) as wait_time_proportion,
ws.wait_time_ms - signal_wait_time_ms as total_wait_ex_signal_time_ms,
ws.max_wait_time_ms,
ws.signal_wait_time_ms as total_signal_wait_time_ms,
@tstamp as tstamp
from
sys.dm_os_wait_stats ws
where
ws.waiting_tasks_count > 0 – Restrict results to requests that have actually occured.
order by
ws.wait_time_ms desc
Comments