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.

[cc lang=”sql”]
CREATE PROCEDURE spGetPresident
(
@Rows int
)
AS
BEGIN

SET ROWCOUNT @Rows

SELECT *
INTO #Presidents
FROM dbo.Presidents_Tmp

SELECT *
FROM #Presidents

SET ROWCOUNT 0

END
[/cc]

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’.

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.

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.

[cc lang=”sql”]
— Execute the procedure selecting 10 rows
EXEC spGetPresident @Rows=10
[/cc]

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

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

[cc lang=”sql”]
— Execute the procedure now selecting 12 rows
EXEC spGetPresident @Rows=12
[/cc]

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.

[cc lang=”sql”]
EXEC spGetPresident @Rows=2000
[/cc]

Now we see a recompilation.

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:
[cc lang=”sql”]

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
[/cc]

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

[cc lang=”sql”]
EXEC spGetPresident @Rows=1
[/cc]

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

[cc lang=”sql”]
EXEC spGetPresident @Rows=10000
[/cc]

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.