Opção SORT_IN_TEMPDB para índicesSORT_IN_TEMPDB Option For Indexes

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Quando você cria ou recria um índice, definindo a opção SORT_IN_TEMPDB como ON, você pode direcionar Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine para usar tempdb para armazenar os resultados intermediários de classificação utilizados para criar o índice.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Embora essa opção aumente a quantidade de espaço temporário em disco usada para criar um índice, a opção pode reduzir o tempo necessário à criação ou recriação de um índice quando tempdb estiver em um conjunto de discos diferente do banco de dados do usuário.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 obter mais informações sobre tempdb, veja Configurar a opção index create memory de configuração de servidor.For more information about tempdb, see Configure the index create memory Server Configuration Option.

Fases da criação do índicePhases of Index Building

Conforme Mecanismo de Banco de DadosDatabase Engine constrói um índice, ele passa pelas fases seguintes:As the Mecanismo de Banco de DadosDatabase Engine builds an index, it goes through the following phases:

  • O Mecanismo de Banco de DadosDatabase Engine primeiro verifica as páginas de dados da tabela base para recuperar valores chave e constrói uma linha de folha de índice para cada linha de dados.The Mecanismo de Banco de DadosDatabase Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. Quando os buffers internos de classificação estiverem preenchidos com entradas de índice de folha, as entradas serão classificadas e serão gravadas no disco como uma execução de classificação intermediária.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. O Mecanismo de Banco de DadosDatabase Engine , em seguida, retoma a verificação da página de dados até que os buffers de classificação sejam preenchidos novamente.The Mecanismo de Banco de DadosDatabase Engine then resumes the data page scan until the sort buffers are again filled. Esse padrão de verificação de várias páginas de dados, seguido pela classificação e gravação de uma execução de classificação, continua até a conclusão do processamento de todas as linhas da tabela 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.

    Em um índice clusterizado, as linhas de folha do índice são as linhas de dados da tabela; portanto, a execução intermediária de classificação contém todas as linhas de dados.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. Em um índice não clusterizado, as linhas de folha podem conter colunas não chave, mas são geralmente menores do que um índice clusterizado.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. Se as chaves do índice forem grandes, ou se houver várias colunas não chave incluídas no índice, uma execução de classificação não clusterizado poderá 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 obter mais informações sobre como incluir colunas não chave, veja Criar índices com colunas incluídas.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • O Mecanismo de Banco de DadosDatabase Engine mescla as execuções classificadas de linhas de folha de índice em um único fluxo classificado.The Mecanismo de Banco de DadosDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. O componente de mescla de classificação do Mecanismo de Banco de DadosDatabase Engine inicia com a primeira página de cada execução de classificação, encontra a menor chave em todas as páginas, e transmite aquela linha de folha ao componente de criação do índice.The sort merge component of the Mecanismo de Banco de DadosDatabase 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. A próxima chave menor é processada, depois a próxima, e assim por diante.The next lowest key is processed, and then the next, and so on. Quando a última linha de índice de folha é extraída de uma página de execução de classificação, o processo alterna para próxima página daquela execução de classificação.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. Quando forem processadas todas as páginas em uma extensão de execução de classificação, a extensão será liberada.When all the pages in a sort run extent have been processed, the extent is freed. Conforme cada linha de índice de folha é transmitida ao componente de criação de índice, é incluída em uma página de índice de folha no buffer.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 de folha é gravada conforme é preenchida.Each leaf page is written as it is filled. Conforme as páginas de folha são gravadas, o Mecanismo de Banco de DadosDatabase Engine também constrói os níveis superiores do índice.As leaf pages are written, the Mecanismo de Banco de DadosDatabase Engine also builds the upper levels of the index. Cada página de índice de nível superior é gravada conforme é preenchida.Each upper level index page is written when it is filled.

opção SORT_IN_TEMPDBSORT_IN_TEMPDB Option

Quando SORT_IN_TEMPDB é definido como OFF, o padrão, as execuções da classificação são armazenadas no grupo de arquivos de destino.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. Durante a primeira fase de criação do índice, as leituras alternadas das páginas da tabela base e gravação das execuções de classificação movem os cabeçalhos de leitura/gravação de disco de uma área do disco para outra.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. Os cabeçalhos estão na área da página de dados conforme as páginas de dados são verificadas.The heads are in the data page area as the data pages are scanned. Eles se mudam para uma área de espaço livre quando os buffers de classificação forem preenchidos e a execução atual de classificação estiver gravada no disco e, em seguida, voltam para a área da página de dados conforme retoma a verificação da página da tabela.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. O movimento do cabeçalho de leitura/gravação é maior na segunda fase.The read/write head movement is greater in the second phase. Neste momento, o processo de classificação está normalmente alternando leituras de cada área de execução de classificação.At that time the sort process is typically alternating reads from each sort run area. Ambas as execuções de classificação e as novas páginas de índice são criadas no grupo de arquivos de destino.Both the sort runs and the new index pages are built in the destination filegroup. Isto significa que ao mesmo tempo em que o Mecanismo de Banco de DadosDatabase Engine está propagando leituras entre as execuções de classificação, ele precisa ir periodicamente até as extensões do índice para gravar novas páginas de índice conforme forem preenchidas.This means that at the same time the Mecanismo de Banco de DadosDatabase 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.

Se a opção SORT_IN_TEMPDB for definida como ON e tempdb estiver em um conjunto separado de discos do grupo de arquivos de destino, durante a primeira fase, as leituras das páginas de dados ocorrerão em um disco diferente da gravação na área de trabalho de classificação em 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. Isto significa que as leituras de disco das chaves de dados geralmente continuam mais em série pelo disco, e a gravação no disco tempdb também é geralmente em série, conforme a gravação faz para criar o í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. Mesmo se outros usuários estiverem usando o banco de dados e acessando endereços de disco separados, o padrão geral de leituras e gravações é mais eficiente quando SORT_IN_TEMPDB é especificado, do que quando não é.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.

A opção SORT_IN_TEMPDB pode melhorar a proximidade de extensões de índice, especialmente se a operação CREATE INDEX não estiver sendo processada em 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. As extensões da área de trabalho de classificação são liberadas um tanto aleatoriamente com respeito à localização no banco de dados.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. Se as áreas de trabalho de classificação estiverem no grupo de arquivos de destino, conforme as extensões de trabalho de classificação são liberadas, elas podem ser adquiridas pelas solicitações de extensões para manterem a estrutura do índice conforme ele for construído.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. Isto pode distribuir aleatoriamente as localizações das extensões de índice até certo ponto.This can randomize the locations of the index extents to a degree. Se as extensões de classificação forem mantidas separadamente em tempdb, a sequência de liberação não afetará a localização das extensões do í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. Além disso, quando as execuções intermediárias de classificação são armazenadas em tempdb em vez do grupo de arquivos de destino, existe mais espaço disponível no grupo de arquivos 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. Isto aumenta as chances de as extensões de índice ser contíguas.This increases the chances that index extents will be contiguous.

A opção SORT_IN_TEMPDB afeta somente a instrução atual.The SORT_IN_TEMPDB option affects only the current statement. Nenhum metadado registra se o índice foi ou não classificado em tempdb.No metadata records that the index was or was not sorted in tempdb. Por exemplo, se você criar um índice não clusterizado usando a opção SORT_IN_TEMPDB, e mais tarde criar um índice clusterizado sem especificar a opção, o Mecanismo de Banco de DadosDatabase Engine não usará a opção quando recriar o índice não clusterizado.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 Mecanismo de Banco de DadosDatabase Engine does not use the option when it re-creates the nonclustered index.

Observação

Se a operação de classificação não for necessária, ou se a classificação puder ser executada na memória, a opção SORT_IN_TEMPDB será ignorada.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 espaço em discoDisk Space Requirements

Quando você definir a opção SORT_IN_TEMPDB como ON, você deverá ter espaço livre em disco suficiente disponível em tempdb para manter as execuções de classificação intermediárias e espaço livre em disco suficiente no grupo de arquivos de destino para manter o novo í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. A instrução CREATE INDEX falha se houver espaço livre insuficiente e se por alguma razão os bancos de dados não puderem crescer automaticamente para adquirir mais espaço, considerando que nenhum espaço no disco ou se o crescimento automático estiver definido como off.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.

Se SORT_IN_TEMPDB for definido como OFF, o espaço livre em disco disponível no grupo de arquivos de destino deve ser praticamente do tamanho do í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 a primeira fase, as execuções de classificação são criadas e requerem praticamente a mesma quantidade de espaço do índice final.During the first phase, the sort runs are built and require about the same amount of space as the final index. Durante a segunda fase, cada extensão de execução de classificação é liberada depois de ser processada.During the second phase, each sort run extent is freed after it has been processed. Isto significa que as extensões de execução de classificação são liberadas aproximadamente a mesma taxa de aquisição das extensões para manter as páginas do índice final; portanto, os requisitos de espaço geral não excedem muito o tamanho do í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. Um efeito colateral disto é que se a quantidade de espaço livre for muito perto do tamanho do índice final, o Mecanismo de Banco de DadosDatabase Engine geralmente vai reutilizar as extensões de execução de classificação muito depressa depois que forem liberadas.One side effect of this is that if the amount of free space is very close to the size of the final index, the Mecanismo de Banco de DadosDatabase Engine will generally reuse the sort run extents very quickly after they are freed. Como as extensões de execução de classificação são liberadas de forma um pouco aleatória, isto reduz a continuidade das extensões do índice neste cenário.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. Se SORT_IN_TEMPDB for definido como OFF, a continuidade das extensões do índice é melhorada se houver espaço livre suficiente disponível no grupo de arquivos de destino, que as extensões do índice podem ser alocadas de um pool contíguo, em vez das extensões de corrida de classificação recentemente desalocadas.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.

Quando você criar um índice não clusterizado, você deve ter disponível, como espaço livre:When you create a nonclustered index, you must have available as free space:

  • Se SORT_IN_TEMPDB for definido como ON, deve haver espaço livre suficiente em tempdb para armazenar as execuções de classificação, e espaço livre suficiente no grupo de arquivos de destino para armazenar a estrutura final do índice.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. As execuções de classificação contêm as linhas de folha do índice.The sort runs contain the leaf rows of the index.

  • Se SORT_IN_TEMPDB for definido como OFF, o espaço livre no grupo de arquivos de destino deve ser grande o suficiente para armazenar a estrutura final do índice.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. A continuidade da extensão do índice pode ser melhorada se houver mais espaço livre disponível.The continuity of the index extends may be improved if more free space is available.

Quando você criar um índice clusterizado em uma tabela que não tem índices não clusterizado, você deverá ter disponível como espaço livre:When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • Se SORT_IN_TEMPDB for definido como ON, deverá haver espaço livre suficiente em tempdb para armazenar as execuções de classificação.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. Isso inclui as linhas de dados da tabela.These include the data rows of the table. Deve haver espaço livre suficiente no grupo de arquivos de destino para armazenar a estrutura final do índice.There must be sufficient free space in the destination filegroup to store the final index structure. Isso inclui as linhas de dados da tabela e o índice árvore B.This includes the data rows of the table and the index B-tree. Você pode precisar ajustar a estimativa de fatores, como ter um tamanho grande de chave ou um fator de preenchimento com um valor baixo.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • Se SORT_IN_TEMPDB for definido como OFF, o espaço livre no grupo de arquivos de destino deverá ser grande o suficiente para armazenar a tabela 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. Isso inclui a estrutura do índice.This includes the index structure. A continuidade da tabela e da extensão do índice poderá ser melhorada se houver mais espaço livre disponível.The continuity of the table and index extents may be improved if more free space is available.

Quando você criar um índice clusterizado em uma tabela que tem índices não clusterizado, você deverá ter disponível como espaço livre:When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • Se SORT_IN_TEMPDB for definido como ON, deve haver espaço livre suficiente em tempdb para armazenar a coleção de execuções de classificação para o maior índice, normalmente o índice clusterizado, e espaço livre suficiente no grupo de arquivos de destino para armazenar as estruturas finais de todos os í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. Isso inclui o índice clusterizado que contém as linhas de dados da tabela.This includes the clustered index that contains the data rows of the table.

  • Se SORT_IN_TEMPDB for definido como OFF, o espaço livre no grupo de arquivos de destino deverá ser grande o suficiente para armazenar a tabela 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. Isso inclui as estruturas de todos os índices.This includes the structures of all the indexes. A continuidade da tabela e da extensão do índice poderá ser melhorada se houver mais espaço livre disponível.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 e recriar índicesReorganize and Rebuild Indexes

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

Configurar a opção index create memory de configuração de servidorConfigure the index create memory Server Configuration Option

Requisitos de espaço em disco para operações de índice DDLDisk Space Requirements for Index DDL Operations