sys.dm_db_index_operational_stats (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Devuelve la actividad actual de E/S de nivel inferior, bloqueo, bloqueo temporal y método de acceso para 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.
Nota
sys.dm_db_index_operational_stats no devuelve información sobre los índices optimizados para memoria. Para obtener información sobre el uso de índices optimizados para memoria, vea sys.dm_db_xtp_index_stats (Transact-SQL).
Convenciones de sintaxis de Transact-SQL
Sintaxis
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 | 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 de la instancia de SQL Server. Si especifica NULL para database_id, también debe especificar NULL para object_id, index_id y partition_number.
Se puede especificar la función integrada DB_ID.
object_id | NULL | 0 | PREDETERMINADO
Id. de objeto de la tabla o vista en la que se encuentra 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 partition_number.
index_id | 0 | NULL | -1 | PREDETERMINADO
Id. del índice. index_id es int. Las entradas válidas son el número de identificador 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 partition_number.
partition_number | NULL | 0 | PREDETERMINADO
Número de partición en el objeto. partition_number 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.
partition_number se basa en 1. Un índice o montón sin particiones tiene partition_number establecido en 1.
Tabla devuelta
| Nombre de la columna | Tipo de datos | 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. |
| id_de_índice | int | Identificador del índice o montón. 0 = Montón |
| partition_number | int | Número de partición en base 1 en el índice o montón. |
| hobt_id | bigint | Se aplica a: SQL Server 2016 (13.x) y versiones posteriores, Azure SQL Database. Identificador del montón de datos o conjunto de filas de árbol B que realiza un seguimiento de los datos internos de un índice de almacén de columnas. NULL: no es un conjunto de filas de almacén de columnas interno. Para obtener más información, consulte sys.internal_partitions (Transact-SQL) |
| leaf_insert_count | bigint | Recuento acumulado de inserciones en el nivel hoja. |
| leaf_delete_count | bigint | Recuento acumulado de eliminaciones en el nivel hoja. leaf_delete_count solo se incrementa para los registros eliminados que no están marcados como fantasma en primer lugar. En primer lugar, en el caso de los registros eliminados que son fantasma, leaf_ghost_count se incrementa en su lugar. |
| leaf_update_count | bigint | Recuento acumulado de actualizaciones en el nivel hoja. |
| leaf_ghost_count | bigint | Recuento acumulado de filas en el nivel hoja marcadas como eliminadas, pero que aún no se han quitado. Este recuento no incluye registros que se eliminan inmediatamente sin marcarse 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_count | bigint | Recuento acumulado de inserciones por encima del nivel hoja. 0 = Montón o almacén de columnas |
| nonleaf_delete_count | bigint | Recuento acumulado de eliminaciones por encima del nivel hoja. 0 = Montón o almacén de columnas |
| nonleaf_update_count | bigint | Recuento acumulado de actualizaciones por encima del nivel hoja. 0 = Montón o almacén de columnas |
| leaf_allocation_count | bigint | Recuento 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_count | bigint | Recuento 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_count | bigint | Recuento acumulado de combinaciones de página en el nivel hoja. Siempre 0 para el índice de almacén de columnas. |
| nonleaf_page_merge_count | bigint | Recuento acumulado de combinaciones de página por encima del nivel hoja. 0 = Montón o 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. 0 = Índices |
| lob_fetch_in_pages | bigint | Recuento acumulado de páginas de objetos grandes (LOB) recuperadas desde la unidad de asignación LOB_DATA. Estas páginas contienen datos almacenados en columnas de tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max)y xml. Para obtener más información, vea Tipos de datos (Transact-SQL). |
| 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. 0 = Índice no clúster |
| lob_orphan_insert_count | bigint | Recuento acumulado de valores de LOB huérfanos insertados durante operaciones masivas. 0 = Índice no clúster |
| row_overflow_fetch_in_pages | bigint | Recuento 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(n),nvarchar(n), varbinary(n)) y sql_variant que se han insertado fuera de fila. |
| 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 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_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 ha esperado en un bloqueo de fila. |
| row_lock_wait_in_ms | bigint | Número total de milisegundos que el motor de base de datos ha esperado 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 ha esperado en un bloqueo de página. |
| page_lock_wait_in_ms | bigint | Número total de milisegundos que el motor de base de datos ha esperado 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 los bloqueos. |
| index_lock_promotion_count | bigint | Número acumulado de veces que el motor de base de datos ha escalado los bloqueos. |
| page_latch_wait_count | bigint | Número acumulado de veces que el motor de base de datos ha esperado debido a la contención de bloqueos temporales. |
| page_latch_wait_in_ms | bigint | Número acumulado de milisegundos que el motor de base de datos ha esperado debido a la contención de bloqueos temporales. |
| page_io_latch_wait_count | bigint | Número acumulado de veces que el motor de base de datos esperaba un bloqueo temporal de página de E/S. |
| page_io_latch_wait_in_ms | bigint | Número acumulado de milisegundos que el motor de base de datos ha esperado en un bloqueo temporal de E/S de página. |
| tree_page_latch_wait_count | bigint | Subconjunto de page_latch_wait_count que incluye solamente las páginas de nivel superior del árbol B. 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 solamente las páginas de nivel superior del árbol B. 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 solamente las páginas de nivel superior del árbol B. 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 solamente las páginas de nivel superior del árbol B. 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 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 0 para el índice de almacén de columnas. |
| page_compression_success_count | bigint | Nú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 0 para el índice de almacén de columnas. |
Nota
La documentación de SQL Server utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, SQL Server implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los almacenes de datos en memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.
Observaciones
Este objeto de administración dinámica no acepta parámetros correlacionados de CROSS APPLY y OUTER APPLY.
Puede utilizar sys.dm_db_index_operational_stats para realizar el seguimiento del 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 la 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 intentó adquirir bloqueos de fila y página.
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 operaciones 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.
Comentarios de columna
Los valores en lob_orphan_create_count y lob_orphan_insert_count siempre deben ser iguales.
El valor en 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 de LOB como columnas incluidas. Para más información, consulte Create Indexes with Included Columns. De forma similar, el valor en las columnas row_overflow_fetch_in_pages y row_overflow_fetch_in_bytes puede ser mayor que cero para índices no clúster si el índice contiene columnas que se pueden insertar de manera no consecutiva.
Cómo se restablecen los contadores de la caché de metadatos
Los datos devueltos por sys.dm_db_index_operational_stats existen solamente mientras está disponible el objeto de memoria caché de metadatos que representa el montón o el índice. 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 sobre esto, vea 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 lo tanto, es probable que un montón o índice activo tenga siempre sus metadatos en la memoria caché, y los recuentos acumulativos pueden reflejar la actividad desde que se inició por última vez 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.
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 de database_id y object_id . 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 de sys.dm_db_index_physical_stats (Transact-SQL).
Permisos
Necesita los siguientes permisos:
CONTROLpermiso en el objeto especificado dentro de la base de datosVIEW DATABASE STATEoVIEW DATABASE PERFORMANCE STATE(SQL Server 2022) permiso para devolver información sobre todos los objetos de la base de datos especificada mediante el carácter comodín de objeto @object_id = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) permiso para devolver información sobre todas las bases de datos mediante el carácter comodín de base de datos @database_id = NULL
La concesión VIEW DATABASE STATE permite devolver todos los objetos de la base de datos, independientemente de los permisos CONTROL denegados en objetos específicos.
VIEW DATABASE STATE Denegar no permite que se devuelvan todos los objetos de la base de datos, independientemente de los permisos CONTROL concedidos en objetos específicos. Además, cuando se especifica el carácter comodín @database_id=NULL de la base de datos, se omite la base de datos.
Para obtener más información, vea Vistas y funciones de administración dinámica (Transact-SQL) .
Ejemplos
A. Devolver información de una tabla especificada
En el ejemplo siguiente se devuelve información para todos los índices y particiones de la Person.Address tabla de la base de datos AdventureWorks2022. La ejecución de esta consulta requiere, como mínimo, permiso CONTROL en la tabla Person.Address.
Importante
Cuando se usan las funciones de Transact-SQL DB_ID y OBJECT_ID para devolver un valor de parámetro, asegúrese siempre de que se devuelve 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. La función sys.dm_db_index_operational_stats interpreta NULL como un valor de carácter 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'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. Devolver información de todas las tablas e índices
En el ejemplo siguiente se devuelve información de todas las tablas e índices dentro de la instancia de SQL Server. La ejecución de esta consulta requiere el permiso VIEW SERVER STATE.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Consulte también
Funciones y vistas de administración dinámica (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con índices (Transact-SQL)
Supervisión y optimización del 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)
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de