Identifier les problèmes de blocages
Les blocages proviennent de verrous maintenus sur les mêmes ressources par des dessions différentes.
Savoir si des blocages se sont déjà produits
Interrogez la requête suivante : Paul Randall, tell me where it hurts et cherchez les attentes de type LCK...
Identifier les blocages en cours
Vous pouvez identifier les verrous en cours à un instant T à l’aide de la requête suivante : analyze-blocked-sessions.sql.
Si vous voyez des attentes sur des wait_type qui commencent par LCK, il s’agit de blocages. Regardez la valeur de wait_duration_sec pour savoir depuis combien de secondes cette session attend.
Mode de verrouillage
Le mode de verrouillage donne plus d’indication sur le verrou qui est en attente.
| mode | explication |
|---|---|
| LCK_M_IX | on essaie d’acquérir un verrous d’intention d’écriture. |
Tracer les cas de blocages
Pour tracer les cas de blocage et remonter plus facilement à la source, utilisez une session d’évènements étendus.
D’abord, définissez sur votre instance le seuil de processus bloqués. Après combien de secondes voulez-vous qu’un évènement soit déclenché ? Vous le définissez à l’aide de la commande suivante :
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'blocked process threshold (s)', N'10'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
GO
Ici, nous demandons une génération d’évènements après dix secondes de blocage.
Vous pouvez aussi le configurer dans SSMS dans les propriétés de l’instance, comme illustré dans la copie d’écran ci-dessous.
Démarrage de la session d’évènements
Créez ensuite la session d’évènements à l’aide de la commande que vous trouvez à cette adresse.
N’exécutez que la première partie de la requête, celle qui contient la commande CREATE EVENT SESSION.
Ensuite, démarrez la session à l’aide de la commande suivante que vous trouvez aussi dans le code source :
ALTER EVENT SESSION [blocked_processes] ON SERVER STATE=START;
Récupération des fichiers de la session d’évènements
La session d’événements que vous avez créée ci-dessus enregistre des fichiers de traces qui portent l’extension .xel dans le répertoire des journaux d’erreur de SQL Server par défaut.
Vous pouvez trouver le chemin de ce répertoire sur le serveur, à l’aide de la requête suivante :
SELECT SERVERPROPERTY('ErrorLogFileName')
Vous y trouvez les fichiers blocked_processes*.xel, que vous pouvez zipper pour les récupérer. La compression va être très importante.