查询SQL Server中各用户数据库的tempdb资源使用情况

一、实时采集DMV数据+归档到自定义表 #

由于sys.dm_db_session_space_usage和sys.dm_db_task_space_usage仅保留当前会话/任务的临时数据，核心思路是定时采集这些实时数据，关联会话所属的用户数据库后存入自定义表，以此积累历史记录。

1. 创建历史记录表 #

先建立用于存储tempdb使用历史的表：

CREATE TABLE TempDBUsageHistory (
    RecordID INT IDENTITY(1,1) PRIMARY KEY,
    CaptureTime DATETIME DEFAULT GETDATE(),
    DatabaseName NVARCHAR(128),
    SessionID INT,
    UserObjectsAllocPages BIGINT,
    UserObjectsDeallocPages BIGINT,
    InternalObjectsAllocPages BIGINT,
    InternalObjectsDeallocPages BIGINT,
    TotalAllocPages BIGINT,
    TotalDeallocPages BIGINT
);

2. 编写采集关联脚本 #

通过sys.dm_exec_sessions获取会话所属数据库，结合tempdb空间使用DMV统计数据：

INSERT INTO TempDBUsageHistory (
    DatabaseName, SessionID, UserObjectsAllocPages,
    UserObjectsDeallocPages, InternalObjectsAllocPages,
    InternalObjectsDeallocPages, TotalAllocPages, TotalDeallocPages
)
SELECT
    DB_NAME(s.database_id) AS DatabaseName,
    s.session_id,
    su.user_objects_alloc_page_count,
    su.user_objects_dealloc_page_count,
    su.internal_objects_alloc_page_count,
    su.internal_objects_dealloc_page_count,
    su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count AS TotalAllocPages,
    su.user_objects_dealloc_page_count + su.internal_objects_dealloc_page_count AS TotalDeallocPages
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s ON su.session_id = s.session_id
WHERE s.database_id > 4 -- 排除master、model、msdb、tempdb系统库
    AND su.session_id <> @@SPID -- 排除当前采集会话

3. 定时执行采集 #

创建SQL Server代理作业，设置合适的执行频率（比如5分钟/10分钟一次，根据业务负载调整），自动运行上述采集脚本，持续积累历史数据。

二、用扩展事件追踪临时对象归属 #

如果需要精准定位临时表、表变量等对象的创建来源数据库，可以通过扩展事件捕获tempdb内的对象创建事件：

1. 创建扩展事件会话 #

CREATE EVENT SESSION TempDBObjectCreation ON SERVER 
ADD EVENT sqlserver.object_created(
    WHERE object_type = N'USER_TABLE' AND database_id = 2) -- tempdb的database_id固定为2
ADD ACTION(sqlserver.session_id, sqlserver.database_id, sqlserver.sql_text)
ADD TARGET package0.event_file(SET filename=N'TempDBObjects.xel', max_file_size=(100), max_rollover_files=(5))
WITH (STARTUP_STATE=ON);

启动会话后，会自动捕获tempdb内的用户表创建事件，并记录会话所属的数据库ID。

2. 解析事件数据并存档 #

定期解析扩展事件文件，将数据存入历史表：

INSERT INTO TempDBUsageHistory (CaptureTime, DatabaseName, SessionID)
SELECT
    CAST(event_data AS XML).value('(/event/@timestamp)[1]', 'DATETIME') AS CaptureTime,
    DB_NAME(CAST(event_data AS XML).value('(/event/action[@name="database_id"]/value)[1]', 'INT')) AS DatabaseName,
    CAST(event_data AS XML).value('(/event/action[@name="session_id"]/value)[1]', 'INT') AS SessionID
FROM sys.fn_xe_file_target_read_file('TempDBObjects*.xel', NULL, NULL, NULL);

三、辅助分析方案 #

• 资源调控器：创建资源池，将不同数据库的会话分配到对应池，通过sys.dm_resource_governor_resource_pools查看各池的tempdb使用情况，适合按数据库分组管控的场景。
• 查询存储：开启查询存储后，筛选出tempdb消耗较高的查询，通过查询的所属数据库统计对应库的tempdb占用，可定位到具体查询的影响。

四、历史数据统计分析 #

基于积累的历史表，可按数据库统计tempdb使用趋势：

SELECT
    DatabaseName,
    CONVERT(DATE, CaptureTime) AS CaptureDate,
    AVG(TotalAllocPages) AS AvgAllocPages,
    MAX(TotalAllocPages) AS MaxAllocPages,
    SUM(TotalAllocPages) AS TotalAllocPages
FROM TempDBUsageHistory
GROUP BY DatabaseName, CONVERT(DATE, CaptureTime)
ORDER BY TotalAllocPages DESC;

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