Discussion :

[Boubou2020]

Bonjour a tous
j'ai un petit souci de compréhension que j'arrive pas a le résoudre , j'ai lancer aujourd’hui un plan du maintenance qui consiste a faire un update en mode fullscan
la taille de la base dépasse le 1Téra ,la base tempdb est mis sur la partition système c:
Ma base est en mode de récupération simple
Après quelque heures d’exécution du plan de maintenance ,mon job ma retourné un message d'erreur avec une occupation d'espace disque sur la Partition data + système voir imprime écran
quel est la solution pour cette situation afin de ne plus perturber l'espace de mes partition ?

[mikedavem]

La mise à jour des statistiques en mode fullscan peut avoir une influence sur l'utilisation de tempdb surtout si la table concernée est relativement importante en taille.
Ceci peut expliquer ton problème d'espace pour tempdb et le message ACTIVE_TRANSACTION. Le mode simple ne te garantit en aucun cas le grossissement de tempdb dans ce cas.

Pour limiter l'impact tu peux déjà commencer par identifier quelle(s) table(s) sont responsables de ce problème et changer ta méthode d'échantillonnage de FULL à SAMPLE.
Su tu es sur 2014, l'utilisation des statistiques incrémentales peut aussi être une bonne alternative mais cela requiert une table partitionnée à la base.

++

[elsuket]

Bonjour,

Pour quelle raison avez-vous besoin de faire une mise à jour des statistiques en mode FULLSCAN ?
Pour ma part, j'implémente une petite procédure stockée exécutée tous les jours par un travail de l'Agent SQL Server qui ne met à jour que les statistiques obsolètes.
Cela marche très bien sur des bases de données qui en prennent plein la tête 24/24 et 7/7

Si vous avez un problème d'estimation de cardinalités, il vous sera moins coûteux et plus instructif de comprendre pourquoi il se produit et de donner un coup de pouce, si besoin est, à l'optimiseur.

@++

[Boubou2020]

Je vous remercie pour l'intérêt que vous avez donné à mon problème.
j'ai un petit souci de compréhension c'est quoi la signification du mot statistique obsolète et comment je peux l'identifier
n'hésitez pas à me corriger SVP

[elsuket]

Lorsque vous soumettez une requête à SQL Server (ou à tout moteur de bases de données relationnel SQL), elle est soumise à un optimiseur, qui détermine la façon la moins coûteuse (c'est-à-dire celle qui consommera le moins de ressources) de résoudre cette requête. Cette suite logique s'appelle un plan de requête. Cette optimisation se fait sur la base de statistiques créées et maintenues automatiquement en arrière plan par SQL Server. Dès lors la qualité des statistiques détermine l'efficacité d'exécution d'un plan de requête.

En supposant que vous avez laissé les options de base de données qui affectent la création et la maintenance automatisée des statistiques, lorsque vous soumettez une requête qui filtre par l'une des colonnes d'une table, une statistique sur cette colonne est créée. Vous ne vous en peut-être pas aperçu parce que ce processus est rapide : il est en fait effectué par échantillonnage des données de la colonne. De la même façon, l'optimiseur recherche si un index peut aider au traitement de la requête; ainsi, dès que vous ajoutez un index à une table, une statistique portant sur l'ensemble des colonnes qui constituent la clé de l'index (donc pas les colonnes qui sont dans la clause INCLUDE) est créée.

Comme vous vous en doutez, cet échantillonnage donne lieu a des approximations qui génèrent parfois des problèmes de performance : la majeure partie du temps, l'optimiseur fait un excellent travail, surtout lorsqu'on considère la variété des requêtes soumises. Cet échantillonnage ne peut pas avoir lieu trop souvent, sinon la consommation de ressources qu'il engendre gênerait l'exécution des requêtes.

Le moment où les statistiques sont ré-échantillonnées est déterminé par le nombre de lignes de la table, ainsi que par le nombre total de lignes affectées par les requêtes en écriture sur les colonnes et index en question. Supposons donc que nous avons une table avec 1000 lignes, et que nous exécutons un UPDATE qui affecte 432 lignes, un INSERT qui ajoute 20 lignes, et un DELETE qui supprime 37 lignes. Le nombre total de lignes affectées est donc 432 + 20 + 37. Le ré-échantillonage des statistiques utilisées par un plan de requête est en règle générale déclenché lorsque 500 + 20% des lignes de la table ont été affectés. Ce seuil est différent pour les tables temporaires et les tables qui ont peu de lignes. Notez que les variables de type TABLE n'ont pas de statistiques.

Ce seuil convient bien aux tables peu volumineuses. Considérons cependant une table comptant, par exemple, 40 millions de lignes : le recalcul d'une statistique se fait alors lorsque 8.000.501 mises à jour au moins ont eu lieu sur cette colonne, ou une colonne participant à la clé d'un index utilisé par un plan. On comprend aisément que ceci est sous-optimal pour une base de données OLTP qui subit de nombreuses transactions par seconde 24 x 7 x 365, et supportant des requêtes qui comptent rarement une seule table dans leur expression. Par ailleurs, il arrive qu'au vu du grand nombre de transactions que subit une table, le ré-échantillonage soit repoussé : on trouve ainsi des statistiques dont le seuil de ré-échantillonage est largement outrepassé.

En gardant à l'esprit que ceci convient assez bien à la plupart des charges de travail, on peut contrevenir à cette gestion des statistiques par défaut en forçant la mise à jour des statistiques suivant des seuils que nous déterminons. Ceci permet donc de ne mettre à jour que les statistiques qui sont un peu obsolètes et qui ne touchent pas encore le seuil de ré-échantillonage, et donc de ne pas consommer autant de ressources que si on forçait leur ré-échantillonage de l'ensemble d'entre-elles, aveuglément.

Ceci peut se faire à l'aide de l'instruction UPDATE STATISTICS, de la vue système sys.stats, de la table système sys.sysindexes et de sa colonne rowmodctr (row modification counter), et enfin de la DMV sys.dm_db_partition_stats et de sa colonne row_count.

La conséquence de ce ré-échantillonage est que tous les plans qui référencent une statistique qui vient d'être mise à jour est recompilé : en effet comme le nombre de lignes qui vérifie une jointure ou un filtre peut avoir changé, il est possible qu'un autre algorithme de tri, de jointure ou un autre opérateur d’agrégation soit plus performant / moins consommateur de ressources. Comme toute recompilation consomme des ressources CPU, et que par ailleurs le ré-échantillonage des statistiques consomme beaucoup de ressources disque et mémoire en lecture, il est important d'effectuer ce type d'opération dans une fenêtre de temps de maintenance, c'est à dire de faible activité transactionnelle sur la base de données en question.

Comme vous le voyez, le sujet est vaste, et j'ai fait quelques approximations pour faire une réponse qui ne soit pas trop longue. Si vous voulez aller plus loin sur ce sujet, je peux vous recommander de lire :

- le chapitre 17, Optimisation et statistiques, du livre que j'ai co-écrit avec Frédéric Brouard (SQLPro), David Barbarin (Mikedavem) et Christian Soutou.
- Les ouvrages de Benjamin Nevarez
- Les blogs de Paul White, Fabiano Amorim, et bien d'autres !

@++