最近幫助客戶調優的過程中，發現客戶的TempDB存在非常大的壓力，經過排查是發現某些語句對TempDB的巨量使用所導致。
在SQL Server中，TempDB主要負責供下述三類情況使用：
- 內部使用（排序、hash join、work table等)
- 外部使用（臨時表，表變數等）
- 行版本控制（樂觀併發控制）
而對於內部使用，一些比較複雜的查詢中由於涉及到了大量的並行、排序等操作時就需要大量的記憶體空間，每一個查詢在開始時都會由SQL Server預估需要多少記憶體，在具體的執行過程中，如果授予的記憶體不足，則需要將多出來的部分由TempDB處理，這也就是所謂的Spill to TempDB。
通過下述語句可以觀察到某個查詢對TempDB造成了多少讀寫：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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使用結果如下:
使用該查詢就可以幫助瞭解某個語句使用了多少TempDB。