Directrices para las operaciones de índices en línea

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Al realizar operaciones de índice en línea se aplican las siguientes directrices:

  • Los índices agrupados deben crearse, volver a compilarse o quitarse sin conexión cuando la tabla subyacente contienen los siguientes tipos de datos de objetos grandes (LOB): image, ntexty text.
  • Los índices no clúster que no son únicos se pueden crear en línea cuando la tabla contiene tipos de datos LOB, pero ninguna de estas columnas se utiliza en la definición del índice como columna de clave o sin clave (incluida).
  • Los índices de tablas temporales locales no se pueden crear, reconstruir ni descartar en línea. Esta restricción no se aplica a los índices de tablas temporales globales.
  • Los índices se pueden reanudar desde la ubicación en la que se hayan detenido tras un error inesperado, una conmutación por error de la base de datos o un comando PAUSE. Consulte CREATE INDEX y ALTER INDEX.

Nota:

Las operaciones de índices en línea no están disponibles en todas las ediciones de Microsoft SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.

En la siguiente tabla se muestran operaciones de índice que se pueden llevar a cabo estando en línea, los índices que se excluyen de esas operaciones en línea y restricciones de índice reanudables. También se incluyen restricciones adicionales.

Operación de índice en línea Índices excluidos Otras restricciones
ALTER INDEX REBUILD Índice clúster deshabilitado o vista indizada deshabilitada

Índice XML

Índice de almacén de columnas

Índice de una tabla temporal local		 Si se especifica la palabra clave ALL, la operación puede ser errónea si la tabla contiene un índice excluido.

Se aplican restricciones adicionales para reconstruir índices deshabilitados. Para obtener más información, vea Deshabilitar índices y restricciones.
CREATE INDEX Índice XML

Índice clúster único inicial en una vista

Índice de una tabla temporal local
CREATE INDEX WITH DROP_EXISTING Índice clúster deshabilitado o vista indizada deshabilitada

Índice de una tabla temporal local

Índice XML
DROP INDEX Índice deshabilitado

Índice XML

Índice no clúster

Índice de una tabla temporal local		 No se pueden especificar varios índices en una única instrucción.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY o UNIQUE) Índice de una tabla temporal local

Índice agrupado		 Solo se permite una subcláusula cada vez. Por ejemplo, no puede agregar y quitar restricciones PRIMARY KEY o UNIQUE en la misma instrucción ALTER TABLE.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY o UNIQUE) Índice agrupado

La tabla subyacente no se puede modificar, truncar o quitar mientras se está llevando a cabo una operación de índice en línea.

La configuración de opción en línea (ON u OFF) especificada al crear o quitar un índice clúster se aplica a índices no clúster que se deben reconstruir. Por ejemplo, si el índice clúster se compila en línea mediante CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, todos los índices no clúster asociados también se vuelven a crear en línea.

Cuando crea o reconstruye un índice UNIQUE en línea, el generador de índices y una transacción de usuario simultánea pueden intentar insertar la misma clave, infringiendo su unicidad. Si una fila especificada por un usuario se inserta en el nuevo índice (destino) antes de que la fila original de la tabla de origen se mueva al nuevo índice, se producirá un error en la operación de índice en línea.

Aunque no es común, la operación de índice en línea puede causar un interbloqueo cuando interactúa con las actualizaciones de la base de datos debido a las actividades de una aplicación o de un usuario. En esos casos poco comunes, el motor de base de datos de SQL Server seleccionará el usuario o la actividad de la aplicación como sujeto de interbloqueo.

Solo puede realizar operaciones DDL de índice en línea simultáneas en la misma tabla o vista cuando crea varios índices no agrupado o reorganiza índices no agrupados. Se producirá un error en todas las operaciones de índice en línea que se realizan al mismo momento. Por ejemplo, no puede crear un índice nuevo en línea mientras reconstruye un índice en línea existente en la misma tabla.

No se puede realizar una operación en línea cuando un índice contiene una columna del tipo de objetos grandes y en la misma transacción hay operaciones de actualización delante de esta operación en línea. Para solucionar temporalmente este problema, ponga la operación en línea fuera de la transacción o colóquela antes que cualquier actualización en la transacción.

Consideraciones acerca del espacio en disco

Las operaciones de índice en línea requieren más espacio en disco necesario que las operaciones de índice sin conexión.

  • Durante las operaciones de creación y regeneración de índices, se requiere espacio adicional para el índice que se crea o regenera.
  • Además, se requiere espacio en disco para el índice de asignación temporal. Este índice temporal se utiliza en operaciones de índice en línea que crean, reconstruyen o quitan un índice clúster.
  • Para quitar un índice agrupado en línea se requiere tanto espacio como para crearlo o regenerarlo.

Para más información, consulte Disk Space Requirements for Index DDL Operations.

Consideraciones de rendimiento

Aunque las operaciones de índice en línea permiten actividades de actualización de usuario simultáneas, las operaciones de índice pueden tardan más si la actividad de actualización es muy grande. Normalmente, las operaciones de índice en línea son más lentas que las operaciones de índice sin conexión equivalentes, independientemente del nivel de actividad de actualización simultánea.

Como las estructuras de origen y de destino se mantienen durante la operación de índice en línea, el uso de recursos para insertar, actualizar y eliminar transacciones aumenta, potencialmente hasta el doble. Esto puede provocar una reducción del rendimiento y un mayor uso de los recursos, especialmente de tiempo de CPU, durante la operación de índice. Las operaciones de índice en línea se registran totalmente.

Aunque se recomiendan las operaciones en línea, se debe evaluar el entorno y los requisitos específicos. Puede ser mejor ejecutar operaciones de índice sin conexión. Al hacerlo así, los usuarios tienen acceso restringido a los datos durante la operación, pero la operación acaba más rápido y utiliza menos recursos.

En los equipos con varios procesadores que ejecutan SQL Server 2016 (13.x), puede que las instrucciones de índice usen más procesadores para realizar las operaciones de examen y ordenación asociadas a la instrucción, al igual que hacen otras consultas. Puede utilizar la opción de índice MAXDOP para controlar el número de procesadores dedicados a la operación de índice en línea. De este modo, puede equilibrar los recursos utilizados por la operación de índice con los recursos de los usuarios simultáneos. Para obtener más información, vea Configurar operaciones de índice en paralelo. Para obtener más información sobre las ediciones de SQL que admiten operaciones indexadas en paralelo, consulte Ediciones y características admitidas de SQL Server 2022.

Debido a que un bloqueo S o un bloqueo Sch-M se conservan en la fase final de la operación de índice, debe tener cuidado cuando ejecute una operación de índice en línea dentro de una transacción de usuario explícita, como el bloque BEGIN TRANSACTION ... COMMIT. De esta manera el bloqueo se conserva hasta el final de la transacción y se impide la simultaneidad de usuarios.

La regeneración de índices en línea puede aumentar la fragmentación cuando se ejecuta con MAXDOP mayor que 1 y ALLOW_PAGE_LOCKS=OFF. Para obtener más información, vea el blog How It Works: Online Index Rebuild - Can Cause Increased Fragmentation(Cómo la regeneración de índices en línea puede provocar una fragmentación mayor).

Consideraciones del registro de transacciones

Las operaciones de índice a gran escala, realizadas sin conexión o en línea, pueden generar grandes cargas de datos que pueden hacer que el registro de transacciones se llene rápidamente. Esto se debe a que las operaciones de recompilación de índices sin conexión y en línea están totalmente registradas. Para estar seguros de que la operación de índice se pueda revertir, el registro de transacciones no se puede truncar hasta que se complete la operación de índice; no obstante, se puede realizar una copia de seguridad del registro durante la operación de índice.

Por lo tanto, el registro de transacciones debe tener suficiente espacio para almacenar las transacciones de la operación de índice y cualquier transacción de usuario simultánea durante la operación de índice. Para más información, consulte Transaction Log Disk Space for Index Operations.

Consideraciones sobre índices reanudables

La opción de índice reanudable para crear y recompilar índices se aplica a SQL Server (la recompilación de índices empieza con SQL Server 2017 (14.x) y la creación de índices también se admite en SQL Server 2019 (15.x)) y base de datos de Azure SQL. Para obtener más información, vea Creación de un índice y Alter Index.

Al realizar la recompilación o la creación de índices en línea reanudables, se aplican las siguientes directrices:

  • Administración, planeamiento y ampliación de los períodos de mantenimiento del índice. Puede pausar y reiniciar una operación de recompilación o creación de índice varias veces para que se ajuste a los períodos de mantenimiento.
  • Recuperación de errores de recompilación o creación de índice (por ejemplo, las conmutaciones por error de base de datos o quedarse sin espacio en disco).
  • Cuando una operación de índice está en pausa, tanto el índice original como el recién creado requieren espacio en disco y deben actualizarse durante las operaciones de DML.
  • Permite el truncamiento de registros de transacciones durante la operación de recompilación o creación de índice.
  • La opción SORT_IN_TEMPDB=ON no se admite.
  • No se admiten índices deshabilitados.

Importante

La recompilación o creación de índice reanudable no le exige que mantenga abierta una transacción de larga ejecución, lo que permite el truncamiento del registro durante esta operación y una mejor administración del espacio de registro. Con el nuevo diseño, se consigue mantener los datos necesarios en una base de datos junto con todas las referencias necesarias para reiniciar la operación reanudable.

Por lo general, no hay ninguna diferencia de rendimiento entre la recompilación de índices en línea reanudables y no reanudables. Para crear el índice reanudable, hay una sobrecarga constante que provoca una pequeña diferencia de rendimiento entre la creación de índices reanudables y no reanudables. Esta diferencia se aprecia principalmente solo en las tablas más pequeñas.

Al actualizar un índice reanudable mientras una operación de índice está en pausa:

  • Para las cargas de trabajo principalmente de solo lectura, el efecto de rendimiento es insignificante.
  • Para las cargas de trabajo con muchas actualizaciones, puede experimentar una reducción del rendimiento (nuestras pruebas muestran una inferior al 10 %).

Por lo general, no hay ninguna diferencia en la calidad de desfragmentación entre la recompilación o la creación de índices en línea reanudables y no reanudables.

Nota:

Mientras se pausa una operación de índice en línea, se producirá un error en cualquier operación que requiera un bloqueo exclusivo de nivel de tabla en la tabla que contenga el índice en pausa. Esto suele encontrarse con las operaciones INSERT ... WITH (TABLOCK). Puede aparecer el error siguiente:

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Para resolver el error 10637, quite la sugerencia TABLOCK de la transacción o reanude la operación del índice y espere a que se complete antes de volver a intentar la transacción.

Opciones predeterminadas en línea

Para establecer opciones predeterminadas para ONLINE o RESUMABLE en un nivel de base de datos, establezca las opciones de configuración de ámbito de base de datos ELEVATE_ONLINE o ELEVATE_RESUMABLE. Con estas opciones predeterminadas puede evitar que se lleve a cabo por error una operación que desactive la tabla de base de datos. Ambas opciones harán que el motor eleve automáticamente determinadas operaciones a la ejecución ONLINE o RESUMABLE.
Puede establecer estas opciones en FAIL_UNSUPPORTED, WHEN_SUPPORTED u OFF con el comando ALTER DATABASE SCOPED CONFIGURATION. Puede establecer valores diferentes para ONLINE y RESUMABLE.

ELEVATE_ONLINE y ELEVATE_RESUMABLE solo se aplican a las instrucciones de DDL que admiten la sintaxis de ONLINE y RESUMABLE, respectivamente. Por ejemplo, si intenta crear un índice XML con ELEVATE_ONLINE=FAIL_UNSUPORTED, la operación se ejecutará sin conexión, ya que los índices XML no admiten la sintaxis ONLINE=. Las opciones solo afectan a las instrucciones de DDL que se envían sin especificar una opción ONLINE o RESUMABLE. Por ejemplo, si se envía una instrucción con ONLINE=OFF o RESUMABLE=OFF, el usuario puede invalidar un valor de configuración FAIL_UNSUPPORTED y ejecutar una instrucción sin conexión o no reanudable.

Nota:

ELEVATE_ONLINE y ELEVATE_RESUMABLE no se aplican a las operaciones de índice XML.

Contenido relacionado