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”
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!
- Usage Scenarios
- 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.