Reorganizar y volver a generar índices
En este tema se describe cómo reorganizar o recompilar un índice fragmentado en SQL Server 2014 mediante SQL Server Management Studio o Transact-SQL. El motor de base de datos de SQL Server mantiene automáticamente los índices cada vez que se realizan operaciones de inserción, actualización o eliminación 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.
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 usar cualquiera de estos métodos en un índice completo o en una sola partición de un índice. El proceso de volver a crear un índice quita y vuelve a crear el índice. Quita la fragmentación, utiliza espacio en disco al compactar las páginas según el valor de factor de relleno especificado o existente y vuelve a ordenar las filas del índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción. La reorganización de un índice usa muy pocos recursos del sistema. Desfragmenta el nivel hoja de los índices agrupados y no clúster de las tablas y las vistas al volver a ordenar físicamente las páginas de nivel hoja para que coincidan con el orden lógico de los nodos hoja, de izquierda a derecha. La reorganización también compacta las páginas de índice. La compactación se basa en el valor de factor de relleno existente.
En este tema
Antes de empezar:
Para comprobar la fragmentación de un índice, usando:
Para reorganizar o volver a generar un índice, usando:
Antes de empezar
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 usa 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 indexada, 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 deavg_fragmentation_in_percent | Instrucción correctiva |
|---|---|
| > 5% y < = 30% | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD WITH (ONLINE = ON) 1 |
1 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.
Sugerencia
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. Por ejemplo, si se usa un índice determinado principalmente para operaciones de examen, la eliminación de la fragmentación puede mejorar el rendimiento de estas operaciones. Las ventajas de rendimiento son menos evidentes para los índices que se usan principalmente para las operaciones de búsqueda. Del mismo modo, la eliminación de la fragmentación en un montón (una tabla sin índice agrupado) es especialmente útil para las operaciones de examen de índices no agrupados, pero tiene poco efecto en las operaciones de búsqueda.
Los niveles de fragmentación muy bajos (inferiores al 5 por ciento) normalmente 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:
Con frecuencia, cuando se vuelven a generar o se reorganizan los índices pequeños no se reduce la fragmentación. Las páginas de índices pequeños a veces 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.
Consideraciones sobre la desfragmentación de índices
En determinadas circunstancias, al recompilar un índice agrupado, se recompilarán automáticamente los índices no agrupados que hagan referencia a la clave de agrupación en clústeres, si es necesario cambiar los identificadores físicos o lógicos contenidos en los registros de índices no agrupados.
Escenarios que obligan a que todos los índices no agrupados se recompilen automáticamente en una tabla:
- Creación de un índice agrupado en una tabla
- Eliminación de un índice agrupado, lo que hace que la tabla se almacene como un montón
- Cambio de la clave de agrupación en clústeres para incluir o excluir columnas
Escenarios que no requieren recompilar automáticamente en una tabla todos los índices no agrupados:
- Recompilación de un índice agrupado único
- Recompilación de un índice agrupado que no es único
- Cambio del esquema de índice, como al aplicar un esquema de partición a un índice agrupado o al mover el índice agrupado a un grupo de archivos diferente
Limitaciones y restricciones
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 las filas de datos se copian y ordenan y luego se mueven a las nuevas unidades de asignación creadas para almacenar el índice recompilado. 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 acerca de las extensiones, consulte la Guía de arquitectura de páginas y extensiones.
La instrucción ALTER INDEX REORGANIZE requiere que el archivo de datos que contiene el índice tenga espacio disponible, ya que la operación solo puede asignar páginas de trabajo temporales en el mismo archivo, no en otro archivo del grupo de archivos. Debido a esto, aunque el grupo de archivos tenga páginas libres disponibles, puede que al usuario le siga apareciendo el error 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Se pueden crear y regenerar índices no alineados en una tabla con más de 1000 particiones, pero no se recomienda. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones.
No es posible volver a organizar o generar un índice si el grupo de archivos en el que se encuentra está sin conexión o está definido como de solo lectura. Cuando se especifica la palabra clave ALL y hay uno o más índices en un grupo de archivos sin conexión o de solo lectura, se produce un error en la instrucción.
Seguridad
Permisos
Debe tener un permiso de ALTER sobre la tabla o vista. El usuario debe ser miembro del rol fijo de servidor sysadmin o de los roles fijos de base de datos db_ddladmin y db_owner .
Uso de SQL Server Management Studio
Para comprobar la fragmentación de un índice
En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que quiera comprobar la fragmentación de un índice.
Expanda la carpeta Tablas .
Expanda la tabla en la que quiera comprobar la fragmentación de un índice.
Expanda la carpeta Índices .
Haga clic con el botón derecho en el índice en el que quiere comprobar la fragmentación y seleccione Propiedades.
Bajo Seleccionar una página, seleccione Fragmentación.
La siguiente información está disponible en la página Fragmentación :
Llenado de página
Indica el promedio de llenado de las páginas de índice como un porcentaje. 100% indica que las páginas de índice están completamente llenas. 50% indica que, como promedio, las páginas de índice están llenas a la mitad.Fragmentación total
Porcentaje de fragmentación lógica. Indica el número de páginas de un índice que no están almacenadas en orden.Promedio de tamaño de fila
Tamaño medio de una fila de nivel hoja.Profundidad
Número de niveles del índice, incluido el nivel hoja.Registros reenviados
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 existe suficiente espacio para almacenar la nueva fila en la ubicación original.Filas fantasma
Número de filas marcadas como eliminadas que todavía no se han quitado. Estas filas se quitarán en un subproceso de limpieza, cuando el servidor no esté ocupado. Este valor no incluye las filas que se retienen debido a una transacción pendiente de aislamiento de instantáneas.Tipo de índice
Tipo de índice. Los valores posibles son Índice clúster, Índice no clústery XML principal. Las tablas también se pueden almacenar como un montón (sin índices), pero en tal caso la página Propiedades del índice no puede abrirse.Filas de nivel de hoja
Número de filas de nivel hoja.Tamaño máximo de la fila
Tamaño máximo de la fila de nivel de hoja.Tamaño mínimo de la fila
Tamaño mínimo de la fila de nivel de hoja.Páginas
Número total de páginas de datos.Identificador de la partición
Id. de partición del árbol b que contiene el índice.Filas fantasma de la versión
Número de registros fantasma que se retienen debido a una transacción de aislamiento de instantánea pendiente.
Usar Transact-SQL
Para comprobar la fragmentación de un índice
En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra de Estándar, haga clic en Nueva consulta.
Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GOLa instrucción anterior puede devolver un conjunto de resultados similar al siguiente:
index_id name avg_fragmentation_in_percent ----------- ----------------------------------------------------- ---------------------------- 1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
Para obtener más información, vea sys.dm_db_index_physical_stats (Transact-SQL).
Uso de SQL Server Management Studio
Para reorganizar o volver a generar un índice
En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.
Expanda la carpeta Tablas .
Expanda la tabla en la que desea reorganizar un índice.
Expanda la carpeta Índices .
Haga clic con el botón derecho en el índice que quiera reorganizar y seleccione Reorganizar.
En el cuadro de diálogo Reorganizar índices , compruebe que el índice correcto se encuentra en la cuadrícula Índices que se van a reorganizar y haga clic en Aceptar.
Active la casilla Compactar datos de columnas de objetos de gran tamaño para especificar que se compacten también todas las páginas que contengan datos de objetos grandes (LOB).
Haga clic en Aceptar.
Para reorganizar todos los índices de una tabla
En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar los índices.
Expanda la carpeta Tablas .
Expanda la tabla en la que desea reorganizar los índices.
Haga clic con el botón derecho en la carpeta Índices y seleccione Reorganizar todo.
En el cuadro de diálogo Reorganizar índices , compruebe que los índices adecuados están en Índices que se van a reorganizar. Para quitar un índice de la cuadrícula Índices que se van a reorganizar , seleccione el índice y, a continuación, presione la tecla SUPR.
Active la casilla Compactar datos de columnas de objetos de gran tamaño para especificar que se compacten también todas las páginas que contengan datos de objetos grandes (LOB).
Haga clic en Aceptar.
Para volver a generar un índice
En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.
Expanda la carpeta Tablas .
Expanda la tabla en la que desea reorganizar un índice.
Expanda la carpeta Índices .
Haga clic con el botón derecho en el índice que quiera reorganizar y seleccione Reorganizar.
En el cuadro de diálogo Volver a generar índices , compruebe que el índice correcto se encuentra en la cuadrícula Índices que se van a volver a generar y haga clic en Aceptar.
Active la casilla Compactar datos de columnas de objetos de gran tamaño para especificar que se compacten también todas las páginas que contengan datos de objetos grandes (LOB).
Haga clic en Aceptar.
Usar Transact-SQL
Para reorganizar un índice desfragmentado
En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra de Estándar, haga clic en Nueva consulta.
Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
Para reorganizar todos los índices de una tabla
En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra de Estándar, haga clic en Nueva consulta.
Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
Para volver a generar un índice desfragmentado
En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra de Estándar, haga clic en Nueva consulta.
Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar. En el ejemplo se vuelve a generar un único índice en la tabla
Employee.USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
Para volver a generar todos los índices de una tabla
En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra de Estándar, haga clic en Nueva consulta.
Copie y pegue el ejemplo siguiente en la ventana de consulta. En el ejemplo se especifica la palabra clave
ALL. Así se regeneran todos los índices asociados a la tabla. Se especifican tres opciones.USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
Para más información, vea ALTER INDEX (Transact-SQL).
Consulte también
Prácticas recomendadas de desfragmentación de índices de Microsoft SQL Server 2000