Update July 5, 2011: Many people are coming to this blog post through search engines trying to figure out what LAZYWRITER_SLEEP means. I looked at this article and determined that it’s not clear. I call the wait type ignorable, but I don’t say why. So here’s why: LazyWriter_Sleep: Happens when a lazy writer task is suspended and waits to do more work. Since lazy writer tasks are background tasks, you don’t have to consider this state when you are looking for bottlenecks. Hope this helps… By the way, a much better article than this one is here: Wait Statistics by Paul Randal. What follows now is the original article:
The sys.dm_os_wait_stats helps to see where server processes are spending most of their time waiting. If the top cause is LAZYWRITER_SLEEP or SQLTRACE_BUFFER_FLUSH, then you’re all right. If not, then you’ve got some digging to do. Maybe start with sys.dm_exec_requests.
What are you waiting for now?
The times that are shown are cumulative since the start of the SQL Server service. I use this script to figure out what SQL Server processes are waiting for now:
select * into #BufferWaitStats from sys.dm_os_wait_stats -- wait ten seconds or so select ws.wait_type, ws.waiting_tasks_count - bws.waiting_tasks_count as waiting_tasks_count, ws.wait_time_ms - bws.wait_time_ms as wait_time_ms, ws.max_wait_time_ms, ws.signal_wait_time_ms - bws.signal_wait_time_ms as signal_wait_time_ms from sys.dm_os_wait_stats ws join #BufferWaitStats bws on ws.wait_type = bws.wait_type order by wait_time_ms desc --clean up drop table #BufferWaitStats
How about adding a WAITFOR DELAY in there too, then you can compare a few results of 10 seconds exactly if you wanted to…
select * into #BufferWaitStats from sys.dm_os_wait_stats
WAITFOR DELAY ’00:00:10′;
— wait ten seconds or so
select ws.wait_type,
ws.waiting_tasks_count – bws.waiting_tasks_count as waiting_tasks_count,
ws.wait_time_ms – bws.wait_time_ms as wait_time_ms,
ws.max_wait_time_ms,
ws.signal_wait_time_ms – bws.signal_wait_time_ms as signal_wait_time_ms
from sys.dm_os_wait_stats ws
join #BufferWaitStats bws
on ws.wait_type = bws.wait_type
order by wait_time_ms desc
–clean up
drop table #BufferWaitStats
Comment by Dan T — March 9, 2011 @ 6:45 am
That’s really smart Dan. In fact looking back on my original script, I wonder why I felt I needed to add an instruction when your modification makes so much sense.
Thanks for dropping by Dan!
Comment by Michael J. Swart — March 9, 2011 @ 8:09 am
BROKER_RECEIVE_WAITFOR is probably not worth worrying about either.
Comment by Martin — January 31, 2012 @ 6:26 am