DBCC SHOWCONTIG (Transact-SQL)

S’applique à :SQL ServerAzure SQL Managed Instance

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

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures

Conventions de la 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 dont 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 obtenir 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 obtenir 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 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'information dont les niveaux de gravité sont compris entre 0 et 10.

Jeux de résultats

Le tableau suivant décrit les informations du jeu de résultats.

Quand table_id et FAST sont spécifiés, DBCC SHOWCONTIG retourne un jeu de résultats qui contient uniquement les colonnes suivantes :

• Pages analysées
• Commutateurs d’extension
• Densité d’analyse [Meilleur nombre:Nombre réel]
• Fragmentation d’analyse d’extension
• Fragmentation d’analyse logique

Lorsque TABLERESULTS est spécifié, DBCC SHOWCONTIG retourne les colonnes suivantes ainsi que les neuf colonnes décrites dans la table précédente.

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

Notes

L’instruction DBCC SHOWCONTIG parcourt la chaîne de la page au niveau feuille de l’index spécifié quand index_id est spécifié. 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 intent-partagé. 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 renvoyées. Cependant, si la commande n'est utilisée que pour évaluer la fragmentation, nous vous recommandons d'utiliser l'option WITH FAST pour des performances optimales. 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 aux segments.

Restrictions

DBCC SHOWCONTIG n’affiche pas les données de types ntext, text et image. Cela est dû au fait que les index de texte qui stockent des données de texte et d'image n'existent plus.

Par ailleurs, DBCC SHOWCONTIG ne prend pas en charge certaines nouvelles fonctionnalités. 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 ligne et d’autres nouveaux types de données hors ligne comme nvarchar(max), varchar(max), varbinary(max) et xml.
• Les index spatiaux ne sont pas pris en charge par DBCC SHOWCONTIG.

Toutes les nouvelles fonctionnalités sont entièrement prises en charge par la vue de gestion dynamique sys.dm_db_index_physical_stats (Transact-SQL).

Fragmentation des tables

DBCC SHOWCONTIG détermine si la table est fortement fragmentée. 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 généralement pas 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 en spécifiant l'option FILLFACTOR dans 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 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 regénérer l’index. Pour plus d’informations, consultez ALTER INDEX (Transact-SQL).

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

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

• En comparant les valeurs Commutateurs d’extension et Extensions analysées.

  La valeur Commutateurs d’extension doit être la plus proche possible de celle d’Extensions analysées. Ce ratio 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.

  Notes

  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.

  Notes

  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 être propriétaire de la table ou être membre du rôle serveur fixe sysadmin, du rôle de base de données fixe db_owner ou du rôle de base de données fixe db_ddladmin.

Exemples

R. Afficher les informations de fragmentation d'une table

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

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

B. Utiliser OBJECT_ID pour obtenir l'ID de table et 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 AdventureWorks2022.

USE AdventureWorks2022;
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. Afficher un jeu de résultats abrégé pour une table

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

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. Afficher le 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 de table entière pour chaque index de chaque table de la base de données AdventureWorks2022.

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

E. Utiliser DBCC SHOWCONTIG et 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

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