Opción SORT_IN_TEMPDB para índicesSORT_IN_TEMPDB Option For Indexes

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL DatabaseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

Cuando crea o vuelve a generar un índice, si establece la opción SORT_IN_TEMPDB en ON, puede indicar a Motor de base de datos de SQL ServerSQL Server Database Engine que use tempdb para almacenar los resultados de ordenación intermedios que se usan para generar el índice.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the Motor de base de datos de SQL ServerSQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Aunque esta opción aumenta la cantidad de espacio en disco temporal utilizado para crear un índice, reduce el tiempo que tarda en crear o volver a generar un índice cuando tempdb está en un conjunto de discos diferente al de la base de datos de usuario.Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database. Para obtener más información acerca de tempdb, vea Establecer la opción de configuración del servidor Memoria para creación de índices.For more information about tempdb, see Configure the index create memory Server Configuration Option.

Fases de la generación de un índicePhases of Index Building

Para generar un índice, Motor de base de datosDatabase Engine pasa por varias fases:As the Motor de base de datosDatabase Engine builds an index, it goes through the following phases:

  • Motor de base de datosDatabase Engine recorre en primer lugar las páginas de datos de la tabla base para recuperar los valores clave y genera una fila hoja de índice para cada fila de datos.The Motor de base de datosDatabase Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. Cuando los búferes de orden interno se han llenado con las entradas del índice hoja, las entradas se ordenan y escriben en el disco como una ordenación intermedia.When the internal sort buffers have been filled with leaf index entries, the entries are sorted and written to disk as an intermediate sort run. Motor de base de datosDatabase Engine reanuda entonces el recorrido de páginas de datos hasta que los búferes de orden se llenan de nuevo.The Motor de base de datosDatabase Engine then resumes the data page scan until the sort buffers are again filled. Este patrón de recorrido de varias páginas de datos seguido de la ordenación y escritura de una ejecución de orden continúa hasta que se han procesado todas las filas de la tabla base.This pattern of scanning multiple data pages followed by sorting and writing a sort run continues until all the rows of the base table have been processed.

    En un índice clúster, las filas hoja del índice son las filas de datos de la tabla, de manera que las ordenaciones intermedias contienen todas las filas de datos.In a clustered index, the leaf rows of the index are the data rows of the table; therefore, the intermediate sort runs contain all the data rows. En un índice no clúster, las filas hoja no contienen valores de columnas sin clave, pero suelen ser más pequeñas que un índice clúster.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. Si las claves de índice son grandes, o el índice incluye varias columnas sin clave, una ordenación no agrupada puede ser grande.If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. Para obtener más información acerca de la inclusión de columnas sin clave, vea Create Indexes with Included Columns.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • Motor de base de datosDatabase Engine mezcla las ordenaciones de las filas hoja del índice en un único flujo ordenado.The Motor de base de datosDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. El componente de mezcla de ordenación de Motor de base de datosDatabase Engine se inicia con la primera página de cada ordenación, busca la clave más baja en todas las páginas y pasa esa fila hoja al componente de creación del índice.The sort merge component of the Motor de base de datosDatabase Engine starts with the first page of each sort run, finds the lowest key in all the pages, and passes that leaf row to the index create component. La siguiente clave más baja se procesa a continuación, después la siguiente, etc.The next lowest key is processed, and then the next, and so on. Cuando se extrae la última fila del índice hoja de una página de ordenación, el proceso cambia a la página siguiente desde esa ordenación.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. Cuando se han procesado todas las páginas de una extensión de ordenación, la extensión se libera.When all the pages in a sort run extent have been processed, the extent is freed. Conforme se pasa cada fila de índice hoja al componente de creación del índice, se coloca en una página de índice hoja en el búfer.As each leaf index row is passed to the index create component, it is included in a leaf index page in the buffer. Cada página hoja se escribe conforme se llena.Each leaf page is written as it is filled. A medida que se escriben las páginas hoja, Motor de base de datosDatabase Engine crea también los niveles superiores del índice.As leaf pages are written, the Motor de base de datosDatabase Engine also builds the upper levels of the index. Cada página de índice de nivel superior se escribe cuando se llena.Each upper level index page is written when it is filled.

SORT_IN_TEMPDB, opciónSORT_IN_TEMPDB Option

Cuando SORT_IN_TEMPDB se establece en OFF (valor predeterminado), las ordenaciones se almacenan en el grupo de archivos de destino.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. Durante la primera fase de la creación del índice, las lecturas alternas de las páginas de la tabla base y las escrituras de las ordenaciones mueven los cabezales de lectura/escritura del disco de un área a otra del disco.During the first phase of creating the index, the alternating reads of the base table pages and writes of the sort runs move the disk read/write heads from one area of the disk to another. Los cabezales están en el área de páginas de datos cuando se recorren las páginas de datos.The heads are in the data page area as the data pages are scanned. Se mueven a un área de espacio disponible cuando los búferes de orden se llenan y se tiene que escribir la ordenación actual en el disco. A continuación, vuelven al área de páginas de datos cuando se reanuda el recorrido de páginas de la tabla.They move to an area of free space when the sort buffers fill and the current sort run has to be written to disk, and then move back to the data page area as the table page scan is resumed. El movimiento de los cabezales de lectura/escritura es superior en la segunda fase.The read/write head movement is greater in the second phase. En ese momento, el proceso de ordenación está alternando lecturas de cada área de ordenación.At that time the sort process is typically alternating reads from each sort run area. Tanto las ordenaciones como las nuevas páginas de índice se crean en el grupo de archivos de destino,Both the sort runs and the new index pages are built in the destination filegroup. lo que significa que, al mismo tiempo que Motor de base de datosDatabase Engine está repartiendo lecturas entre las ordenaciones, tiene que saltar periódicamente a las extensiones de índice para escribir nuevas páginas de índice conforme se llenan.This means that at the same time the Motor de base de datosDatabase Engine is spreading reads across the sort runs, it has to periodically jump to the index extents to write new index pages as they are filled.

Si la opción SORT_IN_TEMPDB se ha establecido en ON y tempdb está en un conjunto de discos diferente del grupo de archivos de destino, durante la primera fase, las lecturas de las páginas de datos tienen lugar en un disco diferente que las escrituras en el área de trabajo de ordenación de tempdb.If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. Esto significa que las lecturas del disco de las claves de datos tienden a proceder en serie en el disco, y las escrituras en el disco tempdb también tienden a ser en serie, igual que las escrituras para crear el índice final.This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the tempdb disk also are generally serial, as do the writes to build the final index. Incluso si otros usuarios están utilizando la base de datos y están teniendo acceso a diferentes direcciones de disco, el patrón global de lecturas y escrituras es más eficaz cuando se especifica SORT_IN_TEMPDB que cuando no se especifica.Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

La opción SORT_IN_TEMPDB puede mejorar la contigüidad de extensiones de índice, especialmente si la operación CREATE INDEX no se procesa en paralelo.The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. Las extensiones del área de trabajo de ordenación se liberan de una manera en cierto modo aleatoria con respecto a su ubicación en la base de datos.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. Si las áreas de trabajo de ordenación están contenidas en el grupo de archivos de destino, a medida que se liberan las extensiones de trabajo de ordenación, pueden ser adquiridas por las solicitudes de extensiones para albergar la estructura de índice conforme ésta se crea.If the sort work areas are contained in the destination filegroup, as the sort work extents are freed, they can be acquired by the requests for extents to hold the index structure as it is built. Esto puede hacer que las ubicaciones de las extensiones del índice sean aleatorias hasta cierto punto.This can randomize the locations of the index extents to a degree. Si las extensiones de orden se mantienen por separado en tempdb, la secuencia en la que se liberan no afecta a la ubicación de las extensiones del índice.If the sort extents are held separately in tempdb, the sequence in which they are freed has no effect on the location of the index extents. Asimismo, cuando las ordenaciones intermedias se almacenan en tempdb en lugar del grupo de archivos de destino, hay más espacio disponible en el grupo de archivos de destino,Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. lo que aumenta las posibilidades de que las extensiones del índice sean contiguas.This increases the chances that index extents will be contiguous.

La opción SORT_IN_TEMPDB afecta únicamente a la instrucción actual.The SORT_IN_TEMPDB option affects only the current statement. Los metadatos no registran que el índice se ordenó o no se ordenó en tempdb.No metadata records that the index was or was not sorted in tempdb. Por ejemplo, si crea un índice no clúster utilizando la opción SORT_IN_TEMPDB y, más adelante, crea un índice clúster sin especificar la opción, Motor de base de datosDatabase Engine no utiliza la opción cuando crea de nuevo el índice no clúster.For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and at a later time create a clustered index without specifying the option, the Motor de base de datosDatabase Engine does not use the option when it re-creates the nonclustered index.

Nota

Si no se necesita una operación de ordenación o si la ordenación se puede realizar en memoria, la opción SORT_IN_TEMPDB se omite.If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Requisitos de espacio en discoDisk Space Requirements

Si establece la opción SORT_IN_TEMPDB en ON, debe tener suficiente espacio de disco disponible en tempdb para contener las ordenaciones intermedias, así como suficiente espacio de disco disponible en el grupo de archivos de destino para contener el nuevo índice.When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index. La instrucción CREATE INDEX produce errores si no hay suficiente espacio disponible y hay alguna razón por la que las bases de datos no pueden crecer automáticamente para adquirir más espacio (como falta de espacio en el disco o el crecimiento automático desactivado).The CREATE INDEX statement fails if there is insufficient free space and there is some reason the databases cannot autogrow to acquire more space, such as no space on the disk or autogrow is set to off.

Si SORT_IN_TEMPDB se establece en OFF, el espacio de disco disponible en el grupo de archivos de destino debe ser aproximadamente el tamaño del índice final.If SORT_IN_TEMPDB is set to OFF, the available free disk space in the destination filegroup must be roughly the size of the final index. Durante la primera fase, se crean las ordenaciones y éstas requieren aproximadamente la misma cantidad de espacio que el índice final.During the first phase, the sort runs are built and require about the same amount of space as the final index. Durante la segunda fase, cada extensión de ordenación se libera después de haberla procesado.During the second phase, each sort run extent is freed after it has been processed. Esto significa que las extensiones de ordenación se liberan más o menos a la misma velocidad a la que se adquieren extensiones para albergar las páginas del índice final; por tanto, los requisitos de espacio globales no exceden en gran medida el tamaño del índice final.This means that sort run extents are freed at about the same rate at which extents are acquired to hold the final index pages; therefore, the overall space requirements do not greatly exceed the size of the final index. Un efecto secundario de esto es que, si la cantidad de espacio disponible es muy próxima al tamaño del índice final, Motor de base de datosDatabase Engine tenderá a reutilizar las extensiones de ordenación con mucha rapidez cuando se liberen.One side effect of this is that if the amount of free space is very close to the size of the final index, the Motor de base de datosDatabase Engine will generally reuse the sort run extents very quickly after they are freed. Puesto que las extensiones de ordenación se liberan de una manera en cierto modo aleatoria, esto reduce la continuidad de las extensiones del índice en este escenario.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. Si SORT_IN_TEMPDB se establece en OFF, la continuidad de las extensiones del índice mejora si hay suficiente espacio disponible en el grupo de archivos de destino que se pueda asignar a las extensiones del índice desde un grupo contiguo en lugar de hacerlo desde extensiones de ordenación cuya asignación ha sido cancelada recientemente.If SORT_IN_TEMPDB is set to OFF, the continuity of the index extents is improved if there is sufficient free space available in the destination filegroup that the index extents can be allocated from a contiguous pool instead of from the freshly deallocated sort run extents.

Cuando crea un índice no clúster, debe tener como espacio disponible:When you create a nonclustered index, you must have available as free space:

  • Si SORT_IN_TEMPDB se establece en ON, debe haber suficiente espacio disponible en tempdb para almacenar las ordenaciones y suficiente espacio disponible en el grupo de archivos de destino para almacenar la estructura del índice final.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. Las ordenaciones contienen las filas hoja del índice.The sort runs contain the leaf rows of the index.

  • Si SORT_IN_TEMPDB se establece en OFF, el espacio disponible del grupo de archivos de destino debe ser lo suficientemente extenso como para almacenar la estructura del índice final.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure. La continuidad de las extensiones del índice se puede mejorar si hay más espacio disponible.The continuity of the index extends may be improved if more free space is available.

Cuando crea un índice clúster en una tabla que carece de índices no clúster, debe tener disponible como espacio disponible:When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • Si SORT_IN_TEMPDB se establece en ON, debe haber suficiente espacio disponible en tempdb para almacenar las ordenaciones.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. incluidas las filas de datos de la tabla,These include the data rows of the table. y suficiente espacio disponible en el grupo de archivos de destino para almacenar la estructura del índice final,There must be sufficient free space in the destination filegroup to store the final index structure. incluidas las filas de datos de la tabla y del árbol b del índice.This includes the data rows of the table and the index B-tree. Puede ser preciso ajustar la estimación para factores como tener un tamaño de clave grande o un factor de relleno con un valor bajo.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • Si SORT_IN_TEMPDB se establece en OFF, el espacio disponible del grupo de archivos de destino debe ser lo suficientemente extenso como para almacenar la tabla final.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Esto incluye la estructura del índice.This includes the index structure. La continuidad de la tabla y las extensiones del índice se puede mejorar si hay más espacio disponible.The continuity of the table and index extents may be improved if more free space is available.

Cuando crea un índice clúster en una tabla que tiene índices no clúster, debe tener disponible como espacio disponible:When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • Si SORT_IN_TEMPDB se establece en ON, debe haber suficiente espacio disponible en tempdb para almacenar el conjunto de ordenaciones para el índice más grande, normalmente el índice agrupado, y suficiente espacio disponible en el grupo de archivos de destino para almacenar las estructuras finales de todos los índices.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index, typically the clustered index, and sufficient free space in the destination filegroup to store the final structures of all the indexes. Esto incluye el índice clúster que contiene las filas de datos de la tabla.This includes the clustered index that contains the data rows of the table.

  • Si SORT_IN_TEMPDB se establece en OFF, el espacio disponible del grupo de archivos de destino debe ser lo suficientemente extenso como para almacenar la tabla final.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Esto incluye las estructuras de todos los índices.This includes the structures of all the indexes. La continuidad de la tabla y las extensiones del índice se puede mejorar si hay más espacio disponible.The continuity of the table and index extents may be improved if more free space is available.

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

Reorganizar y volver a generar índicesReorganize and Rebuild Indexes

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

Establecer la opción de configuración del servidor Memoria para creación de índicesConfigure the index create memory Server Configuration Option

Requisitos de espacio en disco para operaciones DDL de índiceDisk Space Requirements for Index DDL Operations