Reorganizar y volver a generar índices
SQL Server Database Engine (Motor de base de datos de SQL Server) mantiene índices automáticamente cada vez que inserta, actualiza o elimina operaciones realizadas en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando los índices tienen páginas en las que la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos. Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación. Para obtener más información, vea el sitio web de Microsoft.
Puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar. Para los índices con particiones generados en un esquema de partición, puede utilizar cualquiera de estos métodos en un índice completo o en una sola partición de un índice cualquiera.
Detectar la fragmentación
El primer paso necesario para detectar qué método de desfragmentación utilizar es analizar el índice a fin de determinar la magnitud de la fragmentación. Si utiliza la función del sistema sys.dm_db_index_physical_stats, podrá detectar la fragmentación de un índice específico, de todos los índices de una tabla o vista indizada, de todos los índices de una base de datos o de todos los índices de todas las bases de datos. Para los índices con particiones, sys.dm_db_index_physical_stats también proporciona información de la fragmentación para cada partición.
El conjunto de resultados devuelto por la función sys.dm_db_index_physical_stats tiene las columnas siguientes.
Columna |
Descripción |
|---|---|
avg_fragmentation_in_percent |
Porcentaje de fragmentación lógica (páginas de un índice que no funcionan correctamente). |
fragment_count |
Número de fragmentos (páginas hoja físicamente consecutivas) en el índice. |
avg_fragment_size_in_pages |
Número promedio de páginas en un fragmento del índice. |
Una vez determinada la magnitud de la fragmentación, utilice la siguiente tabla para determinar el mejor método para corregir la fragmentación propiamente dicha.
Valor de avg_fragmentation_in_percent |
Instrucción correctiva |
|---|---|
> 5% y < = 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* La regeneración de un índice se puede ejecutar en línea o sin conexión. La reorganización de un índice siempre se ejecuta en línea. Para lograr una disponibilidad similar a la opción de reorganización, debe volver a generar los índices en línea.
Estos valores proporcionan directrices generales para la determinación del punto en el que debe cambiar entre ALTER INDEX REORGANIZE y ALTER INDEX REBUILD. No obstante, los valores reales pueden variar de un caso a otro. Es importante que experimente la determinación del mejor umbral para su entorno.
Los niveles de fragmentación muy bajos (inferiores al 5 por ciento) no deben tratarse con ninguno de estos comandos, dado que el beneficio de quitar una cantidad de fragmentación tan pequeña es casi siempre ampliamente superado por el costo de reorganizar o volver a generar el índice.
Nota
En general, la fragmentación en índices pequeños normalmente no se puede controlar. Las páginas de índices pequeños se almacenan en extensiones mixtas. Las extensiones mixtas pueden estar compartidas por hasta ocho objetos, de modo que es posible que no se pueda reducir la fragmentación en un índice pequeño después de reorganizar o volver a generar dicho índice. Para obtener más información acerca de las extensiones mixtas, vea Descripción de páginas y extensiones.
Ejemplo
En el siguiente ejemplo se realiza una consulta a la función de administración dinámica sys.dm_db_index_physical_stats para que devuelva el promedio de fragmentación de todos los índices de la tabla Production.Product. Si utiliza la tabla anterior, la solución recomendada es reorganizar PK_Product_ProductID y volver a generar los otros índices.
USE AdventureWorks2008R2;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
La instrucción probablemente devolverá un conjunto de resultados similar al siguiente:
index_id name avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1 PK_Product_ProductID 15.076923076923077
2 AK_Product_ProductNumber 50.0
3 AK_Product_Name 66.666666666666657
4 AK_Product_rowguid 50.0
(4 row(s) affected)
Reorganizar un índice
Para reorganizar uno o más índices, utilice la instrucción ALTER INDEX con la cláusula REORGANIZE. Esta instrucción reemplaza la instrucción DBCC INDEXDEFRAG. Para reorganizar una sola partición de un índice con particiones, utilice la cláusula PARTITION de ALTER INDEX.
La reorganización de un índice desfragmenta el nivel hoja de índices clúster y no clúster en tablas y vistas volviendo a ordenar físicamente las páginas en el nivel hoja de manera que coincidan con la ordenación lógica (de izquierda a derecha) de los nodos hoja. La ordenación de las páginas mejora el rendimiento de recorrido del índice. El índice se reorganiza dentro de las páginas existentes que se le han asignado; no se le asignan páginas nuevas. Si un índice abarca más de un archivo, los archivos se reorganizan de uno en uno. Las páginas no migran entre archivos.
La reorganización también compacta las páginas de índice. Todas las páginas vacías que se crean como consecuencia de esta compactación se eliminan para proporcionar una mayor cantidad de espacio en el disco duro. La compactación se basa en el valor de factor de relleno de la vista de catálogo sys.indexes.
El proceso de reorganización utiliza una mínima cantidad de recursos del sistema. Además, la reorganización se realiza automáticamente en línea. El proceso no mantiene bloqueos durante mucho tiempo y, por lo tanto, no bloquea las consultas ni las actualizaciones en ejecución.
Reorganice un índice cuando éste no esté demasiado fragmentado. Vea la tabla anterior para obtener instrucciones para la fragmentación. No obstante, si el índice está muy fragmentado, obtendrá mejores resultados si lo vuelve a generar.
Compactar tipos de datos de objetos grandes
Además de reorganizar uno o más índices, los tipos de datos de objetos grandes (LOB) contenidos en el índice clúster o en la tabla subyacente se compactan de manera predeterminada cada vez que se reorganiza un índice. Los tipos de datos image, text, ntext, varchar(max), nvarchar(max), varbinary(max) y xml son tipos de datos de objetos grandes. La compactación de estos datos puede mejorar el uso del espacio en disco:
La reorganización de un índice clúster en particular permitirá compactar todas las columnas de LOB contenidas en el nivel hoja (filas de datos) de ese índice clúster.
La reorganización de un índice no clúster permitirá compactar todas las columnas de LOB que constituyen columnas sin clave (incluidas) en el índice.
Si se especifica ALL, se reorganizan todos los índices clúster a la tabla o vista especificada y se compactan todas las columnas de LOB asociadas al índice clúster, a la tabla subyacente o al índice no clúster con columnas incluidas.
Si no existen columnas LOB, la cláusula LOB_COMPACTION se omite.
Volver a generar un índice
Cuando se vuelve a generar un índice, se quita el índice anterior y se crea uno nuevo. Al hacerlo, se elimina la fragmentación, se recupera el espacio en el disco duro compactando páginas mediante la configuración especificada o existente del factor de relleno y se reordenan las filas de índices en las páginas contiguas (se asignan páginas nuevas según sea necesario). Esto puede mejorar el rendimiento del disco, reduciendo el número de lecturas de página necesarias para obtener los datos solicitados.
Para volver a generar clúster y no clúster, se pueden utilizar los siguientes métodos:
ALTER INDEX con la cláusula REBUILD. Esta instrucción reemplaza a la instrucción DBCC DBREINDEX.
CREATE INDEX con la cláusula DROP_EXISTING.
Cada método realiza la misma función, pero es preciso considerar algunas ventajas y desventajas tal como se muestran en la siguiente tabla.
Funcionalidad |
ALTER INDEX REBUILD |
CREATE INDEX WITH DROP_EXISTING |
|---|---|---|
La definición del índice se puede cambiar agregando o eliminando columnas de clave, cambiando la ordenación de las columnas o modificando el criterio de ordenación de columnas.* |
No |
Sí** |
Las opciones del índice se pueden establecer o modificar. |
Sí |
Sí |
Se puede volver a generar más de un índice en una sola transacción. |
Sí |
No |
La mayoría de los tipos de índices se puede volver a generar sin necesidad de bloquear consultas ni actualizaciones en ejecución. |
Sí |
Sí |
Se pueden volver a crear particiones en los índices con particiones. |
No |
Sí |
El índice se puede mover a otro grupo de archivos. |
No |
Sí |
Se necesita más espacio temporal en disco. |
Sí |
Sí |
Si se vuelve a generar un índice clúster, se vuelven a generar índices no clúster asociados. |
No A menos que se haya especificado la palabra clave ALL. |
No A menos que se haya cambiado la definición del índice. |
Los índices que aplican las restricciones PRIMARY KEY y UNIQUE se pueden volver a generar sin necesidad de quitar ni volver a crear las restricciones. |
Sí |
Sí |
La partición de un solo índice se puede volver a generar. |
Sí |
No |
*Un índice no clúster se puede convertir en un tipo de índice clúster si se especifica CLUSTERED en la definición del índice. Esta operación se debe realizar con la opción ONLINE establecida en OFF. La conversión de clúster a no clúster no es compatible independientemente de la configuración de ONLINE.
**Si se vuelve a crear el índice con el mismo nombre, las mismas columnas y el mismo criterio de ordenación, posiblemente se omita la operación de ordenación. Cuando se vuelve a generar el índice, se comprueba que las filas estén ordenadas mientras se genera el índice.
También se puede volver a generar un índice quitando el índice con la instrucción DROP INDEX y volviéndolo a crear con una instrucción CREATE INDEX individual. Realizar estas operaciones como instrucciones individuales presenta varias desventajas, por lo cual no se recomienda esta práctica.
Deshabilitar índices no clúster para ahorrar el espacio en el disco durante operaciones de regeneración
Cuando se deshabilita un índice no clúster, se eliminan las filas de datos del índice, aunque la definición del índice permanece en los metadatos. El índice se habilita cuando se vuelve a generar. Cuando no se deshabilita el índice no clúster, la operación de regeneración requiere una cantidad suficiente de espacio temporal en el disco para almacenar el índice anterior y el nuevo. Sin embargo, si se deshabilita y se vuelve a generar un índice no clúster en transacciones individuales, la regeneración subsiguiente o cualquier otra operación puede volver a utilizar el espacio en disco liberado al deshabilitar el índice. No se necesita espacio adicional, salvo el espacio en disco temporal para la ordenación, que suele ser un 20 por ciento del tamaño del índice. Si el índice no clúster está en la clave principal, se deshabilitará automáticamente cualquier restricción FOREIGN KEY activa a la que se haga referencia. Estas restricciones se deben habilitar manualmente después de que se vuelva a generar el índice. Para obtener más información, vea Deshabilitar índices y Directrices para habilitar índices y restricciones.
Volver a generar índices grandes
Los índices que tienen más de 128 extensiones se vuelven a generar en dos fases independientes: lógica y física. En la fase lógica, las unidades de asignación existentes que utiliza el índice están señaladas para cancelación de asignación. En la fase física, las unidades de asignación previamente señaladas para cancelación de asignación se quitan físicamente de las transacciones breves que se realizan en segundo plano y no requieren demasiados bloqueos. Para obtener más información, vea Quitar y volver a generar objetos grandes.
Configurar opciones del índice
Las opciones del índice no se pueden especificar al reorganizar un índice. Sin embargo, las siguientes opciones del índice se pueden establecer cuando vuelve a generar un índice utilizando ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING:
PAD_INDEX |
DROP_EXISTING (sólo CREATE INDEX) |
FILLFACTOR |
ONLINE |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
Nota
Si no se necesita una operación de ordenación o si la ordenación se puede realizar en la memoria, se omite la opción SORT_IN_TEMPDB.
Además, la cláusula SET de la instrucción ALTER INDEX permite establecer las siguientes opciones del índice sin necesidad de volver a generarlo:
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
Para obtener más información, vea Configurar opciones de índice.
Para volver a generar o reorganizar un índice
Para volver a generar un índice quitando y volviendo a crear el índice en un solo paso
Ejemplos
A. Volver a generar un índice
En el siguiente ejemplo, se vuelve a generar un solo índice.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
B. Volver a generar todos los índices de una tabla especificando las opciones
En el siguiente ejemplo se especifica la palabra clave ALL. Así se regeneran todos los índices asociados a la tabla. Se especifican tres opciones.
USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
C. Reorganizar un índice con compactación de LOB
En el siguiente ejemplo, se reorganiza un solo índice clúster. Dado que el índice contiene un tipo de datos de LOB en el nivel hoja, la instrucción también compacta todas las páginas que contienen datos de objetos grandes. Tenga en cuenta que no debe especificar la opción WITH (LOB_Compaction) porque el valor predeterminado es ON.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
Vea también