DTA(데이터베이스 엔진 튜닝 관리자) 권장 사항을 사용하여 성능 향상

적용 대상:SQL Server

---

데이터 웨어하우징 및 분석 작업은 columnstore 인덱스를 사용하면 성능이 향상될 수 있습니다. 특히 큰 테이블을 검색해야 하는 쿼리에 매우 유리합니다. Rowstore (B+트리) 인덱스는 특정 값 또는 값 범위를 검색하는 비교적 적은 양의 데이터에 액세스하는 쿼리에 가장 효과적입니다. rowstore 인덱스는 정렬된 순서로 행을 제공할 수 있으므로 쿼리 실행 계획에서 정렬 비용을 줄일 수도 있습니다. 따라서 빌드할 rowstore 및 columnstore 인덱스의 조합 선택은 애플리케이션의 워크로드에 따라 달라집니다.

SQL Server 2016부터 DTA(데이터베이스 엔진 튜닝 관리자)는 지정된 데이터베이스 워크로드를 분석하여 rowstore 및 columnstore 인덱스의 적절한 조합을 추천할 수 있습니다.

워크로드 성능에 대한 DTA 권장 사항의 이점을 보여주기 위해 몇 가지 실제 고객 워크로드를 실험했습니다. 각 고객 워크로드에 대해 DTA에서 개별 쿼리와 전체 쿼리 워크로드를 분석할 수 있습니다. 다음 세 가지 대안을 고려합니다.

1. Columnstore 전용: DTA를 사용하지 않고 모든 테이블에 대한 columnstore 인덱스만 빌드합니다.
2. DTA(rowstore 전용): rowstore 인덱스에만 권장되는 옵션을 사용하여 DTA를 실행합니다.
3. DTA(rowstore + columnstore): rowstore 및 columnstore 인덱스를 모두 추천하는 옵션을 사용하여 DTA를 실행합니다.

각각의 경우에 권장 인덱스를 구현했습니다. 쿼리 또는 워크로드의 여러 실행에서 평균 CPU 시간(밀리초)을 보고합니다. 아래 그림은 서로 다른 두 고객 데이터베이스의 워크로드에 대한 CPU 시간(밀리초)을 보여 줍니다. y-축(CPU 시간)은 로그 눈금 간격을 사용합니다.

혼합 물리적 디자인의 필요성: Customer 1 쿼리 1에 해당하는 첫 번째 막대 집합입니다. DTA(rowstore + columnstore)는 columnstore 인덱스 전용 및 DTA(rowstore에만 해당)에 비해 CPU 시간이 2.5배- 4배 낮은 4개의 columnstore 및 6개의 rowstore 인덱스 집합을 권장합니다. 이는 단일 쿼리에 대해서도 rowstore 및 columnstore 인덱스로 구성된 혼합 물리적 디자인의 이점을 보여 줍니다.

rowstore 인덱스 권장 사항의 효율성: 두 번째 및 세 번째 막대 집합(고객 1 쿼리 2 및 고객 2 쿼리 1에 해당)은 쿼리에 적합한 rowstore 인덱스의 이점을 활용하는 선택적 필터 조건자가 있는 경우입니다. 이러한 쿼리의 경우 DTA(rowstore에만 해당)와 DTA(rowstore + columnstore)는 rowstore 인덱스만 권장합니다. 또한 이러한 예제에서는 columnstore 인덱스를 추천하는 옵션을 사용하여 DTA를 호출하는 경우에도 비용 기반 접근 방식을 통해 워크로드가 실제로 이점을 얻을 수 있는 경우에만 columnstore 인덱스를 권장한다는 것을 보여 줍니다.

columnstore 인덱스 권장 사항의 효율성: Customer 2 쿼리 2에 해당하는 네 번째 막대 집합은 쿼리가 columnstore 인덱스의 이점을 얻을 수 있는 큰 테이블을 검색하는 경우를 나타냅니다. DTA(rowstore만 해당)는 columnstore 인덱스가 있는 시간에 비해 CPU 시간이 더 높은 권장 사항을 생성합니다. 따라서 DTA(rowstore + columnstore)는 columnstore 전용 옵션의 쿼리 실행 성능과 일치하는 적합한 columnstore 인덱스를 권장합니다.

여러 쿼리가 있는 워크로드에 대한 권장 사항의 효율성: Customer 2의 전체 워크로드에 해당하는 최종 막대 집합은 워크로드의 여러 쿼리를 분석하여 전체 워크로드의 실행 비용을 향상시킬 수 있는 적합한 rowstore 및 columnstore 인덱스 집합을 추천하는 DTA의 기능을 예로 들 수 있습니다. DTA(rowstore + columnstore)는 columnstore 인덱스만 빌드하는 옵션과 비교할 때 워크로드에 대한 크기가 향상되는 4개의 columnstore 인덱스와 수십 개의 rowstore 인덱스를 권장합니다. DTA와 비교할 때 약 4X-5X 개선(rowstore에만 해당).

요약하면 위의 예제에서는 SQL Server 데이터베이스 엔진에서 지원되는 rowstore 및 columnstore 인덱스를 적절히 활용하도록 DTA의 기능을 설명하고 워크로드의 CPU 시간을 크게 줄일 수 있는 적합한 인덱스 조합을 권장합니다.

참고 항목

데이터베이스 엔진 튜닝 관리자

DTA(데이터베이스 엔진 튜닝 관리자)의 Columnstore 인덱스 권장 사항

Columnstore 인덱스 가이드

데이터 웨어하우스용 Columnstore 인덱스

CREATE COLUMNSTORE INDEX(Transact-SQL)

CREATE INDEX(Transact-SQL)