Temp Table Recompiles

If you landed on this article, then you most likely know that temp tables can cause recompilation. This happens because temp tables are treated just like regular tables by the SQL Server Engine. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks the dependent queries to be recompiled so the next execution can create an optimal execution plan. A similar sort of phenomenon also happens in temp tables.

With SQL Server 2005+, recompilation now occurs at the individual statement level rather than stored procedure level. This is advantageous because when input parameters change in a stored procedure the majority of the stored procedure can still utilize the cached execution plan. This is especially beneficial to stored procedures that contain many statements.

In this example, we will create an example showing recompilation, how to detect it, and how to prevent it.

First, we’ll create a procedure that selects a parameterized number of rows from a table. We’re going to use a temp table for purposes of this example.

CREATE PROCEDURE spGetPresident
(
    @ROWS INT
)
AS
BEGIN

    SET ROWCOUNT @ROWS

    SELECT *
    INTO #Presidents
    FROM dbo.Presidents_Tmp
   
    SELECT *
    FROM #Presidents
   
    SET ROWCOUNT 0
   
END

Next, before we execute the procedure, we will set a trace up to determine if we are recompiling.

In management studio, go to tools -> SQL Server Profiler

Connect to your server then choose the ‘Events Selection’ Tab.

Unselect all the checkboxes then check on the checkbox below stating ‘show all events’.

Uncheck All Events

Uncheck All Events

Scroll down to the TSQL event and expand the selection if necessary.

Select the event SQL:StmtRecompile. This will show the individual statements that recompile in the procedure.

Select Run.

Select Statement Recompile

Select Statement Recompile

Now execute the procedure, passing in the parameter of 10 rows. The first time we execute this procedure is the first time all the statements are compiled, so we will not see anything appear in the trace.

-- Execute the procedure selecting 10 rows
EXEC spGetPresident @ROWS=10

We do see anything appear in the trace, because this is the first time the statements in the procedure have been compiled.

No Events Captured

No Events Captured

Next, we will change the parameter to 12 and see if there are any recompiles.

-- Execute the procedure now selecting 12 rows
EXEC spGetPresident @ROWS=12

We still do not see any compilations. Why? Because the SQL Server engine has determined that the cached execution plan used in our last execution, will also suffice for this execution.

Let’s change the game up a little bit. Let’s change the parameter to 2000, and execute the procedure once again.

EXEC spGetPresident @ROWS=2000

Now we see a recompilation.
Profiler_Recompiles

Why did it recompile? Because the Query Engine does not think the cached execution plan will accommodate this query. Is it right? sometimes yes, sometimes no. However letting the Query Engine recompile when it sees fit is the best method. Is there a way not to allow the query engine to compile the query you ask? Why yes there is! However you better be sure you know what you’re doing. But if you are, then you can reap some significant benefits. A statement that recompiles vs one that does not could be the difference between 5 milliseconds and 500 milliseconds. How do you do this? Using Option Keep Fixed Plan.

Let’s alter our procedure:

ALTER PROCEDURE spGetPresident
(
    @ROWS INT
)
AS
BEGIN

    SET ROWCOUNT @ROWS

    SELECT *
    INTO #Presidents
    FROM dbo.Presidents_Tmp
   
    SELECT *
    FROM #Presidents
    OPTION (KEEPFIXED PLAN)  --Does not allow recompilation
   
    SET ROWCOUNT 0
   
END

Now we will execute our procedure once more (this will create one more recompile since we just changed the procedure)

EXEC spGetPresident @ROWS=1

Now let’s execute it passing in a parameter of 10000. This will surely cause a recompile right?

EXEC spGetPresident @ROWS=10000

Nope, nothing appears in the trace. Viola, we mitigated a recompile.

It should be noted that table variables inherently do not cause recompilation. In some situations these may be a better choice. The historical rule of thumb is to use temp tables when storing result sets greater than 1000, and table variables when less than 1000. There is some argument regarding this baseline. I personally however use this rule. I have seen table variables in the past perform very poorly. Temp tables maintain statistics and can also be indexed. In my experience, this far outweighs the overhead of recompilation.

4 comments
Alfredo 21 Feb 2013 at 9:23 pm

Nice article. I use the same rule of thumb.

Derek Dieter 21 Feb 2013 at 9:39 pm

Thanks Alfredo

xxxx 29 Oct 2012 at 8:11 am
Ranjith 08 Apr 2010 at 10:47 pm

You have put it nice and simple. Thanks

Featured Articles

 Site Author