sys.dm_db_index_physical_stats (Transact-SQL)

Article
06/19/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Retourne des informations de taille et de fragmentation pour les données et les index de la table ou de la vue spécifiée dans SQL Server. Pour un index, une ligne est retournée pour chaque niveau de l'arbre B (B-tree) dans chaque partition. Pour un tas, une ligne est retournée pour l’unité d’allocation IN_ROW_DATA de chaque partition. Pour les données d’objet volumineux (LOB), une ligne est retournée pour l’unité d’allocation LOB_DATA de chaque partition. Si des données de dépassement de ligne existent dans la table, une ligne est retournée pour l’unité d’allocation ROW_OVERFLOW_DATA dans chaque partition.

Notes

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, SQL Server implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux magasins de données en mémoire. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

sys.dm_db_index_physical_stats ne retourne pas d’informations sur les index columnstore optimisés en mémoire. Pour plus d’informations sur l’utilisation d’index à mémoire optimisée, consultez sys.dm_db_xtp_index_stats (Transact-SQL).

Si vous interrogez sys.dm_db_index_physical_stats sur un serveur instance qui héberge un groupe de disponibilité réplica secondaire lisible, vous pouvez rencontrer un REDO problème de blocage. En effet, cette vue de gestion dynamique acquiert un IS verrou sur la table ou la vue utilisateur spécifiée qui peut bloquer les demandes d’un REDO thread pour un X verrou sur cette table ou cette vue utilisateur.

Conventions de la syntaxe Transact-SQL

Syntaxe

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Arguments

database_id | NULL | 0 | PAR DÉFAUT

ID de la base de données. database_id est smallint. Les entrées valides sont l’ID d’une base de données, NULL, 0 ou DEFAULT. La valeur par défaut est 0. NULL, 0 et DEFAULT sont des valeurs équivalentes dans ce contexte.

Spécifiez NULL pour retourner des informations pour toutes les bases de données dans le instance de SQL Server. Si vous spécifiez NULL pour database_id, vous devez également spécifier NULL pour object_id, index_id et partition_number.

Vous pouvez spécifier la fonction intégrée DB_ID. Lorsque vous utilisez DB_ID sans spécifier de nom de base de données, le niveau de compatibilité de la base de données actuelle doit être supérieur ou égal à 90.

object_id | NULL | 0 | PAR DÉFAUT

ID d’objet de la table ou de la vue sur laquelle l’index est activé. object_id est int.

Les entrées valides sont l’ID d’une table et d’une vue, NULL, 0 ou DEFAULT. La valeur par défaut est 0. NULL, 0 et DEFAULT sont des valeurs équivalentes dans ce contexte. Depuis SQL Server 2016 (13.x), les entrées valides incluent également le nom de la file d’attente Service Broker ou le nom de la table interne de la file d’attente. Lorsque des paramètres par défaut sont appliqués (c’est-à-dire tous les objets, tous les index, etc.), les informations de fragmentation de toutes les files d’attente sont incluses dans le jeu de résultats.

Spécifiez la valeur NULL pour retourner des informations sur toutes les tables et les vues de la base de données spécifiée. Si vous spécifiez NULL pour object_id, vous devez également spécifier NULL pour index_id et partition_number.

index_id | 0 | NULL | -1 | PAR DÉFAUT

ID de l’index. index_id est int. Les entrées valides sont l’ID d’un index, 0 si object_id est un tas, NULL, -1 ou DEFAULT. La valeur par défaut est -1. NULL, -1 et DEFAULT sont des valeurs équivalentes dans ce contexte.

Spécifiez la valeur NULL pour retourner des informations sur tous les index d'une table de base ou d'une vue. Si vous spécifiez NULL pour index_id, vous devez également spécifier NULL pour partition_number.

partition_number | NULL | 0 | PAR DÉFAUT

Numéro de partition dans l’objet . partition_number est int. Les entrées valides sont les partion_number d’un index ou d’un tas, NULL, 0 ou DEFAULT. La valeur par défaut est 0. NULL, 0 et DEFAULT sont des valeurs équivalentes dans ce contexte.

Spécifiez NULL pour retourner des informations sur toutes les partitions de l'objet propriétaire.

partition_number est basé sur 1. Un index ou un tas non partitionné a partition_number défini sur 1.

mode | NULL | PAR DÉFAUT

Nom du mode. mode spécifie le niveau d’analyse utilisé pour obtenir des statistiques. le mode est sysname. Les entrées autorisées sont DEFAULT, NULL, LIMITED, SAMPLED ou DETAILED. La valeur par défaut (NULL) est LIMITED.

Table retournée

Nom de la colonne	Type de données	Description
database_id	smallint	ID de base de données de la table ou de la vue. Dans Azure SQL Database, les valeurs sont uniques au sein d’une base de données unique ou d’un pool élastique, mais pas dans un serveur logique.
object_id	int	ID d'objet de la table ou de la vue vers laquelle pointe l'index.
index_id	int	ID d'index d'un index. 0 = Segment de mémoire.
partition_number	int	Numéro de partition de base 1 dans l'objet propriétaire : une table, une vue ou un index. 1 = Index ou segment de mémoire non partitionné.
index_type_desc	nvarchar(60)	Description du type d'index : - HEAP - INDEX CLUSTER - INDEX NON CLUSTER - INDEX XML PRINCIPAL - INDEX ÉTENDU - XML INDEX - INDEX DE MAPPAGE COLUMNSTORE (interne) - COLUMNSTORE DELETEBUFFER INDEX (interne) - COLUMNSTORE DELETEBITMAP INDEX (interne)
hobt_id	bigint	Heap ou B-tree ID de l’index ou de la partition. Pour les index columnstore, il s’agit de l’ID d’un ensemble de lignes qui effectue le suivi des données columnstore internes d’une partition. Les ensembles de lignes sont stockés sous forme de segments de données ou d’arborescences B. Ils ont le même ID d’index que l’index columnstore parent. Pour plus d’informations, consultez sys.internal_partitions (Transact-SQL).
alloc_unit_type_desc	nvarchar(60)	Description du type d'unité d'allocation : - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA L’unité `LOB_DATA` d’allocation contient les données stockées dans des colonnes de type texte, ntext, image, varchar(max), nvarchar(max), varbinary(max)et xml. Pour plus d’informations, consultez Types de données (Transact-SQL). L’unité `ROW_OVERFLOW_DATA` d’allocation contient les données stockées dans des colonnes de type varchar(n), nvarchar(n), varbinary(n) et sql_variant qui ont été poussées hors ligne.
index_depth	tinyint	Nombre de niveaux d'index. 1 = Tas, ou `LOB_DATA` unité d’allocation `ROW_OVERFLOW_DATA` .
index_level	tinyint	Niveau actuel de l'index. 0 pour les niveaux de feuilles d’index, les tas et `LOB_DATA` ou les unités d’allocation `ROW_OVERFLOW_DATA` . Supérieur à 0 pour les index de niveaux non-feuille. index_level est le plus élevé au niveau racine d’un index. Les niveaux d’index non en feuilles sont traités uniquement en mode = DÉTAILLÉ.
avg_fragmentation_in_percent	float	Fragmentation logique pour les index ou fragmentation d’étendue pour les tas dans l’unité d’allocation `IN_ROW_DATA` . La valeur est mesurée en pourcentage et prend en compte plusieurs fichiers. Pour les définitions de la fragmentation logique et de la fragmentation de l'étendue, consultez la section Notes. 0 pour et `ROW_OVERFLOW_DATA` unités d’allocation`LOB_DATA`. NULL pour les tas en mode = SAMPLED.
fragment_count	bigint	Nombre de fragments dans le niveau feuille d’une unité d’allocation `IN_ROW_DATA` . Pour plus d'informations sur les fragments, consultez la section Notes. NULL pour les niveaux non sourds d’un index et `LOB_DATA` ou d’unités `ROW_OVERFLOW_DATA` d’allocation. NULL pour les tas en mode = SAMPLED.
avg_fragment_size_in_pages	float	Nombre moyen de pages dans un fragment au niveau feuille d’une unité d’allocation `IN_ROW_DATA` . NULL pour les niveaux non sourds d’un index et `LOB_DATA` ou d’unités `ROW_OVERFLOW_DATA` d’allocation. NULL pour les tas en mode = SAMPLED.
page_count	bigint	Nombre total d'index ou de pages de données. Pour un index, nombre total de pages d’index dans le niveau actuel de l’arborescence B dans l’unité d’allocation `IN_ROW_DATA` . Pour un tas, le nombre total de pages de données dans l’unité d’allocation `IN_ROW_DATA` . Pour `LOB_DATA` ou `ROW_OVERFLOW_DATA` unités d’allocation, nombre total de pages dans l’unité d’allocation.
avg_page_space_used_in_percent	float	Pourcentage moyen d'espace de stockage disponible utilisé dans toutes les pages. Pour un index, la moyenne s’applique au niveau actuel de l’arborescence B dans l’unité d’allocation `IN_ROW_DATA` . Pour un tas, la moyenne de toutes les pages de données de l’unité d’allocation `IN_ROW_DATA` . Pour `LOB_DATA` ou `ROW_OVERFLOW_DATA` unités d’allocation, la moyenne de toutes les pages de l’unité d’allocation. NULL quand mode = LIMITÉ.
record_count	bigint	Nombre total d'enregistrements. Pour un index, le nombre total d’enregistrements s’applique au niveau actuel de l’arborescence B dans l’unité d’allocation `IN_ROW_DATA` . Pour un tas, le nombre total d’enregistrements dans l’unité d’allocation `IN_ROW_DATA` . Note: Pour un tas, le nombre d’enregistrements retournés par cette fonction peut ne pas correspondre au nombre de lignes retournées par l’exécution d’un `SELECT COUNT()` sur le tas. Cela est dû au fait qu'une ligne peut contenir plusieurs enregistrements. Par exemple, lors de certaines mises à jour, une ligne de segment unique peut comporter un enregistrement de transfert et un enregistrement transféré suite à l'opération de mise à jour. En outre, la plupart des lignes métier volumineuses sont divisées en plusieurs enregistrements dans le `LOB_DATA` stockage. Pour `LOB_DATA` ou `ROW_OVERFLOW_DATA` unités d’allocation, le nombre total d’enregistrements dans l’unité d’allocation complète. NULL quand mode* = LIMITÉ.
ghost_record_count	bigint	Nombre d'enregistrements fantômes prêts à être supprimés par la tâche de nettoyage des enregistrements fantômes dans l'unité d'allocation. 0 pour les niveaux non sourds d’un index dans l’unité d’allocation `IN_ROW_DATA` . NULL quand mode = LIMITÉ.
version_ghost_record_count	bigint	Nombre d'enregistrements fantômes retenus par une transaction d'isolation d'instantané en attente dans une unité d'allocation. 0 pour les niveaux non sourds d’un index dans l’unité d’allocation `IN_ROW_DATA` . NULL quand mode = LIMITÉ.
min_record_size_in_bytes	int	Taille minimale des enregistrements en octets. Pour un index, la taille minimale d’enregistrement s’applique au niveau actuel de l’arborescence B dans l’unité d’allocation `IN_ROW_DATA` . Pour un tas, la taille d’enregistrement minimale dans l’unité d’allocation `IN_ROW_DATA` . Pour `LOB_DATA` ou `ROW_OVERFLOW_DATA` unités d’allocation, taille d’enregistrement minimale dans l’unité d’allocation complète. NULL quand mode = LIMITÉ.
max_record_size_in_bytes	int	Taille maximale des enregistrements en octets. Pour un index, la taille d’enregistrement maximale s’applique au niveau actuel de l’arborescence B dans l’unité d’allocation `IN_ROW_DATA` . Pour un tas, la taille d’enregistrement maximale dans l’unité d’allocation `IN_ROW_DATA` . Pour `LOB_DATA` ou `ROW_OVERFLOW_DATA` unités d’allocation, taille d’enregistrement maximale dans l’unité d’allocation complète. NULL quand mode = LIMITÉ.
avg_record_size_in_bytes	float	Taille moyenne des enregistrements en octets. Pour un index, la taille moyenne de l’enregistrement s’applique au niveau actuel de l’arborescence B dans l’unité d’allocation `IN_ROW_DATA` . Pour un tas, taille d’enregistrement moyenne dans l’unité d’allocation `IN_ROW_DATA` . Pour `LOB_DATA` ou `ROW_OVERFLOW_DATA` unités d’allocation, taille d’enregistrement moyenne dans l’unité d’allocation complète. NULL quand mode = LIMITÉ.
forwarded_record_count	bigint	Nombre d'enregistrements d'un segment de mémoire qui contiennent des pointeurs avant vers un autre emplacement de données. (Cet état se produit pendant une mise à jour, lorsqu’il n’y a pas assez d’espace pour stocker la nouvelle ligne à l’emplacement d’origine.) NULL pour toute unité d’allocation autre que les unités d’allocation `IN_ROW_DATA` d’un tas. NULL pour les tas lorsque le mode = LIMITÉ.
compressed_page_count	bigint	Nombre de pages compressées. Pour les tas, les pages nouvellement allouées ne sont pas compressées page. Un segment de mémoire est compressé avec le mode PAGE sous deux conditions spéciales : lorsque les données sont importées en bloc ou lorsqu'un segment de mémoire est reconstruit. Les opérations DML classiques qui provoquent des allocations de pages ne sont pas compressées. Régénérez un tas lorsque la `compressed_page_count` valeur augmente au-delà du seuil souhaité. Pour les tables qui ont un index cluster, la `compressed_page_count` valeur indique l’efficacité de la compression PAGE.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = VIDAGE 3 = VIDAGE 4 = MISE HORS SERVICE 5 = PRÊT S’applique à : SQL Server 2016 (13.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance
columnstore_delete_buffer_state_desc	nvarchar(60)	NON VALIDE : l’index parent n’est pas un index columnstore. OPEN : les deleters et les scanneurs l’utilisent. DRAINING : les deleters se vident, mais les scanneurs l’utilisent toujours. FLUSHING : la mémoire tampon est fermée et les lignes de la mémoire tampon sont écrites dans l’image bitmap de suppression. RETRAIT : les lignes du tampon de suppression fermée ont été écrites dans la bitmap de suppression, mais la mémoire tampon n’a pas été tronquée, car les scanneurs l’utilisent toujours. Les nouveaux scanneurs n’ont pas besoin d’utiliser la mémoire tampon de mise hors service, car la mémoire tampon ouverte est suffisante. PRÊT : cette mémoire tampon de suppression est prête à être utilisée. S’applique à : SQL Server 2016 (13.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance
version_record_count	bigint	Il s’agit du nombre d’enregistrements de version de ligne conservés dans cet index. Ces versions de ligne sont gérées par la fonctionnalité De récupération de base de données accélérée . S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database
inrow_version_record_count	bigint	Nombre d’enregistrements de version ADR conservés dans la ligne de données pour une récupération rapide. S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database
inrow_diff_version_record_count	bigint	Nombre d’enregistrements de version ADR conservés sous forme de différences par rapport à la version de base. S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database
total_inrow_version_payload_size_in_bytes	bigint	Taille totale en octets des enregistrements de version en ligne pour cet index. S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database
offrow_regular_version_record_count	bigint	Nombre d’enregistrements de version conservés en dehors de la ligne de données d’origine. S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database
offrow_long_term_version_record_count	bigint	Nombre d’enregistrements de version considérés à long terme. S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database

Notes

La sys.dm_db_index_physical_stats fonction de gestion dynamique remplace l’instruction DBCC SHOWCONTIG .

Modes d’analyse

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. le mode est spécifié comme LIMITÉ, SAMPLED ou DÉTAILLÉ. 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.

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, les pages PFS et IAM associées sont examinées et les pages de données d'un segment de mémoire sont analysées en mode LIMITED.

Avec le mode LIMITÉ, a la valeur NULL, compressed_page_count car le moteur de base de données analyse uniquement les pages non-feuilles de l’arborescence B et les pages IAM et PFS du tas. Utilisez le mode SAMPLED pour obtenir une valeur estimée pour compressed_page_count, et utilisez le mode DÉTAILLÉ pour obtenir la valeur réelle pour 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 plus rapide et ne retourne pas de ligne pour chaque niveau non sourd dans l’unité IN_ROW_DATA d’allocation de l’index.

Utiliser des fonctions système pour spécifier des valeurs de paramètre

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 qui ne sont pas valides pour ces fonctions peut entraîner des résultats inattendus. Par exemple, si le nom de la base de données ou de l’objet est introuvable parce qu’ils n’existent pas ou sont mal orthographiés, les deux fonctions retournent NULL. La sys.dm_db_index_physical_stats fonction interprète NULL comme une valeur générique spécifiant toutes les bases de données ou tous les objets.

En outre, la OBJECT_ID fonction est traitée avant l’appel de la sys.dm_db_index_physical_stats fonction et est donc évaluée dans le contexte de la base de données active, et non dans la base de données spécifiée dans database_id. Ce comportement peut amener la OBJECT_ID fonction à renvoyer une valeur NULL ; ou, si le nom de l’objet existe à la fois dans le contexte de base de données actuel et dans la base de données spécifiée, un message d’erreur peut être retourné. Les exemples suivants présentent ces résultats inattendus.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
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

Bonne pratique

Assurez-vous toujours qu’un ID valide est retourné lorsque vous utilisez DB_ID ou OBJECT_ID. Par exemple, lorsque vous utilisez OBJECT_ID, spécifiez un nom en trois parties, par OBJECT_ID(N'AdventureWorks2022.Person.Address')exemple , ou testez la valeur retournée par les fonctions avant de les utiliser dans la sys.dm_db_index_physical_stats fonction . Les exemples A et B qui suivent illustrent une méthode sûre pour déterminer des ID de bases de données et d'objets.

Détecter la fragmentation

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. Étant donné que ces modifications ne sont généralement pas réparties de manière égale entre les lignes de la table et des index, la plénitude de chaque page peut varier au fil du 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.

Le niveau de fragmentation d’un index ou d’un tas est affiché dans la avg_fragmentation_in_percent colonne . 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. Contrairement à DBCC SHOWCONTIG, les algorithmes de calcul de fragmentation dans les deux cas prennent en compte le stockage qui s’étend sur plusieurs fichiers et, par conséquent, sont 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 extension en désordre est une extension pour laquelle l’extension qui contient la page active d’un tas n’est pas physiquement l’extension suivante après l’extension qui contient la page précédente.

La valeur de avg_fragmentation_in_percent doit être aussi proche que possible de zéro pour 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 dans un index, consultez Réorganiser et reconstruire des index.

Réduire la fragmentation dans un 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 ALTER INDEX REORGANIZE, le remplacement de DBCC INDEXDEFRAG, pour réorganiser les pages de niveau feuille de l’index dans un ordre logique. 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 qu’elle ne réorganise pas aussi bien les données qu’une opération de reconstruction d’index et qu’elle ne met pas à jour les statistiques.
Utilisez ALTER INDEX REBUILD, le remplacement de DBCC DBREINDEX, pour reconstruire l’index en ligne ou hors connexion. 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 de requête sur une table ou un index fragmenté n’implique pas d’analyses, car la charge de travail est principalement des recherches singleton, la suppression de la fragmentation peut n’avoir aucun effet.

Notes

L’exécution DBCC SHRINKFILE ou DBCC SHRINKDATABASE peut introduire une fragmentation si un index est partiellement ou complètement déplacé pendant l’opération de réduction. Par conséquent, si une opération de compactage doit être effectuée, vous devez l'exécuter avant la suppression de la fragmentation.

Réduire la fragmentation dans un tas

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 ensuite supprimé pour recréer le tas, 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 une table régénère deux fois tous les index non cluster sur cette table.

Compacter les données d’objets volumineux

Par défaut, l'instruction ALTER INDEX REORGANIZE compacte les pages qui contiennent des données LOB. Étant donné que les pages métier 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 métier ont été supprimées ou si une colonne métier 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. En outre, toutes les colonnes métier associées à l’index cluster, à la table sous-jacente ou à l’index non cluster avec des colonnes incluses sont compactées.

Évaluer l’utilisation de l’espace disque

La avg_page_space_used_in_percent colonne indique l’exhaustivité de la page. Pour optimiser l’utilisation de l’espace disque, cette valeur doit être proche de 100 % pour un index qui n’a pas beaucoup d’insertions aléatoires. Toutefois, un index qui a de nombreuses insertions aléatoires et des pages très pleines a un nombre accru 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 Spécifier un facteur de remplissage pour un index. 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 de avg_space_used_in_percent. ALTER INDEX REORGANIZE ne peut pas réduire l’exhaustivité de la page. Au lieu de cela, une reconstruction de l'index doit avoir lieu.

Évaluer les fragments d’index

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. Par conséquent, plus la valeur est avg_fragment_size_in_pages élevée, plus les performances d’analyse de la plage sont élevées. Les avg_fragment_size_in_pages valeurs et avg_fragmentation_in_percent sont inversement proportionnelles les unes aux autres. La reconstruction ou la réorganisation d'un index doit donc réduire la quantité de fragmentation et augmenter la taille des fragments.

Limitations et restrictions

Ne retourne pas de données pour les index columnstore cluster.

Autorisations

Les autorisations suivantes sont nécessaires :

autorisation CONTROL sur l'objet spécifié dans la base de données ;
Autorisation VIEW DATABASE STATE ou VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) pour retourner des informations sur tous les objets de la base de données spécifiée, à l’aide du caractère générique de l’objet @object_id=NULL.
L’autorisation VIEW SERVER STATE ou VIEW SERVER PERFORMANCE STATE (SQL Server 2022) permet de retourner des informations sur toutes les bases de données à l’aide du caractère générique @database_id = NULL.

L'octroi de l'autorisation VIEW DATABASE STATE autorise le renvoi de tous les objets de la base de données, quelles que soient les autorisations CONTROL refusées sur des objets spécifiques.

Le refus de l'autorisation VIEW DATABASE STATE interdit le retour de tous les objets de la base de données, quelles que soient les autorisations CONTROL accordées sur des objets spécifiques. En outre, lorsque le caractère générique de la base de données @database_id=NULL est spécifié, la base de données est omise.

Pour plus d’informations, consultez Vues et fonctions de gestion dynamique (Transact-SQL).

Exemples

R. Retourner des informations sur une table spécifiée

L'exemple de code suivant retourne des statistiques de taille et de fragmentation sur tous les index et partitions de la table Person.Address. 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'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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

B. Retourner des informations sur un tas

L’exemple suivant retourne toutes les statistiques du tas dbo.DatabaseLog dans la base de données AdventureWorks2022. 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'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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

C. Retourner des informations pour toutes les bases de données

L’exemple suivant retourne toutes les statistiques pour tous les tables et index dans le instance de SQL Server en spécifiant le caractère 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

D. Utiliser `sys.dm_db_index_physical_stats` dans un script pour reconstruire ou réorganiser des index

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 est générée si la base de données actuelle a un niveau de compatibilité 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é de la base de données, consultez Niveau de compatibilité ALTER DATABASE (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
    INNER 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

E. Utiliser `sys.dm_db_index_physical_stats` pour afficher le nombre de pages compressées par page

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
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Utiliser `sys.dm_db_index_physical_stats` en mode SAMPLED

L'exemple suivant montre comment le mode SAMPLED retourne un nombre approximatif qui est différent de celui résultant du mode DETAILED.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

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');

G. Interroger les files d’attente service broker pour la fragmentation d’index

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

L’exemple suivant montre comment interroger les files d’attente broker de serveur pour la fragmentation.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

sys.dm_db_index_physical_stats (Transact-SQL)

Syntaxe

Arguments

database_id | NULL | 0 | PAR DÉFAUT

object_id | NULL | 0 | PAR DÉFAUT

index_id | 0 | NULL | -1 | PAR DÉFAUT

partition_number | NULL | 0 | PAR DÉFAUT

mode | NULL | PAR DÉFAUT

Table retournée

Notes

Modes d’analyse

Utiliser des fonctions système pour spécifier des valeurs de paramètre

Bonne pratique

Détecter la fragmentation

Fragmentation logique

Fragmentation de l’étendue

Réduire la fragmentation dans un index

Réduire la fragmentation dans un tas

Compacter les données d’objets volumineux

Évaluer l’utilisation de l’espace disque

Évaluer les fragments d’index

Limitations et restrictions

Autorisations

Exemples

R. Retourner des informations sur une table spécifiée

B. Retourner des informations sur un tas

C. Retourner des informations pour toutes les bases de données

D. Utiliser `sys.dm_db_index_physical_stats` dans un script pour reconstruire ou réorganiser des index

E. Utiliser `sys.dm_db_index_physical_stats` pour afficher le nombre de pages compressées par page

F. Utiliser `sys.dm_db_index_physical_stats` en mode SAMPLED

G. Interroger les files d’attente service broker pour la fragmentation d’index

Voir aussi

Commentaires

Commentaires

Ressources supplémentaires

sys.dm_db_index_physical_stats (Transact-SQL)

Syntaxe

Arguments

database_id | NULL | 0 | PAR DÉFAUT

object_id | NULL | 0 | PAR DÉFAUT

index_id | 0 | NULL | -1 | PAR DÉFAUT

partition_number | NULL | 0 | PAR DÉFAUT

mode | NULL | PAR DÉFAUT

Table retournée

Notes

Modes d’analyse

Utiliser des fonctions système pour spécifier des valeurs de paramètre

Bonne pratique

Détecter la fragmentation

Fragmentation logique

Fragmentation de l’étendue

Réduire la fragmentation dans un index

Réduire la fragmentation dans un tas

Compacter les données d’objets volumineux

Évaluer l’utilisation de l’espace disque

Évaluer les fragments d’index

Limitations et restrictions

Autorisations

Exemples

R. Retourner des informations sur une table spécifiée

B. Retourner des informations sur un tas

C. Retourner des informations pour toutes les bases de données

D. Utiliser sys.dm_db_index_physical_stats dans un script pour reconstruire ou réorganiser des index

E. Utiliser sys.dm_db_index_physical_stats pour afficher le nombre de pages compressées par page

F. Utiliser sys.dm_db_index_physical_stats en mode SAMPLED

G. Interroger les files d’attente service broker pour la fragmentation d’index

Voir aussi

Commentaires

Commentaires

Ressources supplémentaires

D. Utiliser `sys.dm_db_index_physical_stats` dans un script pour reconstruire ou réorganiser des index

E. Utiliser `sys.dm_db_index_physical_stats` pour afficher le nombre de pages compressées par page

F. Utiliser `sys.dm_db_index_physical_stats` en mode SAMPLED