Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

The Start of An Answer For Scott: Temp Tables and Recompiles

The Start of An Answer For Scott: Temp Tables and Recompiles

  • Comments 3
  • Likes

Kimberly Tripp, one of the most incredibly astute SQL poeple on the planet, calls her blog, "Improving *my* SQL skills through your questions".  I would never steal her line, but after a bit of research on MSDN this afternoon, I can certainly relate to it, thanks to Scott Whigham of LearnSQLServer.com.

In response to my recent post on temp tables and table variables, Scott posed a question:

One question, you write, "... temp tables have statistics on them, so they generate a recompile every time they’re built."

When you say "recompile", are you talking about using a temp table inside a stored procedure and that the stored proc will have to recompile upon every execution?

I answered:

A procedure that creates a temp table will recompile every time it's invoked.  A procedure which references an already-defined temp table will only recompile if the cardinality estimate for the query is different than that for the previous compilation.

Scott then asked a wonderful question, the researching of which has improved my SQL skills:

So, if you load up Profiler, do you see an SP:Recompile event upon each invocation of any proc that includes CREATE TABLE #... or SELECT INTO?

Well, based on Thomas Davidson's terrific MSDN article which details SQL Server 2000 recompilation behavior, I would've believed this would be true, but it's not.  A little more searching led me to this article;  here's the money quote (my red underline added):

The SP:Recompile event class indicates that a stored procedure, trigger, or user-defined function has been recompiled. In SQL Server 2005, recompilations reported by this event class occur at the statement level, whereas those in SQL Server 2000 occurred at the batch level.

In SQL Server 2005, the preferred way to trace statement-level recompilations is to use the SQL:StmtRecompile event class. Starting in SQL Server 2005, the SP:Recompile event class is deprecated. For more information, see SQL:StmtRecompile Event Class and "Recompiling Execution Plans" in Execution Plan Caching and Reuse.

Note that, in order to allow the product to profile SQL Server 2000 instances, the SP:Recompile event class is still available for selection in the SQL Server 2005 Profiler.  I can offer first-hand testimony that if you profile this event class on a SQL Server 2005 instance, you won't see any activity on it.

Furthermore, my research to this point shows that the mere creation and population of a temp table doesn't generate a recompile at every execution.  Here's a trivial stored procedure which doesn't generate any SP:Recompile or SQL:StmtRecompile events on my sandbox upon repeated invocations:

ALTER

PROCEDURE qtest AS
SELECT
*
INTO
   #qtemp
FROM   sys.objects

My research into this issue continues.  I'll of course let you know what I find.

Thanks, Scott, for asking such great questions, and for improving my skills in the process!

     -wp

Comments
  • When I posted yesterday on this topic (first broached here), I'd found some good information pertinent...

  • One of the great things about my involvement with the SQL Ranger community is that I occasionally here

  • Binh Cao's customer left a comment on yesterday's post on global temp tables. Here's the money quote:

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment