如何精準監測單條查詢對 TempDB 的空間消耗

TempDB 壓力來源

TempDB 主要服務三類場景：內部運算（排序、Hash Join、Work Table）、外部物件（暫存表、資料表變數）、列版本控制（樂觀並發）。複雜查詢因平行、排序等操作需大量記憶體，記憶體不足時會發生 Spill to TempDB，嚴重拖垮效能。

即時監測腳本

透過比對執行前後的 `sys.dm_io_virtual_file_stats` 與 `sys.dm_db_task_space_usage`，可精確計算單條查詢的 TempDB 讀寫量（MB）與內部物件配置頁數：

DECLARE @read BIGINT, @write BIGINT;
SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written)
FROM tempdb.sys.database_files AS DBF
JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id
WHERE DBF.type_desc = 'ROWS';

-- 這裡放入需測量的查詢語句

SELECT 
    tempdb_read_MB  = (SUM(num_of_bytes_read)  - @read) / 1024. / 1024.,
    tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024.,
    internal_use_MB = (
        SELECT internal_objects_alloc_page_count / 128.0
        FROM sys.dm_db_task_space_usage
        WHERE session_id = @@SPID
    )
FROM tempdb.sys.database_files AS DBF
JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id
WHERE DBF.type_desc = 'ROWS';

實測某爛查詢導致 TempDB 讀寫驚人（圖 1），此腳本可快速定位元兇。