Reorganización y recompilación de í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 í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 en la base de datos (fragmentada). La fragmentación existe cuando los índices tienen páginas en las que el orden lógico, basado en el valor de clave, no coincide con el orden físico dentro del archivo de datos. Los índices muy fragmentados pueden degradar el rendimiento de las consultas y hacer que la aplicación responda lentamente.

Puede solucionar la fragmentación de índices mediante la reorganización o regeneración de un índice. En el caso de los índices con particiones basados en un esquema de partición, puede usar cualquiera de estos métodos en un índice completo o 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 sola 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 comenzar:

  Detección de la fragmentación

  limitaciones y restricciones

  Seguridad

• Para comprobar la fragmentación de un índice, use:

  SQL Server Management Studio

  Transact-SQL

• Para reorganizar o recompilar un índice mediante:

  SQL Server Management Studio

  Transact-SQL

Antes de empezar

Detección de la fragmentación

El primer paso para decidir qué método de desfragmentación se va a usar es analizar el índice para determinar el grado de fragmentación. Mediante el uso de la función del sistema sys.dm_db_index_physical_stats, puede detectar la fragmentación en un índice específico, todos los índices de una tabla o vista indizada, todos los índices de una base de datos o todos los índices de todas las bases de datos. En el caso de los índices con particiones, sys.dm_db_index_physical_stats también proporciona información de fragmentación para cada partición.

El conjunto de resultados devuelto por la función sys.dm_db_index_physical_stats incluye las columnas siguientes.

Una vez conocido el grado de fragmentación, use la tabla siguiente para determinar el mejor método para corregir la fragmentación.

¹ Volver a generar 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 reorganizar, debe volver a generar índices en línea.

Normalmente, ninguno de estos comandos debe abordar niveles muy bajos de fragmentación (menos del 5 por ciento), ya que la ventaja de quitar una pequeña cantidad de fragmentación es casi siempre superada por el costo de reorganizar o volver a generar el índice.

Consideraciones sobre la desfragmentación de índices

En determinadas condiciones, volver a generar un índice agrupado volverá a generar automáticamente cualquier índice no clúster que haga referencia a la clave de agrupación en clústeres, si los identificadores físicos o lógicos contenidos en los registros de índice no clúster deben cambiar.

Escenarios que obligan a que todos los índices no clúster se vuelvan a generar automáticamente en una tabla:

• Creación de un índice agrupado en una tabla
• Quitar 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 que todos los índices no clúster se vuelvan a generar automáticamente en una tabla:

• Recompilación de un índice clúster único
• Recompilación de un índice clúster no único
• Cambiar el esquema de índice, como aplicar un esquema de partición a un índice agrupado o mover el índice agrupado a un grupo de archivos diferente

Limitaciones y restricciones

Los índices con 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 usadas por el índice se marcan para la desasignación, las filas de datos se copian y ordenan y, a continuación, 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 marcadas para la desasignación se eliminan físicamente en transacciones cortas que ocurren en segundo plano y requieren pocos bloqueos. Para obtener más información sobre las extensiones, consulte la Guía de arquitectura de páginas y extensiones.

La ALTER INDEX REORGANIZE instrucción 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. Por lo tanto, aunque el grupo de archivos puede tener páginas gratuitas disponibles, el usuario todavía puede encontrar 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.

Es posible crear y volver a generar í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 se puede reorganizar o volver a generar un índice si el grupo de archivos en el que se encuentra está sin conexión o se establece en de solo lectura. Cuando se especifica la palabra clave ALL y uno o varios índices están 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

1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea comprobar la fragmentación de un índice.

2. Expanda la carpeta Tablas .

3. Expanda la tabla en la que desea comprobar la fragmentación de un índice.

4. Expanda la carpeta Índices .

5. Haga clic con el botón derecho en el índice del que desea comprobar la fragmentación y seleccione Propiedades.

6. En Seleccionar una página, seleccione Fragmentación.

   La siguiente información está disponible en la página Fragmentación :

   Llenación de páginas
   Indica la totalidad media de las páginas de índice, como un porcentaje. 100% significa que las páginas de índice están completamente llenas. 50% significa que, en promedio, cada página de índice está media llena.

   Fragmentación total
   Porcentaje de fragmentación lógica. Esto indica el número de páginas de un índice que no se almacenan en orden.

   Tamaño medio de fila
   Tamaño promedio de una fila en un nivel de hoja.

   Profundidad
   Número de niveles del índice, incluido el nivel de hoja.

   Registros reenviados
   Número de registros de una pila que tienen punteros hacia 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).

   Filas fantasma
   Número de filas marcadas como eliminadas pero que aún no se han quitado. Estas filas se quitarán mediante un subproceso de limpieza cuando el servidor no esté ocupado. Este valor no incluye filas que se conservan debido a una transacción de aislamiento de instantáneas pendiente.

   Tipo de índice
   Tipo de índice. Los valores posibles son Índice agrupado, Índice no clúster y XML principal. Las tablas también se pueden almacenar como un montón (sin índices), pero no se puede abrir esta página Propiedades del índice.

   Filas de nivel hoja
   Número de filas de nivel hoja.

   Tamaño máximo de fila
   Tamaño máximo de fila a nivel de hoja.

   Tamaño mínimo de fila
   Tamaño mínimo de fila de nivel de hoja.

   Páginas
   Número total de páginas de datos.

   Identificador de la partición
   El identificador de partición del árbol B que contiene el índice.

   Filas fantasma de versiones
   Número de registros fantasma que se conservan debido a una transacción de aislamiento de instantáneas pendiente.

Uso de Transact-SQL

Para comprobar la fragmentación de un índice

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra de Estándar, haga clic en Nueva consulta.

3. 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;   
   GO  
   

   La instrucción anterior podría 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 recompilar un índice

1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.

2. Expanda la carpeta Tablas .

3. Expanda la tabla en la que desea reorganizar un índice.

4. Expanda la carpeta Índices .

5. Haga clic con el botón derecho en el índice que desea reorganizar y seleccione Reorganizar.

6. En el cuadro de diálogo Reorganizar índices , compruebe que el índice correcto está en la cuadrícula Índices que se va a reorganizar y haga clic en Aceptar.

7. Active la casilla Compact large object column data (Datos de columna de objetos grandes ) para especificar que todas las páginas que contienen datos de objetos grandes (LOB) también se compactan.

8. Haga clic en Aceptar.

Para reorganizar todos los índices de una tabla

1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar los índices.

2. Expanda la carpeta Tablas .

3. Expanda la tabla en la que desea reorganizar los índices.

4. Haga clic con el botón derecho en la carpeta Índices y seleccione Reorganizar todo.

5. En el cuadro de diálogo Reorganizar índices , compruebe que los índices correctos están en los índices que se van a reorganizar. Para quitar un índice de la cuadrícula Índices que se van a reorganizar , seleccione el índice y presione la tecla Eliminar.

6. Active la casilla Compact large object column data (Datos de columna de objetos grandes ) para especificar que todas las páginas que contienen datos de objetos grandes (LOB) también se compactan.

7. Haga clic en Aceptar.

Para reconstruir un índice

1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.

2. Expanda la carpeta Tablas .

3. Expanda la tabla en la que desea reorganizar un índice.

4. Expanda la carpeta Índices .

5. Haga clic con el botón derecho en el índice que desea reorganizar y seleccione Reorganizar.

6. En el cuadro de diálogo Recompilar índices , compruebe que el índice correcto está en la cuadrícula Índices que se va a volver a generar y haga clic en Aceptar.

7. Active la casilla Compact large object column data (Datos de columna de objetos grandes ) para especificar que todas las páginas que contienen datos de objetos grandes (LOB) también se compactan.

8. Haga clic en Aceptar.

Uso de Transact-SQL

Para reorganizar un índice desfragmentado

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra de Estándar, haga clic en Nueva consulta.

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

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra de Estándar, haga clic en Nueva consulta.

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

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra de Estándar, haga clic en Nueva consulta.

3. 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 Employee tabla.

   USE AdventureWorks2012;
   GO
   ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
   REBUILD;
   GO
   

Para volver a generar todos los índices de una tabla

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra de Estándar, haga clic en Nueva consulta.

3. Copie y pegue el ejemplo siguiente en la consulta El ejemplo especifica la palabra clave ALL. Esto recompila 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).

Véase también

Procedimientos recomendados de desfragmentación de índices de Microsoft SQL Server 2000