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;
