[SQL]監看 TempDB 的狀況
最近跟朋友討論到如何去監看 TempDB 一下子爆增的情況，要找出到底那個Session去把它給搞大的。
所以google了一下，看到這篇「SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events」，非常建議大家看一下。
個人將重點整理如下，
如果您的DB是SQL 2005的話，可以用以下的DMV來看TempDB(有Block，所以如果沒有Block的話，就找不出來!)
--Look for PFS and GAM pages '2:1:1' and '2:1:3'
--'2:1:1'/'2:1:3' is really 2: tempdb, 1: file number, 1: PFS, 3: GAM
SELECT
session_id
, wait_duration_ms
, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%' AND Resource_description LIKE '2.%'以上的SQL是看執行SQL目前的狀況，所以如果要看以前的狀況，則要看 sys.dm_os_wait_stats 。
但 sys.dm_os_wait_stats 只提供統計資訊，所以無法得知，到底是那個 Session 搞的。
因為SQL 2005沒有 Extended Events，SQL 2008(含)以後才有。所以在SQL 2008時，我們可以建立去監看 TempDB 的 Extended Events，如下，
--Drop the event if it already exists
DROP EVENT SESSION Monitor_wait_info_tempdb ON SERVER;
GO
--Create the event
CREATE EVENT SESSION Monitor_wait_info_tempdb ON SERVER
--We are looking at wait info only
ADD EVENT sqlos.wait_info
(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id, sqlserver.tsql_stack)
WHERE sqlserver.database_id = 2 --filter database id = 2 i.e tempdb
--This allows us to track wait statistics at database granularity
) --As a best practise use asynchronous file target, reduces overhead.
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\Monitor_wait_info_tempdb.etl', metadatafile='c:\Monitor_wait_info_tempdb.mta')
GO
--Now start the session
ALTER EVENT SESSION Monitor_wait_info_tempdb ON SERVER
STATE = START;
GO
以上SQL，filename請依環境不同來調整它。
建立好 Extended Events 後，就可以查看 TempDB 透過以下的SQL來查看這些記錄，如下，
SELECT wait_typeName
, SUM(total_duration) AS total_duration
, SUM(signal_duration) AS total_signal_duration
FROM (
SELECT
FinalData.R.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.R.value ('data(data/value)[1]', 'nvarchar(50)') AS wait_typeValue,
FinalData.R.value ('data(data/text)[1]', 'nvarchar(50)') AS wait_typeName,
FinalData.R.value ('data(data/value)[5]', 'int') AS total_duration,
FinalData.R.value ('data(data/value)[6]', 'int') AS signal_duration,
FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID,
FinalData.R.value ('(action/.)[2]', 'nvarchar(50)') AS SQLText,
FinalData.R.value ('(action/.)[3]', 'nvarchar(50)') AS SessionID
FROM
( SELECT CONVERT(xml, event_data) AS xmldata
FROM sys.fn_xe_file_target_read_file
('c:\Monitor_wait_info_tempdb*.etl', 'c:\Monitor_wait_info_tempdb*.mta', NULL, NULL)
) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)) xyz
WHERE wait_typeName NOT IN ('SLEEP_TASK')
GROUP BY wait_typeName
ORDER BY total_duration以上是彙整資訊，如果要看單個Session資訊的話，就將中間的SQL抽出來Run就可以了，如下，
SELECT
FinalData.R.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.R.value ('data(data/value)[1]', 'nvarchar(50)') AS wait_typeValue,
FinalData.R.value ('data(data/text)[1]', 'nvarchar(50)') AS wait_typeName,
FinalData.R.value ('data(data/value)[5]', 'int') AS total_duration,
FinalData.R.value ('data(data/value)[6]', 'int') AS signal_duration,
FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID,
FinalData.R.value ('(action/.)[2]', 'nvarchar(4000)') AS SQLText,
FinalData.R.value ('(action/.)[3]', 'nvarchar(50)') AS SessionID
FROM
( SELECT CONVERT(xml, event_data) AS xmldata
FROM sys.fn_xe_file_target_read_file
('c:\Monitor_wait_info_tempdb*.etl', 'c:\Monitor_wait_info_tempdb*.mta', NULL, NULL)
) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)
ORDER BY total_duration DESCHi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
