인덱스에 대한 SORT_IN_TEMPDB 옵션
적용 대상:
Microsoft Fabric의 SQL ServerAzure SQL Database Azure SQL Managed Instance SQL 데이터베이스
인덱스 만들기 또는 다시 빌드 시 SORT_IN_TEMPDB 옵션을 ON으로 설정하면 SQL Server 데이터베이스 엔진에서 tempdb를 사용하여 인덱스 빌드에 사용되는 중간 정렬 결과를 저장하도록 지시할 수 있습니다. 이 옵션을 사용하면 인덱스를 만드는 데 사용되는 임시 디스크 공간이 늘어나지만 tempdb가 사용자 데이터베이스와 다른 디스크 집합에 있을 때 인덱스를 만들거나 다시 빌드할 때 필요한 시간이 줄어들 수 있습니다. tempdb에 대한 자세한 내용은 인덱스 만들기 메모리 서버 구성 옵션 구성을 참조하세요.
인덱스 빌드 단계
데이터베이스 엔진에서 인덱스를 만들 때 다음 단계가 수행됩니다.
데이터베이스 엔진은 먼저 기본 테이블의 데이터 페이지를 검색하여 키 값을 검색하고 각 데이터 행에 대한 인덱스 리프 행을 작성합니다. 내부 정렬 버퍼가 리프 인덱스 항목으로 채워지면 항목이 정렬되고 중간 정렬 실행으로 디스크에 기록됩니다. 그런 다음 정렬 버퍼가 다시 채워지게 될 때까지 데이터베이스 엔진이 데이터 페이지 검사를 다시 시작합니다. 여러 데이터 페이지를 검색한 다음 정렬 실행을 정렬하고 쓰는 이 패턴은 기본 테이블의 모든 행이 처리될 때까지 계속됩니다.
클러스터형 인덱스의 리프 행은 테이블의 데이터 행입니다. 따라서 중간 정렬 실행에는 모든 데이터 행이 포함됩니다. 비클러스터형 인덱스의 리프 행에는 키가 아닌 열이 포함될 수 있지만 일반적으로 클러스터형 인덱스보다 작습니다. 인덱스 키가 크거나 인덱스에 키가 아닌 열이 여러 개 있는 경우 비클러스터형 정렬 실행이 클 수 있습니다. 키가 아닌 열 포함에 대한 자세한 내용은 Create Indexes with Included Columns를 참조하십시오.
데이터베이스 엔진은 정렬된 단일 스트림으로 인덱스 리프 행의 정렬된 실행을 병합합니다. 데이터베이스 엔진의 정렬 병합 구성 요소는 각 정렬 실행의 첫 번째 페이지로 시작하고, 모든 페이지에서 가장 낮은 키를 찾은 다음, 해당 리프 행을 인덱스 만들기 구성 요소에 전달합니다. 다음으로 가장 낮은 키가 처리된 다음 키가 처리됩니다. 정렬 실행 페이지에서 마지막 리프 인덱스 행을 추출하면 프로세스는 해당 정렬 실행에서 다음 페이지로 이동합니다. 정렬 실행 범위의 모든 페이지가 처리되면 익스텐트가 해제됩니다. 각 리프 인덱스 행이 인덱스 만들기 구성 요소에 전달되면 버퍼의 리프 인덱스 페이지에 포함됩니다. 각 리프 페이지는 채워지는 대로 작성됩니다. 리프 페이지가 작성되면서 데이터베이스 엔진 인덱스의 상위 수준도 빌드됩니다. 각 상위 수준 인덱스 페이지는 채워질 때 작성됩니다.
SORT_IN_TEMPDB 옵션
SORT_IN_TEMPDB가 OFF로 설정되면 기본값인 정렬 실행이 대상 파일 그룹에 저장됩니다. 인덱스를 만드는 첫 번째 단계에서는 기본 테이블 페이지와 정렬 실행의 쓰기를 번갈아 읽고 디스크 읽기/쓰기 헤드가 디스크의 한 영역에서 다른 영역으로 이동합니다. 헤드는 데이터 페이지가 스캔될 때 데이터 페이지 영역에 있습니다. 정렬 버퍼가 채워지고 현재 정렬 실행을 디스크에 기록해야 할 때 사용 가능한 공간 영역으로 이동한 다음 테이블 페이지 검색이 다시 시작될 때 데이터 페이지 영역으로 다시 이동합니다. 읽기/쓰기 헤드 이동은 두 번째 단계에서 더 큽니다. 이때 정렬 프로세스는 일반적으로 각 정렬 실행 영역에서 읽기를 번갈아 가며 진행합니다. 대상 파일 그룹에 정렬 실행과 새 인덱스 페이지가 작성되는데 즉, 데이터베이스 엔진이 정렬 실행에서 읽기를 분산하는 동시에 채워질 때 새 인덱스 페이지를 작성하려면 정기적으로 인덱스 익스텐트로 이동해야 합니다.
SORT_IN_TEMPDB 옵션이 ON으로 설정되고 tempdb가 대상 파일 그룹에서 별도의 디스크 집합에 있는 경우 첫 번째 단계에서 데이터 페이지 읽기는 쓰기에서 tempdb의 정렬 작업 영역까지 다른 디스크에서 발생합니다. 즉, 데이터 키의 디스크 읽기는 일반적으로 디스크에서 더 직렬적으로 계속되고 tempdb 디스크에 대한 쓰기도 일반적으로 최종 인덱스를 작성하기 위한 쓰기와 마찬가지로 직렬입니다. 다른 사용자가 해당 데이터베이스를 사용하고 별도의 디스크 주소를 액세스하는 경우에도 읽기 및 쓰기의 전반적인 패턴은 SORT_IN_TEMPDB가 지정되는 것이 지정되지 않을 때보다 좀 더 효율적으로 나타납니다.
SORT_IN_TEMPDB 옵션은 특히 CREATE INDEX 작업이 병렬로 처리되지 않는 경우 인덱스 익스텐트의 동시성을 향상시킬 수 있습니다. 정렬 작업 영역 익스텐트는 데이터베이스에서 이 익스텐트가 차지하는 위치에 관계없이 해제됩니다. 정렬 작업 영역이 대상 파일 그룹에 포함된 경우 정렬 작업 익스텐트가 해제되면 익스텐트 구조가 작성될 때 인덱스 구조를 보유하기 위한 익스텐트 요청에 의해 정렬 작업 익스텐트가 확보될 수 있습니다. 이렇게 하면 인덱스 익스텐트 위치를 어느 정도 임의로 지정할 수 있습니다. 정렬 익스텐트는 tempdb에서 별도로 유지되는 경우 해제되는 시퀀스는 인덱스 익스텐트의 위치에 영향을 주지 않습니다. 또한 중간 정렬 실행이 대상 파일 그룹 대신 tempdb 에 저장되면 대상 파일 그룹에 사용 가능한 공간이 더 많아지므로 이렇게 하면 인덱스 익스텐트가 연속될 가능성이 높아집니다.
SORT_IN_TEMPDB 옵션은 현재 문에만 영향을 줍니다. 인덱스가 tempdb에서 정렬되어 있는지 여부를 기록하는 메타데이터는 없습니다. 예를 들어 SORT_IN_TEMPDB 옵션을 사용하여 비클러스터형 인덱스를 만들고 이후에 이 옵션을 지정하지 않고 클러스터형 인덱스를 만드는 경우 데이터베이스 엔진은 비클러스터형 인덱스를 다시 만들 때 이 옵션을 사용하지 않습니다.
참고 항목
정렬 작업이 필요하지 않거나 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB 옵션이 무시됩니다.
디스크 공간 요구 사항
SORT_IN_TEMPDB 옵션을 ON으로 설정하면 tempdb에서 중간 정렬 실행을 저장하기에 충분한 사용 가능한 디스크 공간과 대상 파일 그룹에 새 인덱스를 저장하기에 충분한 사용 가능한 디스크 공간이 있어야 합니다. 빈 공간이 충분하지 않고, 디스크에 공간이 없거나 자동 증가 옵션이 해제된 경우처럼 데이터베이스가 자동 증가되어 더 많은 공간을 확보하지 못하는 경우 CREATE INDEX 문은 실패합니다.
SORT_IN_TEMPDB가 OFF로 설정된 경우 대상 파일 그룹에서 사용 가능한 디스크 공간은 최종 인덱스의 대략적인 크기여야 합니다. 첫 번째 단계에서 정렬 실행이 빌드되고 최종 인덱스와 거의 동일한 양의 공간이 필요합니다. 두 번째 단계에서 각 정렬 실행 익스텐트는 처리된 후에 해제됩니다. 따라서 정렬 실행 익스텐트가 최종 인덱스 페이지를 보유하기 위해 익스텐트가 확보되는 비율과 거의 동일한 비율로 해제되므로 전반적인 공간 요구 사항은 최종 인덱스의 크기를 많이 초과하지 않게 됩니다. 이 것의 한 가지 부작용은 사용 가능한 공간의 양이 최종 인덱스의 크기와 매우 가까운 경우 데이터베이스 엔진이 일반적으로 정렬 실행 익스텐트를 해제 후 매우 빠르게 다시 사용한다는 것입니다. 정렬 실행 익스텐트를 임의 방식으로 해제하므로 이 시나리오에서는 인덱스 익스텐트의 연속성이 줄어듭니다. SORT_IN_TEMPDB를 OFF로 설정하면 대상 파일 그룹에서 인덱스 익스텐트가 새로 할당 취소된 정렬 실행 익스텐트가 아닌 연속 풀에서 인덱스 익스텐트를 할당할 수 있는 여유 공간이 충분한 경우 인덱스 익스텐트의 연속성이 향상됩니다.
비클러스터형 인덱스를 만드는 경우 다음과 같이 사용 가능한 공간이 있어야 합니다.
SORT_IN_TEMPDB가 ON으로 설정된 경우 tempdb에 정렬 실행을 저장할 충분한 여유 공간이 있고 최종 인덱스 구조를 저장할 충분한 여유 공간이 대상 파일 그룹에 있어야 합니다. 정렬 실행은 인덱스의 리프 행을 포함합니다.
SORT_IN_TEMPDB가 OFF로 설정된 경우 대상 파일 그룹의 여유 공간은 최종 인덱스 구조를 저장할 수 있을 만큼 커야 합니다. 인덱스 익스텐트의 연속성은 사용 가능한 빈 공간이 늘어날 때 향상될 수 있습니다.
비클러스터형 인덱스가 없는 테이블에 클러스터형 인덱스를 만드는 경우 다음과 같이 사용 가능한 공간이 있어야 합니다.
SORT_IN_TEMPDB가 ON으로 설정된 경우 tempdb에 정렬 실행을 저장할 충분한 여유 공간이 있어야 합니다. 여기에는 테이블의 데이터 행이 포함됩니다. 대상 파일 그룹에는 최종 인덱스 구조를 저장하기 위한 충분한 빈 공간이 있어야 합니다. 여기에는 테이블의 데이터 행과 인덱스 B-트리가 포함됩니다. 키 크기를 크게 사용하거나 채우기 비율을 낮은 값으로 설정하는 등 예상 계수를 조정해야 할 수 있습니다.
SORT_IN_TEMPDB가 OFF로 설정된 경우 대상 파일 그룹의 여유 공간은 최종 테이블을 저장할 수 있을 만큼 커야 합니다. 여기에는 인덱스 구조가 포함됩니다. 테이블 및 인덱스 익스텐트의 근접성은 사용 가능한 빈 공간이 늘어날 때 향상될 수 있습니다.
비클러스터형 인덱스가 있는 테이블에 클러스터형 인덱스를 만드는 경우 다음과 같이 사용 가능한 공간이 있어야 합니다.
SORT_IN_TEMPDB가 ON으로 설정된 경우 tempdb에 가장 큰 인덱스(일반적으로 클러스터형 인덱스)의 정렬 실행 컬렉션을 저장할 충분한 여유 공간이 있고 대상 파일 그룹에 모든 인덱스의 최종 구조를 저장할 충분한 여유 공간이 있어야 합니다. 여기에는 테이블의 데이터 행을 포함하는 클러스터형 인덱스가 포함됩니다.
SORT_IN_TEMPDB가 OFF로 설정된 경우 대상 파일 그룹의 여유 공간은 최종 테이블을 저장할 수 있을 만큼 커야 합니다. 여기에는 모든 인덱스의 구조가 포함됩니다. 테이블 및 인덱스 익스텐트의 근접성은 사용 가능한 빈 공간이 늘어날 때 향상될 수 있습니다.