Category: Execution Plans

I love me some documentation.

For years, I've pointed folks to the SQL Server 2008 documentation on Forced Parameterization, a really useful tool for reducing plan cache bloat, getting more accurate reusable query plans, and enabling SQL Server 2019 and 2022's Intelligent Query Plan features, many of which rely on the same query text coming in repeatedly over time in order to tune it. That documentation was pretty sparse, though.

The official Azure SQL Dev's Corner blog recently wrote about how to enable soft deletes in Azure SQL using row-level security, and it's a nice, clean, short tutorial. I like posts like that because the feature is pretty cool and accomplishes a real business goal. It's always tough deciding where to draw the line on how much to include in a blog post, so I forgive them for not including one vital caveat with this feature.

In the Query Plan Pop Quiz, questions 2 and 3 asked you about what the sizes of arrows on query plans meant. The good news is that almost all of you got Question 2 right, but the bad news is that the vast majority of you got Question 3 completely incorrect, and the saddest part of that is that you've been using that inaccurate knowledge to guide your query tuning - and wasting your time.

In last week's Query Plans Pop Quiz, the first question was, someone hands you these two queries and you get their estimated plans to decide which query to tune. Perhaps you get the estimated plans from SSMS, or from sp_BlitzCache, or from your monitoring tool. The question was, which query should you focus on tuning?…

Question 1: Pick the Problematic Plan: someone hands you a pair of queries, and you get the estimated query plans. (Perhaps you get the estimated plans from SSMS, or from sp_BlitzCache, or from your monitoring tool.) Which one of these two should you focus on tuning first, Query 1 or Query 2?

Question 2: on an estimated plan, what does the thickness of the colored arrow represent?

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.

At the PASS Summit this month, I presented a 1-day workshop called T-SQL Tuning for SQL Server 2019 & 2022. I just took that material and refactored it into a few different modules for various classes: Mastering Server Tuning - Why Query Monitoring is Hard Now - because I see so many people making bad…

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.

Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
[crayon-6a6b34116e52e657742957/]
Their actual execution plans are wildly different:

They both use the index to retrieve their data, sure, but:

An interesting question came in on PollGab. DBAmusing asked: If a query takes 5-7s to calculate the execution plan (then executes <500ms) if multiple SPIDS all submit that query (different param values) when there's no plan at start, does each SPID calc the execution plan, one after the other after waiting for the prior SPID…

A reader posted a question for Office Hours: Hi Brent, What is your take on Hugo Kornelis's explanation of execution plan naming. As her his explanation, estimated exec plan is simply an execution plan whereas actual execution plan = execution plan+run-time stats. Do you agree that the naming is flawed and confusing? - Yourbiggestfan I…

How do we get this to show up in an execution plan:

Without having an index seek or index scan operation on the same table?

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.

When you see "index seek" on an execution plan, that doesn't mean SQL Server is jumping to exactly the row you're looking for. It only means that SQL Server is seeking on the first column of the index.

This is especially misleading on indexes where the first column isn't very selective.

In my free How to Think Like the Engine class, we start out by measuring query performance using logical reads. That's the number of 8KB data pages that SQL Server has to read in order to find your query's results. That's the measure I use the most because generally speaking, the less data your server has to read, the faster your query will finish.

When you execute a query, the database server has to figure out things like:

Which table to process first
Which index to use on that table
Whether to seek on that index or scan it
Which table to process next
How to join the data between those two tables
When to sort the data

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…