監控TempDB的空間容量

(1)   為什麼要監控TempDB的空間容量:

之前的文章TempDB特性與效能簡介文章架構有提到設定適當的TempDB檔案成長量可以增加資料庫的效能。

原因是當TempDB空間不夠時， TempDB的空間容量會增加，空間容量增加會有產生IO而造成低落的效能。

當TempDB檔案成長量設定太小會造成檔案在變大的頻率上升造成產生IO使資料庫效能下降，而設定太大會浪費空間，所以設定TempDB適當的檔案成長量可以使TempDB的空間容量增大的頻率變小。

要精確地設定TempDB空間容量成長量，就要取得空間容量成長空間大小與頻率，而監控TempDB的空間容量可以達成這個目的。

(2)   如何監控TempDB的空間容量

使用sys.dm_db_file_space_usage這個dynamic management view(簡稱dmv)可以取得TempDB的檔案空間資訊，包含TempDB總空間容量、使用與未使用的空間容量。

(3)   實作監控TempDB空間容量

SELECT

SUM(    unallocated_extent_page_count

        + user_object_reserved_page_count

        + internal_object_reserved_page_count

        + mixed_extent_page_count

        + version_store_reserved_page_count) * (8.0/1024.0) AS [TempDB空間容量MB]

,SUM(   user_object_reserved_page_count

        + internal_object_reserved_page_count

        + mixed_extent_page_count

        + version_store_reserved_page_count) * (8.0/1024.0) AS [已使用TempDB空間容量MB]        

, SUM(unallocated_extent_page_count * (8.0/1024.0))       AS [未使用TempDB空間容量MB]

FROM sys.dm_db_file_space_usage

執行結果：

將上述的程式碼使用sql job每天晚上紀錄一次就可以知道TempDB總空間容量、使用與未使用的空間容量的變化並進一步TempDB空間容量成長量。


參考網址:Total, free, and used space in tempdb in SQL Server