Auto défragmentation des index - problème

Je dispose d'un scrip T-SQL permettant de lister, parmis tous les index de la base de données, les index fragmentés et ensuite les défragmenter.

J'utilise les colonnes ScanDensity et ExtentFrag retrounée par l'instruction DBCC SHOWCONTIG. Lorsque la valeur de ScanDentity est < 92 et la valeur ExtentFrag est > 10, l'index est défragmenté.

Je joue aussi avec le taux de remplissage afin que les index souvent sollicités par des opérations d'insertion et de mise à jour ne se fragmentent pas trop vite.

Cependant, il reste un problème résiduel avec cette défragmentation. Lors la défragmentation d'un index, certaines requêtes sollicitant l'index en question provoque des blocages, allongeant ainsi la durée de défragmentation, et provoquant des problèmes lors de l'exécution des requêtes.

Existe-t'il une possibilité, lorsque la défragmentation d'un index dure trop de temps, de l'avorter ? Où alors, existe-t'il des méthodes afin d'éviter ce genre d'inconvéniants ?

D'avance, merci.

Philippe Deltour

Citation:

---

Je dispose d'un scrip T-SQL permettant de lister, parmis tous les index de la base de données, les index fragmentés et ensuite les défragmenter.

---

bien !

Citation:

---

J'utilise les colonnes ScanDensity et ExtentFrag retrounée par l'instruction DBCC SHOWCONTIG. Lorsque la valeur de ScanDentity est < 92 et la valeur ExtentFrag est > 10, l'index est défragmenté.

---

Limite beaucoup trop serrée. En efffet exiger un tel taux est parfois STRICTEMENT IMPOSSIBLE. Donc certains index physiquement non fragmentés seront reconstruit par votre méthode alors quils n'on pas besoin de l'être.
En particulier pour un tout petit index (moins de 20 pages) ou alors pour des index sur des tables ayant de très grandes lignes (à partir de 2000 octets).

Citation:

---

Je joue aussi avec le taux de remplissage afin que les index souvent sollicités par des opérations d'insertion et de mise à jour ne se défragmentent pas trop vite.

---

C'est une bonne chose.

Citation:

---

Cependant, il reste un problème résiduel avec cette défragmentation. Lors la défragmentation d'un index, certaines requêtes sollicitant l'index en question provoque des blocages, allongeant ainsi la durée de défragmentation, et provoquant des problèmes lors de l'exécution des requêtes.

---

C'est pourquoi cette tâche doit être planifiée aux heures creuses et se limiter aux index réellement fragmentés. De plus vous pouvez utiliser des techniques moins agressive que la réindexation (REBUILB) comme la défragmentation (DEFRAG) ou encore si vous êtes en version Enterprise, le faire en ONLINE (prévoyez la place physique).

Citation:

---

Existe-t'il une possibilité, lorsque la défragmentation d'un index dure trop de temps, de l'avorter ? Où alors, existe-t'il des méthodes afin d'éviter ce genre d'inconvéniants ?

---

Non, c'est une opération atomique ! Heureusement d'ailleurs. En revanche vous pouvez opter pour un script qui se limite dans une fenêtre de temps.

A +

Citation:

---

Envoyé par SQLpro

C'est pourquoi cette tâche doit être planifiée aux heures creuses et se limiter aux index réellement fragmentés. De plus vous pouvez utiliser des techniques moins agressive que la réindexation (REBUILB) comme la défragmentation (DEFRAG) ou encore si vous êtes en version Enterprise, le faire en ONLINE (prévoyez la place physique).

---

Justement, c'est à 3h00 du matin, mais comme il s'agit d'un système d'information médicale, le système doit être disponnible 24h/24.

Je vais essayer le DEFRAG et le ONLINE

Quant au ScanDensity et l'ExtrentFrag, j'ai adapté le script afin de les comparer respectivement à <= 80 et et >= 20.

Merci pour les informations

Moi je descendrais encore à 70 et 30...

A +

J'ai pu déterminer l'index sur lequelle cale la défragmentation.

La défragmentation est effectuée par la commande suivante :

SET @Sql = 'USE [' + @NomDataBase + '] DBCC DBREINDEX ([' + @NomTable + '],[' + @NomIndex + ']) WITH NO_INFOMSGS'
EXECUTE (@SQL)

J'ai été visualiser la vue système sys.dm_db_index_usage_stats. La valeur user_scans est bien trop élevée par rapport à la valeur user_seeks. Normalement, cet index sert a rechercher des valeurs déterminées, le user_seeks devrait alors être plus élevé que le user_scans. Il y a donc des requêtes anormale à trouver.

Un lien intéressant : http://msdn.microsoft.com/msdnmag/is...lt.aspx?loc=fr

J'ai analysé une trace effectuée entre 3 et 3h30 du matin (la défragmentation était désactivée pour cette fois) en vue de détecter toutes les requêtes utilisant la table concernée.

Je n'ai vu aucune instruction UPDATE sur cette table, mais la quantité d'instruction SELECT effectuée est ENORME.

Est-ce que ce genre de situation peut provoquer une sorte de goulet d'étranglement, empêchant la défragmentation des index de la table ?

J'ai remarqué aussi que certaines requêtes sont utilisées via un curseur (sp_cursor_open), (utilisation du AdUseServer en VB); est-ce pénalisant ?

En tout cas, je n'ai pas réussi a isoler le ou les processus entrant en collision avec la défragmentation.

Excepté exécuter la défragmentation sur cette table et faire un SELECT * FROM master..sysprocesses ORDER BY Blocked DESC afin de voir ce qu'il se passe, je ne vois pas très bien ce que je peux faire de plus.

Citation:

---

Est-ce que ce genre de situation peut provoquer une sorte de goulet d'étranglement, empêchant la défragmentation des index de la table ?

---

Oui le SELECT pose des verrous

Citation:

---

J'ai remarqué aussi que certaines requêtes sont utilisées via un curseur (sp_cursor_open), (utilisation du AdUseServer en VB); est-ce pénalisant ?

---

Oui, énormément. Un curseur mobile 10 à 1000 fois plus de ressources qu'un SELECT. Il est lent...

Citation:

---

Excepté exécuter la défragmentation sur cette table et faire un SELECT * FROM master..sysprocesses ORDER BY Blocked DESC afin de voir ce qu'il se passe, je ne vois pas très bien ce que je peux faire de plus.

---

Faite une réindexation ONLINE (v Enterprise only).

A +

J'ai adapté le code de la procédure afin d'utiliser l'instruction DBCC INDEXDEFRAG au lieu de DBCC DBREINDEX.

Toutefois, la documentation en ligne de SQL Server 2005 recommande l'utilisation de l'instruction ALTER INDEX car INDEXDEFRAG et DBREINDEX vont disparaître dans une prochaine version de SQL Server.

Tout à fait, ce serait effectivement mieux dans un soucis de portabilité car SQL Server supprime maitenant à échéance rapide les vieilles commandes.

A +

Je crois avoir un cours de MS qui donne des précision sur les taux et la méthode à utiliser avec 2005.
Je vous la donnerais demain matin.

Bonsoir,

Les commandes DBCC sont bonnes pour SQL Server 2000, mais plus sous 2005, puisque bloquantes.
Je vous recommande comme la documentation d'utiliser les instructions ALTER INDEX REBUILD/REORGANIZE avec l'option ONLINE. Cette dernière est coûteuse en performance (SQL Server "copie" votre index, donc toute modifiation sur les colonnes clé d'index sont effectuées deux fois : une fois sur l'index de votre table, et une autre fois sur l'index copié) mais elle n'est pas bloquante ;)

Utilisez également la fonction de gestion dynamique sys.dm_db_index_physical_stats avec le niveau de détail SAMPLED (niveau LIMITED à proscrire et DETAILED bloquant), qui est la digne remplaçante de la commande DBCC SHOWCONTIG de SQL Server 2000 pour trouver les indexes à dégragmenter ;)

Dans le manuel des cours officiel de sql serveur 2005, il est écrit qu'il faut :
1-Réorganier les index si taux de défragmentation <=30
2-Récréer si >30.

Voila pour les taux.