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 start the first lab:
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 Download the Slides and Scripts
- 1.1 How to Measure Your SQL Server
- 1.2 How to Fix PAGEIOLATCH Waits
- 1.3 Lab 1: Fixing PAGEIOLATCH Waits
- 1.4 How to Fix CPU Waits (SOS_SCHEDULER_YIELD)
- 1.5 Lab 2: 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: 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