In the blocking, locking, and deadlocking modules in Mastering Index Tuning and Mastering Query Tuning, I covered how to fix those problems with index & query changes. Another solution is to make database-level changes like the default isolation level or offloading reads to another server with techniques like log shipping, replication, or Availability Groups.
2.4 How to Fix Blocking Waits (LCK%)
In the blocking, locking, and deadlocking modules in Mastering Index Tuning and Mastering Query Tuning, I covered how to fix those problems with index & query changes. Another solution is to make database-level changes like the default isolation level or offloading reads to another server with techniques like log shipping, replication, or Availability Groups.
To access this incredible, amazing content, you gotta get Mastering Server Tuning with Wait Stats or Recorded Class Season Pass, or log in if you already shelled out the cash.
- 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.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