Be cautious when using: OPTION(RECOMPILE)

This is one dangerous query hint. I wish I knew that when I started query tuning. I’ve used it before without fully understanding the impact.

What happens when a statement has OPTION(RECOMPILE)

SQL Server will compile an execution plan specifically for the statement that the query hint is on. There’s some benefits, like something called “constant folding.” To us, that just means that the execution plan might be better than a normal execution plan compiled for the current statement.

It also means that the statement itself won’t be vulnerable to parameter sniffing from other queries in cache. In fact, the statement with option recompile won’t be stored in cache.

The downsides

I have a few reasons why this hint is dangerous. First, compiling queries isn’t free. Using option recompile will use extra cpu and memory every time the statement compiles.

There’s also the fact that the hint applies strictly to the statement level, not the entire query. Let’s take a look at an example.

Reduce, reuse, recycle

I’m going to re-use the stored procedure from this post on parameter sniffing and using the estimated execution plan. Except for this example, I can’t use the estimated execution plan because there’s a temp table.

CREATE OR ALTER PROCEDURE [dbo].[QueryPostType_TempTable] (@PostType INT) as
BEGIN

SELECT PostTypeId, Tags,Body 
INTO #TempExample
FROM Posts where PostTypeId = @PostType


SELECT PostTypeId, Tags, Body
FROM #TempExample 
WHERE PostTypeId = @PostType
OPTION(RECOMPILE)

DROP TABLE #TempExample

END

GO

This stored procedure has OPTION(RECOMPILE) on only one statement

I’ll run the stored procedure with the value of 3, first.

exec [QueryPostType_TempTable] 3

Then, I’ll get the actual execution plan for post type 4.

exec [QueryPostType_TempTable] 4

Each “query” in this example is a separate statement. To prove that, I’ll right-click on the top SELECT INTO and view the properties.

Okay, but what about the cost of compilation?

To see the cost of compilation, just use statistics time. First, let’s create a simpler procedure.

CREATE OR ALTER PROCEDURE [dbo].[QueryPostType_OptionRecompile] (@PostType INT) as
BEGIN
SELECT PostTypeId, Tags,Body FROM Posts
JOIN Users as U on Posts.OwnerUserId = U.Id 
JOIN Users as U2 on Posts.LastEditorUserId = U2.Id
WHERE PostTypeId = @PostType
OPTION(RECOMPILE)
END
GO

And then execute.

SET STATISTICS TIME ON;
EXEC [QueryPostType_OptionRecompile] 4;
/*
SQL Server parse and compile time: 
   CPU time = 5 ms, elapsed time = 5 ms.
*/

Every time that statement runs, it will be compiled again, costing those 5 ms of CPU time.

Summary of the downsides

Each statement with option recompile consumes extra cpu and memory and doesn’t store the execution plan in cache, preventing performance tuners from seeing metrics like total execution count, or total worker time in dm_exec_query_stats.