SQL Server hates you(?) - what the DBAs never told the developers

on YouTube ↗57m

" summary

Defaults kill performance: SQL Server standard edition limits the buffer cache to 128GB (64GB on 2012) so always set max server memory to leave RAM for the OS; parallelism defaults (maxdop = 0, cost threshold = 5) lead to massive CXPACKET and parallelism skew — use cost threshold ~50 and maxdop 1/2/4 and aim for ~20–30% parallel plans. Set-based queries beat row loops (example: 100k rows, 1s vs 126s). Avoid scalar or multi-statement TVFs, beware parameter sniffing, and prefer RCSI with fast tempdb storage.

AI-written summary · about

" chapters (13)

1. 0:00Start
2. 4:09Memory
3. 10:41What Is Parallelism
4. 17:02Max Dop
5. 31:29User Defined Functions
6. 37:24Function Based Index
7. 41:23Read the Manual
8. 41:40Summary
9. 44:51Parameter Sniffing
10. 48:06Locking Strategies
11. 49:26Optimistic Locking
12. 52:37Dirty Reads
13. 53:55Row Versioning in Tempdb

" related talks

• 43mPerformance Tuning, Getting the Biggest Bang for Your BuckMonica Rathbun · SQL Server Virtual Conference 2021
• 57mYour Performance Tuning Check ListMonica Rathbun · Women Data Summit 2021
• 47mArchitecting for High Performance SQL Server on Virtual MachinesAnthony Nocentino · SQLBits 2022
• 47mArchitecting for High Performance SQL Server on Virtual MachinesAnthony Nocentino · SQLBits 2019
• 49mSQL Server on Azure VM - Configuring for Price-PerformanceDavid Pless · SQLBits 2024
• 50mSQL Server 2022 Performance EnhancementsOla Hallengren · SQLBits 2022
• 51mMaking the SQL Query Processor Work for youJoe Sack, Ajay Jagannathan, Derek Wilson · SQLBits 2024
• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2019