Resolve index fragmentation by reorganizing or rebuilding indexes

• 03/19/2020
• 21 minutes to read
• • p
  • j
  • M
  • M
  • d
  • +11

Applies to: SQL Server (todas las versiones compatibles) Azure SQL Database Instancia administrada de Azure SQL Azure Synapse Analytics Almacén de datos en paralelo

Este artículo describe cómo se produce la desfragmentación de índices y analiza su impacto en el rendimiento de las consultas. Una vez que determine la cantidad de fragmentación que existe para un índice, puede desfragmentarlo reorganizando un índice o reconstruyendo un índice ejecutando comandos Transact-SQL en la herramienta que elija o utilizando SQL Server Management Studio.

Descripción general de la fragmentación del índice

Qué es la fragmentación del índice y por qué debería importarme:

• La fragmentación existe cuando los índices tienen páginas en las que el orden lógico dentro del índice, basado en el valor clave del índice, no coincide con el orden físico dentro de las páginas del índice.
• El motor de base de datos modifica automáticamente los índices cada vez que se realizan operaciones de inserción, actualización o eliminación en los datos subyacentes. Por ejemplo, la adición de filas en una tabla puede hacer que las páginas existentes en los índices del almacén de filas se dividan para dejar espacio a la inserción de nuevos valores de clave. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse en la base de datos (fragmentada). La fragmentación existe cuando los índices tienen páginas en las que el orden lógico, basado en el valor de la clave, no coincide con el orden físico dentro del archivo de datos.
• Los índices muy fragmentados pueden degradar el rendimiento de las consultas porque se requieren E/S adicionales para localizar los datos a los que apunta el índice. Más E / S hacen que la aplicación responda lentamente, especialmente cuando se trata de operaciones de escaneo.

Detección de la cantidad de fragmentación

El primer paso para decidir qué método de desfragmentación de índice utilizar es analizar el índice para determinar el grado de fragmentación. La fragmentación se detecta de manera diferente para los índices de almacén de filas y los índices de almacén de columnas.

Detección de fragmentación de índices de almacén de filas

Mediante sys.dm_db_index_physical_stats, puede detectar fragmentación en un índice específico, todos los índices en una tabla o vista indexada, todos los índices en una base de datos o todos los índices en todas las bases de datos. Para índices con particiones, sys. dm_db_index_physical_stats también proporciona información de fragmentación para cada partición.

El conjunto de resultados devuelto por sys.dm_db_index_physical_stats incluye las siguientes columnas:

Columna	Descripción
avg_fragmentation_in_percent	El porcentaje de fragmentación lógica (de-orden de las páginas en el índice).
fragment_count	El número de fragmentos (páginas de hojas físicamente consecutivas) en el índice.
avg_fragment_size_in_pages	número Promedio de páginas en un fragmento del índice.

Después de que el grado de fragmentación, utilice la siguiente tabla para determinar el mejor método para eliminar la fragmentación: ÍNDICE de REORGANIZAR o ÍNDICE.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30% 1	ALTER INDEX REORGANIZE
> 30% 1	ALTER INDEX REBUILD WITH (ONLINE = ON) 2

1 These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE y ALTER INDEX REBUILD. Sin embargo, los valores reales pueden variar de un caso a otro. Es importante que experimente para determinar el mejor umbral para su entorno.

2 La reconstrucció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 reorganizar, debe reconstruir los índices en línea. Para obtener más información, consulte INDEXAR y Realizar operaciones de Indexación en línea.

Los índices con fragmentación inferior al 5 por ciento no necesitan desfragmentarse porque el beneficio de eliminar una cantidad tan pequeña de fragmentación casi siempre se ve compensado por el costo de CPU incurrido para reorganizar o reconstruir el índice. Además, la reconstrucción o reorganización de índices de almacén de filas pequeños generalmente no reduce la fragmentación real.Hasta, e incluyendo, SQL Server 2014 (12.x), el motor de base de datos de SQL Server asigna espacio utilizando extensiones mixtas. Por lo tanto, las páginas de índices pequeños a veces se almacenan en extensiones mixtas. Las extensiones mixtas son compartidas por hasta ocho objetos, por lo que la fragmentación en un índice pequeño podría no reducirse después de reorganizarlo o reconstruirlo. Consulte también Consideraciones específicas para reconstruir índices de almacén de filas. Para obtener más información sobre las extensiones, consulte la Guía de Arquitectura de Páginas y extensiones.

Detectando la fragmentación de los índices del almacén de columnas

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 del almacén de columnas. Utilice esta información para calcular la fragmentación en un índice específico, en todos los índices de una tabla, en todos los índices de una base de datos o en todos los índices de todas las bases de datos.

El conjunto de resultados devuelto por sys.dm_db_column_store_row_group_physical_stats incluye las siguientes columnas:

Columna	Descripción
total_rows	Número de filas físico almacenado en la fila del grupo. Para los grupos de filas comprimidas, esto incluye las filas marcadas como eliminadas.
deleted_rows	Número de filas almacenadas físicamente en un grupo de filas comprimidas que están marcadas para su eliminación. 0 para grupos de filas que están en el almacén delta.

Utilice esta información devuelta para calcular la fragmentación del índice utilizando esta fórmula:

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

Después de conocer el grado de fragmentación del índice, utilice la siguiente tabla para determinar el mejor método para eliminar la fragmentación: REORGANIZACIÓN DEL ÍNDICE o ÍNDICE.

computed fragmentation in percent value	Applies to version	Corrective statement
> = 20%	SQL Server 2012 (11.x) and SQL Server 2014 (12.x)	ALTER INDEX REBUILD
> = 20%	Starting with SQL Server 2016 (13.x)	REORGANIZAR ALTER INDEX

Para comprobar la fragmentación de un índice de almacén de filas mediante Transact-SQL

El siguiente ejemplo encuentra el porcentaje de fragmentación promedio de todos los índices en el HumanResources.Employee tabla en la base de datos AdventureWorks2016.

SELECT a.object_id, object_name(a.object_id) AS TableName, a.index_id, name AS IndedxName, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID (N'AdventureWorks2016_EXT') , OBJECT_ID(N'HumanResources.Employee') , NULL , NULL , NULL) AS aINNER 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.

object_id TableName index_id IndexName avg_fragmentation_in_percent----------- ------------ ----------- ----------------------------------------------------- ------------------------------1557580587 Employee 1 PK_Employee_BusinessEntityID 01557580587 Employee 2 IX_Employee_OrganizationalNode 01557580587 Employee 3 IX_Employee_OrganizationalLevel_OrganizationalNode 01557580587 Employee 5 AK_Employee_LoginID 66.66666666666671557580587 Employee 6 AK_Employee_NationalIDNumber 501557580587 Employee 7 AK_Employee_rowguid 0(6 row(s) affected)

Para obtener más información, consulte sys.dm_db_index_physical_stats.

Para comprobar la fragmentación de un índice de almacén de columnas utilizando Transact-SQL

El siguiente ejemplo encuentra el porcentaje de fragmentación promedio de todos los índices en la tabla dbo.FactResellerSalesXL_CCI en la base de datos AdventureWorksDW2016.

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_idWHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'GROUP BY i.object_id, i.index_id, i.nameORDER BY object_name(i.object_id), i.name;

La instrucción anterior devuelve un conjunto de resultados similar al siguiente.

object_id TableName index_id IndexName Fragmentation----------- --------------------------- ----------- ------------------------------- ---------------114099447 FactResellerSalesXL_CCI 1 IndFactResellerSalesXL_CCI 0(1 row(s) affected)

Comprobar la fragmentación de índices mediante SQL Server Management Studio

1. En el Explorador de objetos, Expanda la base de datos que contiene la tabla en la que desea comprobar la fragmentación de un índice.
2. Expanda la carpeta Tablas.
3. Expanda la tabla en la que desea comprobar la fragmentación de un índice.
4. Expanda la carpeta Índices.
5. Haga clic con el botón derecho en el índice del que desea comprobar la fragmentación y seleccione Propiedades.
6. En Seleccionar una página, seleccione Fragmentación.

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

Valor	Descripción
Página plenitud	Indica el promedio de la plenitud de las páginas de índice, como un porcentaje. 100% significa que las páginas de índice están completamente llenas. 50% significa que, en promedio, cada página de índice está medio llena.
Fragmentación total	El porcentaje de fragmentación lógica. Indica el número de páginas de un índice que no se almacenan en orden.
Tamaño promedio de fila	El tamaño promedio de una fila a nivel de hoja.
Profundidad	El número de niveles en el índice, incluyendo el nivel de hoja.
registros Reenviados	El número de registros en un montón que han adelante punteros a otra ubicación de datos. (Este estado se produce durante una actualización, cuando no hay suficiente espacio para almacenar la nueva fila en la ubicación original.)
Filas fantasma	El número de filas que están marcadas como eliminadas pero que aún no se han eliminado. Estas filas se eliminarán mediante un hilo de limpieza, cuando el servidor no esté ocupado. Este valor no incluye las filas que se conservan debido a una transacción de aislamiento de instantáneas pendiente.
tipo de Índice	El tipo de índice. Los valores posibles son Índice agrupado, Índice no agrupado y XML primario. Las tablas también se pueden almacenar como un montón (sin índices), pero entonces no se puede abrir esta página de propiedades de índice.
Filas a nivel de hoja	El número de filas a nivel de hoja.
Tamaño máximo de fila	El tamaño máximo de fila a nivel de hoja.
tamaño Mínimo de fila	El mínimo nivel de hoja tamaño de la fila.
Páginas	El número total de páginas de datos.
ID de partición	El ID de partición del árbol b que contiene el índice.
Filas fantasma de la versión	El número de registros fantasma que se conservan debido a una transacción de aislamiento de instantáneas pendiente.

Desfragmentar índices reconstruyendo o reorganizando el índice

Desfragmentar un índice fragmentado mediante uno de los siguientes métodos:

• Reorganización de índices
• Reconstrucción de índices

Reorganizar un índice

Reorganizar un índice utiliza recursos mínimos del sistema y es una operación en línea. Esto significa que los bloqueos de tabla de bloqueo a largo plazo no se mantienen y que las consultas o actualizaciones de la tabla subyacente pueden continuar durante la transacción ALTER INDEX REORGANIZE.

• Para los índices de almacén de filas, el motor de base de datos desfragmenta el nivel de hoja de los índices agrupados y no agrupados en tablas y vistas reordenando físicamente las páginas de nivel de hoja para que coincidan con el orden lógico de los nodos de hoja (de izquierda a derecha). La reorganización también compensa las páginas de índice en función del valor del factor de relleno del índice. Para ver la configuración del factor de relleno, utilice sys.índices. Para ver ejemplos de sintaxis, consulte Ejemplos: Reorganización del almacén de filas.

• Cuando se utilizan índices de almacén de columnas, el almacén delta puede terminar con varios grupos de filas pequeños después de insertar, actualizar y eliminar datos con el tiempo. Reorganizar un índice de almacén de columnas fuerza a todos los grupos de filas al almacén de columnas y, a continuación, combina los grupos de filas en menos grupos de filas con más filas. La operación reorganizar también elimina las filas que se han eliminado del almacén de columnas. La reorganización inicialmente requiere recursos de CPU adicionales para comprimir los datos, lo que puede ralentizar el rendimiento general del sistema. Sin embargo, tan pronto como los datos se comprimen, el rendimiento de las consultas mejora. Para ver ejemplos de sintaxis, consulte Ejemplos: reorganizar el almacén de columnas.

Reconstruir un índice

Reconstruir un índice elimina y vuelve a crear el índice. Dependiendo del tipo de índice y de la versión del motor de base de datos, una operación de reconstrucción se puede realizar en línea o sin conexión. Para la sintaxis de T-SQL, consulte RECONSTRUIR ALTER INDEX

• Para los índices del almacén de filas, la reconstrucción elimina la fragmentación, recupera el espacio en disco compactando las páginas en función de la configuración de factor de relleno especificada o existente y reordena las filas de índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se eliminan y se reconstruyen en una sola transacción. Las restricciones de clave externa no tienen que eliminarse por adelantado. Cuando se reconstruyen índices con 128 extensiones o más, el Motor de base de datos aplaza las desasignaciones de páginas reales y sus bloqueos asociados hasta después de que se confirme la transacción. Para ver ejemplos de sintaxis, consulte Ejemplos: Reorganización del almacén de filas.

• Para los índices del almacén de columnas, la reconstrucción elimina la fragmentación, mueve todas las filas al almacén de columnas y recupera el espacio en disco eliminando físicamente las filas que se han eliminado lógicamente de la tabla.

  Tip

  A partir de SQL Server 2016 (13.x), la reconstrucción del índice del almacén de columnas no suele ser necesaria ya que REORGANIZE realiza lo esencial de una reconstrucción en segundo plano como una operación en línea.

  Para ver ejemplos de sintaxis, consulte Ejemplos: Reconstrucción del almacén de columnas.

Permisos

Requiere ALTER permiso en la tabla o vista. El usuario debe ser miembro de al menos uno de los siguientes roles:

• rol de base de datos db_ddladmin 1
• rol de base de datos db_owner
• rol de servidor sysadmin

el rol de base de datos db_dladmin es el menos privilegiado.

Elimine la fragmentación mediante SQL Server Management Studio

Para reorganizar o reconstruir un índice

1. En el Explorador de objetos, Expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.
2. Expanda la carpeta Tablas.
3. Expanda la tabla en la que desea reorganizar un índice.
4. Expanda la carpeta Índices.
5. Haga clic con el botón derecho en el índice que desea reorganizar y seleccione Reorganizar.
6. En el cuadro de diálogo Reorganizar índices, compruebe que el índice correcto se encuentra en la cuadrícula Índices a reorganizar y haga clic en Aceptar.
7. Active la casilla Compactar datos de columnas de objetos grandes para especificar que todas las páginas que contengan datos de objetos grandes (LOB) también se compacten.
8. haga Clic en ACEPTAR.

reorganizar todos los índices de una tabla

1. En el Explorador de Objetos, Expanda la base de datos que contiene la tabla en la que desea reorganizar los índices.
2. Expanda la carpeta Tablas.
3. Expanda la tabla en la que desea reorganizar los índices.
4. Haga clic con el botón derecho en la carpeta Índices y seleccione Reorganizar todo.
5. En el cuadro de diálogo Reorganizar índices, compruebe que los índices correctos están en los Índices que se van a reorganizar. Para eliminar un índice de la cuadrícula de Índices a reorganizar, seleccione el índice y, a continuación, pulse la tecla Eliminar.
6. Active la casilla Compactar datos de columnas de objetos grandes para especificar que todas las páginas que contengan datos de objetos grandes (LOB) también se compacten.
7. haga Clic en ACEPTAR.

Para reconstruir un índice

1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice.
2. Expanda la carpeta Tablas.
3. Expanda la tabla en la que desea reorganizar un índice.
4. Expanda la carpeta Índices.
5. Haga clic con el botón derecho en el índice que desea reorganizar y seleccione Reconstruir.
6. En el cuadro de diálogo Reconstruir índices, compruebe que el índice correcto se encuentra en la cuadrícula Índices a reconstruir y haga clic en Aceptar.
7. Active la casilla Compactar datos de columnas de objetos grandes para especificar que todas las páginas que contengan datos de objetos grandes (LOB) también se compacten.
8. haga Clic en ACEPTAR.

Eliminar la fragmentación mediante Transact-SQL

Para reorganizar un índice fragmentado

El siguiente ejemplo reorganiza el índice IX_Employee_OrganizationalLevel_OrganizationalNode en la tabla HumanResources.Employee en la base de datos AdventureWorks2016.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE;

El siguiente ejemplo reorganiza el índice de almacén de columnas IndFactResellerSalesXL_CCI en la tabla dbo.FactResellerSalesXL_CCI en la base de datos AdventureWorksDW2016.

-- 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);

reorganizar todos los índices de una tabla

El siguiente ejemplo, se reorganiza todos los índices en el HumanResources.Employee tabla en el AdventureWorks2016 base de datos.

ALTER INDEX ALL ON HumanResources.Employee REORGANIZE;

Para reconstruir un índice fragmentado

El siguiente ejemplo reconstruye un índice único en la tabla Employee en la base de datos AdventureWorks2016.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.EmployeeREBUILD;

Para reconstruir todos los índices en una tabla

El siguiente ejemplo reconstruye todos los índices asociados a la tabla en la base de datos AdventureWorks2016 utilizando la palabra clave ALL. Se especifican tres opciones.

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

Para obtener más información, consulte ALTER INDEX (Transact-SQL).

Gestión automática de índices y estadísticas

Aproveche soluciones como la desfragmentación de índice adaptativo para administrar automáticamente la desfragmentación de índices y las actualizaciones de estadísticas de una o más bases de datos. Este procedimiento elige automáticamente si desea reconstruir o reorganizar un índice de acuerdo con su nivel de fragmentación, entre otros parámetros, y actualizar las estadísticas con un umbral lineal.

Consideraciones específicas para reconstruir índices de almacén de filas

Reconstruir un índice agrupado reconstruye automáticamente cualquier índice no agrupado que haga referencia a la clave de agrupamiento, si es necesario cambiar los identificadores físicos o lógicos contenidos en los registros de índice no agrupados.

Los siguientes escenarios obligan a reconstruir automáticamente todos los índices no agrupados del almacén de filas de una tabla:

• Crear un índice agrupado en una tabla
• Eliminar un índice agrupado, lo que hace que la tabla se almacene como un montón
• Cambiar la clave de agrupamiento para incluir o excluir columnas

Los siguientes escenarios no requieren que todos los índices no agrupados del almacén de filas se reconstruyan automáticamente en una tabla:

• Reconstruir un índice agrupado único
• Reconstruir un índice agrupado no único
• Cambiar el esquema de índice, como aplicar un esquema de particiones a un índice agrupado o mover el índice agrupado a un grupo de archivos diferente

Cuando ALL se especifica con la instrucción ALTER INDEX, se reorganizan los índices relacionales, agrupados y no agrupados, y los índices XML de la tabla.

Consideraciones específicas para reconstruir un índice de almacén de columnas

Al reconstruir un índice de almacén de columnas, el motor de base de datos lee todos los datos del índice de almacén de columnas original, incluido el almacén delta. Combina los datos en nuevos grupos de filas y los comprime en el almacén de columnas. El Motor de base de datos desfragmenta el almacén de columnas eliminando físicamente las filas que se han eliminado lógicamente de la tabla. Los bytes eliminados se recuperan en el disco.

Reconstruir una partición en lugar de la tabla completa

• Reconstruir la tabla completa lleva mucho tiempo si el índice es grande y requiere suficiente espacio en disco para almacenar una copia adicional del índice durante la reconstrucción. Por lo general, solo es necesario reconstruir la partición utilizada más recientemente.
• Para tablas con particiones, no es necesario reconstruir todo el índice del almacén de columnas porque es probable que solo se produzca fragmentación en las particiones que se han modificado recientemente. Las tablas de datos y las tablas de grandes dimensiones generalmente se dividen para realizar operaciones de copia de seguridad y administración en trozos de la tabla.

Reconstruir una partición después de operaciones DML pesadas

Reconstruir una partición desfragmenta la partición y reduce el almacenamiento en disco. La reconstrucción elimina todas las filas del almacén de columnas marcadas para eliminación y mueve todos los grupos de filas del almacén delta al almacén de columnas. Puede haber varios grupos de filas en el almacén delta que tengan menos de un millón de filas.

Reconstruir una partición después de cargar datos

Reconstruir una partición después de la fecha de carga garantiza que todos los datos se almacenen en el almacén de columnas. Cuando los procesos simultáneos cargan cada uno menos de 100 000 filas en la misma partición al mismo tiempo, la partición puede terminar con varios almacenes delta. La reconstrucción mueve todas las filas del almacén delta al almacén de columnas.

Consideraciones específicas para reorganizar un índice de almacén de columnas

Al reorganizar un índice de almacén de columnas, el motor de base de datos comprime cada grupo de filas delta CERRADO en el almacén de columnas como un grupo de filas comprimido. A partir de SQL Server 2016 (13.x) y en la base de datos SQL de Azure, el comando REORGANIZE realiza las siguientes optimizaciones de desfragmentación adicionales en línea:

• Elimina físicamente las filas de un grupo de filas cuando el 10% o más de las filas se han eliminado lógicamente. Los bytes eliminados se recuperan en el medio físico. Por ejemplo, si un grupo de filas comprimido de 1 millón de filas ha eliminado 100K filas, SQL Server eliminará las filas eliminadas y recomprará el grupo de filas con 900k filas. Ahorra en el almacenamiento al eliminar las filas eliminadas.

• Combina uno o más grupos de filas comprimidos para aumentar las filas por grupo de filas hasta un máximo de 1.048.576 filas. Por ejemplo, si importa a granel 5 lotes de 102.400 filas, obtendrá 5 grupos de filas comprimidos. Si ejecuta REORGANIZAR, estos grupos de filas se fusionarán en 1 grupo de filas comprimido de tamaño 512.000 filas. Esto supone que no hubo limitaciones de tamaño de diccionario ni de memoria.

• Para grupos de filas en los que el 10% o más de las filas se han eliminado lógicamente, el motor de base de datos intenta combinar este grupo de filas con uno o más grupos de filas. Por ejemplo, rowgroup 1 se comprime con 500.000 filas y rowgroup 21 se comprime con un máximo de 1.048.576 filas. El grupo de filas 21 tiene el 60% de las filas eliminadas, lo que deja 409,830 filas. El motor de base de datos favorece la combinación de estos dos grupos de filas para comprimir un nuevo grupo de filas que tenga 909.830 filas.

Después de realizar cargas de datos, puede tener varios grupos de filas pequeños en el almacén delta. Puede usar ALTER INDEX REORGANIZE para forzar todos los grupos de filas en el almacén de columnas y, a continuación, combinar los grupos de filas en menos grupos de filas con más filas. La operación reorganizar también eliminará las filas que se hayan eliminado del almacén de columnas.

Limitaciones y restricciones

Los índices de almacén de filas con más de 128 extensiones se reconstruyen en dos fases separadas: lógica y física. En la fase lógica, las unidades de asignación existentes utilizadas por el índice se marcan para la desasignación, las filas de datos se copian y ordenan, y luego se mueven a nuevas unidades de asignación creadas para almacenar el índice reconstruido. En la fase física, las unidades de asignación previamente marcadas para la desasignación se eliminan físicamente en transacciones cortas que ocurren en segundo plano y no requieren muchos bloqueos. Para obtener más información sobre extensiones, consulte Páginas y Guía de Arquitectura de extensiones.

La instrucción ALTER INDEX REORGANIZE requiere que el archivo de datos que contiene el índice tenga espacio disponible, porque la operación solo puede asignar páginas de trabajo temporales en el mismo archivo, no en otro archivo dentro del grupo de archivos. Por lo tanto, aunque el grupo de archivos puede tener páginas libres disponibles, el usuario puede encontrar el error 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Un índice no se puede reorganizar o reconstruir si el grupo de archivos en el que se encuentra está desconectado o configurado como de solo lectura. Cuando se especifica la palabra clave ALL y uno o más índices están en un grupo de archivos sin conexión o de solo lectura, la instrucción falla.

Estadísticas:

• Cuando se crea o reconstruye un índice, las estadísticas se crean o actualizan escaneando todas las filas de la tabla. Sin embargo, a partir de SQL Server 2012 (11.x), las estadísticas no se crean ni actualizan escaneando todas las filas de la tabla cuando se crea o reconstruye un índice con particiones. En su lugar, el Optimizador de consultas utiliza el algoritmo de muestreo predeterminado para generar estas estadísticas. Para obtener estadísticas en índices con particiones escaneando todas las filas de la tabla, utilice CREAR ESTADÍSTICAS o ACTUALIZAR ESTADÍSTICAS con la cláusula FULLSCAN.

• Cuando se reorganiza un índice, las estadísticas no se actualizan.

Un índice no se puede reorganizar cuando ALLOW_PAGE_LOCKS está desactivado.

Hasta SQL Server 2017 (14.x), reconstruir un índice de almacén de columnas agrupado es una operación sin conexión. El motor de base de datos tiene que adquirir un bloqueo exclusivo en la tabla o partición mientras se produce la reconstrucción. Los datos están sin conexión y no están disponibles durante la reconstrucción, incluso cuando se usa NOLOCK, el Aislamiento de instantáneas confirmadas de lectura (RCSI) o el Aislamiento de instantáneas.A partir de SQL Server 2019 (15.x), un índice de almacén de columnas agrupado se puede reconstruir utilizando la opción ONLINE = ON.

Para una tabla de Azure Synapse Analytics (anteriormente Azure Synapse Analytics) con un índice de almacén de columnas agrupado ordenado, ALTER INDEX REBUILD reorganizará los datos mediante TempDB. Monitoree TempDB durante las operaciones de reconstrucción. Si necesita más espacio TempDB, amplíe el almacén de datos. Reduzca la escala una vez que se complete la reconstrucción del índice.

Para una tabla de Azure Synapse Analytics (anteriormente Azure Synapse Analytics) con un índice de almacén de columnas agrupado ordenado, ALTER INDEX REORGANIZE no reorganiza los datos. Para utilizar los datos use ALTER INDEX REBUILD.

Mediante la RECONSTRUCCIÓN de ÍNDICES para recuperarse de fallos de hardware

En versiones anteriores de SQL Server, a veces se podía reconstruir un índice no agrupado de almacén de filas para corregir inconsistencias causadas por fallos de hardware.A partir de SQL Server 2008, es posible que aún pueda reparar dichas inconsistencias entre el índice y el índice agrupado reconstruyendo un índice no agrupado sin conexión. Sin embargo, no puede reparar las inconsistencias de índices no agrupados reconstruyendo el índice en línea, porque el mecanismo de reconstrucción en línea utiliza el índice no agrupado existente como base para la reconstrucción y, por lo tanto, persiste la inconsistencia. Reconstruir el índice sin conexión a veces puede forzar un análisis del índice agrupado (o montón) y, por lo tanto, eliminar la inconsistencia. Para garantizar una reconstrucción a partir del índice agrupado, suelte y vuelva a crear el índice no agrupado. Al igual que con versiones anteriores, recomendamos recuperar las inconsistencias restaurando los datos afectados de una copia de seguridad; sin embargo, es posible que pueda reparar las inconsistencias del índice reconstruyendo el índice no agrupado sin conexión. Para obtener más información, consulte DBCC CHECKDB (Transact-SQL).

Consulte también

• Guía de Arquitectura y Diseño de índices de SQL Server
• Realice operaciones de índice en línea
• ALTERAR el ÍNDICE (Transact-SQL)
• Desfragmentación de índice Adaptativo
• CREAR ESTADÍSTICAS (Transact-SQL)
• ACTUALIZAR ESTADÍSTICAS (Transact-SQL)
• Rendimiento de las consultas de índices de almacén de columnas
• análisis operativo
• Índices de almacén de columnas para Almacenamiento de datos
• Índices de almacén de columnas y la directiva de combinación para grupos de filas