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
