DBCC SHOWCONTIG (Transact-SQL)
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.
Important : |
---|
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. |
Conventions 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.
Remarque :
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.
Remarque :
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.
Remarque : |
---|
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.Remarque : 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.Remarque : 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 |
|
17 juillet 2006 |
|
5 décembre 2005 |
|