Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

Con este artículo podrá decidir cuándo y cómo realizar el mantenimiento de índices. Abarca conceptos como la fragmentación de índices y la densidad de páginas, y su impacto en el rendimiento de las consultas y el consumo de recursos. Describe los métodos de mantenimiento, organización y regeneración de índices, y sugiere una estrategia de mantenimiento de índices que equilibra las posibles mejoras de rendimiento con el consumo de recursos necesario para el mantenimiento.

Nota:

Este artículo no se aplica a un grupo de SQL dedicado en Azure Synapse Analytics. Para obtener más información sobre el mantenimiento de la indexación de un grupo de SQL dedicado de Azure Synapse Analytics, consulte Índices en tablas de grupo de SQL dedicadas en Azure Synapse Analytics.

Conceptos: fragmentación de índices y densidad de página

Qué es la fragmentación de índices y cómo afecta al rendimiento:

  • En los índices de árbol B (almacén de filas), 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.

    Nota:

    La documentación de SQL Server utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, SQL Server implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los almacenes de datos en memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

  • El motor de base de datos modifica los índices de forma automática 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 de almacén de filas se dividan para dejar espacio para la inserción de nuevos valores de clave. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse (se fragmente) por la base de datos.

  • En el caso de las consultas que leen muchas páginas mediante análisis de índice completos o de intervalo, los índices muy fragmentados pueden degradar el rendimiento de las consultas porque es posible que se requieran entrada/salida adicionales para leer los datos necesarios por la consulta. En lugar de un pequeño número de solicitudes de entrada/salida grandes, la consulta requeriría un mayor número de solicitudes de entrada/salida pequeñas para leer la misma cantidad de datos.

  • Cuando el subsistema de almacenamiento proporciona un mejor rendimiento de entrada/salida secuencial que de entrada/salida aleatoria, la fragmentación del índice puede degradar el rendimiento porque se requiere más entrada/salida aleatoria para leer los índices fragmentados.

Qué es las densidad de página (también conocida como integridad de página) y cómo afecta al rendimiento:

  • Cada página de la base de datos puede contener un número variable de filas. Si las filas ocupan todo el espacio de una página, la densidad de página es del 100 %. Si una página está vacía, la densidad de página es del 0 %. Si una página con una densidad del 100 % se divide en dos páginas para dar cabida a una nueva fila, la densidad de las dos nuevas páginas es aproximadamente del 50 %.
  • Cuando la densidad de página es baja, se requieren más páginas para almacenar la misma cantidad de datos. Esto significa que se necesita más entrada/salida para leer y escribir estos datos, y más memoria para almacenar en caché estos datos. Cuando la memoria está limitada, se almacenarán en caché menos páginas requeridas por una consulta, lo que provocará incluso más E/S de disco. Por lo tanto, una baja densidad de página afecta negativamente al rendimiento.
  • Cuando el motor de base de datos agrega filas a una página, no rellenará completamente la página si el factor relleno del índice está establecido en un valor distinto de 100 (o de 0, que es equivalente en este contexto). Esto provoca una densidad de página inferior y, de forma similar, agrega sobrecarga de entrada/salida y afecta negativamente al rendimiento.
  • Una baja densidad de página puede aumentar el número de niveles intermedios de árbol B. Esto aumenta moderadamente la CPU y el costo de entrada/salida de encontrar páginas de nivel hoja en análisis de índice y búsquedas.
  • Cuando el optimizador de consultas genera un plan de consulta, tiene en cuenta el costo de entrada/salida necesario para leer los datos requeridos por la consulta. Con una densidad de página baja, hay más páginas que leer, por lo que el costo de la entrada/salida es mayor. Esto puede afectar a la elección del plan de consulta. Por ejemplo, a medida que la densidad de página disminuye con el tiempo debido a divisiones de página, el optimizador puede generar un plan diferente para la misma consulta, con un perfil de rendimiento y consumo de recursos diferente.

Sugerencia

En muchas cargas de trabajo, el aumento de la densidad de página tiene un mayor impacto positivo en el rendimiento que la reducción de la fragmentación.

Para evitar reducir innecesariamente la densidad de página, Microsoft no recomienda establecer el factor de relleno en valores distintos de 100 o de 0, excepto en algunos casos para los índices que experimentan un gran número de divisiones de página, por ejemplo, índices modificados con frecuencia con columnas iniciales que contienen valores GUID no secuenciales.

Fragmentación de índice de medida y densidad de página

Tanto la fragmentación como la densidad de página se encuentran entre los factores que se deben tener en cuenta a la hora de decidir si se debe realizar el mantenimiento de índices y qué método de mantenimiento se debe usar.

La fragmentación se define de forma diferente para los índices de almacén de filas y de almacén de columnas. Para los índices de almacén de filas, sys.dm_db_index_physical_stats() le permite determinar la fragmentación y la densidad de página de un índice específico, de todos los índices de una tabla o de una vista indexada, de todos los índices de una base de datos o de todos los índices de todas las bases de datos. Para los índices con particiones, sys.dm_db_index_physical_stats() también proporciona información de la fragmentación para cada partición.

El conjunto de resultados siguiente muestra las columnas devueltas por sys.dm_db_index_physical_stats:

Column Descripción
avg_fragmentation_in_percent Fragmentación lógica (páginas de un índice que están correctamente ordenadas).
avg_page_space_used_in_percent Densidad media de la página.

En el caso de los grupos de filas comprimidos en índices de almacén de columnas, la fragmentación se define como la proporción de filas eliminadas con el total de filas, expresada como porcentaje. sys.dm_db_column_store_row_group_physical_stats permite determinar el número de filas totales y eliminadas por grupo de filas en un índice específico, todos los índices de una tabla o todos los índices de una base de datos.

El conjunto de resultados siguiente muestra las columnas devueltas por sys.dm_db_column_store_row_group_physical_stats:

Column Descripción
total_rows Número de filas almacenadas físicamente en el grupo de filas. En el caso de los grupos de filas comprimidos, incluye las filas marcadas como eliminadas.
deleted_rows Número de filas almacenadas físicamente en un grupo de filas comprimido que se han marcado para su eliminación. Es 0 en el caso de los grupos de filas que se encuentran en el almacén delta.

La fragmentación de grupos de filas comprimidos en un índice de almacén de columnas se puede calcular mediante esta fórmula:

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

Sugerencia

Para los índices de almacén de filas y de almacén de columnas, es especialmente importante revisar la fragmentación de índices o montones y la densidad de página después de eliminar o actualizar un gran número de filas. En el caso de los montones, si hay actualizaciones frecuentes, también puede ser necesario revisar periódicamente la fragmentación para evitar la proliferación de registros de reenvío. Para más información sobre los montones, vea Montones (tablas sin índices agrupados).

Consulte Ejemplos de consultas de ejemplo para determinar la fragmentación y la densidad de páginas.

Métodos de mantenimiento de índices: reorganización y regeneración

Puede reducir la fragmentación de índices y aumentar la densidad de página mediante uno de los métodos siguientes:

  • Reorganización de un índice
  • Volver a generar un índice

Nota:

Para los índices con particiones, puede usar cualquiera de los métodos siguientes en todas las particiones o en una sola partición de un índice.

Reorganización de un índice

La reorganización de un índice consume menos recursos que volver a generarlo. Por ese motivo, debe ser el método de mantenimiento de índice preferido, a menos que haya una razón específica para usar la regeneración de índices. La reorganización siempre es una operación en línea. Esto significa que los bloqueos de nivel de objeto a largo plazo no se mantienen y que las consultas o actualizaciones en la tabla subyacente pueden continuar durante la operación ALTER INDEX ... REORGANIZE.

  • Para los índices de almacén de filas, el motor de base de datos 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). La reorganización también compacta las páginas de índice para que la densidad de página sea igual al factor de relleno del índice. Para ver el valor de factor de relleno, use sys.indexes. Para obtener ejemplos de sintaxis, consulte Ejemplos: Reorganización del almacén de filas.
  • 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. La reorganización de un índice de almacén de columnas fuerza los grupos de filas de almacén delta en grupos de filas comprimidos en el almacén de columnas y combina grupos de filas comprimidos más pequeños en grupos de filas más grandes. La operación de reorganización también quita físicamente las filas que se hayan marcado como eliminadas del almacén de columnas. La reorganización de un índice de almacén de columnas puede requerir recursos de CPU adicionales para comprimir los datos, lo que puede ralentizar el rendimiento general del sistema mientras se ejecuta la operación. Sin embargo, una vez comprimidos los datos, el rendimiento de las consultas mejora. Para obtener ejemplos de sintaxis, consulte Ejemplos: regeneración de almacén de columnas.

Nota:

A partir de SQL Server 2019 (15.x), Azure SQL Database y Azure SQL Managed Instance, el motor de tuplas cuenta con la ayuda de una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas delta abiertos más pequeños que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina los grupos de filas comprimidos desde donde se ha eliminado un gran número de filas. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo. En la mayoría de los casos, esto elimina la necesidad de emitir comandos ALTER INDEX ... REORGANIZE.

Sugerencia

Si cancela una operación de reorganización, o si se interrumpe de otro modo, el progreso realizado hasta ese momento se conserva en la base de datos. Para reorganizar índices grandes, la operación se puede iniciar y detener varias veces hasta que se complete.

Volver a generar un índice

El proceso de volver a crear un índice quita y vuelve a crear el índice. En función del tipo de índice y de la versión del motor de base de datos, una operación de regeneración puede realizarse en línea o sin conexión. Normalmente, una regeneración de índices sin conexión tarda menos tiempo que una regeneración en línea, pero contiene bloqueos de nivel de objeto mientras dura la operación de regeneración, lo que impide que las consultas accedan a la tabla o vista.

Una regeneración de índices en línea no requiere bloqueos de nivel de objeto hasta el final de la operación, cuando se debe mantener un bloqueo durante un breve período de tiempo para completar la regeneración. Dependiendo de la versión del motor de base de datos, se puede iniciar una regeneración de índices en línea como una operación reanudable. Se puede pausar una regeneración de índices que se puede reanudar, manteniendo el progreso realizado hasta ese momento. Una operación de regeneración se puede reanudar después de haberse pausado o interrumpido, o anularse si no es necesario completar la regeneración.

Para ver la sintaxis de T-SQL, consulte ALTER INDEX REBUILD. Para más información sobre las regeneraciones de índices en línea, consulte Realizar operaciones de índice en línea.

Nota:

Mientras se vuelve a generar un índice en línea, cada modificación de los datos de las columnas indexadas debe actualizar una copia adicional del índice. Esto puede provocar una degradación del rendimiento menor de las instrucciones de modificación de datos durante la regeneración en línea.

Si se pausa una operación de índice que se puede reanudar en línea, este impacto en el rendimiento persiste hasta que la operación que se puede reanudar se complete o se anule. Si no piensa completar una operación de índice que se puede reanudar, anúlela en lugar de pausarla.

Sugerencia

En función de los recursos disponibles y los patrones de carga de trabajo, la especificación de un valor superior al valor MAXDOP predeterminado en la instrucción ALTER INDEX REBUILD puede reducir la duración de la regeneración a costa de un mayor uso de la CPU.

  • En el caso de los índices de almacén de filas, al volver a generar se quita la fragmentación en todos los niveles del índice y se compactan las páginas en función del factor de relleno especificado o actual. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única operación. Cuando se vuelven a generar índices con 128 extensiones o más, el motor de base de datos fracciona las desasignaciones de página y sus bloqueos asociados hasta después de que se complete la regeneración. Para obtener ejemplos de sintaxis, consulte Ejemplos: Regeneración del almacén de filas.

  • En el caso de los índices de almacén de columnas, al volver a generar se quita la fragmentación, se mueven las filas del almacén delta al almacén de columnas y se eliminan físicamente las filas marcadas para su eliminación. Para obtener ejemplos de sintaxis, consulte Ejemplos: Regeneración del almacén de columnas.

    Sugerencia

    A partir de SQL Server 2016 (13.x), no es necesario normalmente volver a generar el índice de almacén de columnas, ya que REORGANIZE realiza las operaciones básicas de una regeneración como una operación en línea.

Use la regeneración de índices para recuperar datos dañados

En versiones anteriores de SQL Server, a veces se podía volver a generar un índice no agrupado de almacén de filas para corregir incoherencias provocadas por datos dañados en el índice.

A partir de SQL Server 2008 (10.0.x), aún es posible reparar estas incoherencias en el índice no agrupado al volver a generar un índice no agrupado sin conexión. Sin embargo, 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. Volver a generar el índice sin conexión a veces puede forzar un examen del índice agrupado (o montón) y, por tanto, reemplazar los datos incoherentes del índice no agrupado por los datos del índice agrupado o montón.

Para asegurarse de que el índice agrupado o el montón se usan como origen de datos, coloque y vuelva a crear el índice no agrupado en lugar de volver a generarlo. 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 agrupado sin conexión. Para obtener más información, vea DBCC CHECKDB (Transact-SQL).

Administración automática de índice y estadísticas

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

Consideraciones específicas para volver a generar índices de almacén de filas

Los siguientes escenarios hacen que se vuelvan a generar automáticamente todos los índices no agrupados del almacén de filas de una tabla:

  • Creación de un índice agrupado en una tabla, incluida la recreación del índice agrupado con una clave diferente mediante CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Anulación de un índice agrupado, lo que hace que la tabla se almacene como un montón

Los escenarios siguientes no vuelven a generar automáticamente todos los índices no agrupados del almacén de filas en la misma tabla:

  • Regeneración de un índice agrupado
  • Cambio del almacenamiento del índice agrupado, como aplicar un esquema de partición o traslado del índice agrupado a un grupo de archivos diferente

Importante

No es posible volver a organizar o volver a generar un índice si el grupo de archivos en el que se encuentra está sin conexión o es de solo lectura. 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.

Mientras se vuelve a generar un índice, el medio físico debe tener espacio suficiente para almacenar dos copias del índice. Cuando finaliza la recompilación, el motor de base de datos elimina el índice original.

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

Con frecuencia, cuando se vuelven a generar o reorganizan índices pequeños de almacén de filas no se reduce la fragmentación. Hasta, e incluso, SQL Server 2014 (12.x), el motor de base de datos de SQL Server asigna espacio mediante extensiones mixtas. Por lo tanto, las páginas de índices pequeños a veces se almacenan en extensiones mixtas, lo que hace que estos índices se fragmenten implícitamente. 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.

Consideraciones específicas para volver a generar un índice de almacén de columnas

Cuando se vuelve a generar 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 datos en nuevos grupos de filas y comprime todos los grupos de filas en el almacén de columnas. El motor de base de datos desfragmenta el almacén de columnas mediante la eliminación física de las filas que se han marcado como eliminadas.

Nota:

A partir de SQL Server 2019 (15.x), el motor de tuplas cuenta con la ayuda de una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas de almacenamiento delta abiertos que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina los grupos de filas comprimidos desde donde se ha eliminado un gran número de filas. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo. 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.

Recompilación de una partición en lugar de la tabla completa

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.

En el caso de las tablas con particiones, no es necesario volver a generar todo el índice de almacén de columnas si la fragmentación solo está presente en algunas particiones, por ejemplo en particiones donde las instrucciones UPDATE, DELETE o MERGE han afectado a un gran número de filas.

La regeneración de una partición después de cargar o modificar los datos garantiza que todos los datos se almacenen en grupos de filas comprimidos en el almacén de columnas. Cuando el proceso de carga de datos inserta datos en una partición mediante lotes menores de 102 400 filas, la partición puede terminar con varios grupos de filas abiertos en el almacén delta. La regeneración mueve todas las filas del almacén delta a grupos de filas comprimidos del 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 cerrado del almacén delta en el almacén de columnas como un grupo de filas comprimido. A partir de SQL Server 2016 (13.x) y en Azure SQL Database, el comando REORGANIZE realiza estas otras optimizaciones de desfragmentación en línea:

  • Quita físicamente las filas de un grupo de filas cuando el 10 % o más de las filas se hayan eliminado lógicamente. Por ejemplo, si en un grupo de filas comprimido que contiene 1 millón de filas se eliminan 100 000 filas, el motor de base de datos quita las filas eliminadas y vuelve a comprimir el grupo de filas con 900 000 filas, lo que reduce la superficie de almacenamiento.
  • 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. Por ejemplo, si inserta de forma masiva cinco lotes de 102 400 filas cada uno, se obtienen cinco grupos de filas comprimidos. Si ejecuta REORGANIZE, estos grupos de filas se combinarán en un grupo de filas comprimido con 512 000 filas. Se supone que no había ninguna limitación de memoria o de tamaño de diccionario.
  • El motor de base de datos intenta combinar grupos de filas en los que el 10 % o más de las filas se han marcado como eliminadas con otros grupos de filas. Por ejemplo, el grupo de filas 1 está comprimido y tiene 500 000 filas, mientras que el grupo de filas 21 está comprimido y tiene 1 048 576 filas. El grupo de filas 21 tiene el 60 % de las filas marcadas como 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 uno nuevo que tenga 909 830 filas.

Después de realizar cargas de datos, puede haber varios grupos de filas pequeños en el almacén delta. Puede usar ALTER INDEX REORGANIZE para forzar estos grupos de filas en el almacén de columnas y, a continuación, combinar grupos de filas comprimidos más pequeños en grupos de filas comprimidos más grandes. La operación de reorganización también quitará las filas que se hayan marcado como eliminadas del almacén de columnas.

Nota:

La reorganización de un índice de almacén de columnas mediante Management Studio combinará grupos de filas comprimidas, pero no obligará a que todos los grupos de filas se compriman en el almacén de columnas. Se comprimirán los grupos de filas cerrados, mientras que los grupos de filas abiertos no se comprimirán en el almacén de columnas. Para formar la compresión de todos los grupos de filas, use el ejemplo de Transact-SQL que incluye COMPRESS_ALL_ROW_GROUPS = ON.

Aspectos a tener en cuenta antes de realizar el mantenimiento de índices

El mantenimiento de índices, realizado mediante la reorganización o regeneración de un índice, consume muchos recursos. Provoca un aumento significativo en el uso de CPU, la memoria usada y la entrada/salida de almacenamiento. Sin embargo, dependiendo de la carga de trabajo de la base de datos y de otros factores, las ventajas que aporta van desde las de vital importancia hasta las minúsculas.

Para evitar el uso innecesario de recursos que puede ser perjudicial para las cargas de trabajo de consulta, Microsoft no recomienda realizar el mantenimiento de índices de forma indiscriminada. En su lugar, las ventajas de rendimiento del mantenimiento de índices deben determinarse empíricamente para cada carga de trabajo mediante la estrategia recomendada y sopesar los costos de recursos y el impacto en la carga de trabajo necesarios para lograr estas ventajas.

La probabilidad de ver las ventajas de rendimiento de reorganizar o volver a generar un índice es mayor cuando el índice está muy fragmentado o cuando su densidad de página es baja. Sin embargo, estos no son los únicos aspectos que se deben tener en cuenta. Factores como los patrones de consulta (procesamiento de transacciones frente a análisis e informes), el comportamiento del subsistema de almacenamiento, la memoria disponible y las mejoras del motor de base de datos a lo largo del tiempo desempeñan un papel fundamental.

Importante

Las decisiones de mantenimiento de índices deben tomarse después de considerar varios factores en el contexto específico de cada carga de trabajo, incluido el costo de mantenimiento de los recursos. No deben basarse solo en umbrales fijos de fragmentación o densidad de página.

Un efecto secundario positivo de la regeneración de índices

A menudo, los clientes observan mejoras de rendimiento después de la regeneración de índices. Sin embargo, en muchos casos estas mejoras no están relacionadas con la reducción de la fragmentación o el aumento de la densidad de página.

Una regeneración de índices tiene una ventaja importante: actualiza las estadísticas de las columnas clave del índice mediante el examen de todas las filas del índice. Esto equivale a ejecutar UPDATE STATISTICS ... WITH FULLSCAN, lo que hace que las estadísticas se actualicen y, a veces, mejoren su calidad en comparación con la actualización de las estadísticas muestreadas predeterminadas. Cuando se actualizan las estadísticas, se vuelven a generar los planes de consulta que hacen referencia a ellas. Si el plan anterior para una consulta no era óptimo debido a estadísticas obsoletas, una proporción de muestreo de estadísticas insuficiente o por otros motivos, el plan que se vuelve a generar es a menudo mejor.

A menudo, los clientes atribuyen incorrectamente esta mejora a la propia regeneración del índice, lo que la convierte en el resultado de una fragmentación reducida y una mayor densidad de página. En realidad, a menudo, la misma ventaja se puede lograr a un costo de recursos mucho más económico mediante la actualización de estadísticas en lugar de volver a generar los índices.

Sugerencia

El costo de recursos de actualizar las estadísticas es menor en comparación con la regeneración de índices y la operación suele completarse en minutos en lugar de horas que pueden ser necesarias para las regeneraciones de índices.

Estrategia de mantenimiento de índices

Microsoft recomienda que los clientes consideren y adopten la siguiente estrategia de mantenimiento de índices:

  • No suponga que el mantenimiento de índices siempre va a mejorar notablemente la carga de trabajo.
  • Mida el impacto específico de reorganizar o volver a generar los índices en el rendimiento de las consultas de la carga de trabajo. \- Almacén de consultas es una buena manera de medir el rendimiento "antes del mantenimiento" y "después del mantenimiento" mediante la técnica de prueba A/B.
  • Si observa que la regeneración de índices mejora el rendimiento, intente reemplazarla por la actualización de las estadísticas. Esto puede dar lugar a una mejora similar. En ese caso, puede que no sea necesario volver a generar los índices con tanta frecuencia, o en absoluto, y en su lugar puede realizar actualizaciones periódicas de las estadísticas. Para algunas estadísticas, es posible que tenga que aumentar la proporción de muestreo mediante las cláusulas WITH SAMPLE ... PERCENT o WITH FULLSCAN (esto no es habitual).
  • Supervise la fragmentación del índice y la densidad de páginas a lo largo del tiempo para ver si existe una correlación entre la tendencia al alza o a la baja de estos valores y el rendimiento de las consultas. Si una mayor fragmentación o una menor densidad de página degradan el rendimiento de forma inaceptable, reorganice o reconstruya los índices. A menudo basta con reorganizar o volver a generar índices específicos usados por las consultas con un rendimiento degradado. Esto evita un mayor costo de recursos al mantener cada índice de la base de datos.
  • Establecer una correlación entre la fragmentación y densidad de página y el rendimiento también permite determinar la frecuencia del mantenimiento de los índices. No suponga que el mantenimiento debe realizarse según una programación fija. Una mejor estrategia es controlar la fragmentación y la densidad de página, y ejecutar el mantenimiento de los índices según sea necesario antes de que el rendimiento se degrade de forma inaceptable.
  • Si ha determinado que el mantenimiento de índices es necesario y su costo de recursos es aceptable, realice el mantenimiento durante los tiempos de uso de recursos bajos, si los hay, teniendo en cuenta que los patrones de uso de recursos pueden cambiar con el tiempo.

Mantenimiento de índices en Azure SQL Database y Azure SQL Managed Instance

Además de las consideraciones y estrategias anteriores, en Azure SQL Database y Azure SQL Managed Instance es especialmente importante tener en cuenta los costos y las ventajas del mantenimiento de índices. Los clientes solo deben realizarlo cuando haya una necesidad demostrada y teniendo en cuenta los siguientes puntos.

  • Azure SQL Database y Azure SQL Managed Instance implementan la gobernanza de recursos para establecer límites en el consumo de CPU, la memoria y la E/S según el plan de tarifa aprovisionado. Estos límites se aplican a todas las cargas de trabajo de usuario, incluido el mantenimiento de índices. Si el consumo acumulativo de recursos por todas las cargas de trabajo se aproxima a los límites de recursos, la operación de regeneración o reorganización puede degradar el rendimiento de otras cargas de trabajo debido a la contención de recursos. Por ejemplo, las cargas masivas de datos pueden ralentizarse porque la entrada/salida del registro de transacciones está al 100 % debido a una regeneración simultánea del índice. En Azure SQL Managed Instance, este impacto se puede reducir mediante la ejecución del mantenimiento de índices en un grupo de cargas de trabajo Resource Governor independiente, con asignación de recursos restringida, a costa de ampliar la duración del mantenimiento del índice.
  • Para ahorrar costos, los clientes suelen aprovisionar bases de datos, grupos elásticos e instancias administradas con una capacidad de aumento de recursos mínima. El plan de tarifa se elige para ser suficiente para las cargas de trabajo de la aplicación. Para dar cabida a un aumento significativo del uso de recursos debido al mantenimiento de índices sin degradar el rendimiento de la aplicación, es posible que los clientes tengan que aprovisionar más recursos y aumentar los costos, sin necesariamente mejorar el rendimiento de la aplicación.
  • En los grupos elásticos, los recursos se comparten entre todas las bases de datos de un grupo. Incluso si una base de datos determinada está inactiva, el mantenimiento de índices en esa base de datos puede afectar a las cargas de trabajo de la aplicación que se ejecutan simultáneamente en otras bases de datos del mismo grupo. Para más información, consulte Administración de recursos en grupos elásticos densos.
  • Para la mayoría de los tipos de almacenamiento usados en Azure SQL Database y Azure SQL Managed Instance, no hay ninguna diferencia en el rendimiento entre la E/S secuencial y la aleatoria. Esto reduce el impacto de la fragmentación de índices en el rendimiento de las consultas.
  • Al usar réplicas de escalado horizontal de lectura o de replicación geográfica, la latencia de datos en las réplicas suele aumentar mientras se realiza el mantenimiento de índices en la réplica principal. Si una réplica geográfica se aprovisiona con recursos insuficientes para sostener un aumento en la generación de registros de transacciones causado por el mantenimiento de índices, puede quedar muy por detrás de la principal, haciendo que el sistema tenga que volver a alimentarlo. Esto hace que la réplica no esté disponible hasta que se complete la nueva propagación. Además, en los niveles de servicio Premium y Crítico para la empresa, las réplicas usadas para alta disponibilidad pueden quedar muy por detrás de la principal durante el mantenimiento del índice. Si se requiere una conmutación por error durante el mantenimiento del índice, o poco después de él, puede tardar más de lo esperado.
  • Si se ejecuta una regeneración de índices en la réplica principal y una consulta de larga duración se ejecuta en una réplica legible al mismo tiempo, la consulta puede finalizar automáticamente para evitar que se bloquee el subproceso de la fase de puesta al día en la réplica.

Hay escenarios específicos pero poco habituales en los que se puede necesitar un mantenimiento de índice único o periódico en Azure SQL Database y Azure SQL Managed Instance:

  • El mantenimiento del índice puede ser necesario para aumentar la densidad de páginas y reducir el espacio utilizado en la base de datos, y así mantenerse dentro del límite de tamaño del nivel de precios. Esto evita tener que escalar verticalmente a un plan de tarifa superior con un límite de tamaño mayor.
  • Si es necesario reducir los archivos, la regeneración o reorganización de índices antes de reducir los archivos aumentará la densidad de página. Esto hace que la operación de reducción sea más rápida, ya que tendrá que mover menos páginas. Para más información, consulta:

Sugerencia

Si ha determinado que el mantenimiento de índices es necesario para las cargas de trabajo Azure SQL Database y Azure SQL Managed Instance, debe reorganizar los índices o usar la regeneración de índices en línea. Esto permite que las cargas de trabajo de consulta accedan a las tablas mientras se vuelven a generar los índices.

Además, hacer que la operación se reanude le permite evitar reiniciarla desde el principio si se interrumpe por una conmutación por error de la base de datos planeada o no planeada. El uso de operaciones de índice que se pueden reanudar es especialmente importante cuando los índices son grandes.

Sugerencia

Las operaciones de índice sin conexión normalmente se completan más rápido que las operaciones en línea. Se deben usar cuando las consultas no tengan acceso a las tablas durante la operación, por ejemplo, después de cargar datos en tablas de almacenamiento provisional como parte de un proceso ETL secuencial.

Limitaciones y restricciones

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. 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. 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. Para más información sobre las unidades de asignación, consulte Guía de arquitectura de páginas y extensiones.

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 mismo grupo de archivos. Aunque el grupo de archivos pueda tener espacio disponible, el usuario todavía puede encontrar el error 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup durante la operación de reorganización si un archivo de datos se queda sin espacio.

No es posible reorganizar un índice cuando ALLOW_PAGE_LOCKS está establecido en OFF.

Hasta SQL Server 2017 (14.x), la regeneración de 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 la partición mientras se produce la regeneración. Los datos están sin conexión y no se encuentran disponibles incluso si se usa NOLOCK, el aislamiento de instantánea de lectura confirmada (RCSI) o el aislamiento de instantánea. A partir de SQL Server 2019 (15.x), se puede volver a generar un índice de almacén de columnas agrupado mediante la opción ONLINE = ON.

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. Si se hace, se puede degradar el rendimiento o consumir excesiva memoria durante estas operaciones. Microsoft recomienda usar solo índices alineados cuando el número de particiones sea superior a 1000.

Limitaciones de estadísticas

  • Cuando se crea un índice o se vuelve a generar, las estadísticas se crean o actualizan mediante el examen de todas las filas de la tabla, que es equivalente a usar la cláusula FULLSCAN en CREATE STATISTICS o UPDATE STATISTICS. Sin embargo, a partir de SQL Server 2012 (11.x), cuando se crea o se vuelve a generar un índice con particiones, las estadísticas no se crean ni actualizan al examinar todas las filas de la tabla. Por el contrario, se utiliza la proporción de muestreo predeterminada. Para crear o actualizar 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.
  • De forma similar, cuando se puede reanudar la operación de creación o regeneración de índices, las estadísticas se crean o actualizan con la proporción de muestreo predeterminada. Si las estadísticas se crearon o actualizaron por última vez con la cláusula PERSIST_SAMPLE_PERCENT establecida en ON, las operaciones de índice que se pueden reanudar usan la proporción de muestreo persistente para crear o actualizar estadísticas.
  • Cuando se reorganiza un índice, las estadísticas no se actualizan.

Ejemplos

Comprobar la fragmentación y la densidad de página de un índice de almacén de filas con Transact-SQL

En el ejemplo siguiente se determina la fragmentación media y la densidad de página de todos los índices de almacén de filas de la base de datos actual. Usa el modo SAMPLED para devolver resultados que se pueden usar rápidamente. Para obtener resultados más precisos, use el modo DETAILED. Esto requiere examinar todas las páginas de índice y puede tardar mucho tiempo.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

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

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

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

Comprobar la fragmentación de un índice de almacén de columnas con Transact-SQL

En el ejemplo siguiente se determina la fragmentación media de todos los índices de almacén de columnas con grupos de filas comprimidos en la base de datos actual.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

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

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Mantenimiento de índices mediante SQL Server Management Studio

Reorganizar o volver a generar 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 quiera 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 que se van a reorganizar y haga clic en Aceptar.
  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).
  8. Seleccione 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 adecuados están en Índices que se van a reorganizar. Para quitar un índice de la cuadrícula Índices que se van a reorganizar , seleccione el índice y, a continuación, presione la tecla SUPR.
  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).
  7. Seleccione Aceptar.

Mantenimiento de índices mediante Transact-SQL

Nota:

Para obtener más ejemplos sobre el uso de Transact-SQL para volver a generar o reorganizar los índices, consulte Ejemplos de ALTER INDEX: Índices de almacén de filas y Ejemplos de ALTER INDEX: Índices de almacén de columnas.

Reorganización de un índice

En el siguiente ejemplo se reorganiza el índice IX_Employee_OrganizationalLevel_OrganizationalNode en la tabla HumanResources.Employee de la base de datos AdventureWorks2022.

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 AdventureWorksDW2022. Este comando fuerza todos los grupos de filas con estado CLOSED y OPEN hacia el almacén de columnas.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Reorganizar todos los índices de una tabla

En el siguiente ejemplo se reorganizan todos los índices en la tabla HumanResources.Employee de la base de datos AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Volver a generar un índice

En el siguiente ejemplo se regenera un único índice en la tabla Employee de la base de datos AdventureWorks2022.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Volver a generar todos los índices de una tabla

En el ejemplo siguiente se vuelven a generar todos los índices asociados con la tabla de la base de datos de AdventureWorks2022 mediante la palabra clave ALL. Se especifican tres opciones.

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

Para más información, consulte ALTER INDEX.

Pasos siguientes