분할된 테이블 및 인덱스

SQL Server는 테이블 및 인덱스 분할을 지원합니다. 분할된 테이블 및 인덱스의 데이터는 데이터베이스에서 둘 이상의 파일 그룹에 분산될 수 있는 단위로 나뉩니다. 행 그룹이 개별 파티션에 매핑되도록 데이터가 가로로 분할됩니다. 단일 인덱스 또는 테이블의 모든 파티션은 동일한 데이터베이스에 있어야 합니다. 데이터에서 쿼리 또는 업데이트가 수행될 때 테이블 또는 인덱스는 단일 논리 엔터티로 처리됩니다. 분할된 테이블 및 인덱스는 MicrosoftSQL Server의 모든 버전에서 사용할 수 없습니다. SQL Server 버전에서 지원하는 기능 목록은 SQL Server 2014 버전에서 지원하는 기능을 참조하세요.

분할의 이점

큰 테이블 또는 인덱스를 분할하면 다음과 같은 관리 효율성과 성능 이점이 있을 수 있습니다.

• 데이터 컬렉션의 무결성을 유지하면서 데이터의 하위 집합을 빠르고 효율적으로 전송하거나 액세스할 수 있습니다. 예를 들어 OLTP에서 OLAP 시스템으로 데이터를 로드하는 등의 작업은 데이터가 분할되지 않을 때 작업에서 소요되는 분 및 시간 대신 몇 초밖에 걸리지 않습니다.

• 하나 이상의 파티션에서 유지 관리 작업을 더 빠르게 수행할 수 있습니다. 작업은 전체 테이블 대신 이러한 데이터 하위 집합만 대상으로 지정하기 때문에 더 효율적입니다. 예를 들어 하나 이상의 파티션에서 데이터를 압축하거나 인덱스의 하나 이상의 파티션을 다시 빌드하도록 선택할 수 있습니다.

• 자주 실행하는 쿼리 유형과 하드웨어 구성에 따라 쿼리 성능을 향상시킬 수 있습니다. 예를 들어 쿼리 최적화 프로그램은 파티션 자체가 조인될 수 있으므로 테이블의 분할 열이 같을 때 둘 이상의 분할된 테이블 간에 동등 조인 쿼리를 더 빠르게 처리할 수 있습니다.

  SQL Server가 I/O 작업에 대한 데이터 정렬을 수행하는 경우 먼저 파티션별로 데이터를 정렬합니다. SQL Server는 한 번에 하나의 드라이브에 액세스하므로 성능이 저하될 수 있습니다. 데이터 정렬 성능을 향상시키려면 RAID를 설정하여 둘 이상의 디스크에 파티션의 데이터 파일을 스트라이프합니다. 이러한 방식으로 SQL Server는 여전히 파티션별로 데이터를 정렬하지만 각 파티션의 모든 드라이브에 동시에 액세스할 수 있습니다.

  또한 전체 테이블 대신 파티션 수준에서 잠금 에스컬레이션을 사용하도록 설정하여 성능을 향상시킬 수 있습니다. 이렇게 하면 테이블에 대한 잠금 경합을 줄일 수 있습니다.

구성 요소 및 개념

다음 용어는 테이블 및 인덱스 분할에 적용할 수 있습니다.

Partition 함수
테이블 또는 인덱스의 행이 분할 열이라고 하는 특정 열의 값을 기반으로 파티션 집합에 매핑되는 방법을 정의하는 데이터베이스 개체입니다. 즉, 파티션 함수는 테이블에 포함할 파티션 수와 파티션의 경계를 정의하는 방법을 정의합니다. 예를 들어 판매 주문 데이터가 포함된 테이블이 있는 경우 판매 날짜와 같은 열에 datetime 따라 테이블을 12개(월별) 파티션으로 분할할 수 있습니다.

파티션 구성표
파티션 함수의 파티션을 파일 그룹 집합에 매핑하는 데이터베이스 개체입니다. 파티션을 별도의 파일 그룹에 배치하는 주된 이유는 파티션에서 독립적으로 백업 작업을 수행할 수 있는지 확인하는 것입니다. 개별 파일 그룹에서 백업을 수행할 수 있기 때문입니다.

분할 열
파티션 함수가 테이블 또는 인덱스 분할에 사용하는 테이블 또는 인덱스의 열입니다. 파티션 함수에 참여하는 계산 열은 PERSISTED로 명시적으로 표시되어야 합니다. 인덱스 열로 사용할 수 있는 모든 데이터 형식은 timestamp를 제외하고 분할 열로 사용할 수 있습니다. ntext, text, image, xml, varchar(max)또는 nvarchar(max)varbinary(max) 데이터 형식을 지정할 수 없습니다. 또한 Microsoft .NET Framework CLR(공용 언어 런타임) 사용자 정의 형식 및 별칭 데이터 형식 열을 지정할 수 없습니다.

정렬된 인덱스
해당 테이블과 동일한 파티션 구성표를 기반으로 하는 인덱스입니다. 테이블과 해당 인덱스가 정렬된 경우 SQL Server는 테이블과 해당 인덱스의 파티션 구조를 유지하면서 파티션을 빠르고 효율적으로 전환할 수 있습니다. 인덱스가 기본 테이블에 맞춰지도록 명명된 동일한 파티션 함수에 참여할 필요가 없습니다. 그러나 인덱스와 기본 테이블의 파티션 함수는 기본적으로 동일해야 합니다. 즉, 1) 파티션 함수의 인수는 데이터 형식이 같고, 2) 동일한 수의 파티션을 정의하고, 3) 파티션에 대해 동일한 경계 값을 정의합니다.

정렬되지 않은 인덱스
해당 테이블과 독립적으로 분할된 인덱스입니다. 즉, 인덱스는 파티션 구성표가 다르거나 기본 테이블과 별도의 파일 그룹에 배치됩니다. 다음 경우에 정렬되지 않은 분할된 인덱스를 디자인하는 것이 유용할 수 있습니다.

• 기본 테이블이 분할되지 않았습니다.

• 인덱스 키는 고유하며 테이블의 분할 열을 포함하지 않습니다.

• 기본 테이블이 서로 다른 조인 열을 사용하여 더 많은 테이블과 함께 정렬된 조인에 참여하도록 합니다.

파티션 제거
쿼리 최적화 프로그램이 쿼리의 필터 조건을 충족하기 위해 관련 파티션에만 액세스하는 프로세스입니다.

성능 지침

15,000개 파티션의 새로운 높은 제한은 메모리, 분할된 인덱스 작업, DBCC 명령 및 쿼리에 영향을 줍니다. 이 섹션에서는 파티션 수를 1,000개 이상으로 늘리면 성능에 미치는 영향을 설명하고 필요에 따라 해결 방법을 제공합니다. 최대 파티션 수에 대한 제한이 15,000개로 늘어나면 더 오랜 시간 동안 데이터를 저장할 수 있습니다. 그러나 필요한 경우에만 데이터를 유지하고 성능과 파티션 수 간의 균형을 유지해야 합니다.

메모리 사용량 및 지침

많은 수의 파티션이 사용 중인 경우 16GB 이상의 RAM을 사용하는 것이 좋습니다. 시스템에 메모리가 충분하지 않으면 메모리 부족으로 인해 DML(데이터 조작 언어) 문, DDL(데이터 정의 언어) 문 및 기타 작업이 실패할 수 있습니다. 많은 메모리 집약적 프로세스를 실행하는 16GB RAM이 있는 시스템은 많은 수의 파티션에서 실행되는 작업에서 메모리가 부족할 수 있습니다. 따라서 16GB를 초과하는 메모리가 많을수록 성능 및 메모리 문제가 발생할 가능성이 줄어듭니다.

메모리 제한은 분할된 인덱스를 빌드하는 SQL Server의 성능 또는 기능에 영향을 줄 수 있습니다. 특히 인덱스가 기본 테이블에 맞지 않거나 클러스터형 인덱스와 정렬되지 않은 경우 테이블에 이미 클러스터형 인덱스가 적용된 경우입니다.

분할된 인덱스 작업

메모리 제한은 분할된 인덱스를 빌드하는 SQL Server의 성능 또는 기능에 영향을 줄 수 있습니다. 특히 정렬되지 않은 인덱스의 경우입니다. 파티션 수가 1,000개를 초과하는 테이블에서 정렬되지 않은 인덱스를 만들거나 다시 작성할 수 있지만 해당 인덱스는 지원되지 않습니다. 그러면 작업 중에 성능이 저하되거나 메모리가 과도하게 소비될 수 있습니다.

파티션 수가 증가함에 따라 정렬된 인덱스를 만들고 다시 빌드하는 데 더 오래 걸릴 수 있습니다. 성능 및 메모리 문제가 발생할 수 있으므로 여러 개의 인덱스 만들기 및 다시 작성 명령을 동시에 실행하지 않는 것이 좋습니다.

SQL Server가 정렬을 수행하여 분할된 인덱스를 빌드할 때 먼저 각 파티션에 대해 하나의 정렬 테이블을 빌드합니다. 그런 다음, SORT_IN_TEMPDB 인덱스 옵션이 지정된 경우에는 tempdb에, 그렇지 않다면 각 파티션의 각 파일 그룹에 정렬 테이블을 만듭니다. 각 정렬 테이블에는 빌드할 최소 메모리 양이 필요합니다. 기본 테이블과 정렬된 분할된 인덱스 빌드 시 정렬 테이블은 메모리를 적게 사용하여 한 번에 하나씩 빌드됩니다. 그러나 정렬되지 않은 분할된 인덱스 빌드 시 정렬 테이블이 동시에 빌드됩니다. 따라서 이러한 동시 정렬을 처리하기에 충분한 메모리가 있어야 합니다. 파티션 수가 클수록 더 많은 메모리가 필요합니다. 각 파티션에 대한 각 정렬 테이블의 최소 크기는 페이지당 8킬로바이트인 40페이지입니다. 예를 들어 파티션이 100개인 정렬되지 않은 분할된 인덱스에는 동시에 4,000페이지(40 * 100) 페이지를 직렬로 정렬하기에 충분한 메모리가 필요합니다. 이 메모리를 사용할 수 있는 경우 빌드 작업이 성공하지만 성능이 저하될 수 있습니다. 이 메모리를 사용할 수 없는 경우 빌드 작업이 실패합니다. 또는 파티션이 100개인 정렬된 분할된 인덱스는 정렬이 동시에 수행되지 않으므로 40페이지를 정렬하기에 충분한 메모리만 필요합니다.

정렬된 인덱스와 정렬되지 않은 인덱스 모두 SQL Server가 다중 프로세서 컴퓨터의 빌드 작업에 병렬 처리 수준을 적용하는 경우 메모리 요구 사항이 더 클 수 있습니다. 병렬 처리의 정도가 클수록 메모리 요구 사항이 커지기 때문입니다. 예를 들어 SQL Server가 병렬 처리 수준을 4로 설정하는 경우 파티션이 100개인 정렬되지 않은 분할된 인덱스에는 4개의 프로세서가 동시에 4,000페이지 또는 16,000페이지를 정렬하는 데 충분한 메모리가 필요합니다. 분할된 인덱스가 정렬되면 메모리 요구 사항이 40페이지 또는 160페이지(4 * 40) 페이지를 정렬하는 4개의 프로세서로 줄어듭니다. MAXDOP 인덱스 옵션을 사용하여 병렬 처리 수준을 수동으로 줄일 수 있습니다.

DBCC 명령

파티션 수가 많을수록 파티션 수가 증가함에 따라 DBCC 명령을 실행하는 데 시간이 더 오래 걸릴 수 있습니다.

질의

파티션 제거를 사용하는 쿼리는 더 많은 수의 파티션을 사용하여 비교할 수 있거나 향상된 성능을 가질 수 있습니다. 파티션 제거를 사용하지 않는 쿼리는 파티션 수가 증가함에 따라 실행하는 데 더 오래 걸릴 수 있습니다.

예를 들어 테이블에 1억 개의 행과 열AB이 있다고 가정합니다C. 시나리오 1에서 테이블은 열 A에 1000개의 파티션으로 나뉩니다. 시나리오 2에서는 테이블이 열 A에 있는 10,000개의 파티션으로 나뉩니다. 열 A 에서 WHERE 절 필터링이 있는 테이블의 쿼리는 파티션 제거를 수행하고 하나의 파티션을 검색합니다. 파티션에서 검색할 행이 적기 때문에 시나리오 2에서 동일한 쿼리가 더 빠르게 실행됩니다. B 열에서 WHERE 절 필터링이 있는 쿼리는 모든 파티션을 검색합니다. 검사할 파티션이 적기 때문에 시나리오 2보다 시나리오 1에서 쿼리가 더 빠르게 실행됩니다.

분할 열이 아닌 열에서 TOP 또는 MAX/MIN과 같은 연산자를 사용하는 쿼리는 모든 파티션을 평가해야 하므로 분할 성능이 저하될 수 있습니다.

분할된 인덱스 작업 중 통계 계산의 동작 변경 내용

SQL Server 2012부터 분할된 인덱스를 만들거나 다시 작성할 때 테이블의 모든 행을 검사하여 통계가 생성되지 않습니다. 대신 쿼리 최적화 프로그램에서 기본 샘플링 알고리즘을 사용하여 통계를 생성합니다. 분할된 인덱스를 사용하여 데이터베이스를 업그레이드한 후에는 이러한 인덱스에 대한 히스토그램 데이터에 차이가 있을 수 있습니다. 이러한 동작 변경은 쿼리 성능에 영향을 미치지 않을 수 있습니다. 테이블의 모든 행을 검사하여 분할된 인덱스에 대한 통계를 가져오려면 FULLSCAN 절과 함께 CREATE STATISTICS 또는 UPDATE STATISTICS를 사용합니다.

관련 작업

관련 내용

분할된 테이블 및 인덱스 전략 및 구현에 대한 다음 백서가 유용할 수 있습니다.

• SQL Server 2008을 사용하여 분할된 테이블 및 인덱스 전략

• 자동 슬라이딩 윈도우를 구현하는 방법

• 파티션된 테이블로의 대량 로드

• 분할된 테이블 및 인덱스에 대한 쿼리 처리 개선 사항

• 대규모 관계형 데이터 웨어하우스를 빌드하기 위한 상위 10가지 모범 사례