Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans

My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them – kinda – with adaptive memory grants.

However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query’s memory grant is based on the last set of parameters used, not the current set.

So let’s imagine that you just got an emergency phone call: the boss says their query was slow just now, and they wanna know why.

In theory, you’ll use the new sys.dm_exec_query_plan_stats.

Microsoft’s Pedro Lopes unveiled this feature last week, and in theory, it sounds pretty easy:

  1. Enable trace flag 2451
  2. Get the query’s plan handle
  3. Get the “actual” plan by running SELECT query_plan FROM sys.dm_exec_query_plan_stats (your_plan_handle)

Let’s start our adventure by running usp_UsersByReputation for @Reputation = 2, which gets the tiny memory grant. Here’s the real actual plan from running the query:

Real actual plan for @Reputation = 1

The real actual plan is full of rich details: the degree of parallelism, how long the plan compilation took, and something very important for this particular issue, the amount of memory grants. In this case, the query desires 11.7MB.

Normally, all those details will be lost in time, like tears in rain. Enter sys.dm_exec_query_plan_stats – we’ll use sp_BlitzCache to show the plan handle, and then pass that to the new DMF to get the most recent “actual” plan:

And at first, things look promising – click on the query_plan, and you get:

“Actual” plan from sys.dm_exec_query_plan_stats for @Reputation = 2

IT’S AN ACTUAL PLAN! FOR A QUERY THAT RAN IN THE PAST! You can tell it’s an actual plan because the numbers below each operator are the actual numbers of rows that returned from each operator. This is a huge step forward because it enables you to see where the query plan’s estimates went wrong: where it thought that only a few rows would come back, but in reality, way more did. But before you pour the champagne, dig a little bit deeper.

But…there’s a whole lot missing from the details.

And one of the most important things there, desired memory, is completely wrong.

The memory grant numbers don’t show what the query executed with – they show the desired memory grant for the query’s next execution! To see it in a painful way, run it again, this time for @Reputation = 1, and here’s the real actual plan:

Real actual plan for @Reputation = 1

Again – rich details, especially for the sort. That yellow bang warning on the sort is absolutely priceless, and when you hover your mouse over it, you get a tooltip showing how many pages spilled to disk.

And a whole lot of them did – because the desired memory for this query is just 1.5 MB! Ring a bell? That’s the desired memory from the supposed last “actual” plan, which wasn’t actual at all. So let’s go query sys.dm_exec_query_plan_stats and see what this query supposedly had for an “actual” plan:

“Actual” plan from sys.dm_exec_query_plan_stats for @Reputation = 1

“It desired 209MB.” THE HELL IT DID. That’s the grant for the next time this query runs!

Henceforth, I shall refer to these plans as air_quote_actual plans.

In practice…well, it’s only CTP 2.4, but…

To recap what I’ve shown so far this week:

  • Adaptive memory grants mean your query’s memory is based on the prior execution’s parameters
  • The air_quote_actual grants in sys.dm_exec_query_plan_stats are based on the query’s next execution

I can see why this is hidden behind a trace flag, and isn’t practical for mainstream distribution. If this shipped to the public as actual plans (no airquotes), it would hurt more than it would help. They don’t include:

  • A lot of details about memory grants (granted, wait time, used, etc) – and what it does show is wrong
  • Degree of parallelism
  • IO statistics (reads)
  • Wait stats
  • TempDB spills – although Pedro reports those are coming soon:

That means the air_quote_actual plans really just serve as clues that will require a trained performance tuner detective to solve. Just like you’ve seen for years in Watch Brent Tune Queries, your mission is to start at the top right of the query plan, review the estimated versus actual numbers to see where they went awry, and then change the query or the supporting indexes to guide the optimizer down a better path. Air_quote_actual plans serve as a tool in that investigation, but you have to know which parts are true – and which parts are lies.

<sigh>

I do like it – just like I like adaptive memory grants – but it’s another sign that this is going to be a 2014-style release. More on that in the next post when we explore another new SQL Server 2019 feature to mitigate the perils of parameter sniffing: adaptive joins. This one really does work as designed, eliminating parameter sniffing issues while building a resilient plan that works for all kinds of parameters.

(What? That was too obvious? You really are sharp. You’ve always been my favorite reader, you. We really get each other.)

Previous Post
Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants
Next Post
Parameter Sniffing in SQL Server 2019: Adaptive Joins

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}