如何清理SQL Server中残留的以#am_wait_types开头的临时表？

问题背景 #

需要清理以#am_wait_types______________________________________________________________________________________________________000000000002为前缀的临时表，目前已在所有使用临时表的自定义存储过程中添加DROP语句，但仍有大量临时表残留，导致SQL Server性能下降。

查询当前临时表的SQL语句：

select name, object_name(object_id) As ObjName,* 
from tempdb.sys.objects 
where name like '#%'

关联业务示例脚本：

(SELECT ((SUM(Quantity1)-SUM(Quantity2)) + SUM(Quantity3)) AS Quantity,ProductId,[Name],ISNULL(Price,0) as Price, FinalPrice,ISNULL(BatchNo,'') AS BatchNo , ISNULL(ExpiryDate,GETDATE()) AS ExpiryDate, ISNULL(Composition,'') AS Composition, ISNULL(ChemicalCompId,0) AS ChemicalCompId, ISNULL(Rack,'') AS Rack, ISNULL(RackId,0) AS RackId, ISNULL(HsnCode,'') AS HsnCode, HsnCodeId,      
    ISNULL(GstSlab,'') AS GstSlab , GstSlabId, GST, PharmacyPurchaseEntryId FROM(      
 SELECT SUM(pp.QuantityApproved) AS Quantity1,0 AS Quantity2, 0 AS Quantity3, pp.ProductId, pm.[Name], PPE.SellingMRP AS Price, ISNULL(PPE.GstMrp,0) AS FinalPrice, pp.BatchNo,       
 pp.ExpiryDate,ccm.[Name] AS Composition, pm.ChemicalCompId, ISNULL(R.Name,'') AS Rack, ISNULL(R.RackId,0) AS RackId, H.Code AS HsnCode, ISNULL(H.Id,0) AS HsnCodeId,      
 G.Code AS GstSlab,ISNULL(G.Id,0) AS GstSlabId, ISNULL(PPE.GST,0) AS GST  , pp.PharmacyPurchaseEntryId    
 FROM dbo.PharmacyStockToCounterEntry pp       
 LEFT JOIN dbo.PharmacyStockToCounterMain ppm ON pp.StocktoCounterId = ppm.StocktoCounterId       
 LEFT JOIN dbo.PharmacyPurchaseEntry PPE ON pp.ProductId = PPE.ProductId AND PPE.BatchNo = pp.BatchNo AND PPE.ExpiryDate = pp.ExpiryDate AND
 pp.PharmacyPurchaseEntryId = PPE.PharmacyPurchaseEntryId
 LEFT JOIN dbo.PharmacyMaster pm ON pp.ProductId = pm.PharmacyItemId        
 LEFT JOIN dbo.ChemicalCompoMaster ccm ON pm.ChemicalCompId = ccm.ChemicalCompoId        
 LEFT JOIN [dbo].[Rack] R ON R.CounterId = @counterId AND R.ProductId = pm.PharmacyItemId       
 LEFT JOIN HsnCode H ON H.Id = ISNULL(pm.HsnCodeId, PPE.HsnCodeId)      
 LEFT JOIN GstSlab G ON G.Id = ISNULL(pm.GstSlabId, PPE.GstSlabId)      
 WHERE ppm.IsCancel='N' AND ppm.CounterId = @counterId AND ppm.TransStatus ='A'   
)      
AS TEMP      
GROUP BY ProductId, Name, Price, FinalPrice, BatchNo, ExpiryDate, Composition, ChemicalCompId, Rack, RackId, HsnCode, GstSlab, HsnCodeId, GstSlabId, GST, PharmacyPurchaseEntryId
HAVING ((SUM(Quantity1)-SUM(Quantity2)) + SUM(Quantity3)) > 0;

一、手动清理指定前缀的临时表 #

通过动态SQL批量生成删除语句，匹配前缀即可：

DECLARE @DropSQL NVARCHAR(MAX) = N''

SELECT @DropSQL += N'DROP TABLE ' + QUOTENAME(name) + N';'
FROM tempdb.sys.objects
WHERE name LIKE '#am_wait_types%' -- 匹配目标前缀
  AND type = N'U' -- 仅针对用户临时表

EXEC sp_executesql @DropSQL

二、排查残留原因及对应解决方法 #

1. 未关闭的会话占用 #

临时表会随创建它的会话关闭自动清理，若会话长期保持（如连接池连接、未结束的业务会话），临时表会残留。查询关联会话：

SELECT 
    o.name AS TempTableName,
    sp.session_id,
    sp.login_name,
    sp.host_name,
    sp.program_name
FROM tempdb.sys.objects o
JOIN tempdb.sys.dm_db_session_space_usage ssu ON o.object_id = ssu.object_id
JOIN sys.dm_exec_sessions sp ON ssu.session_id = sp.session_id
WHERE o.name LIKE '#am_wait_types%'

确认后可执行KILL <session_id>结束会话（注意：会中断对应会话的业务操作，需在业务低峰期操作）。

2. 未提交/回滚的事务 #

若创建临时表的事务长期未结束，临时表无法自动清理。查询关联事务：

SELECT 
    o.name AS TempTableName,
    dt.transaction_id,
    dt.database_transaction_begin_time,
    sp.session_id
FROM tempdb.sys.objects o
JOIN sys.dm_tran_database_transactions dt ON o.object_id = dt.object_id
JOIN sys.dm_exec_sessions sp ON dt.transaction_id = sp.transaction_id
WHERE o.name LIKE '#am_wait_types%'

需结合业务逻辑，手动提交或回滚异常事务。

3. 存储过程异常导致DROP未执行 #

即使添加了DROP语句，若存储过程执行中抛出异常，DROP可能未触发。建议用TRY/CATCH+FINALLY确保清理：

CREATE PROCEDURE YourTargetProcedure
AS
BEGIN
    SET NOCOUNT ON;
    -- 创建临时表
    CREATE TABLE #am_wait_types_xxx (...)

    BEGIN TRY
        -- 业务逻辑代码
    END TRY
    BEGIN CATCH
        -- 异常捕获与处理
        THROW; -- 抛出异常，保留错误信息
    END CATCH
    FINALLY
        -- 无论成功失败都执行清理
        IF OBJECT_ID('tempdb..#am_wait_types_xxx') IS NOT NULL
            DROP TABLE #am_wait_types_xxx;
    END

（注：SQL Server 2012及以上支持FINALLY块，低版本可在TRY和CATCH块末尾分别添加DROP语句）

三、预防残留的最佳实践 #

• 强制在FINALLY块（或TRY/CATCH后）添加临时表清理逻辑，确保执行路径全覆盖
• 避免在长时间运行的会话中创建临时表，尽量采用短会话模式
• 监控tempdb空间使用，设置合理的初始大小（避免频繁自动收缩影响性能）
• 小数据集场景下，可考虑用表变量替代临时表，表变量会随会话结束自动清理

内容的提问来源于stack exchange，提问作者itjanko