Si une bonne partie de votre temps de travail est consacrée à l’optimisation de requête et à la compréhension du fonctionnement de l’Optimiseur de Requêtes, vous vous êtes probablement demandé sur quelles statistiques celui-ci se base pour calculer le plan d’une requête.

Il existe un drapeau de trace non-documenté, n° 8666, qui permet d’ajouter au document XML qui décrit le plan de requête les statistiques utilisées par l’optimiseur pour générer le plan de requêtes.

Voyons comment l’utiliser avec une requête sur la base de données AdventureWorks2012 :

Avant toute chose, notez que ce drapeau de trace n’est pas documenté. Comme toute fonctionnalité non-documentée, on ne doit JAMAIS utiliser celui-ci sur une base de données ou une instance qui sert une charge de production. Si tel devait être le cas, je ne peux en aucun cas être tenu pour responsable des conséquences. Vous devez limiter strictement l’usage de cette instruction aux environnements de test.

Utilisons la requête suivante :

Demandons maintenant à SQL Server de générer le plan de requête estimé. Celui-ci s’obtient soit en pressant CTRL + L, soit par un clic sur l’icône suivante, présente dans la barre d’outils :

On obtiendra aussi les informations ajoutées par le drapeau de trace 8666 avec le plan d’exécution réel, que l’on obtient soit en pressant CTRL + M, soit par un clic sur l’icône, suivante présente elle aussi dans la barre d’outils :

Nous devons maintenant explorer le document XML à l’origine du plan d’exécution graphique, ce qui se fait par un simple clic droit dans une zone vierge de ce dernier :

Il nous suffit maintenant de chercher la chaîne de caractères wszStatName, et nous obtenons :

On voit clairement les détails de ce que SQL Server utilise comme métriques pour calculer le plan, notamment :

– le nom de la statistique (ici c’est la statistiques sous-jacente à l’index qui supporte la clé primaire)
– le nom de la colonne la plus à gauche dans cette statistique (wszColName)
Рle nombre de lignes ̩chantillonn̩es lors de la g̩n̩ration de la statique (m_ullSnapShotModCtr)
Рle nombre de lignes pr̩sentes dans la table (m_ullRowCount)
– le seuil de recalcul automatique de la statistique (ullThreshold)

Pour cette dernière valeur, on peut d’ailleurs faire le calcul suivant :

100.0 * 24,763 / 121,317 = 20.41181367821492

Soit environ 20%. Prenons la partie décimale de ce pourcentage :

(0.41181367821492 / 100.0) * 121,317 = 499.5999999999945

Soit environ 500 lignes. Ceci vérifie bien la règle du seuil de recalcul automatique des statistiques pour les tables de plus de 8MB : 500 lignes + 20% du nombre de lignes de la table.

Bons calculs de plans de requêtes et estimations de cardinalités !

ElSüket