IRL #4 – tuning a server instance in very small increments
Problem:
Main production server is behaving “sluggishly” and additional load makes it worse. Additionally, deadlocks are a problem, at 880 per day on average. Client wants to implement changes in small, tightly controlled steps. Dev, QA, then Prod.
Background:
- SQL Server 2012 (RTM) Enterprise
- 8 cores, 58 GB RAM (32 allocated to SQL Server)
- 1 TempDB file (default setting)
- Cost Threshold for Parallelism – 5 (default setting)
- CXPACKET waits – 57.2%
- LATCH_EX waits – 37.0%
Investigation:
- Multiple queries that run thousands of times per hour or day have a cost at or near 5. Many in the 15-20 range.
- Write stalls on tempdb data file avg 87ms, resides on same drive as primary database. 4.3M writes/day avg.
Recommended Steps:
- Upgrade to current SP and CU
- Move tempdb to dedicated drive
- Increase Cost Threshold for Parallelism to 25
Results:
Query Performance (click to enlarge):
TempDB:
- Before: avg Read stalls 2ms, avg Write Stalls 87ms
- After: avg Read stalls 1ms, avg Write Stalls 275ms
- Investigating drive config (HDD/SSD, RAID level)
Other:
- Deadlocks dropped from 880/day to 380/day – 56.8% improvement without touching any code
- CXPACKET waits dropped to 45.5% – 20% improvement
- LATCH_EX waits dropped to 24.1% – 35% improvement
Conclusions:
- Our methodology of working from Instance to Database to Index to Query continues to prove successful.
- Verify all drive info before moving files around (tempdb, log files, etc.)
Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/
Thanks for reading!
