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