sys.dm_db_index_operational_stats

Actualizado: 17 de julio de 2006

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

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 | DEFAULT
  Id. de la base de datos. database_id es de tipo 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 en database_id, también debe especificar NULL en object_id, index_id y partition_number.

  Se puede especificar la función integrada DB_ID. Al usar DB_ID sin especificar ningún nombre de base de datos, el nivel de compatibilidad de la base de datos actual debe ser 90.

• object_id | NULL | 0 | DEFAULT
  Id. de objeto de la tabla o vista donde está activado el índice. object_id es de tipo 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 de todas las tablas y vistas de la base de datos especificada. Si especifica NULL en object_id, también debe especificar NULL en index_id y partition_number.

• index_id | 0 | NULL | -1 | DEFAULT
  Id. del índice. index_id es de tipo int. Las entradas válidas son el número de Id. 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 de todos los índices de una tabla o vista base. Si especifica NULL en index_id, también debe especificar NULL en partition_number.

• partition_number | NULL | 0 | DEFAULT
  Número de partición en el objeto. partition_number es de tipo int. Las entradas válidas son el número de partición (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 de todas las particiones del índice o montón.

  partition_number está en base 1. Un índice o montón sin particiones tiene partition_number establecido en 1.

Tabla devuelta

Nombre de columna	Tipo de datos	Descripción
database_id	smallint	Id. de la base de datos.
object_id	int	Id. de la tabla o vista.
index_id	int	Id. 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.
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_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. Estas filas se quitan mediante un subproceso de limpieza a intervalos establecidos. Este valor no incluye filas conservadas a causa del resultado de una transacción de aislamiento de instantánea. Para obtener información acerca de las transacciones de aislamiento de instantánea, vea Niveles de aislamiento basado en el control de versiones de filas del motor de base de datos.
nonleaf_insert_count	bigint	Recuento acumulado de inserciones por encima del nivel hoja. 0 = Montón
nonleaf_delete_count	bigint	Recuento acumulado de eliminaciones por encima del nivel hoja. 0 = Montón
nonleaf_update_count	bigint	Recuento acumulado de actualizaciones por encima del nivel hoja. 0 = Montón
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 causadas por divisiones de página por encima del nivel hoja. 0 = Montón
leaf_page_merge_count	bigint	Recuento acumulado de mezclas de página en el nivel hoja.
nonleaf_page_merge_count	bigint	Recuento acumulado de mezclas de página por encima del nivel hoja. 0 = Montón
range_scan_count	bigint	Recuento acumulado de búsquedas de tabla e intervalo iniciadas en el índice o 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 recuperaron mediante un registro reenviado. 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 que se almacenan 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). Para obtener más información sobre las unidades de asignación, vea Organización de tablas e índices.
lob_fetch_in_bytes	bigint	Recuento acumulado de bytes de datos LOB recuperados.
lob_orphan_create_count	bigint	Recuento acumulado de valores LOB huérfanos creados por operaciones masivas. 0 = Índices no agrupados
lob_orphan_insert_count	bigint	Recuento acumulado de valores LOB huérfanos insertados durante operaciones masivas. 0 = Índices no agrupados
row_overflow_fetch_in_pages	bigint	Recuento acumulado de páginas de datos de desbordamiento de filas recuperadas desde la unidad de asignación ROW_OVERFLOW_DATA. Estas páginas contienen datos que se almacenan en columnas de tipo varchar(n), nvarchar(n), varbinary(n) y sql_variant, y que no han estado en filas consecutivas. Para obtener más información, vea Datos de desbordamiento de fila superiores a 8 KB. Para obtener más información sobre las unidades de asignación, vea Organización de tablas e índices.
row_overflow_fetch_in_bytes	bigint	Recuento acumulado de bytes de datos de desbordamiento de filas recuperados.
column_value_push_off_row_count	bigint	Recuento acumulado de valores de columnas de datos LOB y datos de desbordamiento de filas que no están en filas consecutivas para hacer que una fila insertada o actualizada entre en una página.
column_value_pull_in_row_count	bigint	Recuento acumulado de valores de cliente de datos LOB y datos de desbordamiento de filas que están en filas consecutivas. Esto ocurre cuando una operación de actualización libera espacio en un registro y proporciona una oportunidad para indicar uno o más valores de filas no consecutivas de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA en la unidad de asignación IN_ROW_DATA. Para obtener más información sobre las unidades de asignación, vea Organización de tablas e índices.
row_lock_count	bigint	Número acumulado de bloqueos de fila solicitados.
row_lock_wait_count	bigint	Número acumulado de veces que Database Engine (Motor de base de datos) ha esperado en un bloqueo de fila.
row_lock_wait_in_ms	bigint	Número total de milisegundos que Database Engine (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 Database Engine (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 Database Engine (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 Database Engine (Motor de base de datos) ha intentado concentrar bloqueos.
index_lock_promotion_count	bigint	Número acumulado de veces que Database Engine (Motor de base de datos) ha concentrado bloqueos.
page_latch_wait_count	bigint	Número acumulado de veces que Database Engine (Motor de base de datos) ha esperado a causa de contención de pestillos.
page_latch_wait_in_ms	bigint	Número acumulado de milisegundos que Database Engine (Motor de base de datos) ha esperado a causa de contención de pestillos.
page_io_latch_wait_count	bigint	Número acumulado de veces que Database Engine (Motor de base de datos) ha esperado en un pestillo de E/S de páginas.
page_io_latch_wait_in_ms	bigint	Número acumulado de milisegundos que Database Engine (Motor de base de datos) ha esperado en un pestillo de E/S de páginas.

Notas

Este objeto de administración dinámica no acepta parámetros correlacionado 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 siguientes columnas 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 pestillos y bloqueos, utilice estas columnas:

• page_latch_wait_count y page_latch_wait_in_ms
  Estas columnas indican si existe una contención de pestillos 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 Database Engine (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 bloqueo 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 agrupados que contienen una o varias columnas LOB como columnas incluidas. Para obtener más información, vea Índice con columnas incluidas. 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 agrupados si el índice contiene columnas que pueden ser filas no consecutivas. Para obtener más información, vea Datos de desbordamiento de fila superiores a 8 KB.

Cómo se restablecen los recuentos

Los datos devueltos por sys.dm_db_index_operational_stats existen sólo mientras está disponible el objeto de caché de metadatos que representa el montón o el índice. Estos datos nunca son permanentes ni transaccionalmente coherentes. Esto significa que no puede utilizar estos recuentos para determinar si se ha utilizado un índice o cuándo se usó por última vez. Para obtener más información, vea sys.dm_db_index_usage_stats.

Los valores de cada columna se establecen en cero siempre que los metadatos del montón o índice se incorporen a la caché de metadatos y las estadísticas se acumulan hasta que el objeto de la caché se quita de la caché de metadatos. Por tanto, un índice o montón activo probablemente siempre tendrá sus metadatos en la caché, y los recuentos acumulados pueden 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 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 del mismo. Otras operaciones DDL del índice pueden causar que el valor de las estadísticas se restablezca en cero.

Usar funciones del sistema para especificar valores de parámetros

Puede utilizar 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, el envío de valores no válidos a estas funciones puede provocar resultados no deseados. Asegúrese de que se devuelve un Id. válido al utilizar DB_ID u OBJECT_ID. Para obtener más información, vea la sección Notas de sys.dm_db_index_physical_stats.

Permisos

Necesita los siguientes permisos:

• Permiso CONTROL en el objeto especificado en la base de datos
• Permiso VIEW DATABASE STATE para devolver información de todos los objetos en la base de datos especificada, utilizando el carácter comodín de objeto @object_id = NULL
• Permiso VIEW SERVER STATE para devolver información de todas las bases de datos, utilizando 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 para 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 los permisos CONTROL concedidos a objetos específicos. Además, si se especifica el carácter comodín de base de datos @database_id=NULL, la base de datos se omite.

Para obtener más información, vea Funciones y vistas de administración dinámica.

Ejemplos

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 AdventureWorks. La ejecución de esta consulta requiere, como mínimo, permiso CONTROL en la tabla Person.Address.

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 devuelve un Id. 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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. 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 

Vea también

Referencia

Funciones y vistas de administración dinámica
Funciones y vistas de administración dinámica relacionadas con índices
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_os_latch_stats
sys.dm_db_partition_stats
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)

Otros recursos

Supervisar y optimizar para obtener un mayor rendimiento
Arquitectura de tablas e índices

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión	Historial
17 de julio de 2006	Contenido nuevo: Se han aclarado los requisitos de nivel de compatibilidad de base de datos para usar use DB_ID() en el primer parámetro.
14 de abril de 2006	Contenido modificado: Se agregaron los valores de entrada 0 y DEFAULT a la sintaxis de database_id, object_id, y partition_number. Se agregaron los valores de entrada -1 y DEFAULT a la sintaxis de index_id.
5 de diciembre de 2005	Contenido nuevo: Se ha agregado la sección Usar funciones del sistema para especificar valores de parámetros. Contenido modificado: Se ha modificado la sección Permisos.