Wait Stats by Joe Sack


Wait stats rock.  As I shared recently with friends, when it comes to SQL Server performance, wait stats are the #1 arrow in my quiver, the first tool out of my toolbox, the Vera of guns (see the Firefly episode Our Mrs. Reynolds to divine that reference).


In a previous post I stated the importance of wait stats & cited several references.


Joe Sack has recently published the best deck on the topic I’ve seen to date:



Presentation Deck for “Performance Tuning with Wait Statistics”
http://blogs.msdn.com/joesack/archive/2009/04/22/presentation-deck-for-performance-tuning-with-wait-statistics.aspx


Joe’s presentation includes:



  • What are wait stats?

  • What to collect?  How to collect?

  • Where to look for wait stats info, what to collect, what to ignore, & what to pay attention to…

  • Top 10 Patterns from the field

    • Parallelism: CXPACKET

    • CPU pressure: SOS_SCHEDULER_YIELD

    • Network I/O: ASYNC_NETWORK_IO

    • Long term blocking: LCK_X, LCK_M_U, & LCK_M_X

    • Buffer I/O latch: PAGEIOLATCH_X

    • Buffer latch: PAGELATCH_X

    • Non-buffer latch: LATCH_X

    • Memory grants: RESOURCE_SEMAPHORE

    • Tran log disk subsystem: WRITELOG & LOGBUFFER

    • General I/O issues: ASYNC_IO_COMPLETION & IO_COMPLETION

But wait, there’s more!



  • References

  • Usage Scenarios

  • Benefits

  • Wait stats in a nutshell

  • Execution model

  • Query life cycle

  • Why Waits-&-Queues

  • sys.dm_os_wait_stats & sys.dm_os_waiting_tasks

  • Reporting made simple, Performance Dashboard, & the Management Data Warehouse & Data Collector

  • Extended events introduction

  • Interpretation: Resource, Queue, & External

  • Runnable vs. Running

  • Preemptive waits

I’ve transcribed & attached the code.  I invite you to download both Joe’s deck & my transcript.


There is no fate but what we make.
    —John Connor


Administrivia



Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay 


This post was written with the PracticeThis.com plugin for Windows Live Writer

Joe Sack wait stats.sql

Comments (2)

  1. Jason Strate says:

    Joe presented this at the Minnesota SQL Server User Group meeting and it was an excellent discussion.  Also, excellent Firefly reference.