DBCC SHOWCONTIG (Transact-SQL)

  • Article

Mis à jour : 15 septembre 2007

Affiche les informations de fragmentation pour les données et les index de la table ou vue spécifiée.

ms175008.note(fr-fr,SQL.90).gifImportant :
Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez sys.dm_db_index_physical_stats à la place.

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

Arguments

  • table_name | table_id | view_name | view_id
    Table ou vue dans laquelle les informations de fragmentation doivent être vérifiées. Sans aucune précision, toutes les tables et les vues indexées de la base de données active sont contrôlées. Pour déterminer l'ID de la table ou de la vue, utilisez la fonction OBJECT_ID.
  • index_name | index_id
    Index dont les informations de fragmentation doivent être vérifiées. Si aucune option n'est spécifiée, l'instruction traite l'index de base de la table ou de la vue indiquée. Pour déterminer l'ID de l'index, utilisez l'affichage catalogue sys.indexes.
  • WITH
    Spécifie les options relatives au type d'informations renvoyées par l'instruction DBCC.
  • FAST
    Indique si une analyse rapide de l'index doit être effectuée et un minimum d'informations renvoyées. Une analyse rapide ne lit pas les pages des niveaux feuille ou données de l'index.
  • ALL_INDEXES
    Affiche les résultats de tous les index des tables et vues spécifiées, même si un index particulier est spécifié.
  • TABLERESULTS
    Affiche les résultats sous la forme d'un ensemble de lignes, avec des informations complémentaires.
  • ALL_LEVELS
    Conservé pour la compatibilité descendante uniquement. Même si ALL_LEVELS est spécifié, seul le niveau feuille de l'index ou le niveau données de la table est traité.
  • NO_INFOMSGS
    Supprime tous les messages d'informations (avec des niveaux de gravité de 0 à 10).

Jeux de résultats

Le tableau suivant décrit les informations figurant dans le jeu de résultats.

Statistique

Description

Pages analysées

Nombre de pages dans la table ou l'index.

Extensions analysées

Nombre d'extensions dans la table ou l'index

Étendues commutées

Nombre de fois où l'instruction DBCC est passée d'une étendue à l'autre en parcourant les pages de la table ou de l'index.

Nombre moyen de pages par étendue

Nombre de pages par étendue dans la chaîne de pages.

Densité d'analyse [meilleure valeur : valeur réelle]

Il s'agit d'un pourcentage. Il représente le ratio Meilleure valeur sur Valeur réelle. Cette valeur est de 100 si tout est contigu et elle est inférieure à 100 si certaines fragmentations existent.

La Meilleure valeur correspond au nombre idéal de modifications de l'étendue si tout est lié en contigu. La Valeur réelle correspond au nombre effectif de modifications de l'étendue.

Fragmentation d'analyse logique

Pourcentage de pages hors service renvoyées après l'analyse des pages de feuilles d'un index. Cette valeur ne concerne pas les segments. Une page non ordonnée est une page pour laquelle la page physique suivante allouée à l'index n'est pas la page désignée par le pointeur de page suivante dans la page feuille actuelle.

Fragmentation d'analyse d'extension

Pourcentage d'étendues hors service lors de l'analyse des pages de feuilles d'un index. Cette valeur n'est pas pertinente pour les segments. Une étendue hors service est une étendue qui contient la page active d'un index, mais n'est pas physiquement l'étendue qui suit celle contenant la page précédente d'un index.

ms175008.note(fr-fr,SQL.90).gifRemarque :

Ce nombre n'a aucune signification lorsque l'index s'étend sur plusieurs fichiers.

Moyenne d'octets libres par page

Nombre moyen d'octets libres sur les pages analysées. Plus le nombre est élevé et moins les pages sont remplies. Les nombres peu élevés sont préférables si l'index ne doit pas recevoir beaucoup d'insertions aléatoires. Ce nombre est également affecté par la taille des lignes : plus elle sera élevée, plus le nombre le sera également.

Densité de page moyenne (complète)

Densité de page moyenne (pourcentage). Cette valeur prend en compte la taille des lignes. C'est donc une indication plus précise sur le remplissage de vos pages. Plus le pourcentage est élevé, mieux c'est.

Lorsque table_id et FAST sont spécifiés, DBCC SHOWCONTIG retourne un jeu de résultats contenant uniquement les colonnes suivantes.

  • Pages analysées
  • Étendues commutées
  • Densité d'analyse [meilleure valeur : valeur réelle]
  • Fragmentation d'analyse d'extension
  • Fragmentation d'analyse logique

Lorsque TABLERESULTS est spécifié, DBCC SHOWCONTIG retourne les colonnes suivantes et également les neuf colonnes décrites dans le tableau précédente.

Statistique

Description

Nom de l'objet

Nom de la table ou de la vue traitée.

ObjectId

ID du nom d'objet.

IndexName

Nom de l'index traité. A la valeur NULL pour un segment.

IndexId

Identificateur de l'index. A la valeur 0 pour un segment.

Niveau

Niveau de l'index. Le niveau 0 correspond au niveau feuille ou données de l'index.

Un segment a le niveau 0.

Pages

Nombre de pages constituant ce niveau d'index ou segment entier.

Lignes

Nombre d'enregistrements de données ou d'index situés à ce niveau de l'index. Pour un segment, cette valeur représente le nombre d'enregistrements de données dans le segment entier.

Pour un segment de mémoire, le nombre d'enregistrements retournés par cette fonction peut ne pas correspondre au nombre de lignes retournées en exécutant une instruction SELECT COUNT (*) sur le segment de mémoire. Cela est dû au fait qu'une ligne peut contenir plusieurs enregistrements. Par exemple, suite à certaines mises à jour, une ligne de segment peut contenir à elle seule un enregistrement de transfert et un enregistrement transféré. Par ailleurs, la plupart des lignes LOB volumineuses sont fractionnées en plusieurs enregistrements dans le stockage LOB_DATA.

MinimumRecordSize

Taille minimale des enregistrements dans ce niveau d'index ou le segment entier.

MaximumRecordSize

Taille maximale des enregistrements dans ce niveau d'index ou le segment entier.

AverageRecordSize

Taille moyenne des enregistrements dans ce niveau d'index ou le segment entier.

ForwardedRecords

Nombre d'enregistrements transférés dans ce niveau d'index ou le segment entier.

Étendues

Nombre d'étendues dans ce niveau d'index ou le segment entier.

ExtentSwitches

Nombre de fois où l'instruction DBCC est passée d'une étendue à l'autre en parcourant les pages de la table ou de l'index.

AverageFreeBytes

Nombre moyen d'octets libres sur les pages analysées. Plus le nombre est élevé et moins les pages sont remplies. Les nombres peu élevés sont préférables si l'index ne doit pas recevoir beaucoup d'insertions aléatoires. Ce nombre est également affecté par la taille des lignes : plus elle sera élevée, plus le nombre le sera également.

AveragePageDensity

Densité de page moyenne (pourcentage). Cette valeur prend en compte la taille des lignes. C'est donc une indication plus précise sur le remplissage de vos pages. Plus le pourcentage est élevé, mieux c'est.

ScanDensity

Il s'agit d'un pourcentage. Il représente le ratio BestCount sur ActualCount. Cette valeur est de 100 si tout est contigu et elle est inférieure à 100 si certaines fragmentations existent.

BestCount

Nombre idéal de modifications de l'étendue si tout est lié en contigu.

ActualCount

Nombre effectif de modifications de l'étendue.

LogicalFragmentation

Pourcentage de pages hors service renvoyées après l'analyse des pages de feuilles d'un index. Cette valeur ne concerne pas les segments. Une page non ordonnée est une page pour laquelle la page physique suivante allouée à l'index n'est pas la page désignée par le pointeur de page suivante dans la page feuille actuelle.

ExtentFragmentation

Pourcentage d'étendues hors service lors de l'analyse des pages de feuilles d'un index. Cette valeur n'est pas pertinente pour les segments. Une étendue hors service est une étendue qui contient la page active d'un index, mais n'est pas physiquement l'étendue qui suit celle contenant la page précédente d'un index.

ms175008.note(fr-fr,SQL.90).gifRemarque :

Ce nombre n'a aucune signification lorsque l'index s'étend sur plusieurs fichiers.

Lorsque WITH TABLERESULTS et FAST sont spécifiés, le jeu de résultats est le même que lorsque WITH TABLERESULTS est spécifié, excepté que les colonnes suivantes auront des valeurs NULL :

Lignes

Étendues

MinimumRecordSize

AverageFreeBytes

MaximumRecordSize

AveragePageDensity

AverageRecordSize

ExtentFragmentation

ForwardedRecords

 

Notes

L'instruction DBCC SHOWCONTIG parcourt la chaîne de la page au niveau feuille de l'index spécifié lorsque index_id est précisé. Si seule la valeur de table_id est précisée ou si la valeur de index_id correspond à 0, les pages de données de la table indiquée sont analysées. L'opération ne nécessite qu'un verrou de table de partage intentionnel (IS). De cette manière, toutes les mises à jour et insertions peuvent être effectuées, sauf celles nécessitant un verrou de table exclusif (X). Cela permet un compromis entre la vitesse d'exécution et aucune réduction de la concurrence avec le nombre de statistiques retournées. Cependant, si la commande n'est utilisée que pour déterminer la fragmentation, nous vous recommandons d'utiliser l'option WITH FAST pour optimiser les performances. Une analyse rapide ne lit pas les pages des niveaux feuille ou données de l'index. L'option WITH FAST ne s'applique pas à un segment.

Modifications dans SQL Server 2005

L'algorithme de calcul de la fragmentation est plus précis dans SQL Server 2005 que dans SQL Server 2000. Par conséquent, les valeurs de fragmentation semblent plus élevées. Par exemple, dans SQL Server 2000, une table n'est pas considérée comme fragmentée si sa page 11 et sa page 13 ne se trouvent pas dans la même étendue que sa page 12. Comme deux opérations d'E/S physiques seraient toutefois nécessaires pour accéder à ces deux pages, SQL Server 2005 en tient compte dans ses calculs de fragmentation.

DBCC SHOWCONTIG n'affiche pas les données de type ntext, text et image. Cela est dû au fait que les index de texte (ID d'index 255 dans SQL Server 2000) qui stockent des données de texte et d'image n'existent plus dans SQL Server 2005. Pour plus d'informations sur l'ID d'index 255, consultez sys.sysindexes (Transact-SQL).

En outre, DBCC SHOWCONTIG ne prend pas en charge certaines fonctionnalités nouvelles de SQL Server 2005. Par exemple :

  • Si la table ou l'index spécifié est partitionné, DBCC SHOWCONTIG n'affiche que la première partition de la table ou de l'index spécifié.
  • DBCC SHOWCONTIG n'affiche pas d'informations de stockage de dépassement de lignes et d'autres types nouveaux de données hors-ligne tels que nvarchar(max), varchar(max), varbinary(max) et xml.

Toutes les nouvelles fonctionnalités de SQL Server 2005 sont entièrement prises en charge par la vue de gestion dynamique sys.dm_db_index_physical_stats.

Fragmentation de la table

DBCC SHOWCONTIG détermine si la table est fragmentée de manière importante ou non. La fragmentation de la table a lieu lors du processus de modification de données (instructions INSERT, UPDATE et DELETE) effectuées sur la table. Comme ces modifications ne sont pas généralement distribuées équitablement entre les lignes de la table, le remplissage de chaque page peut varier dans le temps. Pour les requêtes qui balaient une partie ou la totalité d'une table, une telle fragmentation de table peut provoquer des lectures de page supplémentaires. Cela perturbe l'analyse parallèle des données.

Lorsqu'un index est très fragmenté, vous disposez des méthodes alternatives suivantes pour réduire la fragmentation :

  • Supprimez puis créez de nouveau un index cluster.
    La nouvelle création d'un index cluster permet de réorganiser les données, ce qui entraîne des pages de données remplies entièrement. Vous pouvez configurer le niveau de remplissage à l'aide de l'option FILLFACTOR de l'instruction CREATE INDEX. Cette méthode présente deux inconvénients : l'index est en mode hors connexion pendant la phase de suppression et de recréation, et l'opération est atomique. Si la création de l'index est interrompue, l'index n'est pas recréé.
  • Réorganisez les pages de niveau feuille de l'index selon un ordre logique.
    Utilisez l'instruction ALTER INDEX…REORGANIZE pour réorganiser les pages de niveau feuille de l'index selon un ordre logique. Comme il s'agit d'une opération en ligne, l'index est disponible lorsque l'instruction est exécutée. L'interruption de cette opération entraîne la perte du travail effectué. L'inconvénient de cette méthode est que la réorganisation des données est moins efficace que celle obtenue par l'opération de suppression et de recréation d'un index cluster.
  • Reconstruisez l'index.
    Utilisez ALTER INDEX avec REBUILD pour reconstruire l'index. Pour plus d'informations, consultez ALTER INDEX (Transact-SQL).

Dans le jeu de résultats, les statistiques Moyenne d'octets libres par page et Densité de page moyenne (complète) indiquent le remplissage des pages d'index. La valeur de Moyenne d'octets libres par page doit être faible et la valeur de Densité de page moyenne (complète) doit être élevée pour un index qui ne reçoit pas beaucoup d'insertions aléatoires. La suppression et la recréation d'un index avec l'option FILLFACTOR spécifiée peut améliorer ces statistiques. Par ailleurs, la commande ALTER INDEX avec REORGANIZE comprimera un index en tenant compte de son option FILLFACTOR, ce qui améliore les statistiques.

ms175008.note(fr-fr,SQL.90).gifRemarque :
Un index possédant de nombreuses insertions aléatoires et des pages très remplies aura un nombre accru de fractionnements de page. Cela implique une fragmentation plus importante.

Le niveau de fragmentation d'un index peut être déterminé des manières suivantes :

  • En comparant les valeurs Étendues commutées et Extensions analysées.
    La valeur Étendues commutées doit être la plus proche possible de celle de Extensions analysées. Ce taux est calculé comme la valeur de Densité d'analyse. Cette valeur doit être aussi élevée que possible et peut être améliorée en réduisant la fragmentation de l'index.
    ms175008.note(fr-fr,SQL.90).gifRemarque :
    Cette méthode ne fonctionne pas si l'index concerne un grand nombre de fichiers.
  • En comprenant les valeurs Fragmentation d'analyse logique et Fragmentation d'analyse d'extension.
    Les valeurs Fragmentation d'analyse logique et, dans une proportion moindre, Fragmentation d'analyse d'extension fournissent la meilleure indication du niveau de fragmentation d'une table. Ces deux valeurs doivent être le plus proche possible de zéro. Toutefois, une valeur de 0 à 10 % est tolérée.
    ms175008.note(fr-fr,SQL.90).gifRemarque :
    La valeur Fragmentation d'analyse d'extension est élevée si l'index s'étend sur plusieurs fichiers. Pour réduire ces valeurs, vous devez réduire la fragmentation de l'index.

Autorisations

L'utilisateur doit posséder la table ou être un membre du rôle serveur fixe sysadmin ou des rôles de base de données fixes db_owner et db_ddladmin.

Exemples

A. Affichage des informations de fragmentation d'une table

L'exemple suivant affiche les informations de fragmentation de la table Employee.

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

B. Utilisation de OBJECT_ID pour obtenir l'ID de table et de sys.indexes pour obtenir l'ID d'index.

L'exemple suivant utilise OBJECT_ID et l'affichage catalogue sys.indexes pour obtenir l'ID de table et l'ID d'index de l'index AK_Product_Name de la table Production.Product de la base de données AdventureWorks .

USE AdventureWorks;
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('Production.Product')
SELECT @indid = index_id 
FROM sys.indexes
WHERE object_id = @id 
   AND name = 'AK_Product_Name'
DBCC SHOWCONTIG (@id, @indid);
GO

C. Affichage d'un jeu de résultats abrégé pour une table

L'exemple suivant retourne un jeu de résultats abrégé de la table Product dans la base de données AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

D. Affichage du jeu de résultats complet pour chaque index de chaque table dans une base de données

L'exemple suivant retourne un jeu de résultats complet pour chaque index de chaque table de la base de données AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

E. Utilisation de DBCC SHOWCONTIG et de DBCC INDEXDEFRAG pour défragmenter les index d'une base de données

L'exemple suivant illustre une méthode simple de défragmentation de tous les index d'une base de données fragmentée au-delà d'un seuil déclaré.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Voir aussi

Référence

ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
DBCC (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
OBJECT_ID (Transact-SQL)
sys.indexes (Transact-SQL)

Autres ressources

Affectation et réutilisation de l'espace

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

15 septembre 2007
Contenu modifié :
  • Correction des informations de verrouillage dans la section Notes. Dans SQL Server 2005, cette commande utilise uniquement un verrou de table de partage intentionnel (IS), et non un verrou S tel qu'indiqué précédemment.
  • Clarification apportée à la définition de la colonne Lignes pour les segments.

17 juillet 2006
Nouveau contenu :
  • Ajout d'informations relatives à l'algorithme de calcul de la fragmentation dans la section « Modifications dans SQL Server 2005 ».

5 décembre 2005
Contenu modifié :
  • La description de LogicalFragmentation a été corrigée.