Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?

SQL Server caches temp tables created within stored procedures and merely renames them when the procedure ends and is subsequently executed. My question has to do with when the tempdb space is released. I've read that the table is truncated at the end of the procedure. I've read in comments that this is handled on a per-session basis and seen a question about whether or not cleanup is necessary answered on MSDN. But what if it is never executed by the same session twice?

I've also heard that there is a background garbage collection process that frees up that space once the table is out of scope.

Truncating a temp table at the end of the stored procedure that creates it seems to cause the space the table uses in tempdb for the data to be released faster than if no truncate statement is used, despite expectations to the contrary. Why?

What would be the relative performance implications of using or not using such a truncate statement? When using SNAPSHOT isolation, tempdb is often stressed and I would think that releasing space used in tempdb from a large temp table as soon as possible would prevent otherwise unnecessary growth of tempdb. Would this potential space savings come at the cost of performance?

Here is some code to reproduce the issue (mostly from @TheGameiswar, with some changes):

```
SET NOCOUNT ON;
GO
ALTER PROC usp_test
AS
BEGIN
IF object_id('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

SELECT *
INTO #temp
FROM [dbo].[Event_28] -- This is a table with 15313 rows, using 35648 KB according to sp_spaceused

--SELECT SUM(user_object_reserved_page_count) AS [user object pages used]
-- ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB]
-- ,getdate() AS BeforeTruncate
--FROM tempdb.sys.dm_db_file_space_usage;
-- TRUNCATE TABLE #temp
--SELECT SUM(user_object_reserved_page_count) AS [user object pages used]
-- ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user ob

Truncating a temp table at the end of the stored procedure that creates it seems to cause the space the table uses in tempdb for the data to be released faster than if no truncate statement is used, despite expectations to the contrary. Why?

If the temporary table is large enough (more than 128 extents), the physical page deallocations are deferred, and performed by a background system task. This is true whether an explicit TRUNCATE TABLE is used or not.

The only difference is a tiny implementation detail. An explicit TRUNCATE TABLE happens to create a task with a shorter timer than the (otherwise identical) deferred drop task created by temporary table cleanup:

Whether this is by accident or design is anyone's guess. It could of course change at any time, since this level of detail goes way beyond the supported product surface area.

If you disable deferred drop globally with a (mostly) undocumented trace flag:

DBCC TRACEON (671, -1);

...the deallocations are performed synchronously in both cases, and you will see no difference in timing.


What would be the relative performance implications of using or not using such a truncate statement? When using SNAPSHOT isolation, tempdb is often stressed and I would think that releasing space used in tempdb from a large temp table as soon as possible would prevent otherwise unnecessary growth of tempdb. Would this potential space savings come at the cost of performance?

I seriously doubt this would ever make much difference either way. If tempdb is sized appropriately to the peak needs of your workload, whether deferred drop occurs after one second or three should not matter. The same work is done; it is just a small difference in timing.

On the other hand: If you feel more comfortable using TRUNCATE TABLE on temporary tables at the end of your stored procedures, go with that. I'm not aware of any particular downside to doing this.

StackExchange Database Administrators Q#188659, answer score: 12