What Happens To Queries With Recompile Hints In Query Store

Worst Behavior

---

For many SQL Server developers, using statement-level OPTION(RECOMPILE) hints is the path of least resistance for tuning parameter sensitive queries.

And I’m okay with that, for the most part. Figuring out what parameters a stored procedure compiled with, and was later executed with is a hard task for someone busy trying to bring new features to a product.

But let’s say one day you have performance issues regardless of all the recompiling in the world. No matter what set of parameters get passed in, your procedure is just sleepy-dog slow.

Things get so bad that you hire a young, handsome consultant with reasonable rates like myself to help you figure out why.

The plan cache will be pretty useless for troubleshooting the recompile-hinted queries, but we can still use Query Store.

Example

---

Here’s a simple test procedure to show what I mean, named after a random set of cursor options.

CREATE OR ALTER PROCEDURE
    dbo.LocalDynamic
(
    @OwnerUserId integer
)
AS 
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
        
    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId;
    

    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId
    OPTION(RECOMPILE);
END;
GO

EXEC dbo.LocalDynamic
    @OwnerUserId = 22656;

EXEC sp_QuickieStore
    @procedure_name = 'LocalDynamic';

The end result in Query Store (as observed with the lovely and talented sp_QuickieStore) will show two execution plans.

1. The query without the recompile hint will show a compiled parameter value of 22656
2. The query with the recompile hint will show the literal values used by parameters as predicates

Here’s what I mean. This query has a Parameter List attribute.

This query won’t have the Parameter List attribute, but that’s okay. We can see what got used as a literal value.

Plumbing

---

This all comes down to the way statement-level recompile hints work. They tell the optimizer to compile a plan based on the literal values that get passed in, that doesn’t have to consider safety issues for other parameter values.

Consider the case of a filtered index to capture only “active” or “not deleted” rows in a table.

Using a parameter or variable to search for those won’t use your filtered index (without a recompile hint), because a plan would have to be cached that safe for searching for 1, 0, or NULL.

If you’re troubleshooting performance problems using Query Store, and you’re dealing with queries with statement-level recompile hints, you just need to look somewhere else for the parameter values.

What this can make tough, though, if you want to re-execute the stored procedure, is if you have multiple queries that use incomplete sets of required parameters. You’ll have to track down other query plans.

But quite often, if there’s one problem query in your procedure, the parameter values it requires will be enough to go on.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.