IRL #4: Instance Tuning

IRL #4 – tuning a server instance in very small increments


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.


  • 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%


  • 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


Query Performance (click to enlarge):


  • 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)


  • 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


  • 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:

Thanks for reading!


Leave a Comment

Sign up for our Newsletter

%d bloggers like this: