Blog

XEvent : Processus bloqués et bloquant


Une requête peut être mise en attente dans le cas où un autre process monopolise déjà la même ressource (RID, Key, Page, etc.). La durée du blocage peut-être plus ou moins pénalisante et la cause très variable : requête à optimiser, indexation non pertinente, niveau d’isolation non adapté, etc.

Expert SQL Server - XEvent : Processus bloqués et bloquant - SQL Server  - blocked_process

Dans un article précédent, la procédure stockée sp_whoisactive a été mentionnée. Elle permet à d’identifier les requêtes en cours sur une instance SQL Server, qu’elles soient coûteuses et/ou bloquées et bloquantes.

Voir sp_whoisactive : http://www.concatskills.com/2016/12/10/process-actifs-instance-sql-server/

Problème : Au moment où on nous signale des lenteurs sur une instance SQL Server, il est parfois trop tard pour identifier le processus en cause, autrement dit la tempête est passée. L’historisation des processus bloqués et bloquant est possible via les évènements étendus, XEvent. La finalité est de pouvoir les collecter et les analyser rétroactivement. A partir de SQL Server 2012, les évènements étendus sont pleinement fonctionnels (disponibles dès SQL Server 2008 mais immatures). Le cas échéant, on utilisera le profiler (il est peut-être temps d’envisager une migration pour les versions concernées). Les traces XEvent peuvent être stockées en mémoire et/ou dans des fichiers. Pour un stockage physique, il est primordial de définir une taille MAX par fichier pour éviter de saturer les disques. Le ROLLOVER est activé par défaut à 5 fichiers. La taille des fichiers enfin ne doit pas être trop importante (quelques Mo) pour faciliter le parsing XML Il vaut mieux avoir beaucoup de petits fichiers que peu et volumineux, en sachant que chaque fichier peut être analysé individuellement.

Les traces XEvent sont disponibles dans la section ci-dessous :

Expert SQL Server - XEvent : Processus bloqués et bloquant - SQL Server  - xevent_list

Etape 1

Cette étape consiste à modifier le seuil de détection des blocages en seconde au niveau de l’instance. Dans le cas présent tous les blocages ayant une durée minimum de 10 secondes seront remontés dans la trace XEvent. Ce changement de paramètre n’a aucun impact au niveau instance, en revanche plus son seuil est bas et plus la trace chargée d’historiser les blocages sera volumineuse et coûteuse.

Ce paramètre est évoqué dans l’article dédié au Tuning d’instance : http://www.concatskills.com/2016/10/20/tuning-dinstance-sql-server/

Etape 2

Nous allons créer une arborescence de répertoires sur laquelle le compte de service SQL Server aura le droit d’écrire. Le répertoire blocked_process stockera les traces XEvent concernées.

Expert SQL Server - XEvent : Processus bloqués et bloquant - SQL Server  - xevent_dir

Etape 3

Création de la trace XEvent en spécifiant le nom blocked_process et le chemin D:\XEvent\blocked_process\blocked_process.xel. Notez qu’il y a un rollover de défini sur 5 fichiers par défaut pour une taille limitée à 5 Mo par fichier, les traces n’étant pas supposée saturer les disques.

Etape 4

Démarrage de la trace XEvent blocked_process

Consultation des processus bloqués

Le détail des processus bloqués et bloquant peut être consulté en parsant le XML des traces XEvent générées et le résultat reste à historiser dans une table au besoin. Il est désormais possible de savoir qui (hostname, application name, etc…) a bloqué qui, à quelle heure, pendant combien de temps avec les requêtes associées aux processus bloqué et bloquant et enfin la ressource concernée.

 

 

Auteur

Expert SQL Server - XEvent : Processus bloqués et bloquant - SQL Server  - avatar_ninja_tete-150x150
Sarah Bessard
Experte SQL Server Prod/Etude avec un bonus sur la BI, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur. Besoin d'une expertise SQL Server ? N'hésitez pas à me contacter.

Leave a comment

Your email address will not be published.

error: