sys.dm_db_index_physical_stats (Transact-SQL)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Devuelve información de tamaño y fragmentación de los datos e índices de la tabla o vista especificadas en el Motor de base de datos de SQL Server. En el caso de un índice, se devuelve una fila por cada nivel de árbol b en cada partición. En el caso de un montón, se devuelve una fila para la unidad de asignación IN_ROW_DATA en 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 usa el término árbol B generalmente en referencia a índices. En los índices de almacén de filas, el Motor de base de datos implementa un árbol B+. Esto no se aplica a índices o índices de almacén de columnas en 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.

sys.dm_db_index_physical_stats no devuelve información sobre los índices optimizados para memoria. Para obtener información sobre el uso de índices optimizados para memoria, consulte sys.dm_db_xtp_index_stats.

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 bloqueo de intención compartida (IS) en la tabla de usuario o vista de usuario especificada que puede bloquear las solicitudes por un REDO subproceso para un bloqueo Exclusivo (X) 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, 0o 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, index_id y partition_number.NULL

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 está el índice. object_id es int. Las entradas válidas son el identificador de una tabla y vista, NULL, 0o DEFAULT. El valor predeterminado es 0. NULL, 0y DEFAULT son valores equivalentes en este contexto.

En SQL Server 2016 (13.x) y versiones posteriores, 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 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 partition_number.NULL

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, -1o 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 | 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, 0o DEFAULT. El valor predeterminado es 0. NULL, 0y DEFAULT son valores equivalentes en este contexto.

Especifique NULL para devolver información para 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, SAMPLEDo 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 = Índice o montón sin particiones.
index_type_desc	nvarchar(60)	Descripción del tipo de índice: - HEAP - CLUSTERED INDEX - NONCLUSTERED INDEX - PRIMARY XML INDEX - EXTENDED INDEX - XML INDEX - COLUMNSTORE MAPPING INDEX (interno) - COLUMNSTORE DELETEBUFFER INDEX (interno) - COLUMNSTORE DELETEBITMAP INDEX (interno)
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. 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 insertan fuera de fila.
index_depth	tinyint	Número de niveles del índice. 1 = montón o LOB_DATA ROW_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 para los niveles de índice que 0 no son deaf. index_level es el más alto en el nivel raíz de un índice. Los niveles que no son de nivel de índice solo se procesan cuando el modo es 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 Comentarios. 0 para las unidades de asignación LOB_DATA y ROW_OVERFLOW_DATA . NULL para montones cuando el modo es 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 Comentarios. NULL para los niveles que no son de nivel de índice y LOB_DATA unidades de ROW_OVERFLOW_DATA asignación. NULL para montones cuando el modo es 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 nivel de índice y LOB_DATA unidades de ROW_OVERFLOW_DATA asignación. NULL para montones cuando el modo es 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 o ROW_OVERFLOW_DATA unidades de asignación, 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 es 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 que se devuelven ejecutando un SELECT COUNT(*) en el montón. Esto se debe a que una fila puede contener varios registros. Por ejemplo, en algunas situaciones de actualización, una sola fila del montón podría tener un registro de reenvío y un registro reenviado como resultado de la operación de 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 es 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 de nivel de índice en la IN_ROW_DATA unidad de asignación. NULL cuando el modo es 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 de nivel de índice en la IN_ROW_DATA unidad de asignación. NULL cuando el modo es 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 en 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 es 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 unidad de IN_ROW_DATA 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 es 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 es LIMITED.
forwarded_record_count	bigint	Número de registros de un montón que tienen punteros de reenvío 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 es LIMITED.
compressed_page_count	bigint	Número de páginas comprimidas. En el caso de los montones, las páginas recién asignadas no PAGE se comprimen. Un montón se PAGE comprime en dos condiciones especiales: cuando los datos se importan de forma masiva o cuando se vuelve a generar un montón. Las operaciones DML típicas que provocan asignaciones de página no PAGE se comprimen. Vuelva a generar un montón cuando el compressed_page_count valor crezca mayor que el umbral que desee. Para las tablas que tienen un índice agrupado, el compressed_page_count valor indica la eficacia de PAGE la compresión.
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 primario de almacén de columnas. Para obtener más información, consulte sys.internal_partitions.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY Se aplica a: SQL Server 2016 (13.x) y versiones posteriores, Azure SQL Database y Azure SQL Instancia administrada
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID : el índice primario no es un índice de almacén de columnas. OPEN - los eliminadores y escáneres lo usan. DRAINING - los eliminadores 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. RETIRING : las filas del búfer de eliminación cerrada se escribieron 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 Instancia administrada
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 de recuperación acelerada de la base 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 usa el término árbol B generalmente en referencia a índices. En los índices de almacén de filas, el Motor de base de datos implementa un árbol B+. Esto no se aplica a índices o índices de almacén de columnas en 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

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

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, SAMPLEDo 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 solo requiere un bloqueo de tabla De intención compartida (IS), independientemente del modo en que se ejecute.

El LIMITED modo 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, las páginas PFS e IAM asociadas se examinan y las páginas de datos de un montón se examinan en LIMITED modo.

Con LIMITED el modo , compressed_page_count se NULL debe a que el Motor de base de datos solo examina páginas que no son del árbol B y las páginas IAM y PFS del montón. Use SAMPLED el modo para obtener un valor estimado para compressed_page_county use DETAILED el modo para obtener el valor real de compressed_page_count. El SAMPLED modo devuelve estadísticas basadas en un ejemplo del 1 por ciento de todas las páginas del índice o montón. Los resultados en SAMPLED modo deben considerarse aproximados. Si el índice o montón tiene menos de 10 000 páginas, DETAILED se usa el modo en lugar de SAMPLED.

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

Los modos son progresivamente más lentos de LIMITED a DETAILED, ya que se realiza más trabajo en cada modo. Para medir rápidamente el tamaño o el nivel de fragmentación de una tabla o índice, use el LIMITED modo . Es la más rápida y no devuelve una fila para cada nivel que no sea deaf 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 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 sys.dm_db_index_physical_stats función 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 NULL valor; 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 devuelve 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 se produce a través del proceso de modificaciones de datos (INSERT, UPDATE, y DELETE instrucciones) que se realizan en la tabla y, por lo tanto, 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. En el caso de las consultas que examinan parte o todos los índices de una tabla, este tipo de fragmentación puede provocar más lecturas de página, lo que dificulta el examen paralelo de los datos.

El nivel de fragmentación de un índice o montón se muestra en la avg_fragmentation_in_percent columna . 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 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 extensión

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 estar lo más cerca posible de cero para el rendimiento máximo. Sin embargo, los valores del 0 al 10 por ciento pueden ser aceptables. Todos los métodos para reducir la fragmentación, como volver a generar, reorganizar o volver a crear, se pueden usar para reducir estos valores. Para obtener más información sobre cómo analizar el grado de fragmentación en un índice, consulte Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos.

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 agrupado.

  Volver a crear un índice agrupado redistribuye los datos y da como resultado páginas de datos completas. El grado de llenado puede configurarse con la opción FILLFACTOR de CREATE INDEX. Las desventajas de este método son que el índice está sin conexión durante el ciclo de eliminación y recreación, y que la operación es atómica. Si se interrumpe la creación del índice, el índice no se vuelve a crear. Para obtener más información, vea CREATE INDEX.

• 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 funciona tan bien como para 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 no puede tener ningún efecto.

Nota:

La ejecución 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 se quita el índice agrupado para volver a crear el montón, los datos no se mueven y permanecen en posición óptima. Para obtener información sobre 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 ALTER INDEX REORGANIZE instrucción compacta las páginas que contienen datos de objetos grandes (LOB). Dado que las páginas loB no están desasignadas cuando están vacías, la compactación de estos datos puede mejorar el uso del espacio en disco si se eliminan muchos datos 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 ALL se especifica en la instrucción , se reorganizan todos los índices asociados a la tabla o vista especificadas. 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 avg_page_space_used_in_percent columna indica la totalidad de la página. Para lograr un uso óptimo del espacio en disco, este valor debe estar cerca del 100 % para un índice que no tenga 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%. Volver a generar un índice con la FILLFACTOR opción especificada permite cambiar la totalidad 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. Además, ALTER INDEX REORGANIZE compactará un índice intentando rellenar las páginas en la FILLFACTOR que se especificó por última vez. 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 avg_fragment_size_in_pages valor, mejor será el rendimiento del examen de intervalo. Los avg_fragment_size_in_pages valores 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

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

Permisos

Necesita los siguientes permisos:

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

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

• VIEW SERVER STATEo VIEW SERVER PERFORMANCE STATE (SQL Server 2022) permiso para devolver información sobre todas las bases de datos, mediante el uso del comodín de la 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 CONTROL permisos denegados en objetos específicos.

Denegar VIEW DATABASE STATE no permite que se devuelvan todos los objetos de la base de datos, independientemente de los CONTROL permisos concedidos en 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, consulte Vistas de administración dinámica del sistema.

Ejemplos

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

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

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

El ejemplo siguiente devuelve todas las estadísticas para el montón dbo.DatabaseLog de 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. La ejecución de esta consulta requiere, como mínimo, CONTROL permiso en la dbo.DatabaseLog tabla.

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 comodín NULL para todos los parámetros. La ejecución de esta consulta requiere el VIEW SERVER STATE permiso .

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

D. Uso de sys.dm_db_index_physical_stats en un script para recompilar 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%. La ejecución de esta consulta requiere el VIEW DATABASE STATE permiso . Este ejemplo especifica DB_ID como primer parámetro, sin especificar un nombre de base de datos.

-- 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. Use 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. Usar sys.dm_db_index_physical_stats en modo SAMPLED

En el ejemplo siguiente se muestra cómo SAMPLED el modo devuelve un valor aproximado diferente al de los resultados del DETAILED modo.

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 del 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);

Contenido relacionado

• Vistas de administración dinámica del sistema
• Funciones y vistas de administración dinámica relacionadas con índices (Transact-SQL)
• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.allocation_units (Transact-SQL)
• Referencia de Transact-SQL (motor de base de datos)