sys.dm_db_index_physical_stats (Transact-SQL)

Le mode d'exécution de la fonction détermine le niveau de l'analyse effectuée pour obtenir les données statistiques utilisées par la fonction. mode est spécifié avec LIMITED, SAMPLED ou DETAILED. La fonction traverse les chaînes de pages des unités d'allocation qui composent les partitions spécifiées de la table ou de l'index. sys.dm_db_index_physical_stats ne nécessite qu'un verrou de table IS (Intent-Shared), quel que soit son mode d'exécution. Pour plus d'informations sur les verrous, consultez Modes de verrouillage.

Le mode LIMITED est le mode plus rapide : il analyse le plus petit nombre de pages. Pour un index, seules les pages de niveau parent de l'arbre B (B-tree) (autrement dit, les pages au-dessus du niveau feuille) sont analysées. Pour un segment de mémoire, seules les pages PFS et IAM associées sont examinées ; les pages de données du segment de mémoire ne sont pas analysées. Dans SQL Server 2005, toutes les pages d'un segment de mémoire sont analysées en mode LIMITED.

Avec le mode LIMITED, compressed_page_count a la valeur NULL, car Moteur de base de données analyse seulement des pages non-feuille de l'arbre B (B-tree) et les pages IAM et PFS du segment de mémoire. Utilisez le mode SAMPLED pour obtenir une valeur estimée de compressed_page_count et utilisez le mode DETAILED pour obtenir la valeur réelle de compressed_page_count. Le mode SAMPLED retourne des statistiques basées sur 1 pour cent d'exemple de toutes les pages dans l'index ou le segment de mémoire. Les résultats en mode SAMPLED doivent être considérés comme étant approximatifs. Si l'index ou le segment de mémoire comporte moins de 10 000 pages, le mode DETAILED est utilisé à la place du mode SAMPLED.

Le mode DETAILED analyse toutes les pages et retourne toutes les statistiques.

Les modes sont graduellement plus lents de LIMITED à DETAILED, du fait qu'ils exécutent plus de travail. Pour évaluer rapidement la taille ou le niveau de fragmentation d'une table ou d'un index, utilisez le mode LIMITED. Il s'agit du mode le plus rapide qui ne retourne pas de ligne pour chaque niveau non-feuille dans l'unité d'allocation IN_ROW_DATA de l'index.

Vous pouvez utiliser les fonctions Transact-SQL DB_ID et OBJECT_ID pour spécifier une valeur pour les paramètres database_id et object_id. Toutefois, la transmission de valeurs non valides à ces fonctions peut entraîner des résultats imprévisibles. Par exemple, si le nom de la base de données ou de l'objet est introuvable car il n'existe pas ou il est mal orthographié, les deux fonctions retournent la valeur NULL. La fonction sys.dm_db_index_physical_stats interprète NULL comme une valeur générique qui spécifie toutes les bases de données ou tous les objets.

Qui plus est, la fonction OBJECT_ID est traitée avant l'appel de la fonction sys.dm_db_index_physical_stats, et par conséquent, elle est évaluée dans le contexte de la base de données active, et non celui de la base de données spécifiée dans database_id. Il est possible que la fonction OBJECT_ID retourne une valeur NULL ; ou, si le nom d'objet existe dans le contexte de la base de données active et la base de données spécifiée, un message d'erreur peut être retourné. Les exemples suivants présentent ces résultats inattendus.

Dans l'exemple suivant, OBJECT_ID est évalué dans le contexte de la base de données master. Étant donné que Person.Address n'existe pas dans master, la fonction retourne NULL. Lorsque NULL est spécifié comme object_id, tous les objets de la base de données sont retournés. Les mêmes résultats sont retournés lorsqu'un objet qui n'est pas valide est spécifié.

USE master;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO

L'exemple suivant montre le résultat de la spécification d'un nom d'objet valide qui existe dans à la fois dans le contexte de base de données actuel et dans la base de données spécifiée dans le paramètre database_id de la fonction sys.dm_db_index_physical_stats. Une erreur est retournée parce que la valeur d'ID retournée par OBJECT_ID ne correspond pas à la valeur d'ID de l'objet dans la base de données spécifiée.

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2008R2;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Meilleure pratique

La fragmentation a lieu lors de la modification des données (instructions INSERT, UPDATE et DELETE) effectuées sur la table, et donc dans les index définis sur la table. Comme ces modifications ne sont généralement pas distribuées équitablement entre les lignes de la table et des index, le remplissage de chaque page peut varier dans le temps. Pour les requêtes qui analysent tout ou partie des index d'une table, ce type de fragmentation peut entraîner des lectures de pages supplémentaires. Cela perturbe l'analyse parallèle des données.

L'algorithme de calcul de la fragmentation est plus précis dans SQL Server 2008 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. Cependant, l'accès à ces deux pages nécessite deux opérations d'E/S physiques, elles sont donc comptées comme une fragmentation dans SQL Server 2008.

Le niveau de fragmentation d'un index ou d'un segment de mémoire est affiché dans la colonne avg_fragmentation_in_percent. Pour les segments de mémoire, cette valeur représente la fragmentation de l'étendue du segment. Pour les index, cette valeur représente la fragmentation logique de l'index. À la différence de DBCC SHOWCONTIG, les algorithmes de calcul de la fragmentation considèrent dans les deux cas l'espace de stockage qui englobe plusieurs fichiers : ils sont donc précis.

Fragmentation logique

Pourcentage de pages hors service dans les pages de feuilles d'un index. 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 de l'étendue

Pourcentage d'étendues hors service dans les pages de feuilles d'un segment de mémoire. Une étendue est hors service lorsque l'étendue qui contient la page active d'un segment de mémoire n'est pas physiquement l'étendue suivant l'étendue contenant la page précédente.

La valeur de avg_fragmentation_in_percent doit être aussi proche que possible de zéro pour obtenir des performances maximales. Cependant, des valeurs comprises entre 0 et 10 % sont acceptables. Toutes les méthodes de réduction de la fragmentation (par exemple, la reconstruction, la réorganisation ou la recréation) peuvent s'utiliser pour diminuer ces valeurs. Pour plus d'informations sur l'analyse du degré de fragmentation d'un index, consultez Réorganisation et reconstruction d'index.

Lorsqu'un index est fragmenté de telle façon que la fragmentation nuit aux performances des requêtes, il existe trois possibilités de réduction de la fragmentation :

• Supprimer l'index cluster, puis le recréer.

  Le fait de recréer un index cluster redistribue les données et produit des pages de données complètes. 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 suppression et la recréation, et l'opération est atomique. Si la création de l'index est interrompue, l'index n'est pas recréé. Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

• Utilisez l'instruction ALTER INDEX REORGANIZE, qui remplace DBCC INDEXDEFRAG, pour réorganiser dans un ordre logique les pages de niveau feuille de l'index. Du fait qu'il s'agit d'une opération en ligne, l'index est disponible lorsque l'instruction est en cours d'exécution. Il est également possible d'interrompre l'opération sans perdre le travail déjà effectué. L'inconvénient de cette méthode est que la réorganisation des données est moins efficace que la reconstruction d'un index et qu'elle ne met pas à jour les statistiques.

• Utilisez l'instruction ALTER INDEX REBUILD, qui remplace DBCC DBREINDEX, pour reconstruire l'index en ligne ou hors ligne. Pour plus d'informations, consultez ALTER INDEX (Transact-SQL).

La fragmentation seule n'est pas une raison suffisante pour réorganiser ou reconstruire un index. Le principal effet de la fragmentation est le ralentissement de la lecture anticipée lors de l'analyse d'un index. Les temps de réponse sont donc plus longs. Si la charge de travail d'une requête sur une table ou un index fragmenté n'implique pas d'analyses car elle concerne essentiellement des recherches de singletons, la suppression de la fragmentation peut n'avoir aucun effet. Pour plus d'informations, consultez ce site Web de Microsoft.

Pour réduire la fragmentation de l'étendue d'un segment de mémoire, créez un index cluster sur la table puis supprimez l'index. Cela redistribue les données pendant la création de l'index cluster. Si l'on considère l'espace disponible dans la base de données, l'organisation des données est également optimale. Lorsque l'index cluster est par la suite supprimé pour recréer le segment de mémoire, les données ne sont pas déplacées et restent en position optimale. Pour plus d'informations sur l'exécution de ces opérations, consultez CREATE INDEX et DROP INDEX.

Attention
La création et la suppression d'un index cluster sur la table, reconstruit deux fois tous les index non cluster de la table.

Par défaut, l'instruction ALTER INDEX REORGANIZE compacte les pages qui contiennent des données LOB. Du fait que les pages LOB ne sont pas libérées lorsqu'elles sont vides, le compactage de ces données peut améliorer l'utilisation de l'espace disque si un grand nombre de données LOB ont été supprimées ou si une colonne LOB est supprimée.

La réorganisation d'un index cluster spécifié compacte toutes les colonnes LOB contenues dans l'index cluster. La réorganisation d'un index non cluster compacte toutes les colonnes LOB qui sont des colonnes non-clés (incluses) dans l'index. Lorsque l'argument ALL est spécifié dans l'instruction, tous les index associés à la table ou à la vue spécifiée sont réorganisés. De plus, toutes les colonnes LOB associées à l'index cluster, la table sous-jacente ou l'index non cluster avec des colonnes incluses sont compactés.

La colonne avg_page_space_used_in_percent indique le remplissage de la page. Pour une utilisation optimale de l'espace disque, cette valeur doit être aussi proche que possible de 100 % pour un index qui n'aura pas beaucoup d'insertions aléatoires. Cependant, un index qui a beaucoup d'insertions aléatoires et des pages très remplies comportera un nombre plus élevé de fractionnements de pages. Cela implique une fragmentation plus importante. Par conséquent, pour réduire les fractionnements, la valeur doit être inférieure à 100 %. La reconstruction d'un index en spécifiant l'option FILLFACTOR permet de modifier le remplissage des pages pour ajuster le modèle de requête à l'index. Pour plus d'informations sur le facteur de remplissage, consultez Facteur de remplissage. L'instruction ALTER INDEX REORGANIZE compacte également un index en essayant de remplir les pages en fonction de la dernière valeur FILLFACTOR spécifiée. Cela augmente la valeur dans avg_space_used_in_percent. Notez que ALTER INDEX REORGANIZE ne peut pas réduire le remplissage des pages. Au lieu de cela, une reconstruction de l'index doit avoir lieu.

Un fragment se compose de pages de feuilles physiquement contiguës dans le même fichier d'une unité d'allocation. Un index comporte au moins un fragment. Le nombre maximal de fragments d'un index est égal au nombre de pages du niveau feuille de l'index. Des fragments plus importants signifient que moins d'opérations d'entrées/sorties sur le disque sont nécessaires pour lire le même nombre de pages. C'est pourquoi, plus grande est la valeur avg_fragment_size_in_pages, meilleures sont les performances d'analyse. Les valeurs avg_fragment_size_in_pages et avg_fragmentation_in_percent sont inversement proportionnelles l'une à l'autre. La reconstruction ou la réorganisation d'un index doit donc réduire la quantité de fragmentation et augmenter la taille des fragments.

Le code exemple suivant retourne des statistiques de taille et de fragmentation sur tous les index et partitions de la table Person.Address de la base de données AdventureWorks2008R2. Le mode d'analyse est défini à 'LIMITED' pour améliorer les performances et limiter les statistiques retournées. L'exécution de cette requête nécessite, au minimum, l'autorisation CONTROL sur la table Person.Address.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

Le code exemple suivant retourne toutes les statistiques sur le segment de mémoire dbo.DatabaseLog de la base de données AdventureWorks2008R2. Comme la table contient des données LOB, une ligne est retournée pour l'unité d'allocation LOB_DATA, en plus de la ligne retournée pour l'unité d'allocation IN_ROW_ALLOCATION_UNIT qui stocke les pages de données du segment de mémoire. L'exécution de cette requête nécessite, au minimum, l'autorisation CONTROL sur la table dbo.DatabaseLog.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.DatabaseLog');
IF @object_id IS NULL 
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

L'exemple suivant retourne toutes les statistiques de l'ensemble des tables et des index dans l'instance de SQL Server en spécifiant la valeur générique NULL pour tous les paramètres. L'exécution de cette requête nécessite l'autorisation VIEW SERVER STATE.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

Le code exemple suivant réorganise ou reconstruit automatiquement toutes les partitions d'une base de données dont la fragmentation moyenne est supérieure à 10 %. L'exécution de cette requête nécessite l'autorisation VIEW DATABASE STATE. Cet exemple spécifie DB_ID en tant que premier paramètre sans fournir de nom de base de données. Une erreur sera générée si le niveau de compatibilité de la base de données active est inférieur ou égal à 80. Pour résoudre cette erreur, remplacez DB_ID() par un nom de base de données valide. Pour plus d'informations sur les niveaux de compatibilité des bases de données, consultez sp_dbcmptlevel (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

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

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

L'exemple suivant montre comment afficher et comparer le nombre total de pages par rapport aux pages qui sont compressées par ligne et par page. Ces informations peuvent être utilisées pour déterminer l'avantage que procure la compression pour un index ou une table.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count, ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;

L'exemple suivant indique comment le mode SAMPLED retourne un nombre approximatif différent du résultat obtenu en mode DETAILED.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
BEGIN TRAN
DECLARE @idx int = 0;
WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx, 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
END
COMMIT;
GO
SELECT page_count, compressed_page_count, forwarded_record_count, * 
FROM sys.dm_db_index_physical_stats (db_id(), 
    object_id ('t3'), null, null, 'SAMPLED');
SELECT page_count, compressed_page_count, forwarded_record_count, * 
FROM sys.dm_db_index_physical_stats (db_id(), 
    object_id ('t3'), null, null, 'DETAILED');