SQL Server ->>監控和管理Tempdb

Tempdb作為一個公共數據庫，存儲著一些臨時的數據。有些是用戶自己創建的，有些是SQL Server自己創建的。Tempdb空間被使用的一些常見場景有

用戶自定義：臨時表和表變量、游標。

SQL Server自己創建：快照事務級別（使用了行版本技術），觸發器需要用到的inserted和deleted表，Hash Join需要用到一部分臨時數據空間，執行計劃中各種Spool操作符，索引重建指定SORT_In_tempdb選項。

差不多這些。那麼Tempdb數據庫必然是一個經常被使用的地方。怎麼確定它是否健康？它的文件增長是否處於一個合理正常的狀態？怎樣維護好這個數據庫是最佳實踐？

那麼首先從Tempdb的文件規劃上，數據文件的數量和大小就是一個點。

文件數量：

作為最佳時間，文件的數量應該是和機器的CPU核心數量是一致的。原因是Tempdb默認情況下只有一個數據文件，這樣的情況下容易引起鎖的爭奪。因為一個文件只有一個叫GAM（Global Allocation Map）的頁面，記錄著這個文件中的頁面分配情況。我們已經知道Tempdb會作為許多上面提到的數據庫活動的目標，同一時間卻只有一個活動可以鎖定這個頁面，就必然造成其他線程的等待。那麼通過添加新的文件來避開這種鎖爭奪。同一時間最多也就是有與CPU核心數量一致的線程數可以是處於活動狀態。

文件大小：

這個需要根據應用程序的實際情況而定，有一點是不要設置成百分比增長。也不要是shrink這個數據庫或者數據庫文件。保持大概初始化500MB，然後按100-200MB這樣就差不多了。

文件的位置：不要把數據文件和用戶數據庫的文件放在同一個磁盤上，這樣對於性能本身是沒有好處了。雖說Tempdb最好放在RAID0上，但是你要專門付出兩塊硬盤來實現RAID0。至少是不要和用戶數據庫的文件放在同一個磁盤上。

其次就是監控Tempdb的空間增長情況

通過Data Collector收集Disk Usage數據，通過觀察過去Tempdb的增長情況來看是否有性能問題。一旦發現有過快的增長，通過下面的語句查找tempdb空間使用最多的會話。看出代碼的特點。再搜索數據庫，找出對應的存儲過程。尋找語句優化空間。還有一個點是Tempdb所在磁盤空間使用是否可以支持它繼續增長？需要制定一個報警線。你可以通過網絡監控軟件或者自己通過使用像sys.dm_os_volume_stats這樣的系統函數來獲取文件系統的磁盤使用信息。自己通過寫一套自己的程序去做這種郵件通知。

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC