Michael J. Swart

April 30, 2008

What are you waiting for?

Filed under: SQL Scripts,Technical Articles — Tags: , , — Michael J. Swart @ 5:04 am

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

3 Comments »

  1. 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

  2. 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

  3. BROKER_RECEIVE_WAITFOR is probably not worth worrying about either.

    Comment by Martin — January 31, 2012 @ 6:26 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress