Using Plan Guides to Remove OPTIMIZE FOR UNKNOWN Hints

Say you’ve got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you’re getting bad query plans.

We’re going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a query that will produce two different execution plans depending on the Reputation parameter.

Disclaimer: This post is not about different ways of fixing parameter sniffing, or the dangers of plan guides, or why OPTIMIZE FOR UNKNOWN is bad – it’s specifically about removing OPTIMIZE FOR UNKNOWN when you can’t fix the queries. There are a lot of other ways to fix this problem, but I got backed into a corner with one particular application, and this was the only way I could fix it, so I’m sharing it here.

I’m going to cover two kinds of queries:

  • Single-statement batches (one-line queries)
  • Multi-statement batches (multi-line queries)
  • (Stored procedures are also doable, but not covering those here)

Building Plan Guides for Single-Statement Batches

If the app passes in a single query like this with parameters:

Then test it by building dynamic SQL like this:

Assuming we’ve got the index on Reputation discussed in the earlier post, SQL Server ends up using that nonclustered index, which turns out to be a really bad idea:

Check out estimated vs actual number of rows on the right side

Check out estimated vs actual number of rows on the right side

You can see the actual execution plan here, and hover your mouse over various parts of it to see the estimated vs actual rows.

The OPTIMIZE FOR UNKNOWN hint tells SQL Server to use the density vector rather than column statistics, so it only estimates that 1,865 rows will come back – when in actuality, 3.3mm rows come back. In performance tuning, that’s what we call a “bad thing,” since SQL Server ends up doing around 10mm page reads due to that key lookup. It would have been much more efficient to just do a clustered index scan.

To remove that hint without changing code, we can create a plan guide with the @type = ‘SQL’, which means one single statement:

In the @hints parameter, I’ve added OPTION (RECOMPILE) because this is the only way I’ve found to override the hint OPTION (OPTIMIZE FOR UNKNOWN). I haven’t been able to use a plan guide to override the UNKNOWN and pass in a specific value instead.

Using RECOMPILE has a couple of big drawbacks:

  • Added overhead for execution plan compilation every time the query is run
  • No cached execution plan metrics to check the overhead of this query

But in my particular case, it’s worth it. Test it by running your single-statement query again, this time with actual execution plans included. Look at the execution plan’s details by right-clicking on the SELECT statement and click Properties:

Guido the Guide

Guido the Guide

The new execution plan does a clustered index scan. Notice on the right of the screenshot that we have a PlanGuideDB and PlanGuideName, and RetrievedFromCache shows as false. More importantly, hover your mouse over the clustered index scan, and we’re now getting an accurate estimate for the number of rows returned (instead of using the density vector.)

And that’s it. Now let’s tackle the other two kinds of queries – multi-statement batches and stored procs – because the plan guide syntax is a little different there.

Building Plan Guides for Multi-Statement Batches

Say the query in question looks like the below – which is a bad example, because local variables use the density vector anyway, but that’s not the point of this demo:

Books Online doesn’t include an example of this, but after a bottle of wine, I got it working. Here’s the plan guide syntax:

The differences here:

  • The @module_or_batch parameter has to have the full, exact syntax of the entire batch that the statement is in. No shortcuts here – it has to be EXACT, down to casing, spacing, and comments.
  • The @type parameter is SQL even though this is a batch.
  • The @params parameter is null because the parameters are defined inside the batch itself.

When you get all this perfect, your actual execution plan will show the PlanGuideDB and PlanGuideName fields in Properties:

Plan guide in action

Plan guide in action

However, this is extraordinarily tough to get right. The batch has to be EXACT, and even an extra line return or a GO in there will throw it off.

 

Previous Post
SQL Server 2016 Standard Edition Now Has Many Enterprise Edition Features.
Next Post
A Little Fun With Math

5 Comments. Leave new

  • Great post Brent 😉
    Would it be correct to say, that the reason why (in the multi statement) the batch has to be 100% identical, is that the hash value of the batch, which you’re trying to run, has to be the same, for the sql server to figure out which plan its associated with. ??

  • If we know the vast majority of the records are likely to have Reputation=1, would it make sense to use filtered indexes on Reputation here?
    – one for Reputation > 1
    – one for Reputation=1 (or even not bother with one?)

    • Rafael – one of the reasons I love doing demos with the Stack Overflow database is that you can download it and play along at home. Why not give your ideas a shot? It’s one of the best ways to learn for yourself. (This is also one of the scenarios we cover in our 4-day training classes, too.)

  • Did you try removing existing hints (e.g. `OPTIMIZE FOR UNKNOWN`) by specifying `@hints = NULL;` with `sys.sp_create_plan_guide`? This is documented in [sp_create_plan_guide (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms179880.aspx).

    On a separate point, the syntax `@hints = N’OPTION (OPTIMIZE FOR (@Reputation = 123))’;`works.

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