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.
Notes
sys.dm_db_index_operational_stats ne retourne pas d’informations sur les index 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).
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 | PAR DÉFAUT
ID de la base de données. database_id est 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 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.
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 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 | PAR 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 | 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. 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é sur 1. Un index ou un tas non partitionné 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 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 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 de l’arborescence B qui effectue le suivi des données internes pour 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 est incrémenté uniquement pour les enregistrements supprimés qui ne sont pas marqués en premier comme fantôme. 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 text, 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 ont été envoyées 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 que le moteur de base de données a attendu sur un verrou de ligne. |
| row_lock_wait_in_ms | bigint | Nombre total de millisecondes que le moteur de base de données a 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 que le moteur de base de données a attendu sur un verrou de page. |
| page_lock_wait_in_ms | bigint | Nombre total de millisecondes que le moteur de base de données a attendu sur un verrou de page. |
| index_lock_promotion_attempt_count | bigint | Nombre cumulé de tentatives de remontée des verrous par le moteur de base de données. |
| index_lock_promotion_count | bigint | Nombre cumulé de fois que le moteur de base de données a augmenté les verrous. |
| page_latch_wait_count | bigint | Nombre cumulé de fois que le moteur de base de données a attendu en raison d’une contention de verrous. |
| page_latch_wait_in_ms | bigint | Nombre cumulé de millisecondes attendues par le moteur de base de données en raison d’une contention de verrous. |
| page_io_latch_wait_count | bigint | Nombre cumulé de fois que le moteur de base de données a attendu sur un verrou de page d’E/S. |
| page_io_latch_wait_in_ms | bigint | Nombre cumulé de millisecondes que 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 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'arbre B (B-tree) 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'arbre B (B-tree) 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'arbre B (B-tree) 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
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.
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 contention.
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 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 le nombre de fois où le moteur de base de données a tenté 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 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 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 Créer des 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.
Comment les compteurs du 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 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 tas ou un index actif aura probablement toujours ses métadonnées dans le cache, et les nombres cumulés peuvent refléter l’activité depuis le dernier instance de SQL Server a été démarré. 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-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) 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 = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) autorisation de retourner des informations sur toutes les bases de données, à l’aide du caractère générique de 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.
Le refus 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 @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 le 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 relatives aux 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)
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour