Test systematique des locks Sql (primary key design)

Voila un article qui sort également de mon grenier.
Entamé en janvier 2009, je n'ai jamais eu l'occasion de pendre le temps nécessaire à son parachèvement.


Introduction
Une table avec un mauvais design (1*) ou ne disposant pas d'index approprié peut vite devenir un véritable goulot d'étranglement.
Si certains index manquent, SQL server sera amené à poser des locks de façon inconsidéré sur les records durant les opérations de lecture (et ce afin de limiter les accès concurrents au moindre coût pour Sql Serveur).
Si le design n'est pas correct, pour une table de 100.000 enregistrements, un simple SELECT peut temporairement engendrer quelques 30.000 locks... et même sur les pages d'index (cas pour l'exemple 1*) ce qui ralentira considérablement les autres processus voulant accéder cette même ressource (la table).
C'est ainsi que par le passé, une simple requête SQL sur une table contenant des  signatures prenait de 5 à 15 secondes (avec même des timed-out ADO en cas de requêtes concurrentes).
Une simple modification de design et l'utilisation d'un index approprié a ramené ce temps entre 0 et 30 milli-secondes.

Ce qu'il faut savoir:

1. C'est que les locks induits par un mauvais design de table peuvent avoir des conséquences importantes sur les ressources SQL. Dans ce cas, SQL serveur peut être amené à manipuler des dizaines de milliers de locks pour satisfaire les différentes requêtes.
   Cela ce traduit par une lenteur excessive SQL, un CPU SQL > 60%. La stored procedure sp_locks retournant dans ce cas plusieurs dizaines de milliers d'entrées... dont la plupart ne concernant qu'un seul objet (comprenez "table").
   Il est aisé d'identifier et cerner ce problème, un simple sp_lock faisant apparaître l'évidence.
   
2. C'est que certains mécanismes de lockings n'apparaîtrons qu'en accès concurrents... lorsque les mécanismes de locking s'escaladeront les uns sur les autres.
   Par exemple, la simple exécution d'une variante d'un même SQL depuis plusieurs threads peut créer cette situation de locking (entre 500 et 1500 locks, voir plus).... alors qu'à contrario, l'exécution unique de la même requête ne révélera absolument rien.
   En soit, c'est moins grave... cela rends seulement la détection des locks en accès concurrents plus difficile (par exemple plusieurs personnes accèdent en même temps une même page sur un site web).
   

1*) Un exemple de mauvais design: La primary key

Un exemple typique de mauvais design est l'utilisation d'une "Clustered Primary Key" sur un champ unique contenant  une valeur incrémentée.
C'est le cas des tables utilisant un ID unique comme identifiant... cas largement
répandu dans l'industrie.
Ce point fera l'objet d'un article plus approfondit.

Test systématique des locks
Le test systématique des locks à l'aide de perfMon durant les phases de tests soutenues (alpha ou en charge) permet de suivre l'évolution (l'escalade) des locks durant ces phases.
Ainsi, stresser une application/fenêtres/pages web (en répétition simple et/ou en séquences) tout en gardant l'oeil sur perfMon, permet de se faire une idée des conditions, problèmes et congestions potentiels en accès concurrent.
Si de surcroît le  Profiler Sql est utilisé durant cette phase, il est même possible d'identifier plus précisément les requêtes qui posent problèmes (voir temps d'exécution, nombre d'I/O, ... dans le profiler).

La première étape restant, avant tout, d'identifier visuellement les zones/séquences de l'application causant des heurts pour ensuite regarder de plus près (avec sp_locks, profiler sql).



Configurer PerfMon pour un test systématique


Etape 1: Modifier les settings du performance monitor (PerfMon)

Par défaut, le performance monitor affiche le graph avec une échelle de 0 à 100.
Il en est ainsi parce que la plupart des compteurs/traces s'expriment en % de ressources.
Dans le cas qui nous concerne, il en va tout autrement. Les traces que nous allons ajouter contiennent des valeurs absolues (du genre 120 locks ou 4312 locks).
Si l'on veut les afficher correctement, c'est à dire sans que les traces ne passent leur temps à quitter le graphique, il faudra modifier l'échelle d'affichage.
Il sera ainsi possible de suivre visuellement l'évolution les locks pour détecter quand il conviendra d'agir (par exemple quand la valeur s'élèvera au dessus de 1500 locks).

Voici donc la marche a suivre: 

Dans les propriétés du graphe, sélectionner l'onglet "Graph" et modifier la valeur Maximum.
Dans un premier temps, la valeur 2000 sera tout à fait raisonnable pour une machine de développement.
En environnement de production, ou les accès concurrents sont fréquents, une valeur Maximum de 2000 pourrait bien être insuffisant.

Etape 2: Sélectionner les traces qui seront monitorées

Ajouter une trace

 

La boite de dialogue "Add Counter" apparaît... 

Dans cette dernière, sélectionner "Lock Request/Sec".

Notez que Key, Page et RID sont sélectionnés dans la sous liste. En effet, suivant les cas, ce sera plutôt un type de lock ou l'autre qui sera utilisé par Sql serveur... dans tous les cas, l'élévation de ces locks peuvent avoir une influence majeure sur les performances de Sql Serveur.

Voici ci-dessous une liste exhaustive des locks monitorés.

Le gros défaut de cette méthode graphique


Il n'est pas possible d'identifier facilement les locks portant sur les tables TABLOCK.
En effet, il ne faut qu'un seul tablock pour bloquer une table entière alors que le graph affiche les valeurs de 0 à 2000. Autant dire que les tablocks ne sont pas visibles sur le graph puisqu'ils se confondront avec l'abscisse.
Cependant le TABLOCK reste un événement exceptionnel, Sql Serveur n'y a recours que lorsqu'il n'est pas possible de faire autrement.
Cependant, l'usage de TabLock par SQL serveur a également des conséquences catastrophiques sur les performances/temps de réponse en accès concurrent (en effet, les autres processus/requêtes accédant cette même ressource/table restent en attente).

Quand le Tablock frappe...
C'est entre autre l'événement qui nous est arrivé, un TABLOCK bloquait la primary key de notre table d'audit (19 millions de records) durant les requêtes de lecture.
Du coup, le software n'était plus capable d'insérer de nouveaux enregistrements dans l'audit trail (Gloups!) et ce pendant des périodes de 1 à 4 minutes.