Discussion :

[StringBuilder]

Bonjour,

Pour ne pas polluer le topic de SQLpro à propos des index inutiles, je préfère créer un nouveau sujet.

Après avoir regardé la vidéo Youtube à propos des statistiques sous SQL Server 2014 j'ai quelques questions.

Jusqu'à présent je ne m'étais jamais trop posé la question de ce qu'étaient les statistiques ni de ce à quoi elles seraient vraiment, partant du principe qu'elles étaient plus ou moins pilotées par les index créés, et donc à priori "bonnes" 95% du temps.

Mais la vidéo me faut poser pas mal de question.

Avec les statistiques, on voit que le nerf de la guerre c'est d'obtenir des cardinalités les plus justes possibles lors de l'estimation du plan.
Par contre, je ne vois pas exactement quel est l'impact d'avoir une erreur à ce niveau.

Jusqu'à présent j'imaginais qu'en fonction des cardinalités, SQL Server allait éventuellement préférer tel ou tel index, notamment pour choisir celui le plus filtrant dès la première colonne... et basta.

Seulement, quand on regarde la vidéo (

), à un moment deux requêtes identiques sont lancées sur une table non indexée, avec le mode d'optimisation de SQL Server 2012 puis celui de SQL Server 2014.
Les cardinalités sont différentes, ok, pas de souci, j'ai tout bien compris.

Par contre, ce que je ne comprend pas, c'est que derrière, vu qu'il n'y a pas d'index, à priori cardinalités correctes ou non, le résultat sera le même : full scan de la table pour retrouver les lignes correspondantes aux requêtes... qu'on soit en 2012 ou en 2014...
Pourtant, la répartition des temps de traitement dans le batch n'est pas du tout égale au minutale 33:45

La première requête, différente, prend 54% du temps. Ok.
Mais les deux suivantes, y'a que le querytraceon qui change. Pourtant en mode SQL Server 2012 ça dure 16% du lot et en mode SQL Server 2014 ça dure 30%... soit le double.

Etant donné que les deux dernières requêtes sont identiques, on peut estimer que le premier fullscan aura chargé les données en cache, donc la troisième requête sera aussi lente que la seconde, voir, en toute probabilité, plus rapide. Et c'est l'inverse qui se produit. On voit dans ce cas d'ailleurs que la cardinalité estimée par SQL Server 2014 est bien moins bonne qu'en mode 2012... Mais comment, sans utilisation d'index ensuite, ça peut changer quoi que ce soit ???
D'ailleurs, ormi ces différences de cardinalité, le plan est bel et bien le même...
Ou si c'est juste le PC de démo qui a traité un truc en même temps et ça a faussé le temps d'exécution ?

Bon, ça c'était pour mon côté emmerdeur, j'ai quelques autres questions :
- Etant donné qu'on peut faire des statistiques filtrées et des index filtrés : où indiquer les filtres ? Plutôt dans les index ou dans les statistiques ? Les deux ? J'aurais naïvement tendance à dire au niveau des statistiques mais pas dans les index pour savoir choisir l'index en connaissance de cause, mais ne pas restreindre l'utilisation de l'index aux seuls cas du filtre... Est-ce complètement débile de raisonner comme ça ?
- Que se passe-t-il si je fais deux statistiques filtrées : une sur la date de commande (> au 1er janvier) et un flag "non réglé" si je recherche ensuite les commande de l'année non encore payée ?

[SQLpro]

Bonjour,

comme je suis un des co auteurs de cette vidé, la réponse est assez simple, mais en 2 points...

1) Les statistiques ne se retrouvent pas que sur les index, mais aussi sur les colonnes des tables.
les statistiques d'index permettent de savoir si l'optimiseur doit prendre l'index, mais comme il arrive que toutes les colonnes filtrées ne figurent pas dans l'index, les stats de colonnes permettent de confirmer ou d'infirmer cette hypothèse.
Exemple : WHERE C1 = 1 AND C2 = 2. Si index seulement sur C1, on peut supposer que la cardinalité sera au plus celle estimée pour C1. Si cette cardinalité est importante, mettons 30% de la table, alors trop de lignes=> scan de table... sauf si C2 à une cardinalité faible dont la corrélation engendrerait par exemple moins de 5% de la table

2) il existe de nombreux algorithmes pour chacune des opération du plan de requête. Par exemple dans SQL Server il existe actuellement 5 algorithmes de jointure. L'estimation d cardinalité de part et d'autre de la jointure va décider quel algorithme sera utilisé. Par exemple la jointure par boucle imbriquée est intéressant si l'un des deux table au moins à peu de ligne, mais très mauvaise si les deux tables ont beaucoup de lignes. Un écart d'estimation peut donc faire basculer l'algorithme du plan... Enfin, derrière les algorithmes il y a de la gestion de ressources... Si une estimation est plus basse que la réalité, alors les ressources allouées devront être "agrandies" au cours de l'exécution... Cela n'est pas gratuit. Pour information depuis la version 2019, et "lintelligent query processing" il y a ajustement de ces ressources en fonction des exécutions passées pour les nouvelles requêtes.

A +