sys.dm_db_index_physical_stats (Transact-SQL)
Se aplica a: SQL Server
Azure SQL Database
Azure SQL Instancia administrada
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. 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 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 optimizados para memoria. Para obtener información sobre el uso de índices optimizados para memoria, consulte 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 para 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. 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 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, 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 la 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. |
id_de_índice | 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) - ÍNDICE DELETEBUFFER DE ALMACÉN DE COLUMNAS (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 primario de almacén de columnas. Para obtener más información, consulte 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_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 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 nivel de índice 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 deaf de un índice o LOB_DATA ROW_OVERFLOW_DATA unidades de 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 deaf de un índice o LOB_DATA ROW_OVERFLOW_DATA unidades de 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 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 = 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 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 de nivel de í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 de nivel de í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 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 = 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 = 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 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 = 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 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 la compresión PAGE. |
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 Instancia administrada |
columnstore_delete_buffer_state_desc | nvarchar(60) | NO VÁLIDO: el índice primario no es un índice de almacén de columnas. OPEN: los eliminadores y escáneres 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 Instancia administrada |
version_record_count | bigint | Este es el recuento de los registros de versión de fila que se mantienen en este índice. La característica Accelerated Database Recovery mantiene estas versiones de fila. 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.
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, 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
solo requiere un bloqueo de tabla De 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 del árbol B y las páginas IAM y PFS del montón. Use el modo SAMPLED para obtener un valor estimado para compressed_page_count
y use el modo DETALLADO 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 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 puede encontrar 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 valor NULL; o bien, si el nombre del objeto existe tanto en el contexto de 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 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 SHOWCONTIG
de , 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. 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 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 puede no tener ningún efecto.
Nota:
Ejecutar DBCC SHRINKFILE
o DBCC SHRINKDATABASE
puede introducir fragmentación si un índice se mueve en parte 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 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 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 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%. 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 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 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 del 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 Dynamic Management Views and Functions (Transact-SQL) (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
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. 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 comodín NULL
para todos los parámetros. La ejecución de esta consulta requiere el permiso VIEW SERVER STATE.
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO
D. Uso 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%. 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 bases de datos, consulte 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 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);
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)
- 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)
- Vistas del sistema (Transact-SQL)