Reorganizar y volver a generar índicesReorganize and Rebuild Indexes

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síAzure SQL DatabasesíAzure SQL Data Warehouse síAlmacenamiento de datos paralelos THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Para obtener contenido relacionado con versiones anteriores de SQL Server, vea Reorganizar y volver a generar índices.For content related to previous versions of SQL Server, see Reorganize and Rebuild Indexes.

En este tema se describe cómo reorganizar o volver a generar un índice fragmentado en SQL Server 2017SQL Server 2017 mediante SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL.This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Motor de base de datos de SQL ServerSQL Server Database Engine mantiene los índices automáticamente cada vez que se realizan operaciones de inserción, actualización o eliminación en los datos subyacentes.The Motor de base de datos de SQL ServerSQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente).Over time these modifications can cause the information in the index to become scattered in the database (fragmented). 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.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. Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación.Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

Puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar.You can remedy index fragmentation by reorganizing or rebuilding an index. 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.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. El proceso de volver a crear un índice quita y vuelve a crear el índice.Rebuilding an index drops and re-creates the index. 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.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. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción.When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. La reorganización de un índice usa muy pocos recursos del sistema.Reorganizing an index uses minimal system resources. 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.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. La reorganización también compacta las páginas de índice.Reorganizing also compacts the index pages. La compactación se basa en el valor de factor de relleno existente.Compaction is based on the existing fill factor value.

Antes de comenzar Before You Begin

Detectar la fragmentación Detecting Fragmentation

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.The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. 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.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. Para los índices con particiones, sys.dm_db_index_physical_stats también proporciona información de la fragmentación para cada partición.For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

El conjunto de resultados devuelto por la función sys.dm_db_index_physical_stats tiene las columnas siguientes.The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

ColumnaColumn DescripciónDescription
avg_fragmentation_in_percentavg_fragmentation_in_percent Porcentaje de fragmentación lógica (páginas de un índice que no funcionan correctamente).The percent of logical fragmentation (out-of-order pages in the index).
fragment_countfragment_count Número de fragmentos (páginas hoja físicamente consecutivas) en el índice.The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pagesavg_fragment_size_in_pages Número promedio de páginas en un fragmento del índice.Average number of pages in one fragment in an index.

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.After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

Valor deavg_fragmentation_in_percent avg_fragmentation_in_percent value Instrucción correctivaCorrective statement
> 5% y < = 30%> 5% and < = 30% ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
> 30%> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)ALTER INDEX REBUILD WITH (ONLINE = ON)

* La regeneración de un índice se puede ejecutar en línea o sin conexión.* Rebuilding an index can be executed online or offline. La reorganización de un índice siempre se ejecuta en línea.Reorganizing an index is always executed online. Para lograr una disponibilidad similar a la opción de reorganización, debe volver a generar los índices en línea.To achieve availability similar to the reorganize option, you should rebuild indexes online.

Estos valores proporcionan directrices generales para la determinación del punto en el que debe cambiar entre ALTER INDEX REORGANIZE y ALTER INDEX REBUILD.These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. No obstante, los valores reales pueden variar de un caso a otro.However, the actual values may vary from case to case. Es importante que experimente la determinación del mejor umbral para su entorno.It is important that you experiment to determine the best threshold for your environment. 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.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.

Nota

En general, la fragmentación en índices pequeños normalmente no se puede controlar.In general, fragmentation on small indexes is often not controllable. Las páginas de índices pequeños a veces se almacenan en extensiones mixtas.The pages of small indexes are sometimes stored on mixed extents. 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.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.

Limitaciones y restricciones Limitations and Restrictions

  • Los índices que tienen más de 128 extensiones se vuelven a generar en dos fases independientes: lógica y física.Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. 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.In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted, then moved to new allocation units created to store the rebuilt index. 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.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.

  • Las opciones del índice no se pueden especificar al reorganizar un índice.Index options cannot be specified when reorganizing an index.

  • 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.The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. Así, aunque el grupo de archivos tenga páginas libres disponibles, puede que siga apareciendo el error 1105 "No se pudo asignar espacio para el objeto <nombreDeÍndice>.<nombreDeTabla> en la base de datos <nombreDeBaseDeDatos> porque el grupo de archivos 'PRIMARY' está lleno".So although the filegroup might have free pages available, the user can still encounter error 1105 "Could not allocate space for object <index name>.<table name> in database <database name> because the 'PRIMARY' filegroup is full."

  • La creación y regeneración de índices no alineados en una tabla con más de 1.000 particiones es posible, pero no se admite.Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones.Doing so may cause degraded performance or excessive memory consumption during these operations.

Nota

A partir de SQL Server 2012SQL Server 2012, las estadísticas no se crean examinando todas las filas de la tabla cuando se crea o se vuelve a generar un índice con particiones.Starting with SQL Server 2012SQL Server 2012, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. En su lugar, el optimizador de consultas usa el algoritmo de muestreo predeterminado para generar estadísticas.Instead, the query optimizer uses the default sampling algorithm to generate statistics. Para obtener estadísticas sobre índices con particiones examinando todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

Seguridad Security

Permisos Permissions

Requiere el permiso ALTER en la tabla o la vista.Requires ALTER permission on the table or view. 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 .User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Usar SQL Server Management Studio Using SQL Server Management Studio

Para comprobar la fragmentación de un índiceTo check the fragmentation of an index

  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.In Object Explorer, Expand the database that contains the table on which you want to check an index’s fragmentation.

  2. Expanda la carpeta Tablas .Expand the Tables folder.

  3. Expanda la tabla en la que desea comprobar la fragmentación de un índice.Expand the table on which you want to check an index’s fragmentation.

  4. Expanda la carpeta Índices .Expand the Indexes folder.

  5. Haga clic con el botón derecho en el índice en el que quiere comprobar la fragmentación y seleccione Propiedades.Right-click the index of which you want to check the fragmentation and select Properties.

  6. Bajo Seleccionar una página, seleccione Fragmentación.Under Select a page, select Fragmentation.

    La siguiente información está disponible en la página Fragmentación :The following information is available on the Fragmentation page:

    Llenado de páginaPage fullness
    Indica el promedio de llenado de las páginas de índice como un porcentaje.Indicates average fullness of the index pages, as a percentage. 100% indica que las páginas de índice están completamente llenas.100% means the index pages are completely full. 50% indica que, como promedio, las páginas de índice están llenas a la mitad.50% means that, on average, each index page is half full.

    Fragmentación totalTotal fragmentation
    Porcentaje de fragmentación lógica.The logical fragmentation percentage. Indica el número de páginas de un índice que no están almacenadas en orden.This indicates the number of pages in an index that are not stored in order.

    Promedio de tamaño de filaAverage row size
    Tamaño medio de una fila de nivel hoja.The average size of a leaf level row.

    ProfundidadDepth
    Número de niveles del índice, incluido el nivel hoja.The number of levels in the index, including the leaf level.

    Registros reenviadosForwarded records
    Número de registros de un montón que han reenviado punteros a otra ubicación de datos.The number of records in a heap that have forward pointers to another data location. Este estado se produce durante una actualización, cuando no existe suficiente espacio para almacenar la nueva fila en la ubicación original.(This state occurs during an update, when there is not enough room to store the new row in the original location.)

    Filas fantasmaGhost rows
    Número de filas marcadas como eliminadas que todavía no se han quitado.The number of rows that are marked as deleted but not yet removed. Estas filas se quitarán en un subproceso de limpieza, cuando el servidor no esté ocupado.These rows will be removed by a clean-up thread, when the server is not busy. Este valor no incluye las filas que se retienen debido a una transacción pendiente de aislamiento de instantáneas.This value does not include rows that are being retained due to an outstanding snapshot isolation transaction.

    Tipo de índiceIndex type
    Tipo de índice.The type of index. Los valores posibles son Índice clúster, Índice no clústery XML principal.Possible values are Clustered index, Nonclustered index, and Primary XML. 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.Tables can also be stored as a heap (without indexes), but then this Index Properties page cannot be opened.

    Filas de nivel de hojaLeaf-level rows
    Número de filas de nivel hoja.The number of leaf level rows.

    Tamaño máximo de la filaMaximum row size
    Tamaño máximo de la fila de nivel de hoja.The maximum leaf-level row size.

    Tamaño mínimo de la filaMinimum row size
    Tamaño mínimo de la fila de nivel de hoja.The minimum leaf-level row size.

    PáginasPages
    Número total de páginas de datos.The total number of data pages.

    Id. de particiónPartition ID
    Id. de partición del árbol b que contiene el índice.The partition ID of the b-tree containing the index.

    Filas fantasma de la versiónVersion ghost rows
    Número de registros fantasma que se retienen debido a una transacción de aislamiento de instantánea pendiente.The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.

Usar Transact-SQL Using Transact-SQL

Para comprobar la fragmentación de un índiceTo check the fragmentation of an index

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.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  
    

    La instrucción anterior puede devolver un conjunto de resultados similar al siguiente: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)  
    

    Para obtener más información, vea sys.dm_db_index_physical_stats (Transact-SQL).For more information, see sys.dm_db_index_physical_stats (Transact-SQL).

Usar SQL Server Management Studio Using SQL Server Management Studio

Para reorganizar o volver a generar un índiceTo reorganize or rebuild an index

  1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.

  2. Expanda la carpeta Tablas .Expand the Tables folder.

  3. Expanda la tabla en la que desea reorganizar un índice.Expand the table on which you want to reorganize an index.

  4. Expanda la carpeta Índices .Expand the Indexes folder.

  5. Haga clic con el botón derecho en el índice que quiera reorganizar y seleccione Reorganizar.Right-click the index you want to reorganize and select Reorganize.

  6. 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.In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.

  7. 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).Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

  8. Haga clic en Aceptar.Click OK.

Para reorganizar todos los índices de una tablaTo reorganize all indexes in a table

  1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar los índices.In Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.

  2. Expanda la carpeta Tablas .Expand the Tables folder.

  3. Expanda la tabla en la que desea reorganizar los índices.Expand the table on which you want to reorganize the indexes.

  4. Haga clic con el botón derecho en la carpeta Índices y seleccione Reorganizar todo.Right-click the Indexes folder and select Reorganize All.

  5. En el cuadro de diálogo Reorganizar índices , compruebe que los índices adecuados están en Índices que se van a reorganizar.In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. 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.To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.

  6. 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).Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

  7. Haga clic en Aceptar.Click OK.

Para volver a generar un índiceTo rebuild an index

  1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.

  2. Expanda la carpeta Tablas .Expand the Tables folder.

  3. Expanda la tabla en la que desea reorganizar un índice.Expand the table on which you want to reorganize an index.

  4. Expanda la carpeta Índices .Expand the Indexes folder.

  5. Haga clic con el botón derecho en el índice que quiera reorganizar y seleccione Volver a generar.Right-click the index you want to reorganize and select Rebuild.

  6. 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.In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.

  7. 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).Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

  8. Haga clic en Aceptar.Click OK.

Usar Transact-SQL Using Transact-SQL

Para reorganizar un índice desfragmentadoTo reorganize a defragmented index

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.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  
    

Para reorganizar todos los índices de una tablaTo reorganize all indexes in a table

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.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  
    

Para volver a generar un índice desfragmentadoTo rebuild a defragmented index

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.Copy and paste the following example into the query window and click Execute. En el ejemplo se vuelve a generar un único índice en la tabla Employee .The example rebuilds a single index on the Employee table.

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

Para volver a generar todos los índices de una tablaTo rebuild all indexes in a table

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el ejemplo siguiente en la ventana de consulta. En el ejemplo se especifica la palabra clave ALL.Copy and paste the following example into the query The example specifies the keyword ALL. Así se regeneran todos los índices asociados a la tabla.This rebuilds all indexes associated with the table. Se especifican tres opciones.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
    

    Para obtener más información, vea ALTER INDEX (Transact-SQL).For more information, see ALTER INDEX (Transact-SQL).

Vea tambiénSee Also

Guía de diseño de índices de SQL ServerSQL Server Index Design Guide