How SQL Server estimates rows and how estimate errors affect plan choices.
60 associated posts57 primary posts
Got questions about SQL Server? Post them at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. This episode was the first live one I shot in my home studio in Cabo:
https://www.youtube.com/watch?v=BfQqQW1PCoM
If you've got good indexes to support your query, and statistics to help SQL Server guess how many rows will come back, how can SQL Server still come up with terribly incorrect row estimates? To demonstrate, I'll use the 2018-06 version of the Stack Overflow database, but any recent version will work as long as…
When SQL Server is purely making an estimate up out of thin air, with no freakin' idea what the real answer is, it doesn't give you any kind of warning. It just produces an estimate that looks scientifically accurate - until you know how it's actually getting calculated. Let's ask SQL Server how many users have…
SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won't get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will nosedive.
When I'm reviewing plans with folks, I get this response a lot:
But SQL Server should know there's no data that matches! It has statistics!
When you run a query, how does SQL Server estimate the number of rows? I'll explain in this half-hour video demo using playing cards, then showing the same issues in SQL Server Management Studio:
https://youtu.be/uqkf1VVZulg
Date tables help users filter data based on day of week, holiday, this year vs last year, and other kinds of filters that business users usually wanna add to their reports. If you haven't used a date table before, here are a couple of primers on how to create one and use it:
Creating a date dimension table by Aaron Bertrand
How to create a date_calendar table by Sean Smith
In the last post, I talked about how we don't get accurate estimates because SQL Server's statistics only have up to 201 buckets in the histogram. It didn't matter much in that post, though, because we were using the small StackOverflow2010 database. But what happens as our data grows? Let's move to a newer Stack…
I'll start with the smallest Stack Overflow 2010 database and set up an index on Location: [crayon-6a6ece74267a9063275346/] There are about 300,000 Users - not a lot, but enough that it will start to give SQL Server some estimation problems: When you create an index, SQL Server automatically creates a statistic with the same name. A…
When you're doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server's estimates. Ideally, you want your delete/update/insert (what - what'd you think I meant?) queries to be as simple as possible - even a simple join can cause SQL Server to do wildly, wildly incorrect estimates, which affects memory grants, parallelism, wide vs narrow plans, and more.
In my last post, we started with a memberships table, and each membership had start & end dates. I'm going to create the table and populate it with everyone having an active membership - their StartDate is the same as their Stack Overflow account creation date, and their EndDate is around a year or two…
Say that you've got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table:
And let's say you wanna find the users who last accessed the site on September 9, 2018 - regardless of the time. Here are a few ways to do it:
[crayon-6a6ece7427aea919744829/]
Here are their actual execution plans. I'm using SQL Server 2019, with 2019 compatibility level, and the 50GB StackOverflow2013 database.
In our last episode, we saw how SQL Server estimates row count using statistics. Let's write two slightly different versions of our query - this time, only looking for a single day's worth of users - and see how its estimations go: [crayon-6a6ece7428516980099053/] Both of those queries are theoretically identical in that they accomplish the…
In our last episode, SQL Server was picking between index seeks and table scans, dancing along the tipping point to figure out which one would be more efficient for a query.
One of my favorite things about SQL Server is the sheer number of things it has to consider when building a query plan. It has to think about:
I get this question a lot, so let's set up an example with the Stack Overflow database. My query's goal is to:
Find the top 5 Locations
List the users who live in those top 5 Locations, alphabetized by their DisplayName
A couple years back, I realized there were at least Ten Ways to Set MAXDOP. These days, as I write and tune queries, I realize there are an awful lot of ways to change the Cardinality Estimator (CE) and Query Optimizer, too. Let's take a quick survey of five intriguing options. 1. Change your SQL…
Say I've got a function in my WHERE clause:
[crayon-6a6ece7429916783046592/]
If I have an index on DisplayName, will SQL Server use it? Sure:
Even though SQL Server can't seek to "Brent Ozar," it will still scan that entire index, along the way trimming the leading & trailing spaces from every user's DisplayName.
For over a decade, SQL Server's handling of table variables has been legendarily bad. I've long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation:
[crayon-6a6ece7429d22147374221/]
It puts a bunch of data into a table variable, and then queries that same table variable. On the small StackOverflow2010 database, it takes almost a full minute, and does almost a million logical reads. Here's the plan:
Get Yourself Together
This is one of the most common patterns that I see in stored procedures. I'm going to simplify things a bit, but hopefully you'll get enough to identify it when you're looking at your own code.
Here's the stored procedure:
[crayon-6a6ece742a336268135186/]
There's a lot of perceived cleverness in here.
For years, when you right-clicked on a database and click Properties, the "Compatibility Level" dropdown was like that light switch in the hallway: You would flip it back and forth, and you didn't really understand what it was doing. Lights didn't go on and off. So after flipping it back and forth a few times,…
