DaveWentzel.com            All Things Data

Do You Explicitly Drop Your Temp Tables?

Do you explicitly drop your #TempTables after you are through with them in your stored procedures?  Whenever I'm asked to performance tune a stored procedure and I see an explicit DROP #TempTable at the bottom of the code, I remove it.  Another developer asked me today why I do this.  I just assumed this would be common sense, but I guess not.  

4GL coders were taught to close object references at the end of their code block.  This isn't taught as much these days since most languages have "garbage collection" that handles this for you, generally.  This same rule holds true for stored procedures.  I could provide you with some test scripts using the standard FOO and BAR tables, but just thinking through the logic should be enough to convince you that a specific DROP is not needed.  

Performing a DROP, no matter how small the temp table, still takes some amount of time.  While the DROP is executing the procedure still may be holding locks (depending on your isolation levels, etc) that it may not need to.  Instead, let the proc complete and allow SQL Server to naturally clean up the temporary objects for you.  Remember that #TempTables are scoped to the code block that created the object, as well as any children, and when the procedure execution completes they are destroyed naturally since they are then out of scope.  

One More Reason 

Here's one more reason...many data access utilities and ORMs require the temp tables to NOT be DROP'd at the end of the procedure.  Most people don't know this.  A very popular ORM (which will remain nameless) cannot generate a "model" against a stored procedure that SELECTs data out of a #TempTable.  The ORM "model" is basically a way to map the underlying OO model to your stored procedure inputs and outputs.  The "model generator" reads metadata about the stored procedure to determine what its output result set looks like (column names, order, and data types).  Suffice it to say that natively SQL Server does not provide the necessary metadata to satisfy the ORM model generator, so it barfs.  The vendor even states that #TempTables are not supported, use @TableVariables instead.  Nevermind that this could have drastic performance ramifications.  Please don't think this is just bad ORM vendor code...SSRS has similar problems and the advertised workaround is to SET FMTONLY OFF within the procedure.  This trick also works with the ORM model generator, but only if you do NOT explicitly DROP your #TempTables.  

Again, granted, the performance improvement likely won't be much, but it is still a Best Practice to not explicitly DROP your #TempTables.  In my opinion anyway.  

Temporary Objects


That's interesting I don't generally drop my temp tables but more out of lazyness than any sort of performance consideration. Does this same rule apply to cursors? I generally use cursor variables when (rarely) working with cursors in SQL Server and I close the cursor when I'm done with it, but is there a performance benefit from not using the deallocate command and rather letting the cursor die a natural death?

I never honestly bothered to test that.  I don't write that many cursors where I'm super-concerned with performance so I never thought of it.  Like most people I use a "template" (SSMS code snippet or copy/paste an existing cursor) which always has the CLOSE and DEALLOCATE.  It would be an interesting test.  

True, super performance tuning I dont generally use cursors either, most cases I use them are for slicking up large requests into batches when pulling data across a linked server. In those cases network performance well overshadows any sort of allocation/deallocation issues so as to make them irrelevant.

This is interesting, "when the procedure execution completes they are destroyed naturally since they are then out of scope" I also thought this was true. But today a DBA is telling me to explicitly drop temp tables because tempDB is getting full. Other articles I am reading are suggesting that yes garbage collection occurs automatically on the temp tables when the procedure ends, but the cleanup is DEFERRED. In my example we have many users running very long procedures.

You are talking about 2 different things.  I can assure you that the object is dropped.  It is out of scope when the proc ends and won't exist if you attempt to query it.  As for the cleanup being DEFERRED I certainly agree with that.  I have worked on MANY systems where tempdb usage (and/or tempdb log) went through the roof and did not recover immediately after the procs either ended or were killed.  This is normal behavior and attests to the fact that there are background processes that are doing housekeeping.  None of that changes anything as to whether you explicitly drop a temp table or if you let it go out of scope.  The only exception would be if the proc in question ran for a very long time and your temp table was only used at the start of the proc and you structured your transactions such that the temp table usage could be committed early thus allowing you to explicitly drop the temp table early and reclaim the space.  There are many other reasons that your tempdb may be getting full.  I'd want to know more from my DBAs about the symptoms they are seeing before I rushed to any conclusions.  The fact is, when I've seen situations like what you are seeing (tempdb usage does not shrink back to normal soon after the proc goes out of scope) I've always found other causes.  For instance, procs that we thought were handling their own transactions were actually called by an implicit transaction in Java.  Because of this the temp tables, while no longer queryable/usable would not cleanup because of a "Java deadlock" which left the SQL Server transaction permanently "orphaned".