Reorganize and Rebuild Indexes This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. In This Topic
Before You Begin
Detecting FragmentationThe first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition. The result set returned by the sys.dm_db_index_physical_stats function includes the following columns. Column | Description |
|---|
avg_fragmentation_in_percent | The percent of logical fragmentation (out-of-order pages in the index). | fragment_count | The number of fragments (physically consecutive leaf pages) in the index. | avg_fragment_size_in_pages | Average number of pages in one fragment in an index. |
After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation. avg_fragmentation_in_percent value | Corrective statement |
|---|
> 5% and < = 30% | ALTER INDEX REORGANIZE | > 30% | ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online. These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. Very low levels of fragmentation (less than 5 percent) should not be addressed by either of these commands because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index. .gif) Note |
|---|
In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. |
Limitations and RestrictionsIndexes with more than 128 extents are rebuilt in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted and then moved to new allocation units created to the store the rebuilt index. In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks. Index options cannot be specified when reorganizing an index.
SecurityPermissionsRequires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. .gif) [Top]
Using SQL Server Management Studio
To check the fragmentation of an indexIn Object Explorer, Expand the database that contains the table on which you want to check an index’s fragmentation. Expand the Tables folder. Expand the table on which you want to check an index’s fragmentation. Expand the Indexes folder. Right-click the index of which you want to check the fragmentation and select Properties. Under Select a page, select Fragmentation. The following information is available on the Fragmentation page: - Page fullness
Indicates average fullness of the index pages, as a percentage. 100% means the index pages are completely full. 50% means that, on average, each index page is half full. - Total fragmentation
The logical fragmentation percentage. This indicates the number of pages in an index that are not stored in order. - Average row size
The average size of a leaf level row. - Depth
The number of levels in the index, including the leaf level. - Forwarded records
The number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.) - Ghost rows
The number of rows that are marked as deleted but not yet removed. These rows will be removed by a clean-up thread, when the server is not busy. This value does not include rows that are being retained due to an outstanding snapshot isolation transaction. - Index type
The type of index. Possible values are Clustered index, Nonclustered index, and Primary XML. Tables can also be stored as a heap (without indexes), but then this Index Properties page cannot be opened. - Leaf-level rows
The number of leaf level rows. - Maximum row size
The maximum leaf-level row size. - Minimum row size
The minimum leaf-level row size. - Pages
The total number of data pages. - Partition ID
The partition ID of the b-tree containing the index. - Version ghost rows
The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.
[Top]
Using Transact-SQL
To check the fragmentation of an indexIn Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute.
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
The statement above might return a result set similar to the following.
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)
For more information, see sys.dm_db_index_physical_stats (Transact-SQL). [Top]
Using SQL Server Management Studio
To reorganize or rebuild an indexIn Object Explorer, Expand the database that contains the table on which you want to reorganize an index. Expand the Tables folder. Expand the table on which you want to reorganize an index. Expand the Indexes folder. Right-click the index you want to reorganize and select Reorganize. In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK. Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted. Click OK.
To reorganize all indexes in a tableIn Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes. Expand the Tables folder. Expand the table on which you want to reorganize the indexes. Right-click the Indexes folder and select Reorganize All. In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key. Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted. Click OK.
To rebuild an indexIn Object Explorer, Expand the database that contains the table on which you want to reorganize an index. Expand the Tables folder. Expand the table on which you want to reorganize an index. Expand the Indexes folder. Right-click the index you want to reorganize and select Reorganize. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK. Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted. Click OK.
[Top]
Using Transact-SQL
To reorganize a defragmented indexIn Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute.
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
To reorganize all indexes in a tableIn Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012;
GO
-- Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ;
GO
To rebuild a defragmented indexIn Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute. The example rebuilds a single index on the Employee table.
USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
To rebuild all indexes in a tableIn Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query The example specifies the keyword ALL. This rebuilds all indexes associated with the table. Three options are specified.
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
For more information, see ALTER INDEX (Transact-SQL). [Top]
See Also
|
Reorganizar y volver a generar índices En este tema se describe cómo reorganizar o volver a generar un índice fragmentado en SQL Server 2012 mediante SQL Server Management Studio o Transact-SQL. Motor de base de datos de SQL Server mantiene los índices automáticamente 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 generar 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 emplea muy pocos recursos del sistema. Desfragmenta el nivel hoja de los índices clúster y no clúster de las tablas y las vistas reordenando 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
Detectar la fragmentaciónEl 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. |
Limitaciones y restriccionesLos í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. Las opciones del índice no se pueden especificar al reorganizar un índice.
SeguridadPermisosRequiere el permiso ALTER en la tabla o la 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. [Top]
Usar SQL Server Management Studio
Para comprobar la fragmentación de un índiceEn el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea comprobar la fragmentación de un índice. Expanda la carpeta Tablas. Expanda la tabla en la que desea comprobar la fragmentación de un índice. Expanda la carpeta Índices. Haga clic con el botón secundario en el índice en el que desea 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úster y 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. - Id. de 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 conservan debido a una transacción pendiente de aislamiento de instantáneas.
[Top]
Usar Transact-SQL
Para comprobar la fragmentación de un índiceEn el Explorador de objetos, conéctese a una instancia del Motor de base de datos. En la barra Estándar, haga clic en Nueva consulta. Copie y pegue el ejemplo siguiente 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 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). [Top]
Usar SQL Server Management Studio
Para reorganizar o volver a generar un índiceEn 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 secundario en el índice que desee 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 tablaEn 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 secundario 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 índiceEn 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 secundario en el índice que desee 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.
[Top]
Usar Transact-SQL
Para reorganizar un índice desfragmentadoEn el Explorador de objetos, conéctese a una instancia del Motor de base de datos. En la barra Estándar, haga clic en Nueva consulta. Copie y pegue el ejemplo siguiente 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 tablaEn 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 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 desfragmentadoEn el Explorador de objetos, conéctese a una instancia del Motor de base de datos. En la barra Estándar, haga clic en Nueva consulta. Copie y pegue el ejemplo siguiente 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 tablaEn el Explorador de objetos, conéctese a una instancia del Motor de base de datos. En la barra 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 obtener más información, vea ALTER INDEX (Transact-SQL). [Top]
Vea también
|