sys.dm_db_index_operational_stats (Transact-SQL)

Retourne l'activité en cours des E/S de bas niveau, des verrous externes, des verrous internes et des méthodes d'accès pour chaque partition d'une table ou d'un index de la base de données.

Conventions de 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 | DEFAULT
  Identificateur de la base de données. database_id est de type smallint. 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 concernant toutes les bases de données de l'instance de SQL Server. Si vous spécifiez la valeur 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. Si vous utilisez DB_ID sans spécifier de nom de base de données, le niveau de compatibilité de la base de données active doit être égal à 90.

• object_id | NULL | 0 | DEFAULT
  Identificateur d'objet de la table ou de la vue sur laquelle l'index est défini. object_id est de type 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 entrez NULL pour object_id, vous devez également entrer NULL pour index_id et partition_number.

• index_id | 0 | NULL | -1 | DEFAULT
  Identificateur de l'index. index_id est de type int. Les entrées autorisées sont l'ID d'un index, 0 si object_id est un segment de mémoire, ou la valeur 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 la valeur Null pour index_id, vous devez également spécifier Null pour partition_number.

• partition_number | NULL | 0 | DEFAULT
  Numéro de partition dans l'objet. partition_number est de type int. Les entrées autorisées sont le numéro de partition (partion_number) d'un index ou d'un segment de mémoire, 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 partitions de l'index ou du segment de mémoire.

  partition_number est de base 1. Pour un index ou un segment de mémoire non partitionné, partition_number est égal à 1.

Table retournée

Nom de colonne	Type de données	Description
database_id	smallint	ID de la base de données.
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.
leaf_insert_count	bigint	Nombre cumulé d'insertions de niveau feuille.
leaf_delete_count	bigint	Nombre cumulé de suppressions de niveau feuille.
leaf_update_count	bigint	Nombre cumulé de mises à jour de niveau feuille.
leaf_ghost_count	bigint	Nombre cumulé de lignes de niveau feuille marquées pour la suppression qui ne sont pas encore supprimées. 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 de capture instantanée en attente. Pour plus d'informations sur les transactions d'isolement de capture instantanée, consultez Niveaux d'isolation basés sur les versions de lignes dans le moteur de base de données.
nonleaf_insert_count	bigint	Nombre cumulé d'insertions au-dessus du niveau feuille. 0 = segment de mémoire
nonleaf_delete_count	bigint	Nombre cumulé de suppressions au-dessus du niveau feuille. 0 = segment de mémoire
nonleaf_update_count	bigint	Nombre cumulé de mises à jour au-dessus du niveau feuille. 0 = segment de mémoire
leaf_allocation_count	bigint	Nombre cumulé 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 cumulé d'allocations de page causées par des fractionnements de page au-dessus du niveau feuille. 0 = segment de mémoire
leaf_page_merge_count	bigint	Nombre cumulé de fusions de pages de niveau feuille.
nonleaf_page_merge_count	bigint	Nombre cumulé de fusions de pages au-dessus du niveau feuille. 0 = segment de mémoire
range_scan_count	bigint	Nombre cumulé d'analyses de plage et de table commencées sur l'index ou le segment de mémoire.
singleton_lookup_count	bigint	Nombre cumulé 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 cumulé 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 text, ntext, image, varchar(max), nvarchar(max), varbinary(max) ou xml. Pour plus d'informations, consultez Types de données (Transact-SQL). Pour plus d'informations sur les unités d'allocation, consultez Organisation des tables et des index.
lob_fetch_in_bytes	bigint	Nombre cumulé d'octets de données LOB extraits.
lob_orphan_create_count	bigint	Nombre cumulé de valeurs LOB orphelines créées pour des opérations en bloc. 0 = Index non cluster
lob_orphan_insert_count	bigint	Nombre cumulé 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 cumulé 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 ont été envoyées hors ligne. Pour plus d'informations, consultez Données de dépassement de ligne de plus de 8 Ko. Pour plus d'informations sur les unités d'allocation, consultez Organisation des tables et des index.
row_overflow_fetch_in_bytes	bigint	Nombre cumulé d'octets de données de dépassement de ligne extraits.
column_value_push_off_row_count	bigint	Nombre cumulé 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 être ajustée à une page.
column_value_pull_in_row_count	bigint	Nombre cumulé 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. Pour plus d'informations sur les unités d'allocation, consultez Organisation des tables et des index.
row_lock_count	bigint	Nombre cumulé de verrous de ligne demandés.
row_lock_wait_count	bigint	Nombre cumulé de fois où le moteur de base de données a attendu sur un verrou de ligne.
row_lock_wait_in_ms	bigint	Durée totale en millisecondes pendant laquelle le moteur de base de données a attendu sur un verrou de ligne.
page_lock_count	bigint	Nombre cumulé de verrous de page demandés.
page_lock_wait_count	bigint	Nombre cumulé de fois où le moteur de base de données a attendu sur un verrou de page.
page_lock_wait_in_ms	bigint	Durée totale en millisecondes pendant laquelle le moteur de base de données a 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'escalader des verrous.
index_lock_promotion_count	bigint	Nombre cumulé de fois où le moteur de base de données a escaladé des verrous.
page_latch_wait_count	bigint	Nombre cumulé de fois où le moteur de base de données a attendu à cause d'un conflit de verrous internes.
page_latch_wait_in_ms	bigint	Durée cumulée en millisecondes pendant laquelle le moteur de base de données a attendu en raison d'un conflit de verrous internes.
page_io_latch_wait_count	bigint	Nombre cumulé de fois où le moteur de base de données a attendu sur un verrou d'E/S de page.
page_io_latch_wait_in_ms	bigint	Durée cumulée en millisecondes pendant laquelle le moteur de base de données a 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'arbre B (B-tree) de niveau supérieur. Toujours 0 pour un segment de mémoire.
tree_page_latch_wait_in_ms	bigint	Sous-ensemble de page_latch_wait_in_ms qui inclut uniquement les pages d'arbre B (B-tree) de niveau supérieur. Toujours 0 pour un segment de mémoire.
tree_page_io_latch_wait_count	bigint	Sous-ensemble de page_io_latch_wait_count qui inclut uniquement les pages d'arbre B (B-tree) de niveau supérieur. Toujours 0 pour un segment de mémoire.
tree_page_io_latch_wait_in_ms	bigint	Sous-ensemble de page_io_latch_wait_in_ms qui inclut uniquement les pages d'arbre B (B-tree) de niveau supérieur. Toujours 0 pour un segment de mémoire.
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.
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.

Notes

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

Vous pouvez utiliser sys.dm_db_index_operational_stats pour effectuer le suivi du temps d'attente des utilisateurs lors des opérations de lecture ou d'écriture dans une table, un index ou une partition ; cette fonction permet également d'identifier les tables ou les index qui connaissent une activité d'E/S importante ou des points sensibles.

Utilisez les colonnes suivantes pour identifier les zones de conflit.

Pour analyser un modèle d'accès commun à la partition de table ou d'index, utilisez les colonnes suivantes :

• leaf_insert_count

• leaf_delete_count

• leaf_update_count

• leaf_ghost_count

• range_scan_count

• singleton_lookup_count

Pour identifier les conflits 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 conflit 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 externes de ligne et de page.

• row_lock_wait_in_ms et page_lock_wait_in_ms

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

Pour analyser les statistiques d'E/S physiques sur une partition d'index ou de segment de mémoire

• 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 de mémoire et précisent le nombre d'E/S envoyées.

Remarques sur les colonnes

Les valeurs de lob_orphan_create_count et 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 LOB en tant que colonnes incluses. Pour plus d'informations, consultez Index avec colonnes incluses. De même, la valeur des colonnes row_overflow_fetch_in_pages et row_overflow_fetch_in_bytes peut être supérieure à zéro pour un index non cluster si ce dernier contient des colonnes pouvant être envoyées hors ligne. Pour plus d'informations, consultez Données de dépassement de ligne de plus de 8 Ko.

Mode de réinitialisation des compteurs dans le cache des métadonnées

Les données retournées par sys.dm_db_index_operational_stats existent uniquement tant que l'objet du cache de métadonnées qui représente le segment de mémoire 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 à ce sujet, 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 segment de mémoire actif ou un index aura probablement toujours ses métadonnées dans le cache et les compteurs cumulatifs peuvent refléter l'activité depuis le dernier redé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 de fonctions système pour spécifier des valeurs de paramètres

Vous pouvez utiliser les fonctions Transact-SQLDB_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 :

• Autorisation CONTROL sur l'objet spécifié dans la base de données

• Autorisation VIEW DATABASE STATE pour obtenir des informations sur tous les objets de la base de données spécifiée en utilisant la spécification générique @object\_id = NULL

• Autorisation VIEW SERVER STATE pour obtenir des informations sur toutes les bases de données en utilisant la spécification 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. De plus, lorsque la base de données générique @database\_id=NULL est spécifiée, la base de données est omise.

Pour plus d'informations, consultez Fonctions et vues de gestion dynamique (Transact-SQL).

Exemples

A. Retour d'informations sur une table spécifique

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

Important

Lorsque vous utilisez les fonctions Transact-SQL DB_ID et OBJECT_ID pour obtenir la valeur d'un paramètre, vérifiez toujours que l'ID retourné est valide. Si le nom de la base de données ou de l'objet est introuvable, par exemple s'il n'existe pas ou n'est pas correctement orthographié, les deux fonctions retournent la valeur NULL. La fonction sys.dm_db_index_operational_stats interprète la valeur NULL comme une valeur générique qui désigne toutes les bases de données ou tous les objets. Comme il peut s'agir d'une opération non intentionnelle, les exemples fournis dans cette section présentent une méthode sûre pour déterminer les ID de base de données et d'objet.

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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 concernant toutes les tables et tous les index compris 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

Référence

Fonctions et vues de gestion dynamique (Transact-SQL)

Fonctions et vues de gestion dynamique relatives aux index (Transact-SQL)

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)

Autres ressources

Analyse et paramétrage des performances

Tables et architecture de structures de données d'index