Extended Events to narzędzie dzięki, któremu można wykonywać bardzo skomplikowane operacje przechwytywania rozmaitych informacji. Jeśli stwierdzimy, że mamy problem z blokowanymi procesami można za pomocą XE wyciągnąć odpowiedni raport o zablokowanych sesjach.

Aby przechwycić raport o zablokowanym procesie czy procesach, musimy najpierw włączyć odpowiednią opcję konfiguracyjną systemu informującą o progach zablokowanego procesu. Dobra wartość początkowa to 20, czyli próg w sekundach, przy którym generowany jest raport. Po przekroczeniu tego czasu XE zaczyna zbierać dane o blokadach. W celu ustawienia tej wartości należy uruchomić skrypt przedstawiony poniżej.

--blocked process threshold (s)	0	86400	0	0
--blocked process threshold ustawia się tam wartość w sekundach 
--po jakim czasie mają być generowane eventy w przypadku blokad
--Wartość domyślna to 0 - brak generowanych eventów typu blocked session
--Wartość minimalna to 5 (5 sekund)
--Wartość maksymalna to 86400 s.
--SELECT 86400/60/60 = 24 h

exec sp_configure 'show advanced options',1
go
reconfigure
GO

-- 5 to wartość minimalna. 5 sekund blokady na wyzwolenie eventu
exec sp_configure 'blocked process threshold',5
go 
reconfigure
go

exec sp_configure 'show advanced options',0
reconfigure

Poniżej skrypt zakładający sesję XE, która będzie przechwytywała zdarzenie czyli informacje o zablokowanych procesach trwających dłużej niż wartość ustawiona w parametrze powyżej.

-- check to see if the event session exists 

IF EXISTS ( SELECT  1
FROM    sys.server_event_sessions
WHERE   name = 'Capture_BlockedProcessReport' )
DROP EVENT SESSION [Capture_BlockedProcessReport] ON SERVER;
 
-- create the event session

CREATE EVENT SESSION [Capture_BlockedProcessReport]
ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(
                               SET filename=N'K:\XE\Capture_BlockedProcessReport.xel'
							  ,max_file_size=(10)
							  ,max_rollover_files=(50)
)
WITH 
(MAX_MEMORY=8192 KB
,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY=30 SECONDS
,MAX_EVENT_SIZE=0 KB
,MEMORY_PARTITION_MODE=NONE
,TRACK_CAUSALITY=OFF
,STARTUP_STATE=ON
);
 

W celu wyświetlenia zebranych danych przechwyconych przez XE: można otworzyć plik .xel w SSMS lub uruchomić poniższy skrypt, który używając funkcji sys.fn_xe_file_target_read_file odczyta dane z pliku.

SELECT
event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS timestamp,
event_data.value('(event/@database_id)[1]', 'varchar(10)') AS database_id,
db_name(event_data.value('(event/data[@name="database_id"]/value)[1]','int')) as [database_id],
event_data.value('(event/data[@name="lock_mode"]/text)[1]','varchar(10)') as [lock_mode],
event_data.value('(event/data[@name="resource_owner_type"]/text)[1]','varchar(10)') as [resource_owner_type],
event_data.query('(event/data[@name="blocked_process"]/value/blocked-process-report)[1]') as [blocked_process_report]
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('K:\XE\Capture_BlockedProcessReport*.xel', NULL, NULL, NULL)
) AS blocked;



Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl