13

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 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
0

1 Answer 1

12

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:

Call stack because people like them

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.