Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?
sql-server add tag
mark freeman (imported from SE) 
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](http://web.archive.org/web/20180130201242/http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx). I've read in comments that this is [handled on a per-session basis](http://web.archive.org/web/20180422150925/http://sqlblog.com:80/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx) and seen a question about whether or not cleanup is necessary [answered on MSDN](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/522d302a-857f-4730-b49f-cca7fb236912/is-it-necessary-to-clean-up-drop-temporary-tables-in-stored-procedures?forum=transactsql). 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 object space in MB]
    	--	,getdate() AS AfterTruncate
    	--FROM tempdb.sys.dm_db_file_space_usage;

    END
    GO
    
    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 'before'
    FROM tempdb.sys.dm_db_file_space_usage;
    
    EXEC usp_test
    GO
    
    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 'final'
    FROM tempdb.sys.dm_db_file_space_usage;
    GO 40

The commented lines were left commented out for some runs and un-commented for others. When the `TRUNCATE` was commented out, it took between 2.25 and 4.5 seconds before the results of the `tempdb.sys.dm_db_file_space_usage` query (4472 more pages and 34.9375 MB larger) matched the result before the procedure was executed. With the lines (including the `TRUNCATE`) un-commented, it only took about 0.11 - 0.9 seconds. These results are from a live system, with some minor data growth in the source table during this experiment.

Sample output with the code commented out (2.69 seconds from the first to last "final" entry):

    user object pages used user object space in MB                 before
    ---------------------- --------------------------------------- -----------------------
    1536                   12.000000                               2017-10-04 21:03:42.197
    
    Beginning execution loop
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:42.423
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:42.533
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:42.643
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:42.883
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:42.990
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:43.100
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:43.450
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:43.650
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:43.767
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:43.993
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:44.103
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:44.213
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:44.437
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:44.553
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:44.663
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:44.887
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6000                   46.875000                               2017-10-04 21:03:45.003
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    1536                   12.000000                               2017-10-04 21:03:45.113

Sample results with the code un-commented (0.11 seconds from the first to last "final" entry):

    user object pages used user object space in MB                 before
    ---------------------- --------------------------------------- -----------------------
    1536                   12.000000                               2017-10-04 21:07:39.807
    
    user object pages used user object space in MB                 BeforeTruncate
    ---------------------- --------------------------------------- -----------------------
    6016                   47.000000                               2017-10-04 21:07:39.923
    
    user object pages used user object space in MB                 AfterTruncate
    ---------------------- --------------------------------------- -----------------------
    6016                   47.000000                               2017-10-04 21:07:39.923
    
    Beginning execution loop
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    6016                   47.000000                               2017-10-04 21:07:40.160
    
    user object pages used user object space in MB                 final
    ---------------------- --------------------------------------- -----------------------
    1536                   12.000000                               2017-10-04 21:07:40.270
Top Answer
Paul White (imported from SE) 
>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][1]), 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:

[![Call stack because people like them][2]][2]

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.


  [1]: https://technet.microsoft.com/en-us/library/ms177495.aspx
  [2]: https://i.stack.imgur.com/SDsv2.png
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.