Statistics: SQL Server’s Guessing Game

Once you’ve mastered How to Think Like the Engine, the next level up is to understand how statistics influence your execution plan. Here’s what we’re going to cover in this class:

A Tale of Two Plans (7m) – We look at two execution plans for the same query, talk about why they’re different, and figure out which one makes the most sense.

Getting to Know Statistics (15m) – We pop open SSMS to look at what statistics drove an execution plan’s estimates. First, we use a query to find which statistics focus on which columns, and then bust out DBCC SHOW_STATISTICS to understand statistics contents.

Combining Multiple Single-Column Statistics (8m) – In the last module, we looked at statistics independently. Now, what happens when our query references multiple columns, and we have to combine multiple statistics to guess how many rows will return. You’ll also see how both the old (pre-2014) and new cardinality estimators shape up.

What is the Cardinality Estimator? (5m) – The CE’s job is to use statistics to determine how many rows will come back for any given operation in a query plan. SQL Server’s Cardinality Estimator changed a lot in 2014, and you need to understand which CE you’re using before you analyze your statistics.

Multi-Column Statistics and the Cardinality Estimators (14m) – In the real world, your indexes often cover multiple columns. We’ll see how the density vector has multiple rows now, and see how the pre-2014 and 2014+ cardinality estimator have huge differences.

Filtered Statistics: ENHANCE! (15m) – Filtered statistics help you get ENHANCED statistical coverage of small parts of your table.

The Ascending Key Problem (11m) – In data warehouse scenarios where you constantly load in new data every night, and then go query the data you just loaded, you often get really bad execution plans.

10 Rules for Managing Statistics (12m) – Now that you understand how SQL Server uses statistics, we need to cover how to manage your statistics.

Scans, Seeks, and Sargability (9m) – SQL Server uses statistics for a lot of things, but one of the most valuable is determining whether it should seek or scan a particular index or scan. The stats aren’t always useful, though, especially if your T-SQL isn’t sargable.

How Execution Plans are Made (13m) – Parse, Bind, and Optimize: SQL Server goes through a few steps, and then goes through that last one repeatedly, honing the plan until it finds a good-enough version.

When you’ve finished this course, start working through Fundamentals of Query Tuning.