A Little About Out Of Date Statistics In SQL Server
In this video, I delve into an often-overlooked issue in SQL Server: outdated statistics and their impact on cardinality estimation. Specifically, I explore how the legacy and default cardinality estimators can lead to significant performance issues when statistics histograms don't accurately reflect the current state of your data. Using a practical example with the Stack Overflow database, I demonstrate how adding new rows that aren't reflected in existing statistics can cause SQL Server to make wildly inaccurate guesses about query execution plans. By comparing results from both cardinality estimators and manipulating statistics properties, I show you exactly why this is problematic and offer insights on how to mitigate these issues through more frequent statistics updates or strategic use of hints. Whether you're a seasoned DBA or just starting out, understanding the nuances of cardinality estimation can greatly improve your query performance tuning efforts.
CHAPTERS
00:00:00 - Introduction
00:00:47 - Legacy Cardinality Estimator
00:02:15 - Outdated Statistics Issues
00:03:56 - Ascending Key Problem
00:04:41 - Performance Impact Example
00:05:34 - Table Setup and Modifications
00:08:43 - Cardinality Estimator Comparison
00:12:03 - Density Vector Estimate
00:16:17 - Conclusion
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━