[SQL SERVER][Maintain]擴充的事件(2)

[SQL SERVER][Maintain]擴充的事件(2)

上一篇我大概簡單介紹了SQL Server擴充的事件,

這篇我們就來實際應用擴充的事件能幫我解決什麼樣的問題。

 

由於擴充的事件沒有UI畫面(SQL2012 SSMS預設已有),

建議可以安裝 SQL Server 2008 Extended Events SSMS Addin 工具(免費),

這樣在管理、建立和修改上會比較來的順手且方便,

下面我將使用該工具建立相關擴充的事件。

 

顯示擴充的事件總管

image

image

 

1.紀綠某資料庫索引頁面分割次數

新增事件工作階段

image

 

選擇事件

image

 

選擇動作

image

我勾選 sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.username。

 

設定述詞

image

image

 

 

新增目標

image

這裡我選擇 ring_buffer 並設定 max_memory

 

設定選項

image

 

完成後如下圖

image

 

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

image

 

新增兩各 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'

 image

image

(擷取部份)

 

查看死結圖形 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'

image

image

(擷取部份)

 

ps:可以將該 XML 文件儲存為 XDL檔案類型,即可使用 SSMS 顯示死結圖形。

 

 

3.紀錄查詢時間過長

UI設定和前面相同,這裡就不重複。

image

 

image

 

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

image

 

查看物件鎖定數

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

SQL Server 擴充的事件如何主題

Advanced Troubleshooting with Extended Events

Advanced SQL Server 2008 Extended Events with Examples

Using Extended Events to troubleshoot SQL Server issues