Share via

Wait Stats by Joe Sack

  • Article

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"
https://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
https://blogs.msdn.com/jimmymay 

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

Joe Sack wait stats.sql

Comments