Do NOT drop #temp tables
Reduce PAGELATCH on TempDB in SQL Server
In queries involving temporary tables, you must have seen DROP TABLE #temp; at the end of queries. While this might seem like a good practice to clean up resources, it's advisable not to drop temporary tables at the end of your session. Here’s why.
The Issue with Dropping Temporary Tables
When temporary tables are dropped, SQL Server must acquire a latch on system object tables to check and insert metadata. In a high-concurrency environment, when multiple sessions attempt to drop temporary tables, these DDL operations must wait in line to acquire PAGELATCHes on system object tables. These waits lead to contention on TempDB.
Let’s create this Stored procedure on a test database:
USE [DBATEST]
GO
CREATE PROC [dbo].[DropTempTable]
AS
BEGIN
CREATE TABLE #TempTable (ID INT null)
INSERT INTO #TempTable values(1)
DROP TABLE #TempTable /*Doing this intentionally to show PAGELATCH waits*/
END
GO
Now, I’m going to run the ostress utility to generate a workload against the server to run this stored procedure 1000 times on 50 concurrent sessions.
>ostress.exe -S"HARIPRIYA\SQL2022" -Q"exec dbatest.dbo.DropTempTable" -n50 -r1000 -q
Now, check contention while this is running.
I’m filtering the query below only for ‘DROP TABLE’ to see the PAGELATCHes due to DROP TABLE command.
USE master
GO
SELECT
er.session_id, er.wait_type, er.wait_resource,
OBJECT_NAME(page_info.[object_id],page_info.database_id) as [object_name],
page_info.page_type_desc,
er.blocking_session_id,er.command,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text,
page_info.database_id,page_info.[file_id], page_info.page_id, page_info.[object_id],
page_info.index_id,
CASE
WHEN page_info.page_type_desc IN('SGAM_PAGE','GAM_PAGE', 'PFS_PAGE')
AND WAIT_TYPE IN ('PAGELATCH_SH','PAGELATCH_UP','PAGELATCH_EX')THEN 'ALLOCATION CONTENTION'
WHEN page_info.page_type_desc IN ('DATA_PAGE', 'INDEX_PAGE')
AND WAIT_TYPE IN ('PAGELATCH_SH','PAGELATCH_UP','PAGELATCH_EX')THEN 'METADATA CONTENTION'
END AS allocation_type
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r /*database ID, file ID, page ID SQL2019*/
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info /*replace dbcc page SQL2019*/
WHERE er.wait_type like 'PAGELATCH%'
AND command LIKE 'DROP TABLE'
GO
Here, you see the PAGELATCHes caused due to DROP TABLE command in the stored procedure. This leads to contention.
So What is the Recommendation
To mitigate this contention, it is recommended to avoid dropping temporary tables at the end of the session. Instead, SQL Server will do the cleanup process for you.
How SQL Server Handles Cleanup
Behind the scenes, SQL Server uses Optimistic Latching Algorithm to drop tables asynchronously, which means that the cleanup process is handled based on the number of concurrent CREATE/DROP operations and a few other factors, so as to avoid contention on TempDB.
Conclusion
In summary, while it may be tempting to clean up temporary tables explicitly by using DROP TABLE commands, it’s a better practice to allow SQL Server to handle this cleanup automatically. By doing so, you can take advantage of the Optimistic Latching Algorithm, which helps prevent contention on TempDB, thereby enhancing overall performance.