231
7.5 Blocages et deadlocks
dans un travail de l’agent SQL planifié pour s’exécuter toutes les quelques minutes
une requête comme celle-ci (adaptée à SQL Server 2005-2008) :
SELECT
es2.session_id as spid_blocking,
es1.session_id as spid_blocked,
er1.start_time as blocked_start,
er1.row_count,
CASE er1.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'Readcommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END as transaction_isolation_level,
DB_NAME(er1.database_id) as db,
est1.text as sql_command_blocked,
er1.wait_type,
es1.host_name as host_name_blocked,
es1.program_name as program_name_blocked,
es1.login_name as login_name_blocked,
es2.host_name as host_name_blocking,
es2.program_name as program_name_blocking,
es2.login_name as login_name_blocking
FROM sys.dm_exec_requests er1
JOIN sys.dm_exec_sessions es1
ON er1.session_id = es1.session_id
CROSS APPLY sys.dm_exec_sql_text(er1.sql_handle) est1
JOIN sys.dm_exec_sessions es2
ON er1.blocking_session_id = es2.session_id
ORDER BY spid_blocking
qui envoie son résultat, s’il y en a un, par e-mail, à l’aide de Database Mail.
Il existe maintenant une méthode intégrée qui utilise la notification d’événements (event notification). Cette technologie est basée sur les événements DDL (Data
Definition Language) disponibles depuis SQL Server 2005 notamment pour écrire des
déclencheurs DDL, ainsi que sur Service Broker (voir ci-dessous). Elle permet de
répondre à des événements en mode asynchrone, et d’envoyer ces événements aussi
bien sur le serveur SQL local que sur une machine distante. L’événement que nous
allons utiliser s’appelle BLOCKED_PROCESS_REPORT.
Cet événement doit être activé dans les options du serveur pour être déclenché
lorsqu’un processus est bloqué depuis un certain temps.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'blocked process threshold', 30
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO