sys.dm_db_index_operational_stats (Transact-SQL)

 

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síBase de datos SQL de AzuresíAlmacenamiento de datos SQL de Azure síAlmacenamiento de datos paralelos

Devuelve las entradas y salidas de bajo nivel actuales, el bloqueo, el bloqueo temporal y la actividad de método de acceso de cada partición de una tabla o índice de la base de datos.

Los índices con optimización para memoria no aparecen en esta DMV.

System_CAPS_ICON_note.jpg Nota


Sys.dm_db_index_operational_stats no devuelve información acerca de los índices con optimización para memoria. Para obtener información sobre el uso de los índices con optimización para memoria, vea sys.dm_db_xtp_index_stats ( Transact-SQL ).

Topic link icon Convenciones de sintaxis de Transact-SQL

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

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

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

La función integrada DB_ID puede especificarse.

object_id | NULL | 0 | VALOR PREDETERMINADO
Identificador de objeto de la tabla o vista donde está activado el índice. object_id es int.

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

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

index_id | 0 | NULL | -1 | VALOR PREDETERMINADO
Id. del índice. index_id es int. Las entradas válidas son el número de identificación de un índice, 0 si object_id es un montón, NULL, -1 o DEFAULT. El valor predeterminado es -1. NULL, -1 y DEFAULT son valores equivalentes en este contexto.

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

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

Especifique NULL para devolver información en memoria caché de todas las particiones del índice o montón.

número_de_partición está basado en 1. Un montón o índice sin particiones tiene número_de_partición establecido en 1.

Nombre de columnaTipo de datosDescription
database_idsmallintId. de la base de datos.
object_idintIdentificador de la tabla o vista.
index_idintIdentificador del índice o montón.

0 = Montón
hobt_idbigintSe aplica a: SQL Server (SQL Server 2016 hasta la versión actual), Base de datos SQL de Azure.

Id. del montón de datos o conjunto de filas de árbol B que realiza un seguimiento de los datos internos para un índice de almacén de columnas.

NULL: esto no es un conjunto de filas del almacén de columnas interno.

Para obtener más información, consulte sys.internal_partitions ( Transact-SQL )
número_de_particiónintNúmero de partición en base 1 en el índice o montón.
leaf_insert_countbigintRecuento acumulado de inserciones en el nivel hoja.
leaf_delete_countbigintRecuento acumulado de eliminaciones en el nivel hoja. leaf_delete_count sólo aumenta con registros eliminados que no están marcados como fantasma en primer lugar. Para los registros eliminados se fantasma en primer lugar, leaf_ghost_count se incrementa en su lugar.
leaf_update_countbigintRecuento acumulado de actualizaciones en el nivel hoja.
leaf_ghost_countbigintRecuento acumulado de filas en el nivel hoja marcadas como eliminadas, pero que aún no se han quitado. Este número no incluye los registros que se eliminan inmediatamente sin que se marcara como fantasma. Estas filas se quitan mediante un subproceso de limpieza a intervalos establecidos. En este valor no se incluyen las filas retenidas a causa de una transacción de aislamiento de instantánea pendiente.
nonleaf_insert_countbigintRecuento acumulado de inserciones por encima del nivel hoja.

0 = Montón o almacén de columnas
nonleaf_delete_countbigintRecuento acumulado de eliminaciones por encima del nivel hoja.

0 = Montón o almacén de columnas
nonleaf_update_countbigintRecuento acumulado de actualizaciones por encima del nivel hoja.

0 = Montón o almacén de columnas
leaf_allocation_countbigintRecuento acumulado de asignaciones de página en el nivel hoja en el índice o el montón.

En un índice, una asignación de página corresponde a una división de página.
nonleaf_allocation_countbigintRecuento acumulado de asignaciones de página ocasionadas por divisiones de página por encima del nivel hoja.

0 = Montón o almacén de columnas
leaf_page_merge_countbigintRecuento acumulado de combinaciones de página en el nivel hoja. Siempre es 0 para el índice de almacén de columnas.
nonleaf_page_merge_countbigintRecuento acumulado de combinaciones de página por encima del nivel hoja.

0 = Montón o almacén de columnas
range_scan_countbigintRecuento acumulado de recorridos de tabla e intervalo iniciados en el índice o el montón.
singleton_lookup_countbigintRecuento acumulado de recuperaciones de filas únicas del índice o montón.
forwarded_fetch_countbigintRecuento de filas que se capturan mediante un registro de reenvío.

0 = Índices
lob_fetch_in_pagesbigintRecuento acumulado de páginas de objetos grandes (LOB) recuperadas desde la unidad de asignación LOB_DATA. Estas páginas contienen datos que se almacenan en columnas de tipo texto, ntext, imagen, varchar (max), nvarchar (max), varbinary (max), y xml. Para obtener más información, vea Tipos de datos (Transact-SQL).
lob_fetch_in_bytesbigintRecuento acumulado de bytes de datos de LOB recuperados.
lob_orphan_create_countbigintRecuento acumulado de valores de LOB huérfanos creados para operaciones masivas.

0 = Índice no clúster
lob_orphan_insert_countbigintRecuento acumulado de valores de LOB huérfanos insertados durante operaciones masivas.

0 = Índice no clúster
row_overflow_fetch_in_pagesbigintRecuento acumulado de páginas de datos de desbordamiento de fila recuperadas desde la unidad de asignación ROW_OVERFLOW_DATA.

Estas páginas contienen datos almacenados en columnas de tipo varchar, nvarchar (n), varbinary, y sql_variant que se han insertado de manera no consecutiva.
row_overflow_fetch_in_bytesbigintRecuento acumulado de bytes de datos de desbordamiento de fila recuperados.
column_value_push_off_row_countbigintRecuento 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_countbigintRecuento 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 trasladar uno o más valores de manera no consecutiva de las unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA a la unidad de asignación IN_ROW_DATA.
row_lock_countbigintNúmero acumulado de bloqueos de fila solicitados.
row_lock_wait_countbigintNúmero acumulado de veces que el Motor de base de datos ha esperado en un bloqueo de fila.
row_lock_wait_in_msbigintNúmero total de milisegundos que el Motor de base de datos ha esperado en un bloqueo de fila.
page_lock_countbigintNúmero acumulado de bloqueos de página solicitados.
page_lock_wait_countbigintNúmero acumulado de veces que el Motor de base de datos ha esperado en un bloqueo de página.
page_lock_wait_in_msbigintNúmero total de milisegundos que el Motor de base de datos ha esperado en un bloqueo de página.
index_lock_promotion_attempt_countbigintNúmero acumulado de veces que el Motor de base de datos ha intentado concentrar bloqueos.
index_lock_promotion_countbigintNúmero acumulado de veces que el Motor de base de datos ha concentrado bloqueos.
page_latch_wait_countbigintNúmero acumulado de veces que el Motor de base de datos ha esperado a causa de la contención de bloqueos temporales.
page_latch_wait_in_msbigintNúmero acumulado de milisegundos que el Motor de base de datos ha esperado a causa de la contención de bloqueos temporales.
page_io_latch_wait_countbigintNúmero acumulado de veces que el Motor de base de datos ha esperado en un bloqueo temporal de E/S de páginas.
page_io_latch_wait_in_msbigintNúmero acumulado de milisegundos que el Motor de base de datos ha esperado en un bloqueo temporal de E/S de páginas.
tree_page_latch_wait_countbigintSubconjunto de page_latch_wait_count que incluye solamente 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_msbigintSubconjunto de page_latch_wait_in_ms que incluye solamente 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_countbigintSubconjunto de page_io_latch_wait_count que incluye solamente 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_msbigintSubconjunto de page_io_latch_wait_in_ms que incluye solamente 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_countbigintNúmero de páginas que se evaluaron para la compresión en el nivel de página para particiones específicas de una tabla, un índice o una vista indizada. Incluye páginas que no se comprimieron porque no se consiguieron ahorros de espacio significativos. Siempre es 0 para el índice de almacén de columnas.
page_compression_success_countbigintNúmero de páginas de datos que se comprimieron utilizando la compresión de páginas para particiones específicas de una tabla, un índice o una vista indizada. Siempre es 0 para el índice de almacén de columnas.

Este objeto de administración dinámica no acepta parámetros correlacionado de CROSS APPLY y OUTER APPLY.

Puede usar sys.dm_db_index_operational_stats para realizar el seguimiento de la cantidad de tiempo que los usuarios tienen que esperar para leer o escribir en una tabla, índice o partición e identificar las tablas o índices que tienen puntos o actividades de E/S importantes.

Use las columnas siguientes para identificar áreas de contención.

Para analizar un patrón de acceso común a la partición del índice o tabla, utilice 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 y bloqueos temporales, utilice estas columnas:

  • page_latch_wait_count y page_latch_wait_in_ms

    Estas columnas indican si existe una contención de bloqueos temporales en el índice o montón, y la importancia de la misma.

  • row_lock_count y page_lock_count

    Estas columnas indican cuántas veces el Motor de base de datos ha intentado adquirir bloqueos de página y fila.

  • row_lock_wait_in_ms y page_lock_wait_in_ms

    Estas columnas indican si existe una contención de bloqueos en el índice o montón y la importancia de la misma.

Para analizar estadísticas de E/s físicas en una partición de índice o montón

  • page_io_latch_wait_count y page_io_latch_wait_in_ms

    Estas columnas indican si las operaciones de E/S físicas tuvieron problemas para traer las páginas de índice o montón a memoria y cuántas operaciones de E/S tuvieron problemas.

Los valores de lob_orphan_create_count y lob_orphan_insert_count siempre deben ser iguales.

El valor de las columnas lob_fetch_in_pages y lob_fetch_in_bytes puede ser mayor que cero para índices no clúster que contengan una o varias columnas LOB como columnas incluidas. Para más información, consulte Create Indexes with Included Columns. De forma similar, el valor de las columnas row_overflow_fetch_in_pages y row_overflow_fetch_in_bytes puede ser mayor que 0 para los índices no clúster si el índice contiene columnas que se pueden insertar filas no consecutivas.

Los datos devueltos por sys.dm_db_index_operational_stats existe solo mientras el objeto de caché de metadatos que representa el montón o índice está disponible. Estos datos nunca son permanentes ni transaccionalmente coherentes. Esto significa que no se pueden utilizar estos contadores para determinar si se ha utilizado un índice o cuándo se usó por última vez. Para obtener información acerca de esto, consulte sys.dm_db_index_usage_stats ( Transact-SQL ).

Los valores de cada columna se establecerán en cero siempre que los metadatos del montón o índice se incorporen a la memoria caché de metadatos y las estadísticas se acumulen hasta que el objeto de la memoria caché se quite de la memoria caché de metadatos. Por tanto, es probable que un índice o montón activo tenga siempre sus metadatos en la memoria caché, y los recuentos acumulados puedan reflejar la actividad desde la última vez que se inició la instancia de SQL Server. Los metadatos de un índice o montón menos activo entrarán y saldrán de la memoria caché según se utilicen. Como resultado, sus valores pueden estar disponibles o no. La eliminación de un índice hará que las estadísticas correspondientes se quiten de la memoria y que la función ya no informe de las mismas. Otras operaciones DDL en el índice pueden hacer que el valor de las estadísticas se restablezca en cero.

Puede usar el Transact-SQL funciones DB_ID y OBJECT_ID para especificar un valor para el database_id y object_id parámetros. Sin embargo, el envío de valores no válidos a estas funciones puede provocar resultados no deseados. Asegúrese de que se devuelva un identificador válido cuando utilice DB_ID u OBJECT_ID. Para obtener más información, vea la sección comentarios en sys.dm_db_index_physical_stats ( Transact-SQL ).

Necesita los siguientes permisos:

  • Permiso CONTROL en el objeto especificado en la base de datos

  • Permiso VIEW DATABASE STATE para devolver información sobre todos los objetos de la base de datos especificada, utilizando el carácter comodín de objeto @object_id = NULL

  • Permiso VIEW SERVER STATE para devolver información sobre todas las bases de datos, mediante el carácter comodín de base de datos @database_id = NULL

El permiso VIEW DATABASE STATE permite devolver todos los objetos de la base de datos, independientemente de los permisos CONTROL denegados en objetos específicos.

Si se deniega el permiso VIEW DATABASE STATE, no se puede devolver ningún objeto de la base de datos, independientemente de que se hayan concedido permisos CONTROL a objetos específicos. Además, cuando el carácter comodín de base de datos @database_id= se especifica NULL, se omite la base de datos.

Para obtener más información, vea funciones y vistas de administración dinámica ( Transact-SQL ).

A. Devolver información de una tabla especificada

En el siguiente ejemplo se devuelve información de todos los índices y particiones de la tabla Person.Address en la base de datos AdventureWorks2012. La ejecución de esta consulta requiere, como mínimo, permiso CONTROL en la tabla Person.Address.

System_CAPS_ICON_important.jpg Importante


Cuando utilice las funciones DB_ID y OBJECT_ID de Transact-SQL para devolver un valor de parámetro, asegúrese de que siempre se devuelva un identificador válido. Si el nombre de objeto o base de datos no se puede encontrar, por ejemplo, cuando no existe o se ha escrito incorrectamente, las dos funciones devolverán NULL. El 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 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. Devolver información de todas las tablas e índices

En el siguiente ejemplo se devuelve información de todas las tablas e índices en la instancia de SQL Server. Para ejecutar esta consulta requiere el permiso VIEW SERVER STATE.

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

Funciones y vistas de administración dinámica ( Transact-SQL )
Índice relacionadas con funciones y vistas de administración dinámica ( Transact-SQL )
Supervisar y optimizar el rendimiento
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 )

Adiciones de comunidad

AGREGAR
Mostrar: