1

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.

5
  • You may find enough of an answer across these posts: Temporary Table Caching in Stored Procedures and Temporary Table Caching Explained. Commented Dec 17, 2023 at 16:56
  • I guess you should also think about why you are using temp tables so heavily in the first place also. Commented Dec 18, 2023 at 14:59
  • @Charlieface It's the heaviest query of the entire server. If you don't give the optimiser a bit of help, then the query hits the server way too hard.
    – J. Mini
    Commented Dec 18, 2023 at 16:33
  • 1
    @ErikDarling So, in summary... By default, statistics for temp tables are cached even if you explicitly drop the table. This caching is similar to that of parameter sniffing and can be completely avoided by using WITH RECOMPILE. Regardless, if a recompilation threshold is crossed, then the entire procedure recompiles and fresh statistics are provided (just like with normal tables). Finally, OPTION (RECOMPILE) only matters in niche circumstances involving either manually updating statistics or crossing column-dependent recompilation thresholds. How does that summary sound? Is it correct?
    – J. Mini
    Commented Dec 25, 2023 at 0:57
  • In addition,if your proc is suffering from parameter sniffing problem and performance is very very slow then it can be avoided using OPTION (RECOMPILE) which is very CPU intensive.Still there is always a scope to optmized Store Procedure query such that parmameter sniffing may be minimized and thus can be ignored,so in this case no need to use OPTION (RECOMPILE)
    – KumarHarsh
    Commented Dec 26, 2023 at 5:58

1 Answer 1

0

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.

4
  • 1
    Most of this answer is about the benefits of WITH RECOMPILE/OPTION(RECOMPILE), but the question was about how temp tables do or do not interact with those.
    – J. Mini
    Commented Dec 17, 2023 at 18:40
  • You asked "Theoretically, is there any benefit to including" - the answer is yes there can be for the scenarios mentioned regardless if temp tables are used. The RECOMPILE hint will also ensure the query is optimized for the exact number of rows in the temp table. I've amended my answer to include that, including a basic example. Commented Dec 17, 2023 at 20:59
  • I think your final code comment has an incorrect number in it.
    – J. Mini
    Commented Dec 17, 2023 at 21:07
  • Yes- should be 10K, not 1K - fixed. Hope it helps. Commented Dec 17, 2023 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.