Stored procedure recompile caused by alter table on temp table

Lately we got a customer who called in and reported  that a particular statement involving a temp table always got recompiled and caused performance problems.  

We have a KB article https://support.microsoft.com/kb/243586  which documents various scenarios that will cause recompile involving temp tables. But none of the conditions seemed to match.  After digging deeper, we were able to track down the problem.

Before we discuss the root cause, let’s talk about how you can track down stored procedure recompiles.  Starting SQL Server 2005, recompile only occurs at statement level.  In other words, one statement recompiling won’t cause the entire stored procedure to recompile.

You can use profiler to watch recompiles.  When you choose event, make sure you choose “SQL:StmtRecompile” event (see below).

When you run your stored procedure.  You will see “SQL:StmtRecompile” event  as shown below if that statement gets recompiled.

Now that we know how to monitor recompiles, let’s talk about what’s expected.  If you have a query involving temp table inside a stored procedure, you will always see SQL:StmtRecompile  for that query first time you run the procedure.   This is a feature called deferred compile.  When the procedure is compiled, the query involving temp table is not even compiled.  We wait until first time the query is executed.  So you will see the recompile at least once for a query involving temp table.  What is not expected is that you see the same query being recompiled again and again.   That is what this customer complained about.

It turned out that this customer had some alter table statement on the temp table.   In the case of temp table, if you have one alter statement on any of the temp tables, statements involving all tables will recompile.  The stored procedure below (proc_test) has two temp tables (#t1 and #t2).   Though #t2 has no alter table against it, the insert statement involving #t2 will also recompile in addition to the insert on #t1.  You can use profiler to monitor yourself.  Avoid alter on temp table.

use tempdb
go
create proc proc_test
as
set nocount on
create table #t1 (c1 int)
create table #t2 (c1 int)
insert into #t1 values (1)
insert into #t2 values (1) --this will always recompile even the alter statement is on a different temp table
alter table #t1 add c2 as c1