November 14, 2016 at 2:16 pm
So, I've read several articles, and seen a good presentation about how interleaving DDL and DML can cause recompilations of stored procs. However, I am a bit confused with DROP statements for temp tables.
I have always been taught that explicitly DROPping your temp tables is a good practice for freeing up resources. So, if I want to avoid recompilations, would it be more appropriate to use the following syntax at the top of the proc?:
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results
Thanks in advance!
November 14, 2016 at 3:12 pm
If the goal is the prevent stored procedure recompilations, then I'd suggest using table variables instead of temporary tables.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 15, 2016 at 12:31 am
Most of those recommendations date back to SQL 2000, where recompilation was at the batch (procedure) level. Since SQL 2005 we've had statement-level recompile, which makes interleaving DDL & DML much less of a problem. No matter how much DDL there is, with statement-level recompile a statement will only recompile once, right before it executes.
I wouldn't recommend explicitly dropping temp tables, there's no need. They're automatically dropped when they go out of scope. The IF in the code you propose adding to stored procs will only ever return true if there's a #results that was created by whatever called that procedure.
Table variables are an option *if* you are having serious problems with recompiles, but they're far from a free lunch. Since they don't cause recompiles, the optimiser cannot get accurate row count estimations for table variables and they can cause terribly bad plans and resulting poor performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply