인덱스에 대한 SORT_IN_TEMPDB 옵션SORT_IN_TEMPDB Option For Indexes

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

인덱스를 만들거나 다시 만들 때 SORT_IN_TEMPDB 옵션을 ON으로 설정하면 SQL Server 데이터베이스 엔진SQL Server Database Engine 에서 tempdb 를 사용하여 인덱스를 만드는 데 사용되는 중간 정렬 결과를 저장하도록 지시할 수 있습니다.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server 데이터베이스 엔진SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. 이 옵션을 사용하면 인덱스를 만드는 데 사용되는 임시 디스크 공간이 늘어나지만 tempdb 가 사용자 데이터베이스와 다른 디스크 집합에 있을 때 인덱스를 만들거나 다시 만드는 데 필요한 시간이 줄어듭니다.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. tempdb에 대한 자세한 내용은 Configure the index create memory Server Configuration Option를 참조하십시오.For more information about tempdb, see Configure the index create memory Server Configuration Option.

인덱스를 만드는 단계Phases of Index Building

데이터베이스 엔진Database Engine 에서 인덱스를 만들 때 다음 단계가 수행됩니다.As the 데이터베이스 엔진Database Engine builds an index, it goes through the following phases:

  • 먼저 데이터베이스 엔진Database Engine 은 기본 테이블의 데이터 페이지를 검색하여 키 값을 찾고 각 데이터 행의 인덱스 리프 행을 만듭니다.The 데이터베이스 엔진Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. 내부 정렬 버퍼가 리프 인덱스 항목으로 채워지면 항목이 정렬되고 중간 정렬 실행으로 디스크에 기록됩니다.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. 그러면 데이터베이스 엔진Database Engine 은 정렬 버퍼가 다시 채워질 때까지 데이터 페이지 검색을 계속합니다.The 데이터베이스 엔진Database Engine then resumes the data page scan until the sort buffers are again filled. 기본 테이블의 모든 행이 처리될 때까지 이러한 여러 데이터 페이지의 검색 패턴에 따라 정렬이 수행되고 정렬 실행 쓰기가 계속됩니다.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.

    클러스터형 인덱스에서 인덱스의 리프 행은 테이블의 데이터 행이므로 중간 정렬 실행은 모든 데이터 행을 포함합니다.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. 비클러스터형 인덱스에서 리프 행은 키가 아닌 열을 포함할 수 있지만 일반적으로 클러스터형 인덱스보다 작습니다.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. 인덱스 키가 클 경우 또는 인덱스에 키가 아닌 열이 여러 개 포함된 경우 비클러스터형 정렬 실행이 클 수 있습니다.If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. 키가 아닌 열 포함에 대한 자세한 내용은 Create Indexes with Included Columns를 참조하십시오.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • 데이터베이스 엔진Database Engine 은 정렬된 단일 스트림으로 인덱스 리프 행의 정렬된 실행을 병합합니다.The 데이터베이스 엔진Database Engine merges the sorted runs of index leaf rows into a single, sorted stream. 데이터베이스 엔진Database Engine 의 정렬 병합 구성 요소는 각 정렬 실행의 첫 페이지에서 시작하여 모든 페이지에서 가장 낮은 키를 찾은 다음 인덱스 만들기 구성 요소에 리프 행을 전달합니다.The sort merge component of the 데이터베이스 엔진Database 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. 다음으로 가장 낮은 키를 처리하는 식으로 이 과정이 계속됩니다.The next lowest key is processed, and then the next, and so on. 마지막 리프 인덱스 행이 정렬 실행 페이지에서 추출되면 프로세스는 해당 정렬 실행에서 다음 페이지로 전환됩니다.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. 정렬 실행 익스텐트의 모든 페이지가 처리되면 익스텐트가 해제됩니다.When all the pages in a sort run extent have been processed, the extent is freed. 각 리프 인덱스 행이 인덱스 만들기 구성 요소에 전달되면 버퍼의 리프 인덱스 페이지에 포함됩니다.As each leaf index row is passed to the index create component, it is included in a leaf index page in the buffer. 각 리프 페이지는 채워진 후에 쓰여집니다.Each leaf page is written as it is filled. 리프 페이지가 쓰여지면 데이터베이스 엔진Database Engine 도 인덱스의 상위 수준을 만듭니다.As leaf pages are written, the 데이터베이스 엔진Database Engine also builds the upper levels of the index. 각 상위 수준 인덱스 페이지는 채워진 후에 쓰여집니다.Each upper level index page is written when it is filled.

SORT_IN_TEMPDB 옵션SORT_IN_TEMPDB Option

SORT_IN_TEMPDB를 기본값인 OFF로 설정한 경우 기본적으로 정렬 실행은 대상 파일 그룹에 저장됩니다.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. 인덱스를 만드는 첫째 단계 동안 기본 테이블 페이지의 읽기와 정렬 실행의 쓰기가 번갈아 수행되어 디스크의 한 영역에서 다른 영역으로 디스크 읽기/쓰기 헤드가 이동됩니다.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. 이 헤드는 데이터 페이지가 검색될 때 데이터 페이지 영역에 있습니다.The heads are in the data page area as the data pages are scanned. 정렬 버퍼가 채워지고 현재 정렬 실행이 디스크에 쓰여져야 할 때 이러한 헤드는 빈 영역으로 이동했다가 테이블 페이지 검색이 다시 시작될 때 데이터 페이지 영역으로 다시 돌아갑니다.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. 읽기/쓰기 헤드 이동은 둘째 단계에서 더 많이 수행됩니다.The read/write head movement is greater in the second phase. 이 단계에서 정렬 프로세스는 일반적으로 각 정렬 실행 영역을 번갈아 읽습니다.At that time the sort process is typically alternating reads from each sort run area. 대상 파일 그룹에 정렬 실행과 새 인덱스 페이지가 작성되는데Both the sort runs and the new index pages are built in the destination filegroup. 이는 데이터베이스 엔진Database Engine 이 각 정렬 실행을 번갈아 읽으면서 동시에 주기적으로 인덱스 익스텐트로 점프하여 채워질 때 새 인덱스 페이지를 써야 함을 의미합니다.This means that at the same time the 데이터베이스 엔진Database 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.

SORT_IN_TEMPDB 옵션을 ON으로 설정하고 tempdb 가 대상 파일 그룹의 개별 디스크 집합에 있으면 첫째 단계 중에 데이터 페이지의 읽기는 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. 그러므로 마지막 인덱스를 작성하기 위한 쓰기를 수행할 때 일반적으로 데이터 키의 디스크 읽기가 디스크에서 좀 더 연속적으로 진행되며 일반적으로 tempdb 디스크에 대한 쓰기 역시 연속적으로 진행됩니다.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. 다른 사용자가 해당 데이터베이스를 사용하고 별도의 디스크 주소를 액세스하는 경우에도 읽기 및 쓰기의 전반적인 패턴은 SORT_IN_TEMPDB가 지정되는 것이 지정되지 않을 때보다 좀 더 효율적으로 나타납니다.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.

SORT_IN_TEMPDB 옵션은 특히 CREATE INDEX 작업이 병렬로 처리되지 않은 경우 인덱스 익스텐트의 근접성을 향상시킬 수 있습니다.The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. 정렬 작업 영역 익스텐트는 데이터베이스에서 이 익스텐트가 차지하는 위치에 관계없이 해제됩니다.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. 정렬 작업 영역이 대상 파일 그룹에 포함된 경우 정렬 작업 익스텐트가 해제되면 익스텐트 구조가 작성될 때 인덱스 구조를 보유하기 위한 익스텐트 요청에 의해 정렬 작업 익스텐트가 확보될 수 있습니다.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. 이로 인해 어느 수준까지 인덱스 익스텐트의 위치가 무작위로 지정될 수 있습니다.This can randomize the locations of the index extents to a degree. 정렬 익스텐트가 tempdb에 별도로 보관되는 경우 해제되는 순서는 인덱스 익스텐트의 위치에 영향을 주지 않습니다.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. 또한 중간 정렬 실행이 대상 파일 그룹 대신 tempdb 에 저장되면 대상 파일 그룹에 사용 가능한 공간이 더 많아지므로Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. 인덱스 익스텐트가 근접하게 될 확률이 높아집니다.This increases the chances that index extents will be contiguous.

SORT_IN_TEMPDB 옵션은 현재 문에만 영향을 줍니다.The SORT_IN_TEMPDB option affects only the current statement. 인덱스가 tempdb에서 정렬되어 있는지 여부를 기록하는 메타데이터는 없습니다.No metadata records that the index was or was not sorted in tempdb. 예를 들어 SORT_IN_TEMPDB 옵션을 사용하여 비클러스터형 인덱스를 만들고 이후에 이 옵션을 지정하지 않고 클러스터형 인덱스를 만드는 경우 데이터베이스 엔진Database Engine 은 비클러스터형 인덱스를 다시 만들 때 이 옵션을 사용하지 않습니다.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 데이터베이스 엔진Database Engine does not use the option when it re-creates the nonclustered index.

참고

정렬 작업이 필요하지 않거나 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB 옵션이 무시됩니다.If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

디스크 공간 요구 사항Disk Space Requirements

SORT_IN_TEMPDB 옵션을 ON으로 설정하면 tempdb 에는 중간 정렬 실행을 보관하기 위한 충분한 빈 디스크 공간이 있어야 하며 대상 파일 그룹에는 새 인덱스를 보관하기 위한 충분한 빈 디스크 공간이 있어야 합니다.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. 빈 공간이 충분하지 않고, 디스크에 공간이 없거나 자동 증가 옵션이 해제된 경우처럼 데이터베이스가 자동 증가되어 더 많은 공간을 확보하지 못하는 경우 CREATE INDEX 문은 실패합니다.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.

SORT_IN_TEMPDB를 OFF로 설정한 경우 대상 파일 그룹의 빈 디스크 공간은 최종 인덱스의 크기와 거의 같아야 합니다.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. 첫째 단계에서 정렬 실행이 작성되고 최종 인덱스와 거의 같은 양의 공간이 요구됩니다.During the first phase, the sort runs are built and require about the same amount of space as the final index. 둘째 단계에서 각 정렬 실행 익스텐트는 처리된 후 해제됩니다.During the second phase, each sort run extent is freed after it has been processed. 따라서 정렬 실행 익스텐트가 최종 인덱스 페이지를 보유하기 위해 익스텐트가 확보되는 비율과 거의 동일한 비율로 해제되므로 전반적인 공간 요구 사항은 최종 인덱스의 크기를 많이 초과하지 않게 됩니다.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. 이로 인해 발생하는 한 가지 부작용은 빈 공간의 양이 최종 인덱스의 크기와 아주 근접할 경우 데이터베이스 엔진Database Engine 은 일반적으로 정렬 실행 익스텐트가 해제되자마자 이 정렬 실행 익스텐트를 다시 사용한다는 것입니다.One side effect of this is that if the amount of free space is very close to the size of the final index, the 데이터베이스 엔진Database Engine will generally reuse the sort run extents very quickly after they are freed. 정렬 실행 익스텐트는 무작위로 해제되므로 이 시나리오에 해당하는 인덱스 익스텐트의 근접성을 감소시킵니다.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. SORT_IN_TEMPDB를 OFF로 설정하면 새롭게 할당 취소된 정렬 실행 익스텐트가 아니라 연속된 풀에서 인덱스 익스텐트를 할당할 수 있도록 대상 파일 그룹에 충분한 빈 공간이 있을 경우 인덱스 익스텐트의 근접성이 향상됩니다.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.

비클러스터형 인덱스를 만들 때 다음과 같이 사용 가능한 공간이 있어야 합니다.When you create a nonclustered index, you must have available as free space:

  • SORT_IN_TEMPDB를 ON으로 설정한 경우 tempdb 에는 정렬 실행을 저장하기 위한 충분한 사용 가능한 공간이 있어야 하며 대상 파일 그룹에는 최종 인덱스 구조를 저장하기 위한 충분한 빈 공간이 있어야 합니다.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. 정렬 실행은 인덱스의 리프 행을 포함합니다.The sort runs contain the leaf rows of the index.

  • SORT_IN_TEMPDB를 OFF로 설정한 경우 대상 파일 그룹에는 최종 인덱스 구조를 저장하기 위한 빈 공간이 충분히 있어야 합니다.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. 인덱스 익스텐트의 근접성은 사용 가능한 빈 공간이 늘어날 때 향상될 수 있습니다.The continuity of the index extends may be improved if more free space is available.

    비클러스터형 인덱스가 없는 테이블에 클러스터형 인덱스를 만드는 경우 다음과 같이 사용 가능한 공간이 있어야 합니다.When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • SORT_IN_TEMPDB를 ON으로 설정한 경우 tempdb 에는 정렬 실행을 저장하기 위한 사용 가능한 공간이 충분히 있어야 합니다.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. 여기에는 테이블의 데이터 행이 포함됩니다.These include the data rows of the table. 대상 파일 그룹에는 최종 인덱스 구조를 저장하기 위한 충분한 빈 공간이 있어야 합니다.There must be sufficient free space in the destination filegroup to store the final index structure. 여기에는 테이블의 데이터 행과 인덱스 B-트리가 포함됩니다.This includes the data rows of the table and the index B-tree. 키 크기를 크게 사용하거나 채우기 비율을 낮은 값으로 설정하는 등 예상 계수를 조정해야 할 수 있습니다.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • SORT_IN_TEMPDB를 OFF로 설정한 경우 대상 파일 그룹에는 최종 테이블을 저장하기 위한 빈 공간이 충분히 있어야 합니다.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. 여기에는 인덱스 구조가 포함됩니다.This includes the index structure. 테이블 및 인덱스 익스텐트의 근접성은 사용 가능한 빈 공간이 늘어날 때 향상될 수 있습니다.The continuity of the table and index extents may be improved if more free space is available.

    비클러스터형 인덱스가 있는 테이블에 클러스터형 인덱스를 만드는 경우 사용 가능한 공간이 있어야 합니다.When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • SORT_IN_TEMPDB를 ON으로 설정한 경우 tempdb 에는 가장 큰 인덱스(일반적으로 클러스터형 인덱스)의 정렬 실행 집합을 저장하기 위한 충분한 빈 공간이 있어야 하며 대상 파일 그룹에는 모든 인덱스의 최종 구조를 저장하기 위한 충분한 빈 공간이 있어야 합니다.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. 여기에는 테이블의 데이터 행이 있는 클러스터형 인덱스가 포함됩니다.This includes the clustered index that contains the data rows of the table.

  • SORT_IN_TEMPDB를 OFF로 설정한 경우 대상 파일 그룹에는 최종 테이블을 저장하기 위한 빈 공간이 충분히 있어야 합니다.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. 여기에는 모든 인덱스의 구조가 포함됩니다.This includes the structures of all the indexes. 테이블 및 인덱스 익스텐트의 근접성은 사용 가능한 빈 공간이 늘어날 때 향상될 수 있습니다.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)

인덱스 다시 구성 및 다시 작성Reorganize and Rebuild Indexes

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

Configure the index create memory Server Configuration OptionConfigure the index create memory Server Configuration Option

인덱스 DDL 작업의 디스크 공간 요구 사항Disk Space Requirements for Index DDL Operations