Statistiques et plan d'execution

Question

Bonjour,

Situation :

1- une procédure stockée qui fait essentiellement un select sur plusieurs tables dont 1 d'une autre base.

2- les statistiques sont recalculées tous les jours pour les 2 bases et plus précisément à 03:30:00 pour la table de la base "externe"

3- la table "externe"

  3.1 contient 315 688 021 lignes, en perpétuel accroissement

  3.2 a un histogramme de 33 étapes - relativement stable car seules les 3 dernières étapes évoluent jour après jour (glissement des valeurs de "range_high_key")

Problème :

Si on recalcule la statistique à 09:00:00 (et uniquement celle-ci) le temps d’exécution est divisé par 10 et -bien sûr- le plan change.

Par la suite le plan reste stable toute la journée.

Questions :

1- Comment déterminer POURQUOI la statistique de 03:30:00 est délaissée.

2- Comment déterminer à partir de QUAND la statistique de 03:30:00 est délaissée.

Answer 1

Bonjour

Une procédure stockée est compilée lors du premier appel. Donc avec les stats de ce moment là. Si l'on invalide la plan, par un recalcul de stat, un vidage du cache de procédure, une réindexation ou un WITh recompile sur la procédure, alors il va de nouveau y avoir une analyse et les statistiques seront utilisée, encore une fois avec leur valeur à ce moment là.

Donc, soit les cardinalités changent entre 3:30 et 9:00 sans être reflété dans les stats (cela est aussi fonction de la version de sql server, de traceflags, ...) soit le plan d'exécution a été claculé sur une valeur atypique et donc un plan d'exécution qui ne correspond pas à l avaleur passé après 9h00...

Il peut aussi y avoir un problème de parameter sniffing, il faut voir le code pour cela, mais le test à fare, avant le recalcul de la stat à 9h00, faire l'exec de la procedure avec l'option recompile.

cdlt

Christophe

---

Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

Answer 2

Bonjour Christophe,

La re-compilation -même systématique- de la procédure n'apporte rien. Il faut aussi exclure le problème de la valeur atypique pour la même raison.

Le cache des plans d’exécution ne comporte jamais qu'une seule version de plan.

Je suis par contre plus intéressé par comprendre le fond de ta pensée quand tu dis :
"les cardinalités changent entre 3:30 et 9:00 sans être reflété dans les stats" alors même que la re-collecte de celle-ci modifie le comportement.

Actuellement le "problème" a été fixé par un "plan guide" mais pour la maintenance et pour ma compréhension du système cela ne me satisfait pas.

En tous les cas merci pour ton aide

Answer 3

L'idée qui sa cache derrière est liée au seuil de déclanchement d'invalidation de la statistique d'index.

Il faudrait que tu capture le plan d'exécution avant recalcul de stat a 9h00 et voir si par exemple il ne repose pas du le vecteur de densité.

Et le comparer avec le plan suivant l'update stats.

Autre piste : est-ce que les paramètre de session sont identiques, car ils peuvent aussi influer sur le plan d'exec.

Essaie aussi de voir si un index filtré ,ou même une stat filtrée sur quelques jours ne corrige pas le pbm.

Cdlt
Christophe

---

Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

Answer 4

Re,

* Il faudrait que tu capture le plan d'exécution avant recalcul de stat a 9h00

  Les plan d’exécution ont été capturés avant et après ; c'est ma base de travail :)

* et voir si par exemple il ne repose pas du le vecteur de densité.

Comment valider ce point ?

Comme déjà dit : la table fait plusieurs millions de lignes, la collecte de statistiques ne porte que sur les 3 derniers histogrammes ; l'analyse des modifications montre un 'glissement' des bornes sans réelle modification des valeurs de l'histogramme lui même.

* Autre piste : est-ce que les paramètre de session sont identiques, car ils peuvent aussi influer sur le plan d'exec.

Oui ils sont identiques ; J'ai fait une procédure 1- capture avant 2- collecte des stat 3- capture après et je corrèle ça avec une trace sur l'activité en prod.

* Essaie aussi de voir si un index filtré ,

le filtre est sur une jointure de la clé ; pas facile de créer un filtre dans ce cas ; une suggestion ?

* ou même une stat filtrée sur quelques jours ne corrige pas le pbm.

Je ne sais pas créer une stat filtrée ; comment fait-on ?

Answer 5

Merci Christophe !

J'ai marqué ton premier post comme "bonne réponse" mais je me permet de commenter pourquoi.

La partie applicable à mon cas est  :

Si l'on invalide la plan, par un recalcul de stat, un vidage du cache de procédure, une réindexation ou un WITh recompile sur la procédure, alors il va de nouveau y avoir une analyse et les statistiques seront utilisée, encore une fois avec leur valeur à ce moment là.

Dans mon cas :

La nuit il y a 2 types d'activité : la planification et l'activité

Le plan est donc compilé avec l'un des 2 cas mais statistiquement c'est la planification qui est prépondérante. En récupérant les traces on récupère la valeur qui au moment de la capture est au delà de la valeur la plus haute collectée par les statistiques (un insert sur une colonne identity). Par contre quelques heures plus tard en recollectant les stat, la même valeur est alors dans les bornes de l'histogramme.

Or pour 1 insert, on a plus d'une centaine d'update à suivre (no comment please).

Je garde mon plan guide, mais au moins je sais pourquoi.

Comme quoi la lumière vient de la discussion.

Answer 6

Quelle version de SQL ? c'est un 2014+ ou bien plus ancien ?

---

Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

Answer 7

C'est un SQL 2017.

Pourquoi la question ?

Answer 8

Le new CE de SQL 2017 a modifié le calcul des stats pour des valeurs en dehors des plages couvertes par l'histogramme. 
Avant 2014, estimation à 1.
Avec new CE, l'estimation se fait suivant une régression linaire (je crois) par rapport aux valeurs existantes dans les intervalles existants. C'est pas parfait, mais c'est qdm mieux que 1.

Ca générait de mauvais plans d'exécution avec des jointures nested loop alors que potentiellement bcp de lignes et que HASH JOIN ou MERGE join aurait été choisis si la cardinalité avait été meilleure.

---

Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

Answer 9

CREATE STATISTICS S_FactOnlineSales_ProductKey_1_250
ON FactOnlineSales
( ProductKey ) 
WHERE ProductKey >= 1 and ProductKey <= 250
WITH FULLSCAN;
GO

CREATE STATISTICS S_FactOnlineSales_ProductKey_251_500
ON FactOnlineSales
( ProductKey ) 
WHERE ProductKey >= 251 and ProductKey <= 500
WITH FULLSCAN;
GO

CREATE STATISTICS S_FactOnlineSales_ProductKey_501_750
ON FactOnlineSales
( ProductKey ) 
WHERE ProductKey >= 501 and ProductKey <= 750
WITH FULLSCAN;
GO

CREATE STATISTICS S_FactOnlineSales_ProductKey_751_1000
ON FactOnlineSales
( ProductKey ) 
WHERE ProductKey >= 751 and ProductKey <= 1000
WITH FULLSCAN;
GO


Attention, le refresh des stats filtrées est à ta charge. L'auto_stats ne les rafraichit pas.

---

Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

Answer 10

Prend ton vecteur de densité, multiplie par le nombre de lignes de la table, cela te donnera le nombre de lignes estimées.
Ton plan d'exécution capturé va te donner lui aussi le nombre de lignes estimée. Compare les 2 valeurs. Si identique, alors vecteur de densité.

Ou bien encore plus simple, ajoute un optimize for unknown en query hint. Le QO va se baser sur le vecteur densité et non pas l'histogramme. 

---

Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM