How SQL Server estimates rows and how estimate errors affect plan choices.
60 associated posts57 primary posts
Want to ask your own questions or pick the ones I discuss? Head over to https://pollgab.com/room/brento to get involved. In the meantime, let's hang out in the backyard, where Beni makes a special guest appearance.
https://youtu.be/aMMBj4owm3A
Nah, not necessarily. SQL Server's query optimizer behavior keeps changing with every freakin' version. Let's illustrate it with a simple query against the Stack Overflow Users table:
[crayon-6a6eb881c5ddb329938773/]
For yeeeeeears, when I've explained execution plans, part of my explanation has included the instructions, "Read the plan from right to left, top to bottom, looking for the place where the estimates vs actuals are suddenly way off." Here's an example:
Things seem to be going okay on the query plan until you hit the key lookup, which brought back 13 rows of an estimated 19,452. That would appear to be a pretty doggone bad estimate.
While waiting for my Husavik hot tub to fill up, I took your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=f0chyzVLFYw
Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they're hard to write, hard to read, and they're even harder to troubleshoot. Once you master 'em, though, they come in handy for very specific situations.
This post isn't about their complexity, though. This post is about Azure SQL DB & SQL Server 2025's regex performance.
About 10 years ago, Microsoft made changes to the Cardinality Estimator (CE) which caused some problems for SQL Server upgrades. When folks upgraded to SQL Server 2014, they also casually switched their databases' compatibility level to the latest version, because for years that hadn't really affected query plans. They just figured they wanted the "latest and greatest" compat level, without regard to the effects. That backfired badly when they suddenly got 2014's Cardinality Estimation changes.
This week's Query Exercise challenged you to figure out why these 3 estimates went so badly:
[crayon-6a6eb881d0218371947513/]
Our prior Query Exercise introduced SQL Server's 201 buckets problem: its inability to accurately estimate rows for more than 201 outliers in a table. I followed up with a solution using filtered statistics to help with the next 200 outliers, and I talked about how that's really overkill for the simple problem we were facing in that initial challenge.
In this week's Query Exercise challenge, I explained SQL Server's 201 buckets problem. SQL Server's statistics only handle up to ~201 outliers, which means that outliers ~202-300 get wildly inaccurate estimates.
In our example, I had an index on Location and perfectly accurate statistics, but even still, this query gets bad estimates because Lithuania is in outliers ~202-300:
When you run a query, SQL Server needs to estimate the number of matching rows it'll find - so that it can decide which indexes to use, whether to go parallel, how much memory to grant, and more.
For example, take any Stack Overflow database, and let's say I have an index on Location, and I want to find the top-ranking users in Lithuania:
Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location:
[crayon-6a6eb881d0d33394853395/]
And make it read less pages only by tuning the query? You weren't allowed to make index or server changes, and you weren't allowed to hard code the location in the query since it might change over time.
The Core of the Problem
The main problem is that when we run a statement (like SELECT), SQL Server:
Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It's even got an index to support it:
[crayon-6a6eb881d1114227621094/]
You can test it with any version of the Stack Overflow database. To test it, we'll turn on a couple of tuning options:
[crayon-6a6eb881d1119694939573/]
The actual execution plan does use our index - not just once, but twice:
It's hard to find good 400-500 level SQL Server training at any price, let alone free. Dave Ballantyne's past sessions from SQLBits are great examples of those.
Today, I'm going to review Dave's most recent session on Estimation and Statistics. It's from 2015, but it's still very relevant today.
This one is a 3-part episode: I take 21 questions from https://pollgab.com/room/brento and then later, work on First Responder Kit pull requests.
https://youtu.be/MNwCDQX8zl4
While in historic and beautiful Camden to hang out with friends, I went to Megunticook Falls and answered your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/QA8jlLfsC0U
After consuming waaaay too much coffee, I went through your top-voted questions from https://pollgab.com/room/brento:
https://youtu.be/3IyG_W7go7k
Y'all post questions at https://pollgab.com/room/brento, and I go through the top-voted ones on my Twitch channel streams.
https://www.youtube.com/watch?v=TpNXUCPzfj4
SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that's the only thing they improved - there's still a critical performance problem with it. Let's take the Stack Overflow database, Users table, put in an index on Location, and then test a…
Got questions for me? Post 'em at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. I filter out the ones that are too short for video answers, and here's the latest batch: Thomas Franz: When I have a very large table with no statistics (or not on a specific column in the…
I wish we got a yellow bang on execution plans when SQL Server was making up an estimate out of thin air.
I'll give you an example: if you compare two columns on the same table, looking to find rows where they're equal, SQL Server has a hard-coded estimate that 10% of the rows will match.
