Not dropping temp tables is better for performance

I have wondered about this one for some time now but have never got to the bottom of it. Whether to drop temp tables at the end of stored procedures or not, I never thought it really mattered to performance given the beefy machines we get to command these days.

I have asked a few fellow DBAs and the response was usually along the line of “yeah you should drop temp tables, because it is a good practice.” That’s just not good enough for me.

After reading Paul White’s blog post one day, an idea came to mind. I could use sys.fn_dblog to see if dropping temp tables made any differences. I then tried out a simple test and reached a conclusion for my peace of mind.

I will show you the test I have done and the findings. The code is pasted below if you would like to try it out yourself.

-- Create two procs, one with DROP TABLE and one without. use tempdb go create proc dbo.DropTempTable as create table #demo (i int) drop table #demo go create proc dbo.NoDropTempTable as create table #demo (i int) go

Once the procs are created, you can then run the example.

checkpoint -- in order to reset the fn_dblog output
exec dbo.DropTempTable -- Run the first proc
select
    operation,
    [transaction name],
    [transaction id]
from sys.fn_dblog(null, null)
go

checkpoint
exec dbo.NoDropTempTable -- Run the second proc
select
    operation,
    [transaction name],
    [transaction id]
from sys.fn_dblog(null, null)
go

The example above should be self-explanatory. What I want to show you is the output from fn_dblog:

image

As you can see, the transaction log entries were identical except the extra transaction required for dropping the temp table in the first proc. Looking at the result, it is reasonable to conclude that NOT dropping temp table is better for performance because SQL Server has less work to do.

To me that settled it, why wouldn’t I write less code if it is better for performance?

–oo00oo–
For more info, please refer to the following links:
Explicitly dropping Temporary Tables and performance – Myth or Fact?