Directrices para operaciones de índices en líneaGuidelines for online index operations

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Al realizar operaciones de índice en línea se aplican las siguientes directrices:When you perform online index operations, the following guidelines apply:

  • 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.Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and 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).Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.
  • Los índices de tablas temporales locales no se pueden crear, reconstruir o quitar en línea.Indexes on local temp tables cannot be created, rebuilt, or dropped online. Esta restricción no se aplica a los índices de tablas temporales globales.This restriction does not apply to indexes on global temp tables.
  • 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.Indexes can be resumed from where it stopped after an unexpected failure, database failover, or a PAUSE command. Consulte CREATE INDEX y ALTER INDEX.See Create Index and Alter Index.

Nota

Las operaciones de índices en línea no están disponibles en todas las ediciones de MicrosoftMicrosoft SQL ServerSQL Server.Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL ServerSQL Server, vea Features supported by editions (Características compatibles con las ediciones de SQL Server 2016).For a list of features that are supported by the editions of SQL ServerSQL Server, see Features supported by editions.

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.The following table shows the index operations that can be performed online, the indexes that are excluded from these online operations, and resumable index restrictions. También se incluyen restricciones adicionales.Additional restrictions are also included.

Operación de índice en líneaOnline index operation Índices excluidosExcluded indexes Otras restriccionesOther restrictions
ALTER INDEX REBUILDALTER INDEX REBUILD Índice clúster deshabilitado o vista indizada deshabilitadaDisabled clustered index or disabled indexed view

Índice XMLXML index

Índice de almacén de columnasColumnstore index

Índice de una tabla temporal localIndex on a local temp table
Si se especifica la palabra clave ALL la operación puede ser errónea si la tabla contiene un índice excluido.Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.

Se aplican restricciones adicionales para reconstruir índices deshabilitados.Additional restrictions on rebuilding disabled indexes apply. Para obtener más información, vea Deshabilitar índices y restricciones.For more information, see Disable Indexes and Constraints.
CREATE INDEXCREATE INDEX Índice XMLXML index

Índice clúster único inicial en una vistaInitial unique clustered index on a view

Índice de una tabla temporal localIndex on a local temp table
CREATE INDEX WITH DROP_EXISTINGCREATE INDEX WITH DROP_EXISTING Índice clúster deshabilitado o vista indizada deshabilitadaDisabled clustered index or disabled indexed view

Índice de una tabla temporal localIndex on a local temp table

Índice XMLXML index
DROP INDEXDROP INDEX Índice deshabilitadoDisabled index

Índice XMLXML index

Índice no clústerNonclustered index

Índice de una tabla temporal localIndex on a local temp table
No se pueden especificar varios índices en una única instrucción.Multiple indexes cannot be specified within a single statement.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY o UNIQUE)ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE) Índice de una tabla temporal localIndex on a local temp table

Índice clústerClustered index
Solo se permite una subcláusula cada vez.Only one subclause is allowed at a time. Por ejemplo, no puede agregar y quitar restricciones PRIMARY KEY o UNIQUE en la misma instrucción ALTER TABLE.For example, you cannot add and drop PRIMARY KEY or UNIQUE constraints in the same ALTER TABLE statement.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY o UNIQUE)ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) Índice clústerClustered index

La tabla subyacente no se puede modificar, truncar o quitar mientras se está llevando a cabo una operación de índice en línea.The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

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.The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. Por ejemplo, si el índice clúster se genera en línea utilizando CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, todos los índices no clúster asociados se vuelven a crear en línea también.For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

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.When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction may try to insert the same key, therefore violating uniqueness. 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.If a row entered by a user is inserted into the new index (target) before the original row from the source table is moved to the new index, the online index operation will fail.

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.Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. En esos casos poco comunes, el Motor de base de datos de SQL ServerSQL Server Database Engine seleccionará el usuario o la actividad de la aplicación como sujeto de interbloqueo.In these rare cases, the Motor de base de datos de SQL ServerSQL Server Database Engine will select the user or application activity as a deadlock victim.

Solo puede realizar operaciones DDL de índice en línea simultáneas en la misma tabla o vista cuando crea varios índices no clúster o reorganiza índices no clúster.You can perform concurrent online index DDL operations on the same table or view only when you are creating multiple new nonclustered indexes, or reorganizing nonclustered indexes. Se producirá un error en todas las operaciones de índice en línea que se realizan al mismo momento.All other online index operations performed at the same time fail. Por ejemplo, no puede crear un índice en línea mientras reconstruye un índice en línea existente en la misma tabla.For example, you cannot create a new index online while rebuilding an existing index online on the same table.

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.An online operation cannot be performed when an index contains a column of the large object type, and in the same transaction there are update operations before this online operation. 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.To work around this issue, place the online operation outside the transaction or place it before any updates in the transaction.

Consideraciones acerca del espacio en discoDisk space considerations

Las operaciones de índice en línea requieren más espacio en disco necesario que las operaciones de índice sin conexión.Online index operations require more disk space requirements than offline index operations.

  • Durante las operaciones de creación y regeneración de índices, se requiere espacio adicional para el índice que se crea o regenera.During index creation and index rebuild operations, additional space is required for the index being built (or rebuilt).
  • Además, se requiere espacio en disco para el índice de asignación temporal.In addition, disk space is required for the temporary mapping index. Este índice temporal se utiliza en operaciones de índice en línea que crean, reconstruyen o quitan un índice clúster.This temporary index is used in online index operations that create, rebuild, or drop a clustered index.
  • Para quitar un índice agrupado en línea se requiere tanto espacio como para crearlo o regenerarlo.Dropping a clustered index online requires as much space as creating (or rebuilding) a clustered index online.

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

Consideraciones de rendimientoPerformance considerations

Aunque las operaciones de índice en línea permiten actividades de actualización de usuario simultáneas, las operaciones de índice tardan más si la actividad de actualización es muy grande.Although online index operations permit concurrent user update activity, the index operations will take longer if the update activity is very heavy. 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.Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

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.Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. 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.This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Las operaciones de índice en línea se registran totalmente.Online index operations are fully logged.

Aunque se recomiendan las operaciones en línea, se debe evaluar el entorno y los requisitos específicos.Although we recommend online operations, you should evaluate your environment and specific requirements. Puede ser mejor ejecutar operaciones de índice sin conexión.It may be optimal to run index operations offline. 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.In doing this, users have restricted access to the data during the operation, but the operation finishes faster and uses fewer resources.

En los equipos con varios procesadores que ejecutan SQL Server 2016, 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.On multiprocessor computers that are running SQL Server 2016, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. 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.You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. De este modo, puede equilibrar los recursos utilizados por la operación de índice con los de los usuarios simultáneos.In this way, you can balance the resources that are used by index operation with those of the concurrent users. Para obtener más información, vea Configurar operaciones de índice en paralelo.For more information, see Configure Parallel Index Operations. Para obtener más información sobre las ediciones de SQL que admiten operaciones indexadas en paralelo, vea Features Supported by edition (Características compatibles con las ediciones de SQL Server 2016).For more information about the editions of SQL Server that support Parallel indexed operations, see Features Supported by editions.

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.Because an S-lock or Sch-M lock is held in the final phase of the index operation, be careful when you run an online index operation inside an explicit user transaction, such as BEGIN TRANSACTION...COMMIT block. De esta manera el bloqueo se conserva hasta el final de la transacción y se impide la simultaneidad de usuarios.Doing this causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

La regeneración de índices en línea puede aumentar la fragmentación cuando se puede ejecutar con las opciones MAX DOP > 1 y ALLOW_PAGE_LOCKS = OFF .Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. Para más información, vea Cómo funciona: la recompilación de índices en línea puede provocar más fragmentación.For more information, see How It Works: Online Index Rebuild - Can Cause Increased Fragmentation.

Consideraciones del registro de transaccionesTransaction log considerations

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.Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. Para estar seguros de que la operación de índice se pueda revertir, el registro de transacciones no se puede truncar hasta que se haya completado la operación de índice; no obstante, se puede realizar una copia de seguridad del registro durante la operación de índice.To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. 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.Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. Para más información, consulte Transaction Log Disk Space for Index Operations.For more information, see Transaction Log Disk Space for Index Operations.

Consideraciones sobre índices reanudablesResumable index considerations

Nota

La opción de índice reanudable se aplica a SQL Server (a partir de SQL Server 2017) (solo recompilación de índice) y SQL Database (creación de índice y recompilación de índice).The resumable index option applies to SQL Server (Starting with SQL Server 2017) (index rebuild only) and SQL Database (create index and index rebuild). Consulte CREATE INDEX (actualmente en versión preliminar pública para Vista previa de SQL Server 2019SQL Server 2019 preview) y ALTER INDEX.See Create Index (currently in public preview for Vista previa de SQL Server 2019SQL Server 2019 preview) and Alter Index.

Al realizar la recompilación o la creación de índices en línea reanudables, se aplican las siguientes directrices:When you perform resumable online index create or rebuild, the following guidelines apply:

  • Administración, planeamiento y ampliación de los períodos de mantenimiento del índice.Managing, planning and extending of index maintenance windows. 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.You can pause and restart an index create or rebuild operation multiple times to fit your maintenance windows.
  • 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).Recovering from index create or rebuild failures (such as database failovers or running out of disk space).
  • 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.When an index operation is paused, both the original index and the newly created one require disk space and need to be updated during DML operations.
  • Permite el truncamiento de registros de transacciones durante la operación de recompilación o creación de índice.Enables truncation of transaction logs during an index create or rebuild operation.
  • Opción SORT_IN_TEMPDB=ON no admitidaSORT_IN_TEMPDB=ON option is not supported

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.Resumable index create or rebuild does not require you to keep open a long running transaction, allowing log truncation during this operation and a better log space management. 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.With the new design, we managed to keep necessary data in a database together with all references required to restart the resumable operation.

Por lo general, no hay ninguna diferencia de rendimiento entre la recompilación de índices en línea reanudables y no reanudables.Generally, there is no performance difference between resumable and non-resumable online index rebuild. 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.For create resumable index, there is a constant overhead that causes a small performance difference between resumable and non-resumable index create. Esta diferencia se aprecia principalmente solo en las tablas más pequeñas.This difference is mostly noticeable only for smaller tables.

Al actualizar un índice reanudable mientras una operación de índice está en pausa:When you update a resumable index while an index operation is paused:

  • Para las cargas de trabajo principalmente de solo lectura, el impacto de rendimiento es insignificante.For read-mostly workloads, the performance impact is insignificant.
  • Para las cargas de trabajo con muchas actualizaciones, puede experimentar una reducción del rendimiento (nuestras pruebas muestran una inferior al 10 %).For update-heavy workloads, you may experience some throughput degradation (our testing shows less than 10% degradation).

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.Generally, there is no difference in defragmentation quality between resumable and non-resumable online index create or rebuild.

Opciones predeterminadas en líneaOnline default options

Importante

Estas opciones están en fase de versión preliminar pública para Vista previa de SQL Server 2019SQL Server 2019 preview.These options are in public preview for Vista previa de SQL Server 2019SQL Server 2019 preview.

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.You can set default options for online or resumable at a database level by setting the ELEVATE_ONLINE or ELEVATE_RESUMABLE database scoped configuration options. Con estas opciones predeterminadas puede evitar que se lleve a cabo por error una operación que desactive la tabla de base de datos.With these default options, you can avoid accidentally performing an operation that takes your database table offline. Ambas opciones harán que el motor eleve automáticamente determinadas operaciones a la ejecución ONLINE o RESUMABLE.Both options will cause the engine to automatically elevate certain operations to online or resumable execution.
Puede establecer estas opciones en FAIL_UNSUPPORTED, WHEN_SUPPORTED u OFF con el comando ALTER DATABASE SCOPED CONFIGURATION.You can set either option as FAIL_UNSUPPORTED, WHEN_SUPPORTED, or OFF using the ALTER DATABASE SCOPED CONFIGURATION command. Puede establecer valores diferentes para ONLINE y RESUMABLE.You can set different values for online and resumable.

ELEVATE_ONLINE y ELEVATE_RESUMABLE solo se aplican a las instrucciones de DDL que admiten la sintaxis de ONLINE y RESUMABLE, respectivamente.Both ELEVATE_ONLINE and ELEVATE_RESUMABLE only apply to DDL statements that support the online and resumable syntax respectively. 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=.For example, if you attempt to create an XML index with ELEVATE_ONLINE=FAIL_UNSUPORTED, the operation will run offline since XML indexes don't support the ONLINE= syntax. Las opciones solo afectan a las instrucciones de DDL que se envían sin especificar una opción ONLINE o RESUMABLE.The options only effect DDL statements that are submitted without specifying an ONLINE or RESUMABLE option. Por ejemplo, si se envía una instrucción con ONLINE=OFF o RESUMABLE=OFF, el usuario puede reemplazar un valor de configuración FAIL_UNSUPPORTED y ejecutar una instrucción sin conexión o no reanudable.For example, by submitting a statement with ONLINE=OFF or RESUMABLE=OFF, the user can override a FAIL_UNSUPPORTED setting and run a statement offline and/or non-resumably.

Nota

ELEVATE_ONLINE y ELEVATE_RESUMABLE no se aplican a las operaciones de índice XML.ELEVATE_ONLINE and ELEVATE_RESUMABLE does not apply to XML index operations.