[SQL SERVER][Maintain]擴充的事件(2)
上一篇我大概簡單介紹了SQL Server擴充的事件，
這篇我們就來實際應用擴充的事件能幫我解決什麼樣的問題。
由於擴充的事件沒有UI畫面(SQL2012 SSMS預設已有)，
建議可以安裝 SQL Server 2008 Extended Events SSMS Addin 工具(免費)，
這樣在管理、建立和修改上會比較來的順手且方便，
下面我將使用該工具建立相關擴充的事件。
顯示擴充的事件總管
1.紀綠某資料庫索引頁面分割次數
新增事件工作階段
選擇事件
選擇動作
我勾選 sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.username。
設定述詞
新增目標
這裡我選擇 ring_buffer 並設定 max_memory
設定選項
完成後如下圖
Script
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='IndexSplitCounts')
DROP EVENT SESSION [IndexSplitCounts] ON SERVER;
CREATE EVENT SESSION [IndexSplitCounts]
ON SERVER
ADD EVENT sqlserver.page_split(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.username)
WHERE (([sqlserver].[database_id]=(7))))
ADD TARGET package0.ring_buffer(
SET max_memory=4096)
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)
ALTER EVENT SESSION [IndexSplitCounts] ON SERVER STATE = START
2.紀錄死結
Events:xml_deadlock_report
新增兩各 SSMS 查詢視窗並執行以下陳述句
begin tran
update t1 set c2='bb'
where c1=2
waitfor delay '00:00:03'
select * from t2 where c1=2
begin tran
update t2 set c2='bb'
where c1=2
waitfor delay '00:00:03'
select * from t1 where c1=2
查看 system_health XML 文件
select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health'
(擷取部份)
查看死結圖形 XML 文件
select CAST(
REPLACE(
REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victim-list>', '<deadlock><victim-list>'),
'<process-list>','</victim-list><process-list>')
as xml) as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
(擷取部份)
ps:可以將該 XML 文件儲存為 XDL檔案類型，即可使用 SSMS 顯示死結圖形。
3.紀錄查詢時間過長
UI設定和前面相同，這裡就不重複。
Script:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongQuery')
DROP EVENT SESSION [LongQuery] ON SERVER;
CREATE EVENT SESSION [LongQuery]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.username)
WHERE (([duration]>=(60000) AND [source_database_id]=(7))))
ADD TARGET package0.ring_buffer(
SET max_memory=4096)
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)
ALTER EVENT SESSION [LongQuery] ON SERVER STATE = START
4.紀錄物件鎖定數
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
DROP EVENT SESSION [LockCounts] ON SERVER;
CREATE EVENT SESSION [LockCounts]
ON SERVER
ADD EVENT sqlserver.lock_acquired(
WHERE (([database_id]=(7)))) -- 填入目標資料庫ID
ADD TARGET package0.synchronous_bucketizer(
SET filtering_event_name='sqlserver.lock_acquired', source_type=0, source='resource_0')
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)
ALTER EVENT SESSION [LockCounts] ON SERVER STATE = START
查看物件鎖定數
SELECT name, object_id, lock_count FROM
(SELECT objstats.value('.','bigint') AS lobject_id,
objstats.value('@count', 'bigint') AS lock_count
FROM (
SELECT CAST(xest.target_data AS XML)
LockData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//BucketizerTarget/Slot') AS T(objstats)
) LockedObjects
INNER JOIN sys.objects o
ON LockedObjects.lobject_id = o.object_id
WHERE o.type != 'S' AND o.type = 'U'
ORDER BY lock_count desc
GO
參考
Using SQL Server 2008 Extended Events
Advanced Troubleshooting with Extended Events