[SQL2008] Fragmentation et Index

Bonjour,

j'aurai souhaité un avis extérieur sur les statistiques renvoyées sur une de nos tables SQL. (Script d'analyse trouvé sur votre site et résultat dans le fichier xlsx en pièce jointe ; également le résultat d'un DBCC SHOWCONTIG).

Un plan de maintenance est réalisé toutes les nuits :
- deux reconstructions sont effectuées par semaine et les autres jours une réorganisation.

L'indice de fragmentation reste haut mais le nombre de page est faible.

La primary key de la table est un bigint unique.

Plusieurs requêtes portant sur la PK de celle-ci sont désormais plus longue depuis que la volumétrie a augmenté.

Merci de votre retour.

Postez la structure de votre table et la cardinalité de celle-ci.

A +

Est-ce-que celà suffit ?

Pourquoi votre PK est non clustered ?

Postez les requètes "lentes" ainsi que leur plan d'execution...

Bonjour, en parcourant plusieurs sources, je m'aperçois effectivement de notre erreur par contre, j'ai plusieurs interrogations :

- Une PK de type clustered implique-t-elle un ralentissement au moment de l'ajout de la données (étant donné que la donnée doit être replacée au bon endroit ; nous avons sur certaines de nos tables, un besoin tout particulier de performance à l'ajout.) ?

- Quelles sont les exception à cette règle décrite dans la msdn http://msdn.microsoft.com/fr-fr/library/ms186342.aspx ? (À quelques exceptions près, chaque table doit posséder un index cluster.Outre le fait qu'il améliore les performances des requêtes, un index cluster peut être reconstruit ou réorganisé à la demande pour contrôler la fragmentation de la table.)

- Toutes nos PK sont de types bigint et séquentielles. Si nous passons sur des PK Clustered, a-t-on intérêt à créer des index trier par ordre décroissant au niveau des clés étrangères sur les autres tables faisant référence à cette PK ?

Merci pour vos réponses.

Au contraire, si vos PK sont de type BIGINT autoincrementée, toute nouvelle insertion est effectuée dans le même sens de tri que celui de l'index CLUSTER, il ne nécessite donc pas un tri pour l'insertion, puisqu'il se mettre à la suite...

SQL SERVER ne place pas d'index systématiquement sur les FK, il est en général opportun de le faire mais tous cela dépend de vos requètes...

Merci pour ces réponses.

Par rapport aux exceptions décrites dans la msdn sur la mise en place de PK Clustered, auriez-vous un retour d'expérience ?

La règles est :

• toute table devrait avoir une primary key (sinon,c'est pas une table relationnelle par définition)
• toute primary key devrait être clustered (pour éviter les tables en heap)

A cela il faut ajouter quelques remarques :
mieux vaut que la PK soit invariante, asémantique, monotone et la plus concise possible.

A +

Bonjour,

Comme vous êtes sous SQL Server 2008, vous devriez utiliser la DMF sys.dm_db_index_physical_stats() qui est plus précise ;)

Citation:

---

deux reconstructions sont effectuées par semaine et les autres jours une réorganisation.

---

Je vous recommanderai plutôt de réaliser une maintenance tous les jours lorsque la base de données subit une faible charge de travail : de cette façon vous aurez une fragmentation qui sera la plus faible possible de façon constante, avec une durée d'élimination assez prévisible.

Lorsque vous parlez de plan de maintenance, entendez-vous le composant livré avec SQL Server, ou bien une procédure stockée que vous avez développé ?
Si vous êtes dans le premier cas, sachez cela peut convenir pour une base de données de faible volume, mais vous obtiendrez beaucoup plus de précision avec une procédure stockée ;)

Citation:

---

L'indice de fragmentation reste haut mais le nombre de page est faible.

---

C'est tout à fait possible pour ne pas dire normal, surtout si les valeurs ne sont pas monotones. Pour ma part, je ne maintiens pas les index qui ont moins de 1024 pages : le gain que l'on obtient est négligeable par rapport à l'effort que cela demande au moteur de base de données.

Citation:

---

Envoyé par iberserk

si vos PK sont de type BIGINT autoincrementée, toute nouvelle insertion est effectuée dans le même sens de tri que celui de l'index CLUSTER, il ne nécessite donc pas un tri pour l'insertion, puisqu'il se mettre à la suite...

---

J'ajouterai que la fragmentation d'un tel index est toujours faible, ce qui en fait une avantage supplémentaire ;)

Lisez les articles de la catégorie indexation de mon blog, vous y trouverez ce dont vous avez besoin ;)

@++ ;)

Merci pour toutes ces réponses.

Il y a-t-il une solution simple pour modifier nos PK en clustered sans devoir supprimer et recréer ?

CREATE INDEX ... WITH DROP_EXISTING

A +

Cette syntaxe ne semble pas fonctionner :

Citation:

---

Msg*1930, Niveau*16, État*1, Ligne*1
Impossible de convertir un index non cluster en un index cluster, car une contrainte de clé étrangère y fait référence. Supprimez la contrainte de clé étrangère, puis réessayez l'opération.

---

Plusieurs tables ont des foreign key pointant sur cette table.

Faites comme indiqué !

A +

Vous pouvez utiliser la requête que j'ai publié ici pour ce faire ;)

@++ ;)