sys.dm_db_index_operational_stats (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Microsoft Fabric

Devuelve el acceso a datos de nivel inferior, el bloqueo y las estadísticas de bloqueo temporal para cada partición de una tabla o índice de una base de datos.

Convenciones de sintaxis de Transact-SQL

Syntax

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 }
)

Argumentos

{ database_id | NULL | 0 | DEFAULT }

Identificador de la base de datos. database_id es smallint. Las entradas válidas son el número ID de una base de datos, NULL, 0, o DEFAULT. El valor predeterminado es 0. NULL, 0y DEFAULT son valores equivalentes en este contexto.

Especifique NULL para devolver información para todas las bases de datos de la instancia de SQL Server. Si especifica para database_id, también debe especificar NULL para object_id, NULL y partition_number.

Se puede especificar la función integrada DB_ID .

{ object_id | NULL | 0 | DEFAULT }

Id. de objeto de la tabla o vista en la que está el índice. object_id es int.

Las entradas válidas son el número ID de una tabla y vista, NULL, 0, o DEFAULT. El valor predeterminado es 0. NULL, 0y DEFAULT son valores equivalentes en este contexto.

Especifique NULL para devolver información para todas las tablas y vistas de la base de datos especificada. Si especifica para object_id, también debe especificar NULL para index_id y NULL.

{ index_id | 0 | NULL | -1 | DEFAULT }

Id. del índice. index_id es inteligencia. Las entradas válidas son el número ID de un índice, 0 si object_id es un heap, NULL, -1, o DEFAULT. El valor predeterminado es -1. NULL, -1y DEFAULT son valores equivalentes en este contexto.

Especifique NULL para devolver información para todos los índices de una tabla base o vista. Si especifica NULL para index_id, también debe especificar NULL para partition_number.

{ partition_number | NULL | 0 | DEFAULT }

Número de partición en el objeto. partition_number es int. Las entradas válidas son el partition_number de un índice o montón, NULL, 0o DEFAULT. El valor predeterminado es 0. NULL, 0y DEFAULT son valores equivalentes en este contexto.

Especificar NULL que devuelva la información de todas las particiones del índice o heap.

partition_number se basa en 1. Un índice o montón no particionado tiene partition_number establecido en 1.

Tabla devuelta

Nombre de la columna Tipo de dato Descripción
database_id smallint Id. de la base de datos.

En Azure SQL Database, los valores son únicos dentro de una base de datos única o un grupo elástico, pero no dentro de un servidor lógico.
object_id int Identificador de la tabla o vista. Para más información, consulta sys.objects.
index_id int Identificador del índice o montón. Para más información, consulta sys.indexes.
partition_number int Número de partición en base 1 en el índice o montón. Para más información, consulte sys.partitions.
hobt_id bigint Identificador del montón de datos o del conjunto de filas de árbol B que realiza un seguimiento de los datos internos de un índice de almacén de columnas.

NULL - Esto no es un conjunto de filas interno de Columnstore.

Para obtener más información, consulte sys.internal_partitions.
leaf_insert_count bigint Recuento acumulado de inserciones de nivel hoja. Para obtener más información sobre los niveles de índice, consulte Guía de diseño y arquitectura de índices.
leaf_delete_count bigint Recuento acumulado de eliminaciones de nivel hoja. leaf_delete_count solo se incrementa para registros eliminados que no están marcados como fantasma primero. En el caso de los registros eliminados que son fantasmas primero, leaf_ghost_count se incrementa en su lugar.
leaf_update_count bigint Recuento acumulado de actualizaciones de nivel hoja.
leaf_ghost_count bigint Recuento acumulado de filas de nivel hoja marcadas como eliminadas, pero que aún no se han quitado. Este recuento no incluye registros que se eliminan inmediatamente sin ser marcados como fantasmas. Un subproceso de limpieza quita las filas fantasma a intervalos establecidos. Este valor no incluye filas fantasma que se mantienen debido a una transacción de instantánea pendiente.
nonleaf_insert_count bigint Recuento acumulado de inserciones por encima del nivel hoja. Solo se aplica a los índices de árbol B. 0 para montones o índices de almacén de columnas.
nonleaf_delete_count bigint Recuento acumulado de eliminaciones por encima del nivel hoja. Solo se aplica a los índices de árbol B. 0 para montones o índices de almacén de columnas.
nonleaf_update_count bigint Recuento acumulado de actualizaciones por encima del nivel hoja. Solo se aplica a los índices de árbol B. 0 para montones o índices de almacén de columnas.
leaf_allocation_count bigint Recuento acumulado de asignaciones de páginas de nivel hoja en el índice o montón.

En un índice, una asignación de página corresponde a una división de página.
nonleaf_allocation_count bigint Recuento acumulado de asignaciones de página ocasionadas por divisiones de página por encima del nivel hoja. Solo se aplica a los índices de árbol B. 0 para montones o índices de almacén de columnas.
leaf_page_merge_count bigint Recuento acumulado de combinaciones de página en el nivel hoja. Siempre 0 para los índices de almacén de columnas.
nonleaf_page_merge_count bigint Recuento acumulado de combinaciones de página por encima del nivel hoja. Solo se aplica a los índices de árbol B. 0 para montones o índices de almacén de columnas.
range_scan_count bigint Recuento acumulado de recorridos de tabla e intervalo iniciados en el índice o el montón.
singleton_lookup_count bigint Recuento acumulado de recuperaciones de filas únicas del índice o montón.
forwarded_fetch_count bigint Recuento de filas que se capturan mediante un registro de reenvío. Solo se aplica a montones, 0 para índices de árbol B.
lob_fetch_in_pages bigint Recuento acumulado de páginas de objetos grandes (LOB) recuperadas de una LOB_DATA unidad de asignación. Estas páginas contienen datos almacenados en columnas de texto tipográfico, ntext, image, varchar(max),nvarchar(max), varbinary(max), xml y json. Para obtener más información, vea Tipos de datos.
lob_fetch_in_bytes bigint Recuento acumulado de bytes de datos de LOB recuperados.
lob_orphan_create_count bigint Recuento acumulado de valores de LOB huérfanos creados para operaciones masivas. Solo se aplica a montones e índices agrupados de árbol B, 0 para índices de almacén de columnas y no agrupados.
lob_orphan_insert_count bigint Recuento acumulado de valores de LOB huérfanos insertados durante operaciones masivas. Solo se aplica a montones e índices agrupados de árbol B, 0 para índices de almacén de columnas y no agrupados.
row_overflow_fetch_in_pages bigint Recuento acumulado de páginas de datos de desbordamiento de fila recuperadas de una ROW_OVERFLOW_DATA unidad de asignación.

Estas páginas contienen datos almacenados en columnas de tipo varchar(n), nvarchar(n), varbinary(n)y sql_variant para filas grandes.
row_overflow_fetch_in_bytes bigint Recuento acumulado de bytes de datos de desbordamiento de fila recuperados.
column_value_push_off_row_count bigint Recuento acumulado de valores de columna de datos de LOB y datos de desbordamiento de fila que se han insertado de manera no consecutiva para que una fila insertada o actualizada entre en una página.
column_value_pull_in_row_count bigint Recuento acumulado de valores de columna de datos de LOB y datos de desbordamiento de fila que se han extraído de manera consecutiva. Esto ocurre cuando una operación de actualización libera espacio en un registro y proporciona una oportunidad para extraer uno o varios valores fuera de fila de una LOB_DATA o ROW_OVERFLOW_DATA unidades de asignación a la IN_ROW_DATA unidad de asignación.
row_lock_count bigint Número acumulado de bloqueos de fila solicitados.
row_lock_wait_count bigint Número acumulado de veces que el Motor de base de datos espera en un bloqueo de fila.
row_lock_wait_in_ms bigint Número total de milisegundos que Motor de base de datos esperaron en un bloqueo de fila.
page_lock_count bigint Número acumulado de bloqueos de página solicitados.
page_lock_wait_count bigint Número acumulado de veces que el Motor de base de datos espera en un bloqueo de página.
page_lock_wait_in_ms bigint Número total de milisegundos que el Motor de base de datos espera en un bloqueo de página.
index_lock_promotion_attempt_count bigint Número acumulado de veces que el Motor de base de datos intentó escalar bloqueos.
index_lock_promotion_count bigint Número acumulado de veces que el Motor de base de datos bloqueos escalados.
page_latch_wait_count bigint Número acumulado de veces que el motor de base de datos esperaba adquirir un bloqueo temporal.
page_latch_wait_in_ms bigint Número acumulado de milisegundos que el motor de base de datos esperaba para adquirir un bloqueo temporal.
page_io_latch_wait_count bigint Número acumulado de veces que el motor de base de datos ha esperado en un bloqueo temporal de E/S de página.
page_io_latch_wait_in_ms bigint Número acumulado de milisegundos que el Motor de base de datos espera en un bloqueo temporal de E/S de página.
tree_page_latch_wait_count bigint Subconjunto de page_latch_wait_count que incluye solo las páginas de árbol B de nivel superior. Siempre es 0 para un índice de montón o de almacén de columnas.
tree_page_latch_wait_in_ms bigint Subconjunto de page_latch_wait_in_ms que incluye solo las páginas de árbol B de nivel superior. Siempre es 0 para un índice de montón o de almacén de columnas.
tree_page_io_latch_wait_count bigint Subconjunto de page_io_latch_wait_count que incluye solo las páginas de árbol B de nivel superior. Siempre es 0 para un índice de montón o de almacén de columnas.
tree_page_io_latch_wait_in_ms bigint Subconjunto de page_io_latch_wait_in_ms que incluye solo las páginas de árbol B de nivel superior. Siempre es 0 para un índice de montón o de almacén de columnas.
page_compression_attempt_count bigint Número de páginas que se evaluaron para PAGE compresión de nivel para una partición específica de una tabla, índice o vista indexada. Incluye páginas que no se comprimieron porque no se lograron ahorros significativos. Siempre 0 para los índices de almacén de columnas.
page_compression_success_count bigint Número de páginas de datos comprimidas usando PAGE compresión para particiones específicas de una tabla, índice o vista indexada. Siempre 0 para los índices de almacén de columnas.
version_generated_inrow bigint Recuento acumulado de versiones en fila con carga generada en el montón o árbol B para una operación de actualización, combinación o inserción sobre fantasma. Una versión en fila almacena la imagen de fila antigua (o una diferencia) directamente en la fila, evitando un viaje al almacén de versiones. Este recuento es un superconjunto que incluye versiones contadas por insert_over_ghost_version_inrow. Para obtener más información sobre las versiones en fila y fuera de fila, consulte Espacio usado por el almacén de versiones persistente (PVS).
version_generated_offrow bigint Recuento acumulado de versiones insertadas en el almacenamiento fuera de fila para una operación de eliminación, de árbol B o loB de eliminación, actualización, combinación o inserción de objetos fantasma. Se genera una versión fuera de la fila cuando la imagen antigua de la fila no puede mantenerse en la fila. Este recuento es un superconjunto que incluye versiones contadas por ghost_version_offrow y insert_over_ghost_version_offrow.
ghost_version_inrow bigint Recuento acumulado de veces que una eliminación o actualización (realizada como una eliminación seguida de una inserción) marcó la fila existente como un fantasma con información de control de versiones en fila. La versión en fila almacena solo una marca de tiempo de transacción y una carga útil de longitud cero, de modo que deshacer la eliminación solo requiere deshospedar la fila.
ghost_version_offrow bigint Recuento acumulado de veces que una eliminación o actualización (realizada como una eliminación seguida de una inserción) insertó los datos de columna de fila o LOB existentes en el almacenamiento fuera de fila, dejando un código auxiliar en la fila para obtener información de control de versiones. Este contador se incrementa junto con version_generated_offrow durante las operaciones fantasma.
insert_over_ghost_version_inrow bigint Recuento acumulado de versiones en fila con carga generada para una operación insert-over-ghost de árbol B. Un elemento insert-over-ghost se produce cuando se inserta una nueva fila en la ranura de un registro fantasma anteriormente, ya sea desde una eliminación explícita seguida de una inserción, o desde una actualización o combinación implementada como una eliminación seguida de una inserción. Este contador es un subconjunto de version_generated_inrow.
insert_over_ghost_version_offrow bigint Recuento acumulado de veces que la fila fantasma existente se insertó en el almacenamiento fuera de fila durante una operación de inserción de árbol B sobre fantasma, dejando un código auxiliar en la fila recién insertada para obtener información de control de versiones. Este contador es un subconjunto de version_generated_offrow.
compaction_attempt_count bigint Recuento acumulado de intentos de autocompactación por índice. Para más información, véase Compactación automática de índices (vista previa).
compaction_complete_count bigint Recuento acumulado de autocompactaciones completas del índice.
compaction_skip_count bigint Recuento acumulado de autocompactaciones de índices saltados. Para más información sobre las razones de los saltos, véase Usar un evento extendido para monitorizar estadísticas de compactación.
compaction_ineligible_count bigint El recuento acumulado de intentos de compactación se saltó porque una página no era elegible para autocompactación.
compaction_failure_count bigint Recuento acumulado de intentos fallidos de compactación.
compaction_row_move_count bigint El número acumulado de filas se movía de una página a otra como parte de la autocompactación.
compaction_page_deallocation_count bigint Número acumulado de páginas que se desasignaron tras mover todas las filas a otra página.

Note

La documentación utiliza el término árbol B generalmente en referencia a los índices. En los índices rowstore, el Motor de base de datos implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los índices de tablas optimizadas para memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

Comentarios

Esta función no devuelve información sobre los índices en tablas optimizadas para memoria. Para información sobre índices en tablas optimizadas para memoria, véase sys.dm_db_xtp_index_stats.

Esta función no acepta parámetros correlacionados de CROSS APPLY y OUTER APPLY.

Puede usar sys.dm_db_index_operational_stats para realizar un seguimiento de las estadísticas de operaciones de lectura y escritura de datos y bloqueo, bloqueo temporal de página y bloqueo temporal de página para una tabla, índice o partición. Puede identificar las tablas, los índices y las particiones que se encuentran con una actividad o contención significativas.

Las estadísticas se proporcionan en el nivel de partición y son sumadas. Esto significa que puede obtener estadísticas de nivel de índice o de tabla escribiendo una consulta de agregación en T-SQL. Para obtener más información, consulte el ejemplo análisis de índice y busca todas las tablas .

Para analizar las estadísticas de operación de lectura y escritura de una tabla, índice o partición, use estas columnas:

  • leaf_insert_count
  • leaf_delete_count
  • leaf_update_count
  • leaf_ghost_count
  • range_scan_count
  • singleton_lookup_count

Para identificar la contención de bloqueos temporales, use estas columnas:

  • page_latch_wait_count
  • page_latch_wait_in_ms

Para identificar la contención de bloqueo, use estas columnas:

  • row_lock_count
  • page_lock_count
  • row_lock_wait_in_ms
  • page_lock_wait_in_ms

Para analizar las estadísticas de E/S físicas, use estas columnas:

  • page_io_latch_wait_count
  • page_io_latch_wait_in_ms

Comentarios de columna

Los valores de las columnas lob_fetch_in_pages y lob_fetch_in_bytes pueden ser mayores que cero para los índices no clúster que contienen una o varias columnas LOB como columnas incluidas. Para obtener más información, consulte Creación de índices con columnas incluidas. De forma similar, los valores de las columnas row_overflow_fetch_in_pages y row_overflow_fetch_in_bytes pueden ser mayores que 0 para los índices no agrupados si el índice contiene filas grandes.

Cómo se restablecen los contadores de la caché de metadatos

Los datos devueltos por sys.dm_db_index_operational_stats solo existen siempre y cuando haya disponible un objeto de caché de metadatos que represente el montón o árbol B. Estos datos no son persistentes. Esto significa que no puedes usar estos contadores para determinar de forma concluyente si se utilizó un índice o no, o cuándo se utilizó por última vez. En su lugar, usa sys.dm_db_index_usage_stats.

Los valores de cada columna numérica se establecen en cero siempre que los metadatos del montón o árbol B se introducen en la caché de metadatos. Las estadísticas se acumulan hasta que el objeto de caché se quita de la caché de metadatos. Un montón activo o árbol B normalmente tiene sus metadatos en la memoria caché y los recuentos acumulados reflejan la actividad desde que se inició por última vez la instancia del motor de base de datos. Los metadatos de un heap o B-tree menos activo pueden moverse dentro y fuera de la caché mientras se usa, especialmente si la instancia del Motor de base de datos está bajo presión de memoria. Como resultado, es posible que las estadísticas operativas de índice a veces no se reflejen en sys.dm_db_index_operational_stats. Esto no es habitual.

Las estadísticas se quitan de la memoria caché y esta función ya no las notifica si se quita una tabla o índice, o si se trunca una partición. Otras operaciones DDL en el índice podrían provocar que el valor de las estadísticas se restablezca a cero.

Uso de funciones del sistema para especificar valores de parámetro

Puede usar las funciones de Transact-SQL DB_ID y OBJECT_ID para especificar un valor para los parámetros database_id y object_id . Sin embargo, pasar valores que no son válidos a estas funciones puede causar resultados no deseados. Asegúrese siempre de que se devuelve un identificador válido al usar DB_ID o OBJECT_ID. Para obtener más información, vea Devolver información para una tabla especificada.

Permissions

Necesita los siguientes permisos:

  • CONTROL permiso en el objeto especificado dentro de la base de datos

  • VIEW DATABASE STATEo VIEW DATABASE PERFORMANCE STATE permiso para devolver información sobre todos los objetos dentro de la base de datos especificada, cuando no se especifica un valor para ().@object_id

  • VIEW SERVER STATEo VIEW SERVER PERFORMANCE STATE permiso para devolver información sobre todas las bases de datos, cuando no se especifica un valor para ().@database_id

Conceder VIEW DATABASE STATE o VIEW SERVER PERFORMANCE STATE permitir que se devuelvan todos los objetos de la base de datos, independientemente de los CONTROL permisos denegados en objetos específicos.

VIEW DATABASE STATE Denegar o VIEW SERVER PERFORMANCE STATE no permitir que se devuelvan todos los objetos de la base de datos, independientemente de los CONTROL permisos concedidos en objetos específicos.

Para más información, consulte vistas y funciones de gestión dinámica del sistema.

Examples

Devolver información para una tabla especificada

El siguiente ejemplo devuelve información para todos los índices y particiones de la Person.Address tabla en la base de datos AdventureWorks2025.

Importante

Cuando uses las funciones DB_ID Transact-SQL y OBJECT_ID devuelvas un valor de parámetro, asegúrate siempre de que se devuelva un ID válido. Si no se encuentra el nombre de la base de datos o del objeto, como cuando no existen o se escriben incorrectamente, ambas funciones devuelven NULL. La sys.dm_db_index_operational_stats función interpreta NULL como un valor comodín que especifica todas las bases de datos o todos los objetos. Puesto que ésta puede ser una operación accidental, los ejemplos de esta sección demuestran una forma segura para determinar los Id. de bases de datos y objetos.

DECLARE @db_id AS INT = DB_ID(N'AdventureWorks2025');
DECLARE @object_id AS INT = OBJECT_ID(N'AdventureWorks2025.Person.Address');

SELECT *
FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL)
WHERE @db_id IS NOT NULL
      AND @object_id IS NOT NULL;

Devolver información para todas las tablas e índices

En el ejemplo siguiente se devuelve información de todas las tablas e índices de una instancia del motor de base de datos.

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

Análisis de índices y busca todas las tablas

En el ejemplo siguiente se agregan datos de nivel de partición para devolver la búsqueda de índice y examinar las estadísticas de todas las tablas de la base de datos actual.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       COUNT(DISTINCT(index_id)) AS index_count,
       COUNT(DISTINCT(partition_number)) AS partition_count,
       SUM(range_scan_count) AS index_scan_count,
       SUM(singleton_lookup_count) AS index_seek_count
FROM sys.dm_db_index_operational_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT)
GROUP BY OBJECT_SCHEMA_NAME(object_id),
         OBJECT_NAME(object_id)
ORDER BY schema_name, object_name;

Contenido relacionado

  • Last updated on