Optimisation base de données SQL Server 2000: Indexation

Bonjour à tous,

Je travail actuellement sur une application web qui utilise une base de données SQL Server 2000. Le principal problème auquel j'étais confronté jusque là étais le temps d'affichage assez long, plus de 4 min environ.
En supprimant les index des tables et en les recréant à nouveau, je suis passé de plus de 4 min à environ 25 s (secondes), ce qui parait assez considérable.

Aussi, je me pose les questions suivantes:

1°) Les tables étant bien indéxées, et surtout n'ayant rien changé, pourquoi la suppression et la recréation d'un index apporte t'il une optimisation à ma base de données. Je n'ai pas créé d'index supplémentaires sur la ou les tables que ceux existant déjà. Je n'ai fait que supprimer les index existants et les recréés à nouveau.

2°) Comment faire pour améliorer d'avantage le temps d'affichage, pour descendre en dessous de 25 s si possible.

3°) Comment faire pour éviter de procéder encore de la même manière au cas où le problème de lenteur se présenterai à nouveau?

Merci d'avance pour vos retours.

Bonjour, c'est un vaste sujet et je n'ai que très peu de temps. Un index doit être maintenu avec un plan de maintenance. Si un index est créé sur une table de 3 lignes et que maintenant elle en fait 5 000 000 avec beaucoup d'ajout et de suppression, l'index ne sert plus à grand chose. Il faut donc mettre en place un plan de maintenance qui réorganisera/recréera les indexs ainsi que les statistiques.

Bonjour, c'est certainement dû à la fragmentation de tes index.

Il est recommandé de reconstruire un index si la fragmentation est supérieure ou égale à 30% et de réorganiser l'index si elle est supérieure ou égale à 5%.

Bonjour,

Merci pour vos retours.

Bonjour,

Effectivement il est nécessaire de maintenir les index régulièrement; comme l'a dit darkelend, au fur et à mesure que la base de données vit, donc que ses données sont modifiées, l'index peut se fragmenter :

- si l'on doit ajouter une ligne au milieu d'une page pleine (dans le sens où elle ne peut plus accepter l'ajout d'une nouvelle ligne), alors la page est coupée en deux (split de page) et une nouvelle page est créé, les deux pages étant remplies à moitié

- si l'on a une table avec de nombreuses lignes et que l'on réalise par exemple de l'archivage, un certain nombre de lignes disparaissent de la table source

- si l'on modifie la valeur des colonnes participant à la clé d'un index, celui-ci peut s'en fragmenter

D'autre part le chaînage logique des pages ne suit plus l'ordre physique, ce qui entraîne des lectures supplémentaires. La reconstruction (ALTER INDEX ... REBUILD ou DBCC DBREINDEX sous SQL Server 2000 et antérieurs) et la défragmentation (ALTER INDEX ... REORGANIZE ou DBCC INDEXDEFRAG sous SQL Server 2000 et antérieurs) permettent de réduire, si ce n'est d'éliminer cet effet.
Pour en savoir un peu plus, jetez un œil par ici.

En ce qui concerne les plans de maintenance, s'ils permettent avec un minimum d'effort de maintenir une base de données de petite taille, ils trouvent vite leur limite dès que le volume de la base de données est conséquent. Il en va de même pour les statistiques. C'est là que l'on peut écrire des procédures pour ce faire, comme le montre SQLPro, qui seront plus sélectives et plus fines qu'un plan de maintenance.

Donc si vous prévoyez de réaliser des procédures, faites attention à réaliser la maintenance des statistiques avant celle des index, ou de ne mettre à jour que les statistiques de colonne (UPDATE STATISTICS maTable WITH COLUMNS). En effet lorsqu'on reconstruit un index, comme il est nécessaire de parcourir toute la table, la collection des statistiques se fait en parallèle et sans échantillonnage (équivalent à UPDATE STATISTICS maTable (monIndex) WITH FULLSCAN). Si on la fait suivre d'un UPDATE STATISTICS maTable, alors le recalcul de la statistique se fait par échatillonage, écrasant la précédente collection, qui était de meilleure qualité / précision.

@++ ;)