Discussion :

[Philippe Robert]

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

[SQLpro]

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 !

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).

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.

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).

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 +

[Philippe Robert]

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

[SQLpro]

Moi je descendrais encore à 70 et 30...

A +

[Philippe Robert]

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

[Philippe Robert]

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.