Solución de la fragmentación de índices mediante la reorganización o recompilación de índicesResolve index fragmentation by reorganizing or rebuilding indexes

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics síAlmacenamiento de datos paralelosParallel Data WarehouseyesAlmacenamiento de datos paralelosParallel Data WarehouseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics síAlmacenamiento de datos paralelosParallel Data WarehouseyesAlmacenamiento de datos paralelosParallel Data Warehouse

En este artículo se describe cómo se produce la desfragmentación de los índices y se describe su impacto en el rendimiento de las consultas.This article describes how index defragmentation occurs and discusses its impact on query performance. Una vez que determine la cantidad de fragmentación que existe para un índice, puede desfragmentarlo si lo reorganiza o lo vuelve a generar mediante la ejecución de comandos de Transact-SQL en la herramienta que prefiera o con SQL Server Management Studio.Once you determine the amount of fragmentation that exists for an index, you can defragment an index by either reorganizing an index or rebuilding an index by running Transact-SQL commands in your tool of choice or by using SQL Server Management Studio.

Introducción a la fragmentación de índicesIndex fragmentation overview

Qué es la fragmentación de índices y por qué le debería interesar:What is index fragmentation and why should I care about it:

  • La fragmentación se produce cuando los índices tienen páginas en las que la ordenación lógica dentro del índice, basada en su valor de clave, no coincide con la ordenación física dentro de las páginas de índice.Fragmentation exists when indexes have pages in which the logical ordering within the index, based on the key value of the index, does not match the physical ordering inside the index pages.
  • Motor de base de datosDatabase Engine modifica 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 datosDatabase Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Por ejemplo, la adición de filas en una tabla puede hacer que las páginas existentes en los índices de almacén de filas se dividan para dejar espacio para la inserción de nuevos valores de clave.For example, the addition of rows in a table may cause existing pages in rowstore indexes to split to make room for the insertion of new key values. 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 las consultas porque se necesita E/S adicional para localizar los datos a los que señala el índice.Heavily fragmented indexes can degrade query performance because additional I/O is required to locate data to which the index points. Más E/S hace que la aplicación responda con lentitud, sobre todo cuando se trata de operaciones de examen.More I/O causes your application to respond slowly, especially when scan operations are involved.

Detección de la cantidad de fragmentaciónDetecting the amount of fragmentation

El primer paso para decidir qué método de desfragmentación de índice se va a usar consiste en analizar el índice a fin de determinar la magnitud de la fragmentación.The first step in deciding which index defragmentation method to use is to analyze the index to determine the degree of fragmentation. La fragmentación se detecta de manera diferente para los índices de almacén de filas y los de almacén de columnas.You detect fragmentation differently for rowstore indexes and columnstore indexes.

Nota

Es especialmente importante revisar la fragmentación de índices o montones después de eliminar grandes cantidades de datos.It's especially important to review index or heap fragmentation after large amounts of data are deleted. En el caso de los montones, si hay actualizaciones frecuentes, también puede ser necesario revisar la fragmentación para evitar la proliferación de registros de reenvío.For heaps, if there are frequent updates, it may also be needed to review fragmentation to avoid proliferation of forwarding records. Para más información sobre los montones, vea Montones (tablas sin índices agrupados).For more information about heaps, see Heaps (Tables without Clustered Indexes).

Detección de la fragmentación de índices de almacén de filasDetecting fragmentation of rowstore indexes

Con sys.dm_db_index_physical_stats, puede 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 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 que devuelve sys.dm_db_index_physical_stats incluye las columnas siguientes:The result set returned by sys.dm_db_index_physical_stats 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.

Después de determinar la magnitud de la fragmentación, use la tabla siguiente para determinar el mejor método para quitarla: INDEX REORGANIZE o INDEX.After the degree of fragmentation is known, use the following table to determine the best method to remove the fragmentation: INDEX REORGANIZE or INDEX.

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

1 Estos valores proporcionan directrices generales para la determinación del punto en el que debe cambiar entre ALTER INDEX REORGANIZE y ALTER INDEX REBUILD.1 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.

Sugerencia

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.For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. Es posible que la ventaja de rendimiento no sea perceptible en los índices que se usan principalmente con operaciones de búsqueda.The performance benefit may not be noticeable for indexes that are used primarily for seek operations.
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.Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.

2 La regeneración de un índice se puede ejecutar en línea o sin conexión.2 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. Para obtener más información, vea INDEX y Realización de operaciones de índice en línea.For more information, see INDEX and Perform Index Operations Online.

Los índices con fragmentación o menos del 5 % no se deben desfragmentar, ya que el beneficio de quitar una cantidad de fragmentación tan pequeña es casi siempre ampliamente superado por el costo de CPU implicado en la reorganización o recompilación del índice.Indexes with fragmentation or less than 5 percent do not need to be defragmented because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the CPU cost incurred to reorganize or rebuild the index. Además, cuando se vuelven a generar o se reorganizan índices pequeños de almacén de filas, por lo general la fragmentación no se reduce realmente.Also, rebuilding or reorganizing small rowstore indexes generally does not reduce actually fragmentation. Hasta SQL Server 2014 (12.x)SQL Server 2014 (12.x), inclusive, Motor de base de datos de SQL ServerSQL Server Database Engine asigna espacio mediante extensiones mixtas.Up to, and including, SQL Server 2014 (12.x)SQL Server 2014 (12.x), the Motor de base de datos de SQL ServerSQL Server Database Engine allocates space using mixed extents. Por consiguiente, las páginas de índices pequeños a veces se almacenan en extensiones mixtas.Therefore, 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 it. Vea también Consideraciones específicas para volver a generar índices de almacén de filas.See also Considerations specific to rebuilding rowstore indexes. Para más información sobre las extensiones, vea la Guía de arquitectura de páginas y extensiones.For more information about extents, see the Pages and Extents Architecture Guide.

Detección de la fragmentación en índices de almacén de columnasDetecting fragmentation of columnstore indexes

Mediante sys.dm_db_column_store_row_group_physical_stats, puede determinar el porcentaje de filas eliminadas en un índice, que es una medida razonable para la fragmentación en un grupo de filas de un índice de almacén de columnas.By using sys.dm_db_column_store_row_group_physical_stats, you can determine the percentage of deleted rows in an index, which is a reasonable measure for fragmentation in a rowgroup of a columnstore index. Use esta información para calcular la fragmentación en un índice específico, todos los índices de una tabla, todos los índices de una base de datos o todos los índices de todas las bases de datos.Use this information to compute the fragmentation in a specific index, all indexes on a table, all indexes in a database, or all indexes in all databases.

El conjunto de resultados devuelto por sys.dm_db_column_store_row_group_physical_stats incluye las columnas siguientes:The result set returned by sys.dm_db_column_store_row_group_physical_stats includes the following columns:

ColumnaColumn DescripciónDescription
total_rowstotal_rows Número de filas almacenadas físicamente en el grupo de filas.Number of rows physical stored in the row group. En el caso de los grupos de filas comprimidos, incluye las filas marcadas como eliminadas.For compressed row groups, this includes the rows that are marked as deleted.
deleted_rowsdeleted_rows Número de filas almacenadas físicamente en un grupo de filas comprimido que se han marcado para su eliminación.Number of rows physically stored in a compressed row group that are marked for deletion. Es 0 en el caso de los grupos de filas que se encuentran en el almacén delta.0 for row groups that are in the delta store.

Use esta información devuelta para calcular la fragmentación del índice mediante esta fórmula:Use this information returned to compute index fragmentation using this formula:

100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Una vez que se conoce el grado de la fragmentación del índice, use la tabla siguiente para determinar el mejor método para eliminarla: INDEX REORGANIZE o INDEX.After the degree of index fragmentation is known, use the following table to determine the best method to remove the fragmentation: INDEX REORGANIZE or INDEX.

Valor de la fragmentación calculada en porcentajecomputed fragmentation in percent value Se aplica a la versiónApplies to version Instrucción correctivaCorrective statement
> = 20 %> = 20% SQL Server 2012 (11.x)SQL Server 2012 (11.x) y SQL Server 2014 (12.x)SQL Server 2014 (12.x)and SQL Server 2014 (12.x)SQL Server 2014 (12.x) ALTER INDEX REBUILDALTER INDEX REBUILD
> = 20 %> = 20% A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) ALTER INDEX REORGANIZEALTER INDEX REORGANIZE

Para comprobar la fragmentación de un índice de almacén de filas con Transact-SQLTransact-SQLTo check the fragmentation of a rowstore index using Transact-SQLTransact-SQL

En el ejemplo siguiente, se encuentra el porcentaje de fragmentación promedio de todos los índices de la tabla HumanResources.Employee de la base de datos AdventureWorks2016.The following example finds the average fragmentation percentage of all indexes in the HumanResources.Employee table in the AdventureWorks2016 database.

SELECT a.object_id, object_name(a.object_id) AS TableName,
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'AdventureWorks2016_EXT')
        , OBJECT_ID(N'HumanResources.Employee')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
GO

La instrucción anterior devuelve un conjunto de resultados similar al siguiente.The previous statement returns a result set similar to the following.

object_id   TableName    index_id    IndexName                                             avg_fragmentation_in_percent
----------- ------------ ----------- ----------------------------------------------------- ------------------------------
1557580587  Employee     1           PK_Employee_BusinessEntityID                          0
1557580587  Employee     2           IX_Employee_OrganizationalNode                        0
1557580587  Employee     3           IX_Employee_OrganizationalLevel_OrganizationalNode    0
1557580587  Employee     5           AK_Employee_LoginID                                   66.6666666666667
1557580587  Employee     6           AK_Employee_NationalIDNumber                          50
1557580587  Employee     7           AK_Employee_rowguid                                   0

(6 row(s) affected)

Para más información, consulte sys.dm_db_index_physical_stats.For more information, see sys.dm_db_index_physical_stats.

Para comprobar la fragmentación de un índice de almacén de columnas con Transact-SQLTransact-SQLTo check the fragmentation of a columnstore index using Transact-SQLTransact-SQL

En el ejemplo siguiente, se encuentra el porcentaje de fragmentación promedio de todos los índices de la tabla dbo.FactResellerSalesXL_CCI de la base de datos AdventureWorksDW2016.The following example finds the average fragmentation percentage of all indexes in the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

SELECT i.object_id,
    object_name(i.object_id) AS TableName,
    i.index_id,
    i.name AS IndexName,
    100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'
FROM sys.indexes AS i  
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups
    ON i.object_id = CSRowGroups.object_id
    AND i.index_id = CSRowGroups.index_id
WHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY object_name(i.object_id), i.name;

La instrucción anterior devuelve un conjunto de resultados similar al siguiente.The previous statement returns a result set similar to the following.

object_id   TableName                   index_id    IndexName                       Fragmentation
----------- --------------------------- ----------- ------------------------------- ---------------
114099447   FactResellerSalesXL_CCI     1           IndFactResellerSalesXL_CCI      0

(1 row(s) affected)

Comprobación de la fragmentación de un índice con SQL Server Management StudioCheck index fragmentation using SQL Server Management Studio

Nota

No se puede usar Management StudioManagement Studio para calcular la fragmentación de los índices de almacén de columnas en SQL Server y tampoco para calcular la fragmentación de los índices en Azure SQL Database.Management StudioManagement Studio cannot be used to compute fragmentation of columnstore indexes in SQL Server and cannot be used to compute fragmentation of any indexes in Azure SQL Database. Use el ejemplo de Transact-SQLTransact-SQL anterior para estos escenarios.Use the preceding Transact-SQLTransact-SQL example for these scenarios.

  1. 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.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 quiera 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:

ValueValue DescripciónDescription
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.
Identificador de la 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.

Desfragmentación de índices mediante la recompilación o reorganización del índiceDefragmenting indexes by rebuilding or reorganizing the index

Para desfragmentar un índice fragmentado, use uno de los métodos siguientes:You defragment a fragmented index by using one of the following methods:

  • Reorganización de índicesIndex reorganization
  • Recompilación de índicesIndex rebuild

Nota

Para los índices con particiones creados a partir de un esquema de partición, puede usar cualquiera de los métodos siguientes 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 the following methods on a complete index or a single partition of an index.

Reorganización de un índiceReorganize an index

La reorganización de un índice usa muy pocos recursos del sistema y es una operación en línea.Reorganizing an index uses minimal system resources and is an online operation. Esto significa que los bloqueos de tabla a largo plazo no se mantienen y que las consultas o actualizaciones en la tabla subyacente pueden continuar durante la transacción ALTER INDEX REORGANIZE.This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

  • Para los índices de almacén de filas, el Motor de base de datosDatabase Engine desfragmenta el nivel hoja de los índices agrupados y no agrupados de las tablas y las vistas mediante la reordenación física de las páginas de nivel hoja para que coincidan con el orden lógico de los nodos hoja (de izquierda a derecha).For rowstore indexes, the Motor de base de datosDatabase Engine defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order of the leaf nodes (left to right). La reorganización también compacta las páginas de índice en función del valor de factor de relleno del índice.Reorganizing also compacts the index pages based on the index's fill factor value. Para ver el valor de factor de relleno, use sys.indexes.To view the fill factor setting, use sys.indexes. Para obtener ejemplos de sintaxis, vea Ejemplos: Reorganización del almacén de filas.For syntax examples, see Examples: Rowstore reorganize.

  • Al usar índices de almacén de columnas, el almacén delta puede acabar con múltiples grupos filas de pequeño tamaño después de insertar, actualizar y eliminar datos a lo largo del tiempo.When using columnstore indexes, the delta store may end up with multiple small rowgroups after inserting, updating, and deleting data over time. La reorganización de un índice de almacén de columnas fuerza a todos los grupos de filas al almacén de columnas y, luego, los combina en menos grupos de filas con más filas.Reorganizing a columnstore index forces all of the rowgroups into the columnstore, and then combines the rowgroups into fewer rowgroups with more rows. La operación de reorganización también quita las filas que se hayan eliminado del almacén de columnas.The reorganize operation also removes rows that have been deleted from the columnstore. Inicialmente, el proceso de reorganización requiere recursos de CPU adicionales para comprimir los datos, lo que puede reducir el rendimiento general del sistema.Reorganizing initially requires additional CPU resources to compress the data, which may slow overall system performance. Pero tan pronto como se comprimen los datos, el rendimiento de las consultas mejora.However, as soon as the data is compressed, query performance improves. Para obtener ejemplos de sintaxis, vea Ejemplos: Reorganización de ColumnStore.For syntax examples, see Examples: ColumnStore reorganize.

Volver a generar un índiceRebuild 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. En función del tipo de índice y de la versión del Motor de base de datosDatabase Engine, una operación de recompilación puede realizarse en línea o sin conexión.Depending on the type of index and Motor de base de datosDatabase Engine version, a rebuild operation can be done online or offline. Para obtener la sintaxis de T-SQL, vea ALTER INDEX REBUILDFor the T-SQL syntax, see ALTER INDEX REBUILD

  • Para los índices de almacén de filas, la recompilación quita la fragmentación, reclama el espacio en disco mediante la compactación de las páginas según el valor de factor de relleno especificado o existente, y reordena las filas del índice en páginas contiguas.For rowstore indexes, rebuilding 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. No es necesario quitar las restricciones FOREIGN KEY por adelantado.Foreign key constraints do not have to be dropped in advance. Cuando se regeneran índices con 128 extensiones o más, el Motor de base de datosDatabase Engine difiere las cancelaciones de asignación de página y sus bloqueos asociados hasta después de la confirmación de la transacción.When indexes with 128 extents or more are rebuilt, the Motor de base de datosDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Para obtener ejemplos de sintaxis, vea Ejemplos: Reorganización del almacén de filas.For syntax examples, see Examples: Rowstore reorganize.

  • Para los índices de almacén de columnas, la recompilación quita la fragmentación, mueve todas las filas al almacén de columnas y recupera el espacio en disco mediante la eliminación física de las filas que se han quitado de forma lógica de la tabla.For columnstore indexes, rebuilding removes fragmentation, moves all rows into the columnstore, and reclaims disk space by physically deleting rows that have been logically deleted from the table.

    Sugerencia

    A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), normalmente no es necesario volver a generar el índice de almacén de columnas, ya que REORGANIZE realiza las operaciones básicas de una regeneración en segundo plano como una operación en línea.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), rebuilding the columnstore index is usually not needed since REORGANIZE performs the essentials of a rebuild in the background as an online operation.

    Para obtener ejemplos de sintaxis, vea Ejemplos: recompilación de almacén de columnas.For syntax examples, see Examples: ColumnStore rebuild.

PermisosPermissions

Debe tener un permiso de ALTER sobre la tabla o vista.Requires ALTER permission on the table or view. El usuario debe ser miembro de al menos uno de los siguientes roles:User must be a member of at least one of the following roles:

  • Rol de base de datos db_ddladmin1db_ddladmin database role 1
  • Rol de base de datos db_ownerdb_owner database role
  • Rol de servidor sysadminsysadmin server role

1El rol de base de datos db_ddladmin es el que tiene menos privilegios.1db_ddladmin database role is the least privileged.

Eliminación de la fragmentación con SQL Server Management StudioSQL Server Management StudioRemove fragmentation using SQL Server Management StudioSQL 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.

Eliminación de la fragmentación con Transact-SQLTransact-SQLRemove fragmentation using Transact-SQLTransact-SQL

Nota

Para obtener más ejemplos sobre el uso de Transact-SQLTransact-SQL para volver a generar o reorganizar los índices, vea Ejemplos de ALTER INDEX: índices de almacén de columnas y Ejemplos de ALTER INDEX: índices de almacén de filas.For more examples about using Transact-SQLTransact-SQL to rebuild or reorganize indexes, see ALTER INDEX Examples: Columnstore Indexes and ALTER INDEX Examples: Rowstore Indexes.

Para reorganizar un índice fragmentadoTo reorganize a fragmented index

En el siguiente ejemplo se reorganiza el índice IX_Employee_OrganizationalLevel_OrganizationalNode en la tabla HumanResources.Employee de la base de datos AdventureWorks2016.The following example reorganizes the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table in the AdventureWorks2016 database.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

En el siguiente ejemplo se reorganiza el índice de almacén de columnas IndFactResellerSalesXL_CCI en la tabla dbo.FactResellerSalesXL_CCI de la base de datos AdventureWorksDW2016.The following example reorganizes the IndFactResellerSalesXL_CCI columnstore index on the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

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

En el siguiente ejemplo se reorganizan todos los índices en la tabla HumanResources.Employee de la base de datos AdventureWorks2016.The following example reorganizes all indexes on the HumanResources.Employee table in the AdventureWorks2016 database.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Para volver a generar un índice fragmentadoTo rebuild a fragmented index

En el siguiente ejemplo se regenera un único índice en la tabla Employee de la base de datos AdventureWorks2016.The following example rebuilds a single index on the Employee table in the AdventureWorks2016 database.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

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

En el ejemplo siguiente se vuelven a generar todos los índices asociados con la tabla de la base de datos de AdventureWorks2016 mediante la palabra clave ALL.The following example rebuilds all indexes associated with the table in the AdventureWorks2016 database using the ALL keyword. Se especifican tres opciones.Three options are specified.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

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

Administración automática de índice y estadísticasAutomatic index and statistics management

Aproveche soluciones como la desfragmentación de índice adaptable para administrar automáticamente las actualizaciones de estadísticas y la desfragmentación de índices para una o varias bases de datos.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Este procedimiento elige automáticamente si se debe volver a generar o reorganizar un índice según su nivel de fragmentación, entre otros parámetros y actualiza las estadísticas con un umbral lineal.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

Consideraciones específicas para volver a generar índices de almacén de filasConsiderations specific to rebuilding rowstore indexes

Al volver a generar un índice agrupado se vuelven a generar de forma automática 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.Rebuilding a clustered index automatically rebuilds any nonclustered index that reference the clustering key, if the physical or logical identifiers contained in the nonclustered index records need to change.

En los escenarios siguiente se obliga a que todos los índices no agrupados de almacén de filas de una tabla se vuelvan a generar de forma automática:The following scenarios force all rowstore nonclustered indexes on a table to be automatically rebuilt:

  • Creación de un índice agrupado en una tablaCreating a clustered index on a table
  • Eliminación de un índice agrupado, lo que hace que la tabla se almacene como un montónRemoving a clustered index, which causes the table to be stored as a heap
  • Cambio de la clave de agrupación en clústeres para incluir o excluir columnasChanging the clustering key to include or exclude columns

En los escenarios siguientes no es necesario que todos los índices no agrupados de almacén de filas se vuelvan a generar automáticamente en una tabla:The following scenarios do not require all rowstore nonclustered indexes to be automatically rebuilt on a table:

  • Recompilación de un índice agrupado únicoRebuilding a unique clustered index
  • Recompilación de un índice agrupado que no es únicoRebuilding a non-unique clustered index
  • 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 diferenteChanging the index schema, such as applying a partitioning scheme to a clustered index or moving the clustered index to a different filegroup

Importante

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.An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. 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.When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

Mientras se vuelve a generar un índice, el medio físico debe tener espacio suficiente para almacenar dos copias del índice.While an index rebuild occurs, the physical media must have enough space to store two copies of the index. Cuando finaliza la recompilación, el Motor de base de datosDatabase Engine elimina el índice original.When the rebuild is finished, the Motor de base de datosDatabase Engine deletes the original index.

Cuando se especifica ALL en la instrucción ALTER INDEX, se reorganizan los índices relacionales, tanto agrupados como no agrupados, y los índices XML.When ALL is specified with the ALTER INDEX statement, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized.

Consideraciones específicas para volver a generar un índice de almacén de columnasConsiderations specific to rebuilding a columnstore index

Cuando se vuelve a generar un índice de almacén de columnas, Motor de base de datosDatabase Engine lee todos los datos del índice original de almacén de columnas, incluido el almacén delta.When rebuilding a columnstore index, the Motor de base de datosDatabase Engine reads all data from the original columnstore index, including the delta store. Combina los datos en grupos de filas nuevos y comprime los grupos de filas en el almacén de columnas.It combines the data into new rowgroups, and compresses the rowgroups into the columnstore. El Motor de base de datosDatabase Engine desfragmenta el almacén de columnas mediante la eliminación física de las filas que se han eliminado lógicamente de la tabla.The Motor de base de datosDatabase Engine defragments the columnstore by physically deleting rows that have been logically deleted from the table. Los bytes eliminados se reclaman en el disco.The deleted bytes are reclaimed on the disk.

Nota

La reorganización de un índice de almacén de columnas mediante Management StudioManagement Studio combinará grupos de filas COMPRESSED, pero no obligará a que todos los grupos de filas se compriman en el almacén de columnas.Reorganizing a columnstore index using Management StudioManagement Studio will combine COMPRESSED rowgroups together, but does not force all rowgroups to be compressed into the columnstore. Se comprimirán los grupos de filas con el estado CLOSED, mientras que los grupos de filas con el estado OPEN no se comprimirán en el almacén de columnas.CLOSED rowgroups will be compressed but OPEN rowgroups will not be compressed into the columnstore. Para comprimir forzosamente todos los grupos de filas, use el ejemplo de Transact-SQLTransact-SQL siguiente.To forcibly compress all rowgroups, use the Transact-SQLTransact-SQL example below.

Nota

A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), el motor de tupla cuenta con la ayuda de una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas delta OPEN que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina los grupos de filas COMPRESSED desde donde se ha eliminado un gran número de filas.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the tuple-mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo.This improves the columnstore index quality over time.
Para obtener más información sobre los términos y conceptos de almacén de columnas, vea Índices de almacén de columnas: Información general.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Recompilación de una partición en lugar de la tabla completaRebuild a partition instead of the entire table

  • Volver a generar la tabla completa tarda mucho si el índice es grande y requiere el espacio en disco suficiente para almacenar una copia adicional del índice durante la regeneración.Rebuilding the entire table takes a long time if the index is large, and it requires enough disk space to store an additional copy of the index during the rebuild. Por lo general, solo es necesario volver a generar la partición que se ha usado más recientemente.Usually it is only necessary to rebuild the most recently used partition.
  • Para las tablas con particiones, no es necesario que vuelva a generar el índice de almacén de columnas completo, ya que es probable que se produzca la fragmentación solo en las particiones que se han modificado recientemente.For partitioned tables, you do not need to rebuild the entire columnstore index because fragmentation is likely to occur in only the partitions that have been modified recently. Las tablas de hechos y las tablas de dimensiones de gran tamaño normalmente tienen particiones para poder realizar operaciones de copia de seguridad y de administración con los fragmentos de la tabla.Fact tables and large dimension tables are usually partitioned in order to perform backup and management operations on chunks of the table.

Recompilación de una partición después de haber realizado operaciones DML intensivasRebuild a partition after heavy DML operations

La recompilación de una partición la desfragmenta y reduce el almacenamiento de disco.Rebuilding a partition defragments the partition and reduces disk storage. La recompilación elimina todas las filas del almacén de columnas que están marcadas para la eliminación y traslada todos los grupos de filas del almacén delta al de columnas.Rebuilding deletes all rows from the columnstore that are marked for deletion and moves all rowgroups from the delta store into the columnstore. Puede haber varios grupos de filas en el almacén delta con menos de un millón de filas.There can be multiple rowgroups in the delta store that have less than one million rows.

Recompilación de una partición después de cargar los datosRebuild a partition after loading data

La recompilación de una partición después de cargar datos permite asegurarse de que todos los datos se almacenan en el almacén de columnas.Rebuilding a partition after loading date ensures all data is stored in the columnstore. Cuando cada proceso simultáneo carga al mismo tiempo menos de 100 000 filas en la misma partición, esta puede acabar con varios almacenes delta.When concurrent processes each load less than 100,000 rows into the same partition at the same time, the partition can end up with multiple delta stores. La recompilación traslada todas las filas del almacén delta al almacén de columnas.Rebuilding moves all delta store rows into the columnstore.

Consideraciones específicas para reorganizar un índice de almacén de columnasConsiderations specific to reorganizing a columnstore index

Al reorganizar un índice de almacén de columnas, Motor de base de datosDatabase Engine comprime cada grupo de filas delta con el estado CLOSED en el almacén de columnas como un grupo de filas comprimido.When reorganizing a columnstore index, the Motor de base de datosDatabase Engine compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) y en Azure SQL DatabaseAzure SQL Database, el comando REORGANIZE realiza estas otras optimizaciones de desfragmentación en línea:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database, the REORGANIZE command performs the following additional defragmentation optimizations online:

  • Quita físicamente las filas de un grupo de filas cuando el 10 % o más de las filas se hayan eliminado lógicamente.Physically removes rows from a rowgroup when 10% or more of the rows have been logically deleted. Los bytes eliminados se reclaman en los medios físicos.The deleted bytes are reclaimed on the physical media. Por ejemplo, si en un grupo de filas comprimido que contiene un millón de filas se eliminan 100 000 filas, SQL ServerSQL Server quita las filas eliminadas y vuelve a comprimir el grupo de filas con 900 000 filas.For example, if a compressed row group of 1 million rows has 100K rows deleted, SQL ServerSQL Server will remove the deleted rows and recompress the rowgroup with 900k rows. Ahorra almacenamiento mediante la eliminación de filas eliminadas.It saves on the storage by removing deleted rows.

  • Combina uno o varios grupos de filas comprimidos para aumentar las filas por grupo de filas, hasta alcanzar el máximo de 1 048 576 filas.Combines one or more compressed rowgroups to increase rows per rowgroup up to the maximum of 1,048,576 rows. Por ejemplo, si importa de forma masiva 5 lotes de 102 400 filas, obtendrá 5 grupos de filas comprimidos.For example, if you bulk import 5 batches of 102,400 rows you will get 5 compressed rowgroups. Si ejecuta REORGANIZE, estos grupos de filas se combinarán en un grupo de filas comprimido del tamaño de 512 000 filas.If you run REORGANIZE, these rowgroups will get merged into 1 compressed rowgroup of size 512,000 rows. Se supone que no había ninguna limitación de memoria o de tamaño de diccionario.This assumes there were no dictionary size or memory limitations.

  • Para los grupos de filas en los que un 10 % o más de las filas se han eliminado lógicamente, el Motor de base de datosDatabase Engine intenta combinar este grupo de filas con uno o varios grupos de filas.For rowgroups in which 10% or more of the rows have been logically deleted, the Motor de base de datosDatabase Engine tries to combine this rowgroup with one or more rowgroups. Por ejemplo, el grupo de filas 1 se comprimió con 500 000 filas y el grupo de filas 21 se comprimió con el máximo de 1 048 576 filas.For example, rowgroup 1 is compressed with 500,000 rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows. El grupo de filas 21 tiene el 60 % de las filas eliminadas, lo que deja 409 830 filas.Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. Motor de base de datosDatabase Engine favorece la opción de combinar estos dos grupos de filas para comprimir un nuevo grupo de filas que tenga 909 830 filas.The Motor de base de datosDatabase Engine favors combining these two rowgroups to compress a new rowgroup that has 909,830 rows.

Después de realizar cargas de datos, puede haber varios grupos de filas pequeños en el almacén delta.After performing data loads, you can have multiple small rowgroups in the delta store. Puede usar ALTER INDEX REORGANIZE para forzar a todos los grupos de filas al almacén de columnas y luego combinar los grupos de filas en menos grupos de filas con más filas.You can use ALTER INDEX REORGANIZE to force all of the rowgroups into the columnstore, and then to combine the rowgroups into fewer rowgroups with more rows. La operación de reorganización también quitará las filas que se hayan eliminado del almacén de columnas.The reorganize operation will also remove rows that have been deleted from the columnstore.

Limitaciones y restriccionesLimitations and restrictions

Los índices de almacén de filas que tienen más de 128 extensiones se vuelven a generar en dos fases independientes: lógica y física.Rowstore 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. Para obtener más información sobre las extensiones, vea Guía de arquitectura de páginas y extensiones.For more information about extents, see Pages and Extents Architecture Guide.

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 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 in another file within the filegroup. 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.So although the filegroup might have free pages available, the user can still encounter 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.

Advertencia

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. Microsoft recomienda usar solo índices alineados cuando el número de particiones sea superior a 1000.Microsoft recommends using only aligned indexes when the number of partitions exceed 1,000.

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.An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. 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.When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

Estadísticas:Statistics:

  • Cuando se crea o se vuelve a generar un índice, las estadísticas se crean o actualizan mediante el examen de todas las filas de la tabla.When an index is created or rebuilt, statistics are created or updated by scanning all the rows in the table. Pero a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), las estadísticas no se crean o actualizan examinando todas las filas de la tabla cuando se crea o se vuelve a generar un índice con particiones.However, starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created or updated 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 estas estadísticas.Instead, the Query Optimizer uses the default sampling algorithm to generate these statistics. Para obtener estadísticas sobre índices con particiones mediante el examen de 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.

  • Cuando se reorganiza un índice, las estadísticas no se actualizan.When an index is reorganized, statistics are not updated.

No es posible reorganizar un índice cuando ALLOW_PAGE_LOCKS está establecido en OFF.An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

Hasta SQL Server 2017 (14.x)SQL Server 2017 (14.x), la regeneración de un índice de almacén de columnas agrupado es una operación sin conexión.Up to SQL Server 2017 (14.x)SQL Server 2017 (14.x), rebuilding a clustered columnstore index is an offline operation. El motor de base de datos tiene que adquirir un bloqueo exclusivo en la tabla o la partición mientras se produce la regeneración.The database engine has to acquire an exclusive lock on the table or partition while the rebuild occurs. Los datos están sin conexión y no se encuentran disponibles durante el proceso, incluso si se usa NOLOCK, el aislamiento de instantánea de lectura confirmada (RCSI) o el aislamiento de instantánea.The data is offline and unavailable during the rebuild even when using NOLOCK, Read-committed Snapshot Isolation (RCSI), or Snapshot Isolation. A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), se puede volver a generar un índice de almacén de columnas agrupado mediante la opción ONLINE = ON.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), a clustered columnstore index can be rebuilt using the ONLINE = ON option.

Para una tabla de Azure Synapse Analytics (anteriormente Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)) con un índice de almacén de columnas agrupado ordenado, ALTER INDEX REBUILD reordenará los datos mediante TempDB.For an Azure Synapse Analytics (formerly Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)) table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data using TempDB. Supervise TempDB durante las operaciones de regeneración.Monitor TempDB during rebuild operations. Si necesita más espacio en TempDB, escale verticalmente el almacenamiento de datos.If you need more TempDB space, scale up the data warehouse. Vuelva a reducirlo verticalmente una vez completada la recompilación del índice.Scale back down once the index rebuild is complete.

Para una tabla de Azure Synapse Analytics (anteriormente Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)) con un índice de almacén de columnas agrupado ordenado, ALTER INDEX REORGANIZE no reordena los datos.For an Azure Synapse Analytics (formerly Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)) table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. Para reordenar los datos, use ALTER INDEX REBUILD.To resort the data use ALTER INDEX REBUILD.

Uso de INDEX REBUILD para recuperarse de errores de hardwareUsing INDEX REBUILD to recover from hardware failures

En versiones anteriores de SQL ServerSQL Server, a veces se podía volver a generar un índice no agrupado de almacén de filas para corregir incoherencias provocadas por errores de hardware.In earlier versions of SQL ServerSQL Server, you could sometimes rebuild a rowstore nonclustered index to correct inconsistencies caused by hardware failures. A partir de SQL Server 2008SQL Server 2008, aún es posible reparar estas incoherencias entre el índice y el índice agrupado al volver a generar un índice no agrupado sin conexión.Starting with SQL Server 2008SQL Server 2008, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. Pero no es posible reparar las incoherencias de índices no agrupados mediante la regeneración del índice en línea, ya que el mecanismo de regeneración en línea usa el índice no agrupado existente como base para la regeneración y, por tanto, la incoherencia persiste.However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism uses the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. La regeneración del índice sin conexión hará que se examine el índice clúster (o montón) y eliminará la incoherencia.Rebuilding the index offline can sometimes force a scan of the clustered index (or heap) and so remove the inconsistency. Para garantizar una regeneración del índice agrupado, quite y vuelva a crear el índice no agrupado.To assure a rebuild from the clustered index, drop and recreate the nonclustered index. Al igual que en las versiones anteriores, para recuperar incoherencias se recomienda restaurar los datos afectados desde una copia de seguridad. No obstante, es posible que pueda reparar las incoherencias del índice mediante la regeneración del índice no clúster sin conexión.As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. Para obtener más información, vea DBCC CHECKDB (Transact-SQL).For more information, see DBCC CHECKDB (Transact-SQL).

Consulte tambiénSee also