sys.dm_db_index_operational_stats (Transact-SQL)
S’applique à :
SQL ServerAzure SQL DatabaseAzure 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.
Remarque
sys.dm_db_index_operational_stats ne retourne pas d’informations sur les index à mémoire optimisée. Pour plus d’informations sur l’utilisation de l’index optimisé en mémoire, consultez sys.dm_db_xtp_index_stats (Transact-SQL).
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 partition_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. |
Remarque
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, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.
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 :
CONTROLautorisation sur l’objet spécifié dans la base de donnéesVIEW DATABASE STATEouVIEW 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 = NULLVIEW 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.
Important
Lorsque vous utilisez les fonctions Transact-SQL DB_ID et OBJECT_ID pour retourner une valeur de paramètre, vérifiez toujours qu’un ID valide est retourné. 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'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)