sys.dm_db_index_operational_stats (Transact-SQL)

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

Retourne l’activité actuelle d’E/S de niveau inférieur, de verrouillage, de verrouillage et de méthode d’accès pour chaque partition d’une table ou d’un index dans la base de données.

Les index optimisés en mémoire n'apparaissent pas dans cette vue DMV.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

database_id | NULL | 0 | FAIRE DÉFAUT

ID de la base de données. database_id est petit. Les entrées autorisées sont l'ID d'une base de données ou la valeur NULL, 0 ou DEFAULT. La valeur par défaut est 0. Les valeurs 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 l’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.

La fonction intégrée DB_ID peut être spécifiée.

object_id | NULL | 0 | FAIRE DÉFAUT

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

Les entrées autorisées sont l'ID d'une table et d'une vue ou la valeur NULL, 0 ou DEFAULT. La valeur par défaut est 0. Les valeurs NULL, 0 et DEFAULT sont des valeurs équivalentes dans ce contexte.

Spécifiez la valeur NULL pour retourner des informations mises en cache pour 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 | FAIRE DÉFAUT

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

Spécifiez la valeur NULL pour retourner des informations mises en cache pour 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 | FAIRE 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. Les valeurs NULL, 0 et DEFAULT sont des valeurs équivalentes dans ce contexte.

Spécifiez la valeur NULL pour retourner des informations mises en cache pour toutes les partitions de l'index ou du segment de mémoire.

partition_number est basée sur 1. Un index ou un tas nonpartitionné a partition_number défini sur 1.

Table retournée

Nom de la colonne	Type de données	Description
database_id	smallint	ID de la base de données. Dans la base de données Azure SQL, 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 de la table ou de la vue.
index_id	int	ID de l'index ou du segment de mémoire. 0 = Segment de mémoire
partition_number	int	Numéro de partition (basé sur la valeur 1) au sein de l'index ou du segment de mémoire.
hobt_id	bigint	S’applique à : SQL Server 2016 (13.x) et versions ultérieures, Azure SQL Database. ID du tas de données ou de l’ensemble de lignes d’arborescence B qui suit les données internes d’un index columnstore. NULL : il ne s’agit pas d’un ensemble de lignes columnstore interne. Pour plus d’informations, consultez sys.internal_partitions (Transact-SQL)
leaf_insert_count	bigint	Nombre cumulatif d'insertions de niveau feuille.
leaf_delete_count	bigint	Nombre cumulatif de suppressions de niveau feuille. leaf_delete_count n’est incrémenté que pour les enregistrements supprimés qui ne sont pas marqués comme fantômes en premier. Pour les enregistrements supprimés qui sont d’abord fantômes, leaf_ghost_count est incrémenté à la place.
leaf_update_count	bigint	Nombre cumulatif de mises à jour de niveau feuille.
leaf_ghost_count	bigint	Nombre cumulatif de lignes de niveau feuille marquées pour la suppression qui ne sont pas encore supprimées. Ce nombre n’inclut pas les enregistrements qui sont immédiatement supprimés sans être marqués comme fantômes. Ces lignes sont supprimées par un thread de nettoyage à intervalles définis. Cette valeur ne comprend pas les lignes qui sont conservées à cause d'une transaction d'isolement d'instantané en attente.
nonleaf_insert_count	bigint	Nombre cumulatif d'insertions au-dessus du niveau feuille. 0 = Segment de mémoire ou columnstore
nonleaf_delete_count	bigint	Nombre cumulatif de suppressions au-dessus du niveau feuille. 0 = Segment de mémoire ou columnstore
nonleaf_update_count	bigint	Nombre cumulatif de mises à jour au-dessus du niveau feuille. 0 = Segment de mémoire ou columnstore
leaf_allocation_count	bigint	Nombre cumulatif d'allocations de page de niveau feuille dans l'index ou le segment de mémoire. Pour un index, une allocation de page correspond à un fractionnement de page.
nonleaf_allocation_count	bigint	Nombre cumulatif d'allocations de page causées par des fractionnements de page au-dessus du niveau feuille. 0 = Segment de mémoire ou columnstore
leaf_page_merge_count	bigint	Nombre cumulatif de fusions de pages de niveau feuille. Toujours 0 pour l’index columnstore.
nonleaf_page_merge_count	bigint	Nombre cumulatif de fusions de pages au-dessus du niveau feuille. 0 = Segment de mémoire ou columnstore
range_scan_count	bigint	Nombre cumulatif d'analyses de plage et de table commencées sur l'index ou le segment de mémoire.
singleton_lookup_count	bigint	Nombre cumulatif d'extractions de ligne unique à partir de l'index ou du segment de mémoire.
forwarded_fetch_count	bigint	Nombre de lignes extraites via un enregistrement de transfert. 0 = Index
lob_fetch_in_pages	bigint	Nombre cumulatif de pages d'objets volumineux (LOB) extraites de l'unité d'allocation LOB_DATA. Ces pages contiennent des 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).
lob_fetch_in_bytes	bigint	Nombre cumulatif d'octets de données LOB extraits.
lob_orphan_create_count	bigint	Nombre cumulatif de valeurs LOB orphelines créées pour des opérations en bloc. 0 = Index non cluster
lob_orphan_insert_count	bigint	Nombre cumulatif de valeurs LOB orphelines insérées au cours d'opérations en bloc. 0 = Index non cluster
row_overflow_fetch_in_pages	bigint	Nombre cumulatif de pages de données de dépassement de ligne qui ont été extraites de l'unité d'allocation ROW_OVERFLOW_DATA. Ces pages contiennent des données stockées dans des colonnes de type varchar(n), nvarchar(n), varbinary(n), et sql_variant qui a été poussée hors ligne.
row_overflow_fetch_in_bytes	bigint	Nombre cumulatif d'octets de données de dépassement de ligne extraits.
column_value_push_off_row_count	bigint	Nombre cumulatif de valeurs de colonne correspondant aux données LOB et aux données de dépassement de ligne qui sont envoyées hors ligne pour qu'une ligne insérée ou mise à jour puisse figurer dans une page.
column_value_pull_in_row_count	bigint	Nombre cumulatif de valeurs de colonne correspondant aux données LOB et aux données de dépassement de ligne qui sont extraites dans la ligne. Cette situation se produit lorsqu'une opération de mise à jour libère de l'espace dans un enregistrement et permet ainsi d'extraire une ou plusieurs valeurs hors ligne des unités d'allocation LOB_DATA ou ROW_OVERFLOW_DATA dans l'unité d'allocation IN_ROW_DATA.
row_lock_count	bigint	Nombre cumulatif de verrous de ligne demandés.
row_lock_wait_count	bigint	Nombre cumulé de fois où le Moteur de base de données attendu sur un verrou de ligne.
row_lock_wait_in_ms	bigint	Nombre total de millisecondes pendant lesquelles le Moteur de base de données attendu sur un verrou de ligne.
page_lock_count	bigint	Nombre cumulatif de verrous de page demandés.
page_lock_wait_count	bigint	Nombre cumulé de fois où le Moteur de base de données attendu sur un verrou de page.
page_lock_wait_in_ms	bigint	Nombre total de millisecondes pendant lesquelles le Moteur de base de données attendu sur un verrou de page.
index_lock_promotion_attempt_count	bigint	Nombre cumulé de fois où le Moteur de base de données a essayé d’élever les verrous.
index_lock_promotion_count	bigint	Nombre cumulé de fois où le Moteur de base de données les verrous augmentés.
page_latch_wait_count	bigint	Nombre cumulé de fois où le Moteur de base de données attendu, en raison de la contention du verrou.
page_latch_wait_in_ms	bigint	Nombre cumulé de millisecondes pendant lesquelles le Moteur de base de données attendu, en raison de la contention du verrou.
page_io_latch_wait_count	bigint	Nombre cumulé de fois où le Moteur de base de données attendu sur un verrou de page d’E/S.
page_io_latch_wait_in_ms	bigint	Nombre cumulé de millisecondes pendant lesquelles le Moteur de base de données attendu sur un verrou d’E/S de page.
tree_page_latch_wait_count	bigint	Sous-ensemble de page_latch_wait_count qui inclut uniquement les pages d’arborescence B de niveau supérieur. Toujours 0 pour un segment de mémoire ou un index columnstore.
tree_page_latch_wait_in_ms	bigint	Sous-ensemble de page_latch_wait_in_ms qui inclut uniquement les pages d’arborescence B de niveau supérieur. Toujours 0 pour un segment de mémoire ou un index columnstore.
tree_page_io_latch_wait_count	bigint	Sous-ensemble de page_io_latch_wait_count qui inclut uniquement les pages d’arborescence B de niveau supérieur. Toujours 0 pour un segment de mémoire ou un index columnstore.
tree_page_io_latch_wait_in_ms	bigint	Sous-ensemble de page_io_latch_wait_in_ms qui inclut uniquement les pages d’arborescence B de niveau supérieur. Toujours 0 pour un segment de mémoire ou un index columnstore.
page_compression_attempt_count	bigint	Nombre de pages évaluées pour la compression de niveau PAGE pour des partitions spécifiques d'une table, d'un index ou d'une vue indexée. Inclut des pages qui n'ont pas été compressées car des économies significatives n'ont pas pu être obtenues. Toujours 0 pour l’index columnstore.
page_compression_success_count	bigint	Nombre de pages de données compressées à l'aide de la compression PAGE pour des partitions spécifiques d'une table, d'un index ou d'une vue indexée. Toujours 0 pour l’index columnstore.

Notes

Cet objet de gestion dynamique n’accepte pas les paramètres corrélés à partir de CROSS APPLY et OUTER APPLY.

Vous pouvez utiliser sys.dm_db_index_operational_stats pour suivre la durée pendant laquelle les utilisateurs doivent attendre la lecture ou l’écriture dans une table, un index ou une partition, et identifier les tables ou les index qui rencontrent une activité d’E/S significative ou des points chauds.

Utilisez les colonnes suivantes pour identifier les zones de contention.

Pour analyser un modèle d’accès courant à la table ou à la partition d’index, utilisez ces colonnes :

• leaf_insert_count

• leaf_delete_count

• leaf_update_count

• leaf_ghost_count

• range_scan_count

• singleton_lookup_count

Pour identifier les contentions de verrous (internes et externes), utilisez les colonnes suivantes :

• page_latch_wait_count et page_latch_wait_in_ms

  Ces colonnes indiquent s'il y a contention de verrous internes sur l'index ou le segment de mémoire et précisent l'importance de ce conflit.

• row_lock_count et page_lock_count

  Ces colonnes indiquent combien de fois le Moteur de base de données a essayé d’acquérir des verrous de ligne et de page.

• row_lock_wait_in_ms et page_lock_wait_in_ms

  Ces colonnes indiquent s'il y a contention de verrous externes sur l'index ou le segment de mémoire et précisent l'importance de cette contention.

Pour analyser les statistiques d’E/S physiques sur une partition d’index ou de tas

• page_io_latch_wait_count et page_io_latch_wait_in_ms

  Ces colonnes indiquent si des E/S physiques ont été envoyées pour placer en mémoire les pages de l'index ou du segment et précisent le nombre d'E/S envoyées.

Notes de colonne

Les valeurs de lob_orphan_create_count et de lob_orphan_insert_count doivent toujours être égales.

La valeur des colonnes lob_fetch_in_pages et lob_fetch_in_bytes peut être supérieure à zéro pour les index non cluster qui contiennent une ou plusieurs colonnes métier sous forme de colonnes incluses. Pour plus d’informations, consultez Créer des index avec colonnes incluses. De même, la valeur dans les colonnes row_overflow_fetch_in_pages et row_overflow_fetch_in_bytes peut être supérieure à 0 pour les index non cluster si l’index contient des colonnes qui peuvent être envoyées hors ligne.

Comment les compteurs dans le cache de métadonnées sont réinitialisés

Les données retournées par sys.dm_db_index_operational_stats existent uniquement tant que l’objet de cache de métadonnées qui représente le tas ou l’index est disponible. Ces données ne sont ni persistantes, ni cohérentes d'un point de vue transactionnel. Autrement dit, vous ne pouvez pas utiliser ces compteurs pour déterminer si un index a été utilisé ou pas, ni pour savoir quand il a été utilisé pour la dernière fois. Pour plus d’informations sur ce problème, consultez sys.dm_db_index_usage_stats (Transact-SQL).

Les valeurs de chaque colonne sont remises à zéro chaque fois que les métadonnées associées au segment de mémoire ou à l'index sont envoyées dans le cache de métadonnées et les statistiques s'accumulent jusqu'à ce que l'objet cache soit supprimé du cache de métadonnées. Par conséquent, un tas ou un index actif aura probablement toujours ses métadonnées dans le cache, et le nombre cumulé peut refléter l’activité depuis le dernier démarrage de l’instance de SQL Server. Les métadonnées d'un segment de mémoire ou d'un index moins actif entrent dans le cache et en sortent à mesure qu'elles sont utilisées. Le cache ne contient donc pas forcément des valeurs. La suppression d'un index entraîne l'effacement des statistiques correspondantes en mémoire, de sorte que la fonction n'en fera plus état. D'autres opérations DDL par rapport à l'index peuvent provoquer la remise à zéro de la valeur des statistiques.

Utilisation 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 non valides à ces fonctions peut entraîner des résultats imprévisibles. Vérifiez systématiquement qu'un ID valide est retourné lorsque vous utilisez DB_ID ou OBJECT_ID. Pour plus d’informations, consultez la section Remarques dans sys.dm_db_index_physical_stats (Transact-SQL).

autorisations

Les autorisations suivantes sont nécessaires :

• CONTROL autorisation sur l’objet spécifié dans la base de données

• VIEW DATABASE STATE ou VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) autorisation de retourner des informations sur tous les objets de la base de données spécifiée, à l’aide du caractère générique d’objet @object_id = NULL

• VIEW SERVER STATEVIEW SERVER PERFORMANCE STATE (Autorisation SQL Server 2022) de retourner des informations sur toutes les bases de données à l’aide du caractère générique de la base de données @database_id = NULL

L’octroi VIEW DATABASE STATE permet à tous les objets de la base de données d’être retournés, quelles que soient les autorisations CONTROL refusées sur des objets spécifiques.

VIEW DATABASE STATE Refuser que tous les objets de la base de données soient retournés, quelles que soient les autorisations CONTROL accordées sur des objets spécifiques. En outre, lorsque le caractère générique @database_id=NULL de la base de données 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. Retour d'informations sur une table spécifique

L’exemple suivant retourne des informations pour tous les index et partitions de la Person.Address table dans la base de données AdventureWorks2022. L'exécution de cette requête nécessite au minimum l'autorisation CONTROL sur la table Person.Address.

DECLARE @db_id int;    
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_operational_stats(@db_id, @object_id, NULL, NULL);    
  END;    
GO    

B. Retour d'informations sur toutes les tables et tous les index

L’exemple suivant retourne des informations pour toutes les tables et index dans l’instance de SQL Server. L’exécution de cette requête nécessite l’autorisation VIEW SERVER STATE.

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

Voir aussi

Fonctions et vues de gestion dynamique (Transact-SQL)
Fonctions et vues de gestion dynamique associées à l’index (Transact-SQL)
Surveillance et réglage des performances
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)