The Start of An Answer For Scott: Temp Tables and Recompiles
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?
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