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.
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 à propos des index mémoire optimisés.Pour plus d'informations sur l'utilisation des index mémoire optimisés, consultez sys.dm_db_xtp_index_stats (Transact-SQL).
S'applique à : SQL Server (SQL Server 2008 via la version actuelle, Base de données SQL Windows Azure (version initiale via la version actuelle. |
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 | DEFAULT
ID 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.
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
ID 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 la 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 cumulatif d'insertions de niveau feuille. |
leaf_delete_count |
bigint |
Nombre cumulatif de suppressions de niveau feuille. |
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. 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 un 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) ou 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 cumulatif 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 cumulatif de verrous de page demandés. |
page_lock_wait_count |
bigint |
Nombre cumulatif 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 cumulatif de fois où le Moteur de base de données a essayé de promouvoir des verrous. |
index_lock_promotion_count |
bigint |
Nombre cumulatif de fois où le Moteur de base de données a promu des verrous. |
page_latch_wait_count |
bigint |
Nombre cumulatif de fois où le Moteur de base de données a attendu à cause d'une contention 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'une contention de verrous internes. |
page_io_latch_wait_count |
bigint |
Nombre cumulatif 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 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 un 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 un index columnstore. |
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 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 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.
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 nombres 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-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 :
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 AdventureWorks2012 : 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'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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)