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:

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:

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

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.