設定適當的TempDB檔案"成長量"可以增強效能而透過監控TempDB的空間容量變化可以達到此目的。
(1) 為什麼要監控TempDB的空間容量:
之前TempDB特性與效能簡介一文有提到設定適當的TempDB檔案成長量可以增加資料庫的效能,原因是當TempDB空間容量不足時,TempDB的空間容量便會自動增加,所產生檔案IO的行為會造成效能的低落。
TempDB檔案成長量設定太小會不斷產生IO使資料庫效能下降,而設定太大會浪費空間。所以設定適當的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空間容量成長量。