Mastering Server Tuning with Wait Stats
You need to speed up a SQL Server, but you’re not sure where to begin. Is it a database problem? A query issue? Not enough hardware? Goofy SQL Server settings? Good news – I do this for a living, and I’ll teach you how in 3 days of learning and fun. Join me, Brent Ozar, as I explain how to assess your SQL Server’s bottlenecks and figure out where to focus your tuning efforts.
Labs & lectures include:
- How to use sp_BlitzFirst to identify your server’s top bottlenecks
- How to fix common wait types like CXPACKET, PAGEIOLATCH, LCK%, SOS_SCHEDULER_YIELD
- How to identify and mitigate poison wait types like RESOURCE_SEMAPHORE and THREADPOOL
- How to prove that your changes made things better
- How to decide whether configuration changes, query/index tuning, or hardware/VM changes will give you the biggest bang for the buck
Your Progress So Far in This Class
This is driven by the mark-as-complete buttons in each module of the class. (Let’s be honest: you’re probably just going to mark them as complete because you’re that kind of student. I feel you.)
Introductions
We introduce ourselves, you included, then explain the server you’ll be using as your playground this week, and how its storage is configured.
Click on the modules on the right side of the page to keep moving through the class.
- 0.1 Prerequisites Before the Class
- 0.2 How to Set Up Your Own Lab Server
- 1.2 How to Measure Your SQL Server
- 1.3 How to Fix PAGEIOLATCH Waits
- 1.4 Lab 1 Setup: Fixing PAGEIOLATCH Waits
- 1.5 How to Fix CPU Waits (SOS_SCHEDULER_YIELD)
- 1.6 Lab 2 Setup: CPU-Intensive Workload
- 2.1 How to Fix Parallelism Waits (CXPACKET, CXCONSUMER, and LATCH_EX)
- 2.2 Plan Caching and Parameterization
- 2.3 Lab 3 Setup: Mixed Workload
- 2.4 How to Fix Blocking Waits (LCK%)
- 2.5 Lab 4 Setup: Planning the Work
- 3.1 How to Fix Worker Thread Waits (THREADPOOL)
- 3.2 How to Fix Query Memory Waits (RESOURCE_SEMAPHORE)
- 3.3 How to Fix Hardware-Sounding Waits (WRITELOG, HADR_SYNC_COMMIT, ASYNC_NETWORK_IO)
- 3.4 Lab 5 Setup: Architecture Changes
- 3.5 How to Triage Performance Emergencies
- 3.6 Lab 6 Setup: Emergency Triage
- Bonus: Abnormal Parallelism
- Bonus: Storytelling Time