Performance Tuning, Getting the Biggest Bang for Your Buck
summary
Checklist of concrete fixes and measured wins: set Windows to High Performance power plan (avoids ~30% CPU loss), remove unused SQL services, apply CUs (e.g., CU8 for 2016, CU5 for 2017) that fixed tempdb metadata contention, set max server memory (example: 28 GB on a 32 GB host) and account for SSIS/SSAS/SSRS, reserve VM memory and watch CPU ready/scheduler yields, put tempdb on fast storage with one tempdb file per core up to eight sized identically and use TF 1117/2453 as needed, enable RCSI, enable IFI, tune MAXDOP/Cost Threshold, add included columns to eliminate key lookups.
AI-written summary · about
chapters (17)
- 0:00Intro
- 1:24Presentation Rules
- 1:53Best Practices
- 2:12Power Plan
- 3:53Services
- 5:38Patching
- 7:34Max Memory
- 12:45TempDB
- 17:02Build Relationships
- 19:32Virtual Environment
- 23:17Compression
- 26:34Isolation
- 28:19Statistics
- 31:20Max Dop and Cost Threshold
- 34:13Instant File Initialization
- 36:24Index Maintenance
- 38:56Execution Plans
related talks
57mYour Performance Tuning Check List
54mYour Performance Tuning Check List
47mArchitecting for High Performance SQL Server on Virtual Machines
01h 00m7 Tips to Performance Tuning, Optimization, and Everything
49mAssessing and Optimize Enterprise SQL Server Environments
57mSQL Server hates you(?) - what the DBAs never told the developers
51mPerformance Tuning Azure SQL Database
49mHidden Gems in SQL Server 2022 Database Engine