none
Reconstruction inopérante sur index fragmenté à 90% RRS feed

  • Question

  • Bonjour,

    Nous constatons que la reconstruction d'index (via un plan de maintenance configuré sur toutes les tables utilisateurs) n'agit pas sur la fragmentation d'index non clusterisés supérieur à 90% notamment (meme ceux à 70%...)

    1) Quelles sont les raisons possibles svp ?

    2) Est-il possible d'avoir un log détaillé sur les index qui ont fait l'objet d'une restructuration ? Dans le log, je n'obtiens qu'un "succès" avec l'eure de début et de fin. C'est un peu léger .. J'ai regardé dans les propriétés de l'index via Management Studio mais l'info ne ressort pas.

    Merci par avance.


    • Modifié Amstrad44 mercredi 8 septembre 2021 20:45
    mercredi 8 septembre 2021 20:44

Réponses

  • Salut

    nan, VSS n'intervient absolument pas sur le process de rebuild des index. Le VSS Writer de SQL n'est là que pour supporter le snapshot de VM, des disques en l'occurrence, afin de garantir un état consistant des données sur disque.

    Le rebuild va tout simplement allouer de nouvelles pages pour ton index, qu'il soit cluster ou non cluster.

    Comme cela a été mentionné, suivant la volumétrie de la table, reconstruire l'index ne sert à rien. Pour comprendre, il faut se plonger dans la manière dont SQL Server stocke les données : les fameuses pages de 8Ko, qui sont ensuite organisées en Extents de 64Ko. Détailler l'intégralité du fonctionnement ici m'amènerait à me déclarer en arrêt maladie, doigts top usés a force de taper sur le clavier. Et donc lorsque la table comporte un trop faible nombre de data pages, consommer des ressources pour réindexer n'est pas efficace.

    Par contre, il est essentiel de bien se rappeler une chose : toutes els requêtes s'exécutent en mémoire (sauf restaure database) et que donc, la notion de fragmentation d'un index a dans la grande majorité des cas que peu d'incidence sur la performance, car que l'on accès à une page à l'adresse mémoire X ou Y, le temps d'accès, donc la perf, sera identique.
    Mais la fragmentation engendre une densité plus faible des données, donc du vide, ce qui à terme, peut, je le concède faire bisser la perf, mais il faut vraiment pousser au niveau workload pour le voir.

    Le seul cas où la fragmentation a un effet négatif sur les requêtes : lorsque les requêtes font des lectures complètes sur l'index (cluster index scan, nonclustered index scan). En règle générale, les application de type OLTP font majoritairement des seeks car elle ne requièrent que peu de données, ou a lors vous avez mal indexé.

    Il faut donc arrêter de faire une fixette sur le taux de fragmentation. Il ne faut pas oublier non plus que un index fragmenté va aussi accélérer des Inserts au début ou milieu d'index. Car le page split ne sera pas nécéssaire ...

    Au final, du process de réindexation, je ne retiens qu'un seul bénéfice : le recalcul de la statistique d'index. Et là, oui cela va avoir un gros impact sur la perf, car les plans d'exécution ont davantage de chance d'être performants, car les cardinalités sont proches de la réalité.

    Pour terminer, je vous suggère de laisser tomber les plans de maintenance version SSIS (ceux proposés par Microsoft) et d'utiliser les scripts de Ola Hallengren. Dans la table de log vous auriez de suite vu la raison de la non réindexation, avec en outre des possibilité de paramétrage bien supérieures. Il y a des scrips PowerShell sur mon Github qui automatisent la création de "plans de maintenance" via la solution Ola Hallengren.

    Christophe


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

    mardi 21 septembre 2021 07:27
  • Bonjour Amstrad44,

    Merci pour ce retour.

    Après quelques recherches j ai trouvé différents liens forums qui traitent du sujet, j espère que cela vous aidera:

    A bientôt

    Alexis


    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni « tel quel » et il n'implique aucune responsabilité de la part de Microsoft. S'il vous plaît n'oubliez pas de « Marquer comme réponse » les réponses qui ont résolu votre problème. C'est une voie commune pour reconnaître ceux qui vous ont aidé, et rend plus facile l’accès aux solutions.

    lundi 13 septembre 2021 10:41
    Modérateur

Toutes les réponses

  • Bonjour Amstrad44,

    Voici des articles et liens forums qui vous aideront et répondront à vos questions:

    A bientôt

    Alexis


    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni « tel quel » et il n'implique aucune responsabilité de la part de Microsoft. S'il vous plaît n'oubliez pas de « Marquer comme réponse » les réponses qui ont résolu votre problème. C'est une voie commune pour reconnaître ceux qui vous ont aidé, et rend plus facile l’accès aux solutions.

    jeudi 9 septembre 2021 05:45
    Modérateur
  • bonjour Amstrad44

    Selon mon expérience personnelle, cela peut provenir d'un insuffisance d'espace accordée aux VSS. SQL s'appuie sur les VSS pour une défragmentation à chaud. La solution que j'avais mise en oeuvre était d'agrandir l'espace dédié pour les VSS (vssadmin https://docs.microsoft.com/fr-fr/windows-server/administration/windows-commands/vssadmin)

    Mais si un expert SQL passe par là, il confirmera (ou pas).

    cordialement

    Olivier

    jeudi 9 septembre 2021 05:47
  • Merci Olivier mais les clichés instantantanés sont désactivés ! :|

    @Alexis : Merci pour les liens. J'avais pris connaissance des url 1 et 3 mais pas des deux autres.

    Le nombre de pages requis (minimum 1000) m'avait fait tilt : j'avais remarqué que ma table contenait plus de 20 000 lignes mais c'est la quantité en nombre de pages qui me manque. Je vais essayer de trouver l'info...

    Edit : après avoir recherché, la table contient 10 000 pages donc...cela ne devient pas de là.

    Je me demandais si le fait que ce soit un index non clusterisé pouvait expliquer ce fait ?

    Car pour le coup, l'index clusterisé de la table en question est fragmenté à 0%...

    Merci





    • Modifié Amstrad44 jeudi 9 septembre 2021 06:54
    jeudi 9 septembre 2021 05:57
  • Bonjour Amstrad44,

    Merci pour ce retour.

    Après quelques recherches j ai trouvé différents liens forums qui traitent du sujet, j espère que cela vous aidera:

    A bientôt

    Alexis


    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni « tel quel » et il n'implique aucune responsabilité de la part de Microsoft. S'il vous plaît n'oubliez pas de « Marquer comme réponse » les réponses qui ont résolu votre problème. C'est une voie commune pour reconnaître ceux qui vous ont aidé, et rend plus facile l’accès aux solutions.

    lundi 13 septembre 2021 10:41
    Modérateur
  • Je rencontrais le même souci, merci pour ces liens Alexis
    mardi 14 septembre 2021 07:59
  • Bonjour Amstrad44,

    Avez-vous avancé dans votre problématique ?

    N'oubliez pas de marquer comme réponse les propositions des intervenants qui vous ont amené à la solution du problème.

    Je vous remercie par avance pour votre retour.

    Cordialement,

    Alexis


    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni « tel quel » et il n'implique aucune responsabilité de la part de Microsoft. S'il vous plaît n'oubliez pas de « Marquer comme réponse » les réponses qui ont résolu votre problème. C'est une voie commune pour reconnaître ceux qui vous ont aidé, et rend plus facile l’accès aux solutions.

    jeudi 16 septembre 2021 06:01
    Modérateur
  • Salut

    nan, VSS n'intervient absolument pas sur le process de rebuild des index. Le VSS Writer de SQL n'est là que pour supporter le snapshot de VM, des disques en l'occurrence, afin de garantir un état consistant des données sur disque.

    Le rebuild va tout simplement allouer de nouvelles pages pour ton index, qu'il soit cluster ou non cluster.

    Comme cela a été mentionné, suivant la volumétrie de la table, reconstruire l'index ne sert à rien. Pour comprendre, il faut se plonger dans la manière dont SQL Server stocke les données : les fameuses pages de 8Ko, qui sont ensuite organisées en Extents de 64Ko. Détailler l'intégralité du fonctionnement ici m'amènerait à me déclarer en arrêt maladie, doigts top usés a force de taper sur le clavier. Et donc lorsque la table comporte un trop faible nombre de data pages, consommer des ressources pour réindexer n'est pas efficace.

    Par contre, il est essentiel de bien se rappeler une chose : toutes els requêtes s'exécutent en mémoire (sauf restaure database) et que donc, la notion de fragmentation d'un index a dans la grande majorité des cas que peu d'incidence sur la performance, car que l'on accès à une page à l'adresse mémoire X ou Y, le temps d'accès, donc la perf, sera identique.
    Mais la fragmentation engendre une densité plus faible des données, donc du vide, ce qui à terme, peut, je le concède faire bisser la perf, mais il faut vraiment pousser au niveau workload pour le voir.

    Le seul cas où la fragmentation a un effet négatif sur les requêtes : lorsque les requêtes font des lectures complètes sur l'index (cluster index scan, nonclustered index scan). En règle générale, les application de type OLTP font majoritairement des seeks car elle ne requièrent que peu de données, ou a lors vous avez mal indexé.

    Il faut donc arrêter de faire une fixette sur le taux de fragmentation. Il ne faut pas oublier non plus que un index fragmenté va aussi accélérer des Inserts au début ou milieu d'index. Car le page split ne sera pas nécéssaire ...

    Au final, du process de réindexation, je ne retiens qu'un seul bénéfice : le recalcul de la statistique d'index. Et là, oui cela va avoir un gros impact sur la perf, car les plans d'exécution ont davantage de chance d'être performants, car les cardinalités sont proches de la réalité.

    Pour terminer, je vous suggère de laisser tomber les plans de maintenance version SSIS (ceux proposés par Microsoft) et d'utiliser les scripts de Ola Hallengren. Dans la table de log vous auriez de suite vu la raison de la non réindexation, avec en outre des possibilité de paramétrage bien supérieures. Il y a des scrips PowerShell sur mon Github qui automatisent la création de "plans de maintenance" via la solution Ola Hallengren.

    Christophe


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

    mardi 21 septembre 2021 07:27