Il est difficile de détecter en temps réel les blocages dans une base de données. C’est pour cette raison qu’il est important de configurer SQL Extended Event. Cet outil vous permettra d’enregistrer l’historique des blocages.
Avant de configurer une session de type Extended Event, vous devez créer un dossier C:\SQLTRACE sur le serveur. Ensuite, vous pouvez exécuter la requête suivante qui vient de la solution DynamicsPerf 2.0. Celle-ci configure une session afin de collecter les requêtes bloquées pour plus de 2 secondes.
USE [master]
GO
sp_configure 'Show Advanced Options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'blocked process threshold', 2
RECONFIGURE WITH OVERRIDE
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='DYNPERF_BLOCKING_DATA')
DROP EVENT SESSION DYNPERF_BLOCKING_DATA ON SERVER
GO
CREATE EVENT SESSION [DYNPERF_BLOCKING_DATA] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.context_info)),
ADD EVENT sqlserver.xml_deadlock_report(
ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.context_info)),
ADD EVENT sqlserver.lock_escalation(
ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.context_info))
ADD TARGET package0.event_file(SET filename=N'C:\SQLTrace\DYNAMICS_BLOCKING.xel',max_file_size=(10),max_rollover_files=(100))
--ADD TARGET package0.ring_buffer(SET max_memory=(131072))
WITH (MAX_MEMORY=4096 KB,MAX_DISPATCH_LATENCY=5 SECONDS,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [DYNPERF_BLOCKING_DATA] ON SERVER
STATE=START
GO
Ensuite, les événements sont collectés dans les fichiers .xel
Ensuite, vous pouvez analyser les évènements avec la requête suivante:
SELECT TOP 100 *
FROM (SELECT event_data.value('(event/@name)[1]', 'varchar(50)')
AS EVENT_NAME,
DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS END_TIME,
event_data.value('(event/data[@name="duration"]/value)[1]', 'decimal(38,3)') / 1000
AS DURATION,
event_data.value('(event/data[@name="object_id"]/value)[1]', 'int')
AS OBJECT_ID,
event_data.value('(event/data[@name="resource_owner_type"]/value)[1]', 'varchar(max)')
AS RESOURCE_OWNER_TYPE,
event_data.value('(event/data[@name="index_id"]/value)[1]', 'int')
AS INDEX_ID,
event_data.value('(event/data[@name="lock_mode"]/value)[1]', 'varchar(max)')
AS LOCK_MODE,
event_data.value('(event/data[@name="transaction_id"]/value)[1]', 'bigint')
AS TRANSACTION_ID,
event_data.value('(event/data[@name="database_name"]/value)[1]', 'varchar(max)')
AS DATABASE_NAME,
event_data
AS EVENT_DATA
FROM (SELECT CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file('C:\SQLTRACE\DYNAMICS_BLOCKING*.XEL', NULL, NULL, NULL)) AS evts ( event_data )) AS DYNPERF_BLOCKING
WHERE EVENT_NAME = 'blocked_process_report'
--AND END_TIME BETWEEN '2016-02-13 10:19:37.5520000' AND '2016-02-13 10:21:17.9560000'
ORDER BY END_TIME DESC;
Vous pouvez cliquer sur le lien dans la colonne EVENT_DATA et pouvez y voir plusieurs informations intéressantes:
- Blocked-process
- Waitresource
- Waittime
- Lockmode
- Status
- Hostname
- Isolationlevel
