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
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
Comments
Leave a Comment
About Jimmy May
Jimmy May is a Principal Engineer for SQL Server in the Microsoft Assessment. Consulting, & Engineering (A.C.E.) team & is assigned to Team Lee. He is the Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest, SQL Server high-throughput OLTP VLDBs. He is a founder & on the executive committees of both the Indiana Windows User Group (www.iwug.net) & Indianapolis Professional Association for SQL Server (www.indypass.org). He is a recipient of the MS IT Gold Star award, is collaborating with the SQL Server Customer Advisory Team (SQL CAT) on a series of SQL best practices papers, & is a membe of the Microsoft Oracle Center of Excellence. Jimmy lives in Indianapolis, Indiana with his lovely new bride, Phyllis, & Fannie May the Wonder Dog. Contact him at jimmymay@microsoft.com. Visit his SQL Server performance & personal productivity blog at http://blogs.msdn.com/jimmymay.
“In the late 90’s I made a conscious decision to become a geek, & started working with SQL Server in 1999. Since then it’s been quite a ride—going from the Help Desk to DBA to Architect to entrepreneur to Microsoft Principal Engineer & consultant.”
