2

Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates a local temp table (usually by SELECT INTO) or references one created earlier in this procedure. Theoretically, is there any benefit to including either WITH RECOMPILE or OPTION (RECOMPILE) in such a stored procedure? If it helps, assume that I am on a 2016 version of SQL Server.

I am ignorant about when and how the presence of temp tables in stored procedures cause recompiles. This question is entirely born of that ignorance. I know the benefits of recompilation in general, but not how or when temp tables cause it.

Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

1

I would avoid the use of WITH RECOMPILE in favor of statement-level recompilation with OPTION(RECOMPILE) where it is required.

With the use of temp tables, you might get automatic recompilation due to changes in statistics in your temp tables. This will occur without the use of OPTION(RECOMPILE).

So what's the benefit of adding OPTION(RECOMPILE)? If your query uses parameters, query performance might vary depending on the input parameters. It might be better to recompile the query on each execution to have a tailored plan optimized for the input parameters.

A recompile hint can also allow the query optimizer to generate a plan with knowledge of the values of any local variables. Also, if you have any dynamic conditions in your WHERE clause like this:

WHERE (col1 = @col1 OR @col1 IS NULL)

The OPTION(RECOMPILE) is required to get an index seek on col1 if an appropriate index exists. An alternative to the above might be to use dynamic SQL.

The use of OPTION(RECOMPILE) has its drawbacks. Query compilation is CPU intensive and it can add to the cost of your query. For queries with very frequent executions, you should avoid recompiling. For queries that are executed less frequently where the compilation cost is small in comparison to the cost of the query - OPTION(RECOMPILE) can be a valid fix. Use sparingly though.

The above all remains relevant regardless of the use of temp tables.

One other point specific to temp tables is that you would get accurate row counts with a RECOMPILE hint. Here is a basic example - capture the actual execution plans and look at the differences in the estimated rows for the COUNT statement:

Note: Uses GENERATE_SERIES which was added in SQL 2022, but replace with your favourite table of numbers function for older SQL versions.

CREATE PROC TestRecompile(
    @Count INT
)
AS
CREATE TABLE #Test(
    ID INT
)
INSERT INTO #Test
SELECT value 
FROM GENERATE_SERIES(1,@Count)

SELECT COUNT(*) FROM #Test
OPTION(RECOMPILE)


GO


CREATE PROC TestNoRecompile(
    @Count INT
)
AS
CREATE TABLE #Test(
    ID INT
)
INSERT INTO #Test
SELECT value
FROM GENERATE_SERIES(1,@Count)


SELECT COUNT(*) FROM #Test


GO


-- count estimated 10000 rows
EXEC dbo.TestRecompile @Count=10000
-- count estimated 10 rows
EXEC dbo.TestRecompile @Count=10


-- count estimated 10000 rows
EXEC dbo.TestNoRecompile @Count=10000
-- count still estimated 10000 rows - reusing the previous cached plan
EXEC dbo.TestNoRecompile @Count=10

Hope this helps.

David Wiseman
  • 771
  • 3
  • 6