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:

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:

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:

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:

“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:

Compile time are there. Spills will soon. Being LWP v3-based there's no I/O, CPU or waits to keep it *very low* overhead at scale. Getting those for highly chatty servers is not doable now. https://t.co/XXtU48JTv8

— Pedro Lopes (@SQLPedro) April 7, 2019

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.)

Hey, while you’re here – I built a whole series of classes around parameter sniffing. It looks so easy, but it gets so hard – and backfires so hard. The first class: Fundamentals of Parameter Sniffing. Conquer that, and you can move on to Mastering Parameter Sniffing.