sys.dm_db_index_physical_stats (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Devuelve información de tamaño y fragmentación de los datos e índices de la tabla o vista especificadas en SQL Server. En el caso de un índice, se devuelve una fila por cada nivel de árbol b en cada partición. Para un montón, se devuelve una fila para la IN_ROW_DATA unidad de asignación de cada partición. Para los datos de objetos grandes (LOB), se devuelve una fila para la LOB_DATA unidad de asignación de cada partición. Si existen datos de desbordamiento de fila en la tabla, se devuelve una fila para la ROW_OVERFLOW_DATA unidad de asignación de cada partición.

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.

sys.dm_db_index_physical_stats no devuelve información sobre los índices de almacén de columnas optimizados para memoria. Para obtener información sobre el uso de índices optimizados para memoria, vea sys.dm_db_xtp_index_stats (Transact-SQL).

Si consulta sys.dm_db_index_physical_stats en una instancia de servidor que hospeda una réplica secundaria legible del grupo de disponibilidad, es posible que encuentre un REDO problema de bloqueo. Esto se debe a que esta vista de administración dinámica adquiere un IS bloqueo en la tabla o vista de usuario especificada que puede bloquear las solicitudes por un REDO subproceso para un X bloqueo en esa tabla o vista de usuario.

Convenciones de sintaxis de Transact-SQL

Sintaxis

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Argumentos

database_id | NULL | 0 | PREDETERMINADO

El Id. de la base de datos. database_id es smallint. Las entradas válidas son el identificador de una base de datos, 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 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. Cuando se usa DB_ID sin especificar un nombre de base de datos, el nivel de compatibilidad de la base de datos actual debe ser 90 o superior.

object_id | NULL | 0 | PREDETERMINADO

Identificador de objeto de la tabla o vista en la que se encuentra el índice. object_id es int.

Las entradas válidas son el identificador de una tabla y vista, NULL, 0 o DEFAULT. El valor predeterminado es 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto. A partir de SQL Server 2016 (13.x), las entradas válidas también incluyen el nombre de la cola de Service Broker o el nombre de la tabla interna de cola. Cuando se aplican parámetros predeterminados (es decir, todos los objetos, todos los índices, etc.), la información de fragmentación de todas las colas se incluye en el conjunto de resultados.

Especifique NULL para devolver información 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

Identificador del índice. index_id es int. Las entradas válidas son el 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 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 del 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 obtener información sobre todas las particiones del objeto propietario.

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

mode | NULL | PREDETERMINADO

Nombre del modo. mode especifica el nivel de examen que se usa para obtener estadísticas. mode es sysname. Las entradas válidas son DEFAULT, NULL, LIMITED, SAMPLED o DETAILED. El valor predeterminado (NULL) es LIMITED.

Tabla devuelta

Nombre de la columna Tipo de datos Descripción
database_id smallint Identificador de base de datos de la tabla o vista.

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 objeto de la tabla o vista en la que se encuentra el índice.
index_id int Identificador de índice.

0 = Montón.
partition_number int Número de partición de base 1 en el objeto propietario, una tabla, vista o índice.

1 = Montón o índice sin particiones.
index_type_desc nvarchar(60) Descripción del tipo de índice:

- HEAP
- ÍNDICE AGRUPADO
- ÍNDICE NO CLÚSTER
- ÍNDICE XML PRINCIPAL
- ÍNDICE EXTENDIDO
- ÍNDICE XML
- ÍNDICE DE ASIGNACIÓN DE ALMACÉN DE COLUMNAS (interno)
- COLUMNSTORE DELETEBUFFER INDEX (interno)
- COLUMNSTORE DELETEBITMAP INDEX (interno)
hobt_id bigint Identificador de montón o árbol B del índice o partición.

En el caso de los índices de almacén de columnas, este es el identificador de un conjunto de filas que realiza un seguimiento de los datos internos del almacén de columnas de una partición. Los conjuntos de filas se almacenan como montones de datos o árboles B. Tienen el mismo identificador de índice que el índice de almacén de columnas primario. Para obtener más información, vea sys.internal_partitions (Transact-SQL).
alloc_unit_type_desc nvarchar(60) Descripción del tipo de unidad de asignación:

- IN_ROW_DATA
- LOB_DATA
- ROW_OVERFLOW_DATA

La LOB_DATA unidad de asignación contiene los 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).

La ROW_OVERFLOW_DATA unidad de asignación contiene los datos almacenados en columnas de tipo varchar(n),nvarchar(n), varbinary(n) y sql_variant que se han insertado fuera de fila.
index_depth tinyint Número de niveles del índice.

1 = Montón o LOB_DATAROW_OVERFLOW_DATA unidad de asignación.
index_level tinyint Nivel actual del índice.

0 para los niveles de hoja de índice, montones y LOB_DATA unidades de ROW_OVERFLOW_DATA asignación.

Mayor que 0 para niveles de índice no hoja. index_level es el más alto en el nivel raíz de un índice.

Los niveles que no son de hoja de índices solo se procesan cuando el modo = DETAILED.
avg_fragmentation_in_percent float Fragmentación lógica de índices o fragmentación de extensión para montones en la IN_ROW_DATA unidad de asignación.

El valor se mide como porcentaje y tiene en cuenta varios archivos. Para obtener definiciones de fragmentación lógica y de extensión, vea la sección Comentarios.

0 para LOB_DATA unidades de asignación y ROW_OVERFLOW_DATA .

NULL para montones cuando el modo = SAMPLED.
fragment_count bigint Número de fragmentos en el nivel hoja de una IN_ROW_DATA unidad de asignación. Para obtener más información sobre los fragmentos, vea la sección Comentarios.

NULL para los niveles que no son de hoja de un índice y LOB_DATA unidades de ROW_OVERFLOW_DATA asignación.

NULL para montones cuando el modo = SAMPLED.
avg_fragment_size_in_pages float Número medio de páginas de un fragmento en el nivel hoja de una IN_ROW_DATA unidad de asignación.

NULL para los niveles que no son de hoja de un índice y LOB_DATA unidades de ROW_OVERFLOW_DATA asignación.

NULL para montones cuando el modo = SAMPLED.
page_count bigint Número total de páginas de datos o de índice.

Para un índice, el número total de páginas de índice en el nivel actual del árbol B de la IN_ROW_DATA unidad de asignación.

Para un montón, el número total de páginas de datos de la IN_ROW_DATA unidad de asignación.

Para LOB_DATA unidades de asignación o ROW_OVERFLOW_DATA , número total de páginas de la unidad de asignación.
avg_page_space_used_in_percent float Porcentaje medio del espacio de almacenamiento de datos disponible utilizado en todas las páginas.

Para un índice, el promedio se aplica al nivel actual del árbol B de la IN_ROW_DATA unidad de asignación.

Para un montón, el promedio de todas las páginas de datos de la IN_ROW_DATA unidad de asignación.

Para LOB_DATA las unidades de asignación o ROW_OVERFLOW_DATA , el promedio de todas las páginas de la unidad de asignación.

NULL cuando el modo = LIMITED.
record_count bigint Número total de registros.

Para un índice, el número total de registros se aplica al nivel actual del árbol B de la IN_ROW_DATA unidad de asignación.

Para un montón, el número total de registros de la IN_ROW_DATA unidad de asignación.

Nota: Para un montón, es posible que el número de registros devueltos de esta función no coincida con el número de filas devueltas mediante la ejecución de un SELECT COUNT(*) objeto en el montón. Esto es debido a que una fila puede contener varios registros. Por ejemplo, en algunas situaciones de una actualización, una única fila del montón puede tener un registro de reenvío y un registro reenviado como resultado de la actualización. Además, la mayoría de las filas loB grandes se dividen en varios registros en LOB_DATA el almacenamiento.

Para LOB_DATA las unidades de asignación o ROW_OVERFLOW_DATA , el número total de registros de la unidad de asignación completa.

NULL cuando el modo = LIMITED.
ghost_record_count bigint Número de registros fantasma preparados para su eliminación mediante la tarea de limpieza de registros fantasma en la unidad de asignación.

0 para los niveles que no son deaf de un índice en la IN_ROW_DATA unidad de asignación.

NULL cuando el modo = LIMITED.
version_ghost_record_count bigint Número de registros fantasma retenidos por una transacción de aislamiento de instantánea pendiente en una unidad de asignación.

0 para los niveles que no son deaf de un índice en la IN_ROW_DATA unidad de asignación.

NULL cuando el modo = LIMITED.
min_record_size_in_bytes int Tamaño mínimo del registro en bytes.

Para un índice, el tamaño mínimo del registro se aplica al nivel actual del árbol B de la IN_ROW_DATA unidad de asignación.

Para un montón, el tamaño mínimo del registro en la IN_ROW_DATA unidad de asignación.

Para LOB_DATA las unidades de asignación o ROW_OVERFLOW_DATA , el tamaño mínimo del registro en la unidad de asignación completa.

NULL cuando el modo = LIMITED.
max_record_size_in_bytes int Tamaño máximo del registro en bytes.

Para un índice, el tamaño máximo del registro se aplica al nivel actual del árbol B de la IN_ROW_DATA unidad de asignación.

Para un montón, el tamaño máximo del registro en la IN_ROW_DATA unidad de asignación.

Para LOB_DATA las unidades de asignación o ROW_OVERFLOW_DATA , el tamaño máximo del registro en la unidad de asignación completa.

NULL cuando el modo = LIMITED.
avg_record_size_in_bytes float Promedio de tamaño del registro en bytes.

Para un índice, el tamaño medio del registro se aplica al nivel actual del árbol B de la IN_ROW_DATA unidad de asignación.

Para un montón, el tamaño medio del registro en la IN_ROW_DATA unidad de asignación.

Para LOB_DATA las unidades de asignación o ROW_OVERFLOW_DATA , el tamaño medio del registro en la unidad de asignación completa.

NULL cuando el modo = LIMITED.
forwarded_record_count bigint Número de registros de un montón que han reenviado punteros a otra ubicación de datos. (Este estado se produce durante una actualización, cuando no hay suficiente espacio para almacenar la nueva fila en la ubicación original).

NULL para cualquier unidad de asignación distinta de las IN_ROW_DATA unidades de asignación de un montón.

NULL para montones cuando el modo = LIMITED.
compressed_page_count bigint Número de páginas comprimidas.

En el caso de los montones, las páginas recién asignadas no están comprimidas por PAGE. Un montón usa la compresión de página bajo dos condiciones especiales: cuando los datos se importan de forma masiva o cuando vuelve a generarse un montón. Las operaciones DML típicas que provocan asignaciones de página no están comprimidas por PAGE. Vuelva a generar un montón cuando el valor compressed_page_count sea mayor que el umbral que desea.

En las tablas que tienen un índice clúster, el valor compressed_page_count indica la eficacia de la compresión de página.
columnstore_delete_buffer_state tinyint 0 = NOT_APPLICABLE
1 = OPEN
2 = PURGA
3 = VACIADO
4 = RETIRADA
5 = LISTO

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance
columnstore_delete_buffer_state_desc nvarchar(60) NO VÁLIDO: el índice primario no es un índice de almacén de columnas.

OPEN: los analizadores y eliminadores lo usan.

PURGADO: los eliminadores se están purgando, pero los escáneres siguen utilizándolo.

FLUSHING: el búfer está cerrado y las filas del búfer se escriben en el mapa de bits de eliminación.

RETIRADA: las filas del búfer de eliminación cerrada se han escrito en el mapa de bits de eliminación, pero el búfer no se ha truncado porque los escáneres siguen usándolo. Los nuevos escáneres no necesitan usar el búfer de retirada porque el búfer abierto es suficiente.

READY: este búfer de eliminación está listo para su uso.

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance
version_record_count bigint Este es el recuento de los registros de versión de fila que se mantienen en este índice. Estas versiones de fila se mantienen mediante la característica Recuperación acelerada de bases de datos .

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database
inrow_version_record_count bigint Recuento de registros de versión de ADR guardados en la fila de datos para una recuperación rápida.

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database
inrow_diff_version_record_count bigint Recuento de registros de versión de ADR mantenidos en forma de diferencias con respecto a la versión base.

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database
total_inrow_version_payload_size_in_bytes bigint Tamaño total en bytes de los registros de versión en fila para este índice.

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database
offrow_regular_version_record_count bigint Recuento de registros de versión que se mantienen fuera de la fila de datos original.

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database
offrow_long_term_version_record_count bigint Recuento de registros de versión considerados a largo plazo.

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database

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

La sys.dm_db_index_physical_stats función de administración dinámica reemplaza la DBCC SHOWCONTIG instrucción .

Modos de examen

El modo en que se ejecuta la función determina el nivel de recorrido realizado para obtener los datos estadísticos que utiliza la función. el modo se especifica como LIMITED, SAMPLED o DETAILED. La función recorre las cadenas de páginas buscando las unidades de asignación que producen las particiones especificadas de la tabla o el índice. sys.dm_db_index_physical_stats requiere únicamente un bloqueo de tabla con intención compartida (IS), independientemente del modo en que se ejecute.

El modo LIMITED es el modo más rápido y examina el menor número de páginas. Para un índice, solamente se examinan las páginas del nivel primario del árbol b (es decir, las páginas sobre el nivel hoja). Para un montón, se examinan las páginas PFS e IAM asociadas y las páginas de datos de un montón se exploran en modo LIMITED.

Con el modo LIMITED, compressed_page_count es NULL porque el motor de base de datos solo examina páginas que no son de hoja del árbol B y las páginas IAM y PFS del montón. Use el modo SAMPLED para obtener un valor estimado de compressed_page_count y el modo DETAILED para obtener el valor real de compressed_page_count. El modo SAMPLED devuelve estadísticas basadas en una muestra de un uno por ciento de todas las páginas del índice o montón. Los resultados en el modo SAMPLED se deben considerar como aproximados. Si el índice o montón tiene menos de 10.000 páginas, se utiliza el modo DETAILED en lugar del modo SAMPLED.

El modo DETAILED recorre todas las páginas y devuelve todas las estadísticas.

Los modos son progresivamente más lentos de LIMITED a DETAILED, ya que se va realizando más trabajo en cada nodo. Para analizar rápidamente el nivel de fragmentación o tamaño de una tabla o índice, utilice el modo LIMITED. Es la más rápida y no devuelve una fila para cada nivel que no sea de hoja en la IN_ROW_DATA unidad de asignación del índice.

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, pasar valores que no son válidos para estas funciones puede provocar resultados no deseados. Por ejemplo, si no se encuentra el nombre de la base de datos o del objeto porque no existen o se escriben incorrectamente, ambas funciones devuelven NULL. La función sys.dm_db_index_physical_stats interpreta NULL como un valor comodín que especifica todas las bases de datos o todos los objetos.

Además, la OBJECT_ID función se procesa antes de llamar a la sys.dm_db_index_physical_stats función y, por tanto, se evalúa en el contexto de la base de datos actual, no en la base de datos especificada en database_id. Este comportamiento puede hacer que la OBJECT_ID función devuelva un valor NULL; o bien, si el nombre del objeto existe tanto en el contexto de la base de datos actual como en la base de datos especificada, se puede devolver un mensaje de error. En los siguientes ejemplos se ilustran estos resultados no deseados.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Procedimiento recomendado

Asegúrese siempre de que se devuelve un identificador válido al usar DB_ID o OBJECT_ID. Por ejemplo, cuando se usa OBJECT_ID, especifique un nombre de tres partes, como OBJECT_ID(N'AdventureWorks2022.Person.Address'), o pruebe el valor devuelto por las funciones antes de usarlos en la sys.dm_db_index_physical_stats función . En los ejemplos A y B siguientes se ilustra una forma segura de especificar identificadores de objetos y bases de datos.

Detección de fragmentación

La fragmentación es consecuencia de los procesos de modificación de los datos (instrucciones INSERT, UPDATE y DELETE) efectuados en la tabla y en los índices definidos en la tabla. Dado que estas modificaciones no se distribuyen normalmente de forma equitativa entre las filas de la tabla y los índices, la totalidad de cada página puede variar con el tiempo. Para las consultas que examinan parcial o totalmente los índices de una tabla, este tipo de fragmentación puede producir lecturas de páginas adicionales. Esto impide el examen paralelo de los datos.

El nivel de fragmentación de un índice o montón aparece en la columna avg_fragmentation_in_percent. En el caso de montones, el valor representa la fragmentación de extensión del montón. En el caso de índices, el valor representa la fragmentación lógica del índice. A diferencia DBCC SHOWCONTIGde , los algoritmos de cálculo de fragmentación en ambos casos consideran el almacenamiento que abarca varios archivos y, por lo tanto, son precisos.

Fragmentación lógica

Se trata del porcentaje de páginas sin orden en las páginas hoja de un índice. Una página no ordenada es aquella en la que la siguiente página física asignada al índice no es la que señala el puntero de página siguiente en la página hoja actual.

Fragmentación de extensiones

Se trata del porcentaje de extensiones sin orden en las páginas hoja de un montón. Una extensión fuera del orden es una para la que la extensión que contiene la página actual de un montón no es físicamente la siguiente extensión después de la extensión que contiene la página anterior.

El valor de avg_fragmentation_in_percent debe ser lo más cercano posible a cero para obtener un rendimiento máximo. No obstante, los valores de 0% a 10% son aceptables. Puede utilizar todos los métodos de reducción de la fragmentación (por ejemplo, volver a generar, reorganizar o volver a crear) para disminuir estos valores. Para obtener más información sobre cómo analizar el grado de fragmentación en un índice, vea Reorganizar y recompilar índices.

Reducción de la fragmentación en un índice

Cuando un índice está fragmentado de tal forma que el rendimiento de las consultas se ve afectado, hay tres opciones para reducir la fragmentación:

  • Quite y vuelva a crear el índice clúster.

    La reconstrucción de un índice clúster redistribuye los datos, lo que ocasiona que las páginas de datos se llenen. El grado de llenado puede configurarse con la opción FILLFACTOR de CREATE INDEX. Los inconvenientes de este método son que el índice está sin conexión durante el proceso de eliminación y creación, y que la operación es atómica. Si se interrumpe la creación del índice, no vuelve a crearse. Para más información, consulte CREATE INDEX (Transact-SQL).

  • Use ALTER INDEX REORGANIZE, el reemplazo de DBCC INDEXDEFRAG, para reordenar las páginas de nivel hoja del índice en un orden lógico. Se trata de una operación en línea, por lo que el índice está disponible mientras se ejecuta la instrucción. La operación también puede interrumpirse sin perder el trabajo ya completado. El inconveniente de este método es que no hace tan bien un trabajo de reorganizar los datos como una operación de recompilación de índices y no actualiza las estadísticas.

  • Use ALTER INDEX REBUILD, el reemplazo de DBCC DBREINDEX, para recompilar el índice en línea o sin conexión. Para más información, vea ALTER INDEX (Transact-SQL).

La fragmentación por sí sola no es una razón suficiente para reorganizar o recompilar un índice. El principal efecto de la fragmentación es una disminución del rendimiento de la lectura anticipada de páginas durante los recorridos de índice. Esto se traduce en tiempos de respuesta más lentos. Si la carga de trabajo de consulta en una tabla o índice fragmentado no implica exámenes, ya que la carga de trabajo es principalmente búsquedas singleton, la eliminación de la fragmentación puede no tener ningún efecto.

Nota

Ejecutar DBCC SHRINKFILE o DBCC SHRINKDATABASE puede introducir fragmentación si un índice se mueve parcialmente o completamente durante la operación de reducción. Por esta razón, si tiene que realizar una operación de reducción, debe realizarla antes de quitar la fragmentación.

Reducción de la fragmentación en un montón

Para reducir la fragmentación de extensión de un montón, cree un índice clúster en la tabla y, a continuación, quítelo. Con esta acción se redistribuyen los datos mientras se crea el índice clúster. Esto también optimiza la distribución del espacio disponible en la base de datos. Cuando el índice agrupado se quita para volver a crear el montón, los datos no se mueven y permanecen en posición óptima. Para obtener información acerca de cómo realizar estas operaciones, vea CREATE INDEX y DROP INDEX.

Precaución

Al crear y quitar un índice agrupado en una tabla, se vuelven a generar todos los índices no agrupados en esa tabla dos veces.

Compactar datos de objetos grandes

De forma predeterminada, la instrucción ALTER INDEX REORGANIZE compacta las páginas que contienen datos de objetos grandes (LOB). Dado que las páginas loB no se desasignan cuando están vacías, la compactación de estos datos puede mejorar el uso del espacio en disco si se han eliminado muchos datos de LOB o se quita una columna loB.

Si reorganiza un índice clúster específico, se compactan todas las columnas LOB incluidas en el índice clúster. Si reorganiza un índice no clúster, se compactan todas las columnas LOB sin clave incluidas en el índice. Cuando se especifica ALL en la instrucción, todos los índices asociados a la tabla o vista especificada se reorganizan. Además, todas las columnas loB asociadas al índice agrupado, la tabla subyacente o el índice no agrupado con columnas incluidas se compactan.

Evaluación del uso del espacio en disco

La columna avg_page_space_used_in_percent indica el llenado de páginas. Para lograr un uso óptimo del espacio en disco, este valor debe estar cerca del 100 por ciento para un índice que no tiene muchas inserciones aleatorias. Sin embargo, un índice que tiene muchas inserciones aleatorias y tiene páginas muy completas tienen un mayor número de divisiones de página. Esto causa más fragmentación. Por lo tanto, para reducir las divisiones de páginas, el valor debe ser inferior a 100%. Si vuelve a generar un índice con la opción FILLFACTOR especificada, podrá cambiar el llenado de la página para ajustarse al patrón de consulta en el índice. Para obtener más información sobre el factor de relleno, vea Especificar factor de relleno para un índice. Asimismo, ALTER INDEX REORGANIZE compactará un índice intentando rellenar las páginas según el último valor de FILLFACTOR especificado. Esto aumenta el valor de avg_space_used_in_percent. ALTER INDEX REORGANIZE no puede reducir la totalidad de la página. Para ello, deberá volver a generar el índice.

Evaluación de fragmentos de índice

Un fragmento se compone de páginas hoja consecutivas físicamente en el mismo archivo para una unidad de asignación. Un índice tiene al menos un fragmento. El número máximo de fragmentos que puede tener un índice es igual al número de páginas en el nivel hoja de un índice. El uso de fragmentos mayores indica que se necesita menos E/S de disco para leer el mismo número de páginas. Por lo tanto, cuanto mayor sea el valor de avg_fragment_size_in_pages, mejor será el rendimiento del examen de intervalos. Los valores avg_fragment_size_in_pages y avg_fragmentation_in_percent son inversamente proporcionales entre sí. Por lo tanto, si vuelve a generar o reorganiza un índice, debe reducir la cantidad de fragmentación y aumentar el tamaño de los fragmentos.

Limitaciones y restricciones

No devuelve datos para los índices de almacén de columnas agrupados.

Permisos

Necesita los siguientes permisos:

  • El permiso CONTROL en el objeto especificado en la base de datos.

  • PERMISO VIEW DATABASE STATE o VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) para devolver información sobre todos los objetos de la base de datos especificada mediante el carácter comodín de objeto @object_id=NULL.

  • PERMISO VIEW SERVER STATE o VIEW SERVER PERFORMANCE STATE (SQL Server 2022) para devolver información sobre todas las bases de datos, mediante el carácter comodín de la 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 se especifica el carácter comodín de la base de datos @database_id=NULL, 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 sobre una tabla especificada

El ejemplo siguiente devuelve estadísticas de fragmentación y tamaño de todos los índices y particiones de la tabla Person.Address. El modo de recorrido se establece en 'LIMITED' para obtener el mayor rendimiento posible y limitar las estadísticas devueltas. Para ejecutar esta consulta, es necesario, como mínimo, el permiso CONTROL en la tabla Person.Address.

DECLARE @db_id SMALLINT;
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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Devolver información sobre un montón

En el ejemplo siguiente se devuelven todas las estadísticas del montón dbo.DatabaseLog en la base de datos AdventureWorks2022. Dado que la tabla contiene datos LOB, se devuelve una fila para la unidad de asignación LOB_DATA y una fila para el valor IN_ROW_ALLOCATION_UNIT que está almacenando las páginas de datos del montón. Para ejecutar esta consulta, es necesario, como mínimo, el permiso CONTROL en la tabla dbo.DatabaseLog.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. Devolver información para todas las bases de datos

En el ejemplo siguiente se devuelven todas las estadísticas de todas las tablas e índices dentro de la instancia de SQL Server especificando el carácter NULL comodín para todos los parámetros. Para ejecutar esta consulta necesita el permiso VIEW SERVER STATE.

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

D. Usar sys.dm_db_index_physical_stats en un script para volver a generar o reorganizar índices

El ejemplo siguiente reorganiza o vuelve a generar automáticamente todas las particiones de una base de datos que tienen un promedio de fragmentación superior al 10%. Para ejecutar esta consulta necesita el permiso VIEW DATABASE STATE. Este ejemplo especifica DB_ID como primer parámetro, sin especificar un nombre de base de datos. Se genera un error si la base de datos actual tiene un nivel de compatibilidad de 80 o inferior. Para solucionar el error, reemplace DB_ID() por un nombre de base de datos válido. Para obtener más información sobre los niveles de compatibilidad de base de datos, vea Nivel de compatibilidad de ALTER DATABASE (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. Se usa sys.dm_db_index_physical_stats para mostrar el número de páginas comprimidas por páginas

En el ejemplo siguiente se muestra cómo mostrar y comparar el número total de páginas con las páginas sometidas a compresión de filas y páginas. Esta información se puede utilizar para determinar el beneficio que aporta la compresión a un índice o una tabla.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Uso sys.dm_db_index_physical_stats en modo SAMPLED

El ejemplo siguiente muestra cómo el modo SAMPLED devuelve un valor aproximado diferente a los resultados del modo DETAILED.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Consultas de colas de Service Broker para la fragmentación de índices

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores.

En el ejemplo siguiente se muestra cómo consultar las colas de agente de servidor para la fragmentación.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

Consulte también