Posted on

Find the Scalar Function Call: What it Means If It’s Hiding in Probe Residual

User defined functions are fairly simple to create in SQL Server, but figuring out exactly how they are being used can take a little time – even in a simple execution plan. This is because the functions can be tucked away into lots of different operators in the plan, even join operators.

The good news: once you learn what to look for, it’s not terribly complicated. There are some fancy terms involved, but the definitions are pretty simple.

Here’s a quick look at a function call hidden in a hash match operator, which is being used to implement an inner join.

The estimated query execution plan

This query uses the Microsoft WideWorldImporters sample database. The query calls the built in scalar user defined function, Website.CalculateCustomerPrice:

SELECT
    il.InvoiceLineID
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS il
    ON i.InvoiceID=il.InvoiceID
WHERE 
    [Website].[CalculateCustomerPrice] ( i.CustomerID, il.StockItemID, i. InvoiceDate ) 
        <>
     il.UnitPrice;
GO

Here’s the query execution plan:

There’s not a lot of places for the function call to hide in this particular plan, right? No Compute Scalars to look into at all.

If we look at the properties of the Hash Match join, we can find the function call:

How does the hash join work?

The description at the top of this tool tip is quite helpful when it comes to remembering how hash matches work: SQL Server is building a table in memory from the Sales.Invoices table. It’s then “probing” into it with items from the Sales.InvoicesLines table.

The “Hash Keys Probe” part of the tool tip tells us that it is looking for matches based on the InvoiceID column (our join column).

What about the ‘Probe Residual’?

After we find matches based on the customer id, we have more work “left over” — that’s the “residual” bit.

For every row that matches, SQL Server is plugging values into the Website.CalculateCustomerPrice() function and comparing the result to the Unit price column, just like we asked for in the where clause.

In other words, this is happening for every row in Sales.InvoiceLines that has a matching row in Sales.Invoices.

Which is every single invoice & invoice line, as it turns out.

Bonus

If you’re ever looking for an example of a query where collecting an actual execution plan dramatically slows down execution (observe effect), this is a good one. On instances I’ve tested, this query takes 14-18 seconds to run when no plan is collected, and about a minute longer when you enable actual plan collection.

Takeaways

When you’re looking at execution plans, it can be very tricky to spot user defined functions in the plan:

  • You need to not only check into Compute Scalar operators if they are around, but also look carefully at the properties of other operators, including joins
  • If you spot the function call as part of a “probe residual” on a hash join, it is being applied to every row that meets the initial join criteria
Posted on

A Case of Inconsistent Wait Stats and Query Duration

You know how they say “don’t sweat the small stuff”?

That’s good advice for lots of things, but when it comes to query tuning – I WANT TO KNOW MORE when something is inconsistent. I just can’t let it go. I go total X-Files: The Truth Is Out There.

The bright side of this habit is that it makes finding blog topics fairly easy.

I was measuring query performance at different degrees of parallelism

I set up a script to measure duration and wait statistics for a few queries. It followed this flow:

  • Set a variable with the “starter” degree of parallelism (DOP)
  • Begin a loop
    • Clean up anything from the last round
    • Record start time and snapshot of waits for the session from sys.dm_exec_session_wait_stats
    • Run the test query at given DOP (using Dynamic SQL)
    • Record end time and snapshot of waits for the session from sys.dm_exec_session_wait_stats
    • Decrement the DOP

I was gathering the data to graph and show query durations along with the amount of CXPACKET and CXCONSUMER waits at different DOPs.

The first weird thing: inconsistent wait stats

Whenever I can, I run a test more than once. I was graphing my wait statistics, and I noticed that on different executions of the procedure, my parallelism waits varied.

Varied more than a small amount.

Luckily for me, I read the release notes for Cumulative Updates for SQL Server most of the time, and after noticing the variation I remembered FIX: CXPACKET and CXCONSUMER wait types show inconsistent results for some parallel query plans in SQL Server 2017.

I checked, and sure enough, my little cloud VM was on SQL Server 2017 CU3, and that fix is in CU4.

I patched to CU5 (latest and greatest for my little test instance, ya know), redid all my testing twice, and admired my now-more-consistent charts.

The second weird thing: inconsistent query duration

I was getting consistent results for query duration when I ran the query in the script above, which started at a high degree of parallelism, and then went to low.

But I found that for one of my test queries, when I took the query and ran it in my own session to look at Query Time Stats in the execution plan, it took much longer.

For example, at MAXDOP 13:

  • Duration was consistently 55-65 seconds in the looping script
  • Running it in a session by itself, I was seeing durations of 2 minutes to 2 minutes 30 seconds

More than twice as long.

Why was it so much slower when I ran it in another session?

It wasn’t about cache/buffer pool reuse

I had plenty of memory on this instance, and I’d made sure to set ‘max server memory (mb)’ plenty high, but could something be causing me to do physical reads when I ran the query in my own session?

This made me realize that my test script wasn’t quite fair to the DOP that ran first — it might have to do physical reads where following queries made use of data in memory. This is a private test instance, so I changed the script to run everything with cold cache, by dropping clean buffers between runs.

I wasn’t getting a different query execution plan

I was getting the same plan with the same cost both in my “slow session” and in the script.

It wasn’t ASYNC_NETWORK_IO_WAITS

I was running everything from an SSMS instance on my laptop against a SQL Server on a VM in the Azure Cloud. Could I be getting some weird kind of latency on one session?

Nope. And my query wasn’t even returning a result set to my SSMS window — it was putting the results into a table in the cloud.

Was it the Dynamic SQL?

Could something about the Dynamic SQL be making it faster? Or had I made some sort of typo and the Dynamic SQL query was a little bit different in a way that made it faster?

Nope, it really was the same query, and the same plan. When I copied the longer script and adjusted the variables to only run for DOP 13 in my “slow” session, it was also slow.

Was it using STATISTICS TIME or STATISTICS IO?

I sometimes use these to measure queries, but in this case I wasn’t using them in either session.

Was it having Actual Execution Plans on?

Yes, it was.

This query was a reminder of the observer effect: watching something can change its behavior. In this case, “watching” the query by enabling actual execution plans made it take twice the duration.

I had actual plans enabled in my “slow” session, and I didn’t have them on in the session where I was measuring duration and wait statistics in the loop. (If you’ve ever accidentally left actual plans enabled when you’re running TSQL in a loop, you know how much SSMS doesn’t love that.)

I tested this a few different ways, including in another instance of SSMS local to the VM itself, and sure enough – actual plan drags this puppy down, although it’s a relatively simple query.

Was it only graphical plans?

I disabled graphical execution plans in Management Studio, then tested a few varieties of outputting actual plan info:

  • SET STATISTICS XML ON – Actual plan information output as XML in a column named “Microsoft SQL Server 2005 XML Showplan”
  • SET STATISTICS PROFILE ON – Actual plan information output as text across a variety of columns (screenshot below)

I tested these independently. Both of these had the same impact on the query as using the graphic execution plan – the query took twice as long.

I did feel kind of fancy looking at actual plan info in text format, though. We all know that hackers ALWAYS use text interfaces.

Kinda cool how execution count pops out in this view

What about ‘Live Query Statistics’?

I expected this to take a full three minutes, but it only took a bit longer than the ‘Actual Plan’ run in this case: 2 minutes 5 seconds.

Does this happen against different instances? At different DOPs?

I ran the same query against the same data on a local test VM on my MacBook Pro, which has 4 cores.

The duration at MAXDOP 4 on the MacBook Pro:

  • No actual execution plans = 2:41
  • Actual execution plans enabled = 2:50

Hmm, the discrepancy is much smaller on the small instance.

So I retested at MAXDOP 4 on the cloud VM. Note that this gets a different plan – similar shapes, but different costs (much more memory on that instance).

  • No actual execution plans =  2:20
  • Actual execution plans enabled = 3:07

To recap what I mentioned above, MAXDOP 13 on the cloud VM:

  • No actual execution plans =  0:55
  • Actual execution plans enabled = 2:00

Just for fun, MAXDOP 16 on the cloud VM (just in case you thought the issue was 13 being unlucky)

  • No actual execution plans =  1:00
  • Actual execution plans enabled = 2:14

Did I mention I’m compulsive about investigating this? MAXDOP 20 on the cloud VM (all its cores):

  • No actual execution plans =  1:12
  • Actual execution plans enabled = 2:10

Innnnnteresting. The impact of having actual plans enabled for this query is more pronounced on the cloud VM. The skew is greater at a higher DOP, although it’s not linear.

Actual plans are awesome, but make sure they don’t confuse your tuning process by secretly skewing your execution times!

There’s always overhead to using an actual plan.

Frequently it’s minimal, but sometimes it’s really significant – and the amount it varies may differ on different hardware, and at different degrees of parallelism.

Posted on

Adaptive Queries in Standard Edition: Interleaved Exec for Multi-Statement TVFs

It’s tough to keep track of which features work in each version of SQL Server, and which Editions support them.

My memory told me that the new Adaptive Joins feature in SQL Server 2017 was Enterprise Edition only…  and that’s correct, but I didn’t realize that the fancy new feature to make Multi-Statement TVFs smarter has much wider licensing.

Interleaved Execution for Multi-Statement Table Valued Functions works in Enterprise, Standard, Web, and Express Editions

I discovered this by accident when I was doing some testing on my Standard Edition instance. I was looking at an execution plan, and I checked the properties of a TVF and …

Function Beaver couldn’t be more excited about this

First, I double-checked and made sure I really was connected to my Standard Edition instance. YEP.

Then I checked the SQL Server 2017 Editions and Components chart to see if this was an accident or not

Sure enough, ‘Batch Mode Adaptive Joins’ and ‘Batch Mode Memory Grant Feedback’ are Enterprise Edition features.

But ‘Interleaved Execution for Multi-Statement Table Valued Functions’ is available in all the editions.

What does ‘Interleaved Execution for Multi-Statement TVFs’ do for you?

Multi-statement table valued functions are difficult to optimize as part of a query: they run multiple statements, so how the heck do you estimate how many rows are going to come out of that?

This new feature lets the optimizer stop and take a peek!

When the optimizer finds a multi-statement TVF that qualifies*, it pauses the optimization process (badass!), and then goes briefly into the execution phase.

(This is the interleaving part.) It can run through that little part of the plan to get a good estimate, instead of making a blind guess.

Good estimates means a much better chance that you’ll get the right joins, memory grant, and qualify for parallelism when needed.

That usually adds up to faster query execution.

*Which Multi-Statement TVFs qualify?

Not all multi-statement TVFs are created equal. If your TVF is part of a data modification, or if your TVF is inside a CROSS APPLY, it doesn’t qualify for this magic — at least not in SQL Server 2017. That may change in the future, this is just the V1.

How do I turn this on?

If you’re on SQL Server 2017, your database compatibility level controls whether or not this feature is available.

If your compat mode is 140, your multi-statement table valued functions are very likely to speed up.

I say ‘very likely’ because there can be some edge cases where better estimates lead to worse performance.

Want to learn more?

If you agree with Function Beaver that this is the best thing since CREATE FUNCTION…

Posted on

Auto-Tuning: Automatic Plan Correction is Cleared on Restart

The most delicious plans are mauve, but teal is a close second

The new Enterprise Automatic Tuning feature in SQL Server 2017 may sound intimidating at first — one question I get a lot lately is whether or not there’s a future for DBAs. Will Auto-Tune mean we don’t need any more human tuning?

Well, not anytime super soon.

I’m a big fan of the Automatic Plan Correction feature in SQL Server 2017, but it reminds me a bit of the missing index DMVs we got in SQL Server 2005: the suggestions are a fantastic indication of where you need to look and use your powerful human brain.

Automatic plan correction is temporary — and that’s a good thing

One of the things I love about this feature is that it wisely includes some caution.

If you’ve enabled automatic tuning and it finds what looks like a plan regression, it won’t just force a plan and forget about it.

Let’s say we have a query where the average CPU time with Plan A is 2 seconds, and the average CPU time for Plan B is 10 seconds. If Automatic Plan Correction kicks in, we go into a state where Plan A is being verified.

Trust, but verify

Is Plan A really always faster? It may be that after Plan A is being ever so gently forced (ok, that’s not gentle), the query is run with different parameters, that make Plan A skew horribly wrong, and our average CPU time goes to 12 seconds. SQL Server is looking for that.

Un-force when things change

Automatic Plan Correction is also pretty skittish when it comes to change.

Change indexes on the table? Whoops, we better un-force everything in case there’s something that could be faster! Update statistics on the table? Same thing!

And also…

Automatic Plan Corrections don’t persist over restarts

I did a little demo of this, just to prove it to myself.

I have an Automatic Plan Correction in verification state. Here’s a view of it in sys.dm_db_tuning_recommendations:

But then, I restart my SQL Server instance:

Don’t tease me about instance stacking on my test machine! I keep the Standard Edition instance off most of the time 😀

Once the instance comes back up, Automatic Plan correction is no longer in place.

Here’s a different view, after restart. Looking at Query Id 25 in the Queries with High Variation report, there are NO check boxes in those bubbles to the right. Nothing is being forced.

Everything is also gone from view in the sys.dm_db_tuning_recommendations. That data isn’t persisted after restarts.

The good news: we don’t completely start from scratch

My Query Store still has data about past performance of the plan.

After restart, if my query happens to compile with the “slow plan”, the fast plan can be identified from the history capture in Query Store before the restart.

In other words, Automatic Plan Correction doesn’t only consider information since the last restart when making its suggestions. I tested, and just by repeatedly running the “slow plan” for Query Id 25, a recommendation popped up. Since I have automatic tuning enabled for this database, it was automatically applied.

Check boxes indicate that plan forcing is currently active

Think of Automatic Tuning as an assistant DBA

And it’s better than just a random assistant! It’s an assistant that can help point you in the direction of problems like bad parameter sniffing, which are really hard to track down.

If your environment is so sensitive that you don’t trust your assistant to just go tuning things, that’s fine! You don’t have to enable Automatic Tuning, and you can still look at the suggestions manually (as long as you’ve got Enterprise Edition).

But at this point, and for the foreseeable future, your assistant still needs you to dig into the code, indexes, and architecture to find a better long term solution than just freezing the plan. Because if just freezing the plan was enough, this feature would be a whole lot simpler.

Posted on

Why Is My Function Missing From sys.dm_exec_query_stats and Query Store?

It’s time to write a blog post, so I can save time when I go searching for this in a couple years!

Functions can be tricksy in SQL Server

It can be difficult to see functions in execution plans, and to figure out when they’re eating a lot of CPU time on your SQL Server.

One way that’s handy to see if functions are being executed frequently is to check out the sys.dm_exec_query_stats Dynamic Management View and look at the execution count, CPU time, and logical reads of the ‘CREATE FUNCTION’ statement for a given function, to estimate its impact.

But recently, I was working on a project and…

I couldn’t find my functions in sys.dm_exec_query_stats! I also couldn’t find them in Query Store.

Or, rather, I couldn’t find SOME of my functions

Some of my functions in the demo code were showing up just fine. I was really puzzled by that. I thought …

Maybe this is a bug with ‘CREATE OR ALTER’? A sign of some weird memory pressure? Something introduced in SQL Server 2017? A buggy side effect of implicit conversions in some of the functions? A problem with the queries I was using? A weird setting on the database? (Also: about 100 other things that didn’t turn out to be the case.)

I finally wrote up some simple demo code, tested it against a SQL Server 2008 R2 instance (omitting the Query Store components), compared it with SQL Server 2017, and found it to be consistent.

Some functions just don’t accrue information in sys.dm_exec_query_stats, and that’s been true for quite a while.

I tested six types of functions:

  • Scalar functions that do, and do not do data access
  • Multi-statement table valued functions that do, and do not do data access
  • Single statement table valued functions that do, and do not do data access

Which of these functions appear in sys.dm_exec_query_stats and sys.query_store_query?

Philosoraptor, reinvented

To be clear, the queries run which call the functions all appear in sys.dm_exec_query_stats and sys.query_store_query. There are no missing calling queries.

However, the calling query only shows things like how many times it was executed. It doesn’t show how many times the function was executed (the function may be executed many times per query). Sometimes that’s handy to see when you’re troubleshooting.

CREATE FUNCTION entries with per-function-execution data only appeared for my:

  • Scalar function that does data access
  • Multi-statement table valued function that does data access
  • Multi-statement table valued function that does NOT do data access

It makes sense to me that my single statement table valued functions are missing from the list, because those functions can be “inlined” into their outer queries. They don’t really run on their own.

However, it is a bit confusing that the scalar function that does not do data access is missing from the list. (I don’t think it’s a huge tragedy, but it makes it look like you’re missing something — hence me spending a while on this!)

Which of these functions appear in sys.dm_exec_function_stats?

We got sys.dm_exec_function_stats in SQL Server 2016, so you can’t use this on Ye Older SQL Instances. This view recorded information for my:

  • Scalar function that does data access
  • Scalar function that does NOT do data access

Why no table valued functions? This one is documented! The DMV simply doesn’t have information about Table Valued Functions.

What does this all mean?

If you have a lot of scalar functions that don’t do data access, and you’re on SQL Server 2016 and higher, check out sys.dm_exec_function_stats  for aggregate information on how they are running.

And if you’re just confused about why not all your functions are showing up in sys.dm_exec_query_stats or Query Store, hopefully this post saves you some time!

Posted on

What is a “Morally Equivalent Execution Plan,” and Why Is It Good?

I recently wrote a fairly complicated post, called “Forced Plan Confusion: Is_Forced vs Use Plan = True.”

You do NOT need to go read that post to understand this one. I think I’ve found simpler way to explain the most important part of that post – and why it’s A Very Good Thing.

Plan forcing in Query Store is clever

The gist of this post is that plan forcing in Query Store is not the same as just putting duct-tape on an execution plan.

Query Store is a little more flexible than that. It has an understanding that if you force something too tightly, it may break.

It leaves a little wiggle room. SQL Server may compile a “Morally Equivalent Plan” if a query plan has been forced, but the optimizer looks at the plan and the “compile for” values and sees that the query plan is the wrong size pants for that query.

Step 1: I put a plan in Query Store

To get this party started, I restore the WideWorldImporters sample database and enable Query Store. I run a query and get its plan into cache.

The plan looks like this — notice that @packagetypeid is parameterized…

This plan has an estimated cost of .0067516. It thinks one row is going to flow through the whole query.

Step 2: I force that plan

I run a query against the Query Store DMVs to figure out the query_id and plan_id for this query.

Then I force it by running:

exec sp_query_store_force_plan @query_id=1, @plan_id=1;
GO

That one-row plan you see above is now forced!

Step 3: I cause a recompile to happen

The forced plan is currently in my execution plan cache. I want to cause a recompile to happen, so I clear out the cache for this database on my test instance:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

The next time my query runs, it’s going to get a fresh compile. Yes, it has a forced plan, but let’s see what happens.

Step 4: I run the same query, but with a different value for @packagetypeid

When SQL Server compiles my query, it doesn’t blindly give me the exact forced plan with its one row estimates.

Instead, it sniffs the value of my parameters. It uses that sniffed value to look at the statistics. And it sees that I’m pretty likely to have more than one row flowing through this plan. Based on the statistics, it looks more like 221,241 rows!

So, clever SQL Server gives me a “morally equivalent plan”.

Meet the “morally equivalent plan”

Notice that this plan has the same shape as the plan above. That’s the “moral equivalency.” We’ve got our Seek -> Nested Loops -> Key Lookups. Compute Scalar feeds into Stream Aggregate, etc.

But there are some differences!

  • We have thicker row estimate bars, based on that statistic that was sniffed
  • My plan estimated subtree cost is 51.0062 (the forced plan is .0067516)
  • My plan has a green hint that points out my indexing on OrderLines isn’t so great — and my costs are different on my operators!
    • The Key Lookup is now estimated at 97% of the work, whereas before it was 50%
    • SQL Server isn’t thrilled about doing that lookup 221,241 times. It was no big deal to just do it once, so there was no missing index request when this was optimized for a different parameter.

The “morally equivalent plan” has its own query_plan_id, query_plan_hash, and shows up as separate in Query Store

Both the forced plan and the “morally equivalent” plan are visible in Query Store. It can be a little confusing to identify them if you haven’t seen them before.

  • The forced plan has is_forced = 1 in sys.query_store_plan and will have a check mark in the Query Store reports.
  • The “morally equivalent plan” will have “Use Plan=true” in the properties of the left-most operator in the plan.

The good news: if you open the “Queries with forced plans” report, both of them show up there.

Same shape, sized to fit the parameters it compiled with

I think this “wiggle room” is a terrific feature for forced plans.

Forcing plans is a somewhat crude thing to do — just because a forced plan works better today, doesn’t mean that the forced plan is going to be great tomorrow, if a bunch of my data changes. Or if a different customer is running the procedure, one who has quite a different data profile.

This feature is kind of like a stretchy waistband for the sweatpants of an execution plan. Maybe you need a bigger memory grant than the one forced with the original plan? Here’s a little wiggle room.

Plus, if I happen to check up on my forced queries in Query Store, I can notice that often when this runs it’s saying it could use a little indexing help. And just the fact that I’m sometimes getting a “morally equivalent plan” is an indication to me that I haven’t forced something that’s perfect, I should look deeper.

I love that!

I didn’t come up with the term “morally equivalent plan”

I heard this term from Erland Sommarskog, who said that he heard the term for Conor Cunningham. I love that it’s unique, not an acronym (yet), and it seems like a good description to me – so let’s popularize it among those of us who talk about plans 🙂

And if you’d like to play with the code for this, it is based on a sample from Jovan Popovic published under the MIT license: grab it here.

Posted on

Forced Plan Confusion: Is_Forced vs Use Plan = True

Identifying that a query plan has been bossed around in Query Store can be a bit tricky, because it can appear in different ways.

This is a long post, but there is a recap at the bottom if you’re short on time. Or just vote up my suggestion to make this easier to see: vote here

1. The “Original FORCER” plan

This type of forced plan will have a check mark in its bubble on the graphical Query Store reports, and also have is_forced = 1 in sys.query_store_plan.

However, this type of forced plan will NOT have “Use plan” = true in the properties of the top leftmost operator.

2. The “Slightly Different FORCEE” plan

This type of forced plan with NOT have a check mark on in its bubble on the Query Store reports, and will NOT have is_forced = 1 in sys.query_store plan.

But it WILL have “Use plan” = true in the properties of the top leftmost operator.

I see these differences both if a plan is manually forced, or if I’m using Automatic Tuning

Whether or not you have the 2017 Automatic Plan Correction feature enabled (part of Auto-Tuning), you’re clicking the graphical “Force Plan” button in the Query Store reports, or you’re running sys.sp_query_store_force_plan, you will see these differences.

Let’s take a look at a demo

In this post, I’m going to show you how this works if I manually run sys.sp_query_store_force_plan. The demo code is at the bottom of the post and also supports letting Automatic Plan Correction force the plan, so you can run it either way and play with it for yourself.

Thanks to Jovan Popovic of Microsoft for publishing the original code under the MIT license. I adapted his code for this demo.

This is a parameter sniffing problem. First we get the ‘fast plan’ in cache

I’m using manual plan forcing in Query Store for this demo, so first up I:

  • Restore the WideWorldImporters sampled database from WideWorldImporters-Full.bak
  • Configure Query Store and make sure it’s enabled
  • Make sure AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF);

Then I start a workload. I have changed the demo slightly from Jovan’s original, and in our version we run a SELECT query from Sales.OrderLines where the PackageTypeID = 5.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int',
          @packagetypeid = 5;
GO 60 -

We run this query 60 times.

This gets a nested loop plan in cache for our query with an average CPU time of .06 milliseconds. Here’s what it looks like in the Top Resource Consumers Report:

Click for a larger image

But now… we have a recompile, and a different parameter is sniffed

Lots of things can cause a recompile: memory pressure, data in the tables changing, or someone running ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE.

In this case, it’s the third one.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

/* Get slow plan in cache */
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int',
          @packagetypeid = 0;
GO

The query that happens to compile now for our query has @packagetypeid = 0.

This query doesn’t get a nested loop join, it gets a hash match join. It doesn’t take too long to run by itself, but unfortunately this plan is slow as dirt when it is reused for a value like @packagetypeid = 7.

That runs 20 times, and this plan has an average CPU time of 389 seconds.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
go 20

I haven’t enabled Automatic Plan Correction, but I get a suggestion

Because I’m on SQL Server 2017 and I have Query Store configured, sys.dm_db_tuning_recommendations has a little heads-up for me, if I know to look there.

Click for a larger image

It looks like I’ve got a plan re-use problem, aka ‘Bad Parameter Sniffing’.

The right thing to do would be to look into why I’m getting different plans

If I were to do the right thing, I would try to figure out how to stabilize the plans without forcing anything.

But this post isn’t about doing the right thing, it’s about forcing!

Let’s say I take the script from the suggestion, and manually force this plan with TSQL

exec sys.sp_query_store_force_plan @query_id = 18, @plan_id = 1
GO

I have now forced plan_id 1!

If I look at Top Resource Consumers, plan_id 1 now has a check mark by it:

After forcing the plan, the query runs some more

Our query just runs once, again with @packagetypeid = 7.

This is the first time the query has run with @packagetypeid = 7 while being forced to use a plan compiled for @packagetypeid=7.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
GO

Whoa, now we have THREE plans

Our query uses the forced plan, but, wow, it gets a new plan_id: 10. Here’s what plan_id 10 looks like with its plan:

Click for a larger image

Plan_id 10 has no check-mark, but it has been forced to use the nested loop plan shape.

However, notice that it got a missing index request, and the bars in the plan are much thicker than the nested loop plan in the screenshot above.

Plan_id 10 is a forced plan, but it was compiled for @packagetypeid=7

If we look in the properties of the SELECT operator on plan_id 10, I can see a couple of things:

Click for a larger image

Parameter compiled value

In the Parameter List, @packagetypeid = 7. That is the value this was compiled for. That influenced the costing. That’s good, because there WERE more rows flowing into that stream aggregate. The higher estimates are also responsible for the green hint notifying us that we should take a look at our indexes, because it could be better.

Use plan = true

This plan was generated with a directive to use this plan shape. It didn’t just happen.

Plan 10[not forced]

Look right above the SELECT operator in the plan. It says “not forced”.

But, uh….. Use plan = true.

If it wasn’t forced, it was leaned on pretty heavily!

I read that “not forced” as, “This is the exact plan that was forced.” To know if it was forced but has different costs, you have to look for that use_plan=true.

Here’s what it looks like in sys.query_store_plan

The checkmark in the graphic Query Store reports is reflecting the plan which has is_forced_plan = 1.

For plan_id 10, if you dig into the plan stored in the query_plan column in the DMV, you will find that “Use plan=true” property, just like we saw in the report.

Recap: Plan FORCER and Plan FORCEE

This isn’t easy to summarize, but I’m going to give it my best shot!

When plans are forced in Query Store, whether it’s done by a user or by Automatic Tuning, there is more than one type of “forced” plan.

Plan FORCER: The exact plan which is forced will show with a check mark in Query Store reports, and will have Is_Forced = 1 in sys.query_store_plan.

Sometimes (probably often), you will see Slightly Different Plan FORCEEs: Once a plan is forced, either that exact plan can be compiled (which will have a checkmark and is_forced=1, it’s the same plan id), or sometimes a slightly different FORCEE can be compiled with different costs and different row estimates. This has the same shape as the forced plan, but you’ll see different shaped bars in it, perhaps a missing index suggestion and different memory grants. This FORCEE plan does not show with a check mark, has Is_forced = 0, but does have a plan property of ‘Use plan’ = true on the top leftmost operator.

I totally made up the words “FORCER” and “Slightly Different Plan FORCEE”, for the lack of better terms. And they make me laugh.

Wanna play with it yourself?

Here’s the demo code in a gist.

Want this to be easier to see?

Vote up my suggestion to make this easier to see: vote here.

Subscribers: watch live demos of this with more scripts in the course on Automatic Plan Correction! New users, subscription options are here.