Explicitly Drop Temporary Tables Or Wait For Cleanup?

I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn’t remember the specifics, but I said it actually didn’t matter. However, that answer has bugged me, so I set up a quick test.

Explicitly Drop Temporary Tables

We could make this a crazy set of tests, but I wanted to keep things relatively simple. I created two procedures that create identical temporary tables. One drops the tables, the other doesn’t:

CREATE PROC dbo.BOMDrop
AS
CREATE TABLE #BOMData (BOMLevel SMALLINT,
                       PerAssemblyQty DECIMAL(8, 2),
                       ComponentName VARCHAR(50),
                       ProductAssemblyName VARCHAR(50),
                       UnitMeasure VARCHAR(50));

INSERT #BOMData (BOMLevel,
                 PerAssemblyQty,
                 ComponentName,
                 ProductAssemblyName,
                 UnitMeasure)
SELECT bom.BOMLevel,
       bom.PerAssemblyQty,
       c.Name,
       pa.Name,
       um.Name
FROM Production.BillOfMaterials AS bom
    JOIN Production.Product AS pa
        ON pa.ProductID = bom.ProductAssemblyID
    JOIN Production.Product AS c
        ON c.ProductID = bom.ComponentID
    JOIN Production.UnitMeasure AS um
        ON um.UnitMeasureCode = bom.UnitMeasureCode;


SELECT COUNT(*)
FROM #BOMData AS bd;

DROP TABLE #BOMData;
GO

CREATE PROC dbo.BOMNoDrop
AS
CREATE TABLE #BOMData (BOMLevel SMALLINT,
                       PerAssemblyQty DECIMAL(8, 2),
                       ComponentName VARCHAR(50),
                       ProductAssemblyName VARCHAR(50),
                       UnitMeasure VARCHAR(50));

INSERT #BOMData (BOMLevel,
                 PerAssemblyQty,
                 ComponentName,
                 ProductAssemblyName,
                 UnitMeasure)
SELECT bom.BOMLevel,
       bom.PerAssemblyQty,
       c.Name,
       pa.Name,
       um.Name
FROM Production.BillOfMaterials AS bom
    JOIN Production.Product AS pa
        ON pa.ProductID = bom.ProductAssemblyID
    JOIN Production.Product AS c
        ON c.ProductID = bom.ComponentID
    JOIN Production.UnitMeasure AS um
        ON um.UnitMeasureCode = bom.UnitMeasureCode;


SELECT COUNT(*)
FROM #BOMData AS bd;
GO

I then set up Extended Events to capture the query metrics and I executed each of the queries multiple times (also, just for the test, I discarded the results because I didn’t want that process mucking with my measurements). After executing both procedures 500 times, the results were quite simple. The average execution time with an explicit drop was 8,672 microseconds. Meanwhile, the average for not dropping the temporary table was 8,530 microseconds. That’s about a 1% difference across hundreds of executions. Reads were identical and so were writes.

In short, the behavior is the same.

What about the impact on the system? Could I see changes in memory or I/O as these different processes ran? Nope. Identical behaviors in all ways.

So what is happening?

I’m going to leave it to Paul White to explain the details, but, essentially, since SQL Server 2005, temporary objects are cached in a way that explicitly dropping (or truncating) doesn’t immediately remove them from cache. In short, they get cleaned up much the same way a temporary table that you don’t explicitly drop does.

Conclusion

I would not say that this means you shouldn’t drop a temporary table within code if you’re done with it. However, I would say that you do not need an explicit drop for all your temporary tables. I’d go with a simple rule. If you’re done with a temporary table and you have a lot more other processing to do, drop it. If you use a temporary table right to the end of a procedure, leave it there. It’ll get cleaned up in due course. Your drop isn’t going to affect things in that case.