분할된 테이블 및 인덱스에서의 향상된 쿼리 처리
SQL Server 2008에서는 여러 병렬 계획에 대해 분할된 테이블에서의 쿼리 처리 성능을 향상시키고, 병렬 및 직렬 계획이 표시되는 방식을 변경하고, 컴파일 시간 및 런타임 실행 계획에 제공되는 분할 정보를 개선합니다. 이 항목에서는 이러한 향상된 기능에 대해 설명하고, 분할된 테이블 및 인덱스의 쿼리 실행 계획을 해석하는 방법에 대해 안내하며, 분할된 개체에서의 쿼리 성능 향상을 위한 최선의 방법을 알려 줍니다.
[!참고]
분할된 테이블 및 인덱스는 SQL Server Enterprise, Developer 및 Evaluation Edition에서만 지원됩니다.
새 파티션 인식 Seek 연산
SQL Server 2008에서는 분할된 테이블의 내부 표현이 변경되므로 선행 열로 PartitionID가 있는 여러 열로 구성된 인덱스가 되기 위해 해당 테이블이 쿼리 프로세서에 표시됩니다. PartitionID는 특정 행을 포함하는 파티션의 ID를 나타내기 위해 내부적으로 사용되는 숨겨진 계산 열입니다. 예를 들어 T(a, b, c)로 정의되는 T 테이블은 a 열에서 분할되고 b 열에 클러스터형 인덱스가 있다고 가정합니다. SQL Server 2008에서 이렇게 분할된 테이블은 내부적으로 T(PartitionID, a, b, c) 스키마와 복합 키(PartitionID, b)에 클러스터형 인덱스가 있는 분할되지 않은 테이블로 취급됩니다. 이로 인해 쿼리 최적화 프로그램은 분할된 테이블 또는 인덱스에서 PartitionID를 기준으로 seek 연산을 수행할 수 있습니다.
이제 파티션 제거는 이 seek 연산에서 수행됩니다.
또한 쿼리 최적화 프로그램은 확장되므로 seek 또는 scan 연산이 한 가지 조건을 사용하여 PartitionID(논리적 선행 열)과 가능한 다른 인덱스 키 열에서 수행된 후, 두 번째 수준의 seek 연산은 다른 조건을 사용하여 첫 번째 수준의 seek 연산에 대한 제한을 충족시키는 각각의 고유 값에 대해 수행될 수 있습니다. 즉 skip scan이라고 하는 이 연산을 통해 쿼리 최적화 프로그램은 seek 또는 scan 연산을 하나의 조건을 기준으로 수행하여 액세스할 파티션을 결정하고 해당 연산자에서 두 번째 수준 index seek 연산을 수행하여 다른 조건을 충족시키는 이러한 파티션에서 행을 반환할 수 있습니다. 예를 들어 다음 쿼리를 살펴보십시오.
SELECT * FROM T WHERE a < 10 and b = 2;
이 예에서 T(a, b, c)로 정의되는 T 테이블은 a 열에서 분할되고 b 열에 클러스터형 인덱스가 있다고 가정합니다. T 테이블의 파티션 경계는 다음 파티션 함수로 정의됩니다.
CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);
쿼리를 해결하려면 쿼리 프로세서는 첫 번째 수준 seek 연산을 수행하여 T.a < 10 조건을 충족시키는 행을 포함하는 모든 파티션을 찾습니다. 이 작업을 통해 액세스할 파티션을 식별합니다. 식별된 각 파티션에서 쿼리 프로세서는 b 열에서 클러스터형 인덱스로 두 번째 수준 seek 연산을 수행하여 T.b = 2 및 T.a < 10 조건을 충족시키는 행을 찾습니다.
다음 그림은 skip scan 연산을 논리적으로 표현한 것으로 a 및 b 열의 데이터와 함께 T 테이블을 보여 줍니다. 파티션은 세로줄 파선으로 표시되는 파티션 경계와 함께 1에서 4까지 번호가 매겨져 있습니다. 파티션에 대한 첫 번째 수준 seek 연산(그림에는 표시되지 않음)에서는 파티션 1, 2 및 3이 a 열에 테이블 및 조건자에 대해 정의된 분할로 포함된 검색 조건을 충족시키는지 확인했습니다. 즉 T.a < 10입니다. skip scan 연산의 두 번째 수준 seek 부분에 의해 이동된 경로는 곡선으로 표시됩니다. 기본적으로 skip scan 연산은 b = 2 조건을 충족시키는 행을 이러한 파티션마다 검색합니다. skip scan 연산의 총 비용은 세 가지 개별 index seek 연산의 총 비용과 같습니다.
.gif "skip scan 연산 표시")
쿼리 실행 계획의 분할 정보 표시
분할된 테이블 및 인덱스에서의 쿼리 실행 계획은 Transact-SQL SET 문인 SET SHOWPLAN_XML 또는 SET STATISTICS XML을 사용하거나 SQL Server Management Studio의 그래픽 실행 계획 출력을 사용하여 검사할 수 있습니다. 예를 들어 쿼리 편집기 도구 모음에서 예상 실행 계획 표시를 클릭하여 컴파일 시간 실행 계획을 표시할 수 있고 실제 실행 계획 포함을 클릭하여 런타임 계획을 표시할 수 있습니다.
이러한 도구를 사용하여 다음 정보를 확인할 수 있습니다.
분할된 테이블 또는 인덱스에 액세스하는 scan, seek, insert, update, merge 및 deletes 연산
쿼리에 의해 액세스되는 파티션. 예를 들어 액세스한 총 파티션 수 및 액세스한 근접 파티션의 범위가 런타임 실행 계획에서 사용할 수 있습니다.
한 개 이상의 파티션에서 데이터를 검색하기 위해 skip scan 연산이 seek 또는 scan 연산에서 사용되는 경우
실행 계획 표시에 대한 자세한 내용은 실행 계획 방법 도움말 항목을 참조하십시오.
향상된 파티션 정보 기능
SQL Server 2008에서는 컴파일 시간과 런타임 실행 계획 모두에 대한 향상된 분할 정보를 제공합니다. 실행 계획은 현재 다음 정보를 제공합니다.
분할된 테이블에서 수행된 seek, scan, insert, update, merge, delete 등의 연산자를 나타내는 선택적인 Partitioned 특성.
PartitionID에서 범위 검색을 지정하는 선행 인덱스 키 열 및 필터 조건으로 PartitionID를 포함하는 새로운 SeekPredicateNew 요소와 SeekKeys 하위 요소. SeekKeys 하위 요소 두 개의 존재는 PartitionID에서 skip scan 연산이 사용되는 것을 나타냅니다.
액세스한 총 파티션 수를 제공하는 요약 정보. 이 정보는 런타임 계획에서만 사용할 수 있습니다.
그래픽 실행 계획 출력 및 XML 실행 계획 출력 모두에 이 정보가 표시되는 방법을 보여 주려면 분할된 테이블 fact_sales의 다음 쿼리를 살펴봅니다. 이 쿼리는 두 개의 파티션에 있는 데이터를 업데이트합니다.
UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;
다음 그림에서는 이 쿼리에 대해 컴파일 시간 실행 계획의 Clustered Index Seek 연산자 속성을 보여 줍니다. fact_sales 테이블의 정의 및 파티션 정의를 보려면 이 항목의 “예”를 참조하십시오.
.gif "실행 계획 출력의 파티션 정보")
Partitioned 특성
Index Seek와 같은 연산자가 분할된 테이블 또는 인덱스에서 실행되면 Partitioned 특성이 컴파일 시간 및 런타임 계획에 나타나고 이 특성이 True(1)로 설정됩니다. 이 특성이 False(0)로 설정되면 표시되지 않습니다.
Partitioned 특성은 다음 물리적 및 논리적 연산자에서 나타날 수 있습니다.
Table Scan
Index Scan
Index Seek
Insert
Update
Delete
Merge
앞의 그림과 같이 이 특성은 자신이 정의된 연산자의 속성에서 표시됩니다. XML 실행 계획 출력에서 이 특성은 자신이 정의된 연산자의 RelOp 노드에서 **Partitioned="1"**로 표시됩니다.
새 검색 조건자
XML 실행 계획 출력에서 SeekPredicateNew 요소는 자신이 정의된 연산자에 표시됩니다. 이 출력에는 SeekKeys 하위 요소를 2개까지 포함할 수 있습니다. 첫 번째 SeekKeys 항목은 논리적 인덱스의 파티션 ID 수준에 첫 번째 수준 seek 연산을 지정합니다. 즉 이 seek 연산에서는 쿼리 조건을 충족시키기 위해 액세스해야 하는 파티션을 결정합니다. 두 번째 SeekKeys 항목은 첫 번째 수준 seek 연산자에서 식별된 각 파티션에서 발생하는 skip scan 연산의 두 번째 수준 seek 부분을 지정합니다.
파티션 요약 정보
런타임 실행 계획에서 파티션 요약 정보에는 액세스한 파티션의 수 및 액세스한 실제 파티션의 식별이 제공됩니다. 이 정보를 사용하여 쿼리에서 액세스한 올바른 파티션을 확인하면 모든 다른 파티션이 고려 대상에서 제외됩니다.
제공되는 정보는 실제 파티션 수 및 액세스한 파티션입니다.
실제 파티션 수는 쿼리에 의해 액세스되는 총 파티션의 수입니다.
XML 실행 계획 출력에서 액세스한 파티션은 자신이 정의된 연산자의 RelOp 노드에서 새로운 RuntimePartitionSummary 요소에 나타나는 파티션 요약 정보입니다. 다음 예에서는 액세스한 총 2개의 파티션(파티션 2와 3)을 나타내는 RuntimePartitionSummary 요소의 내용을 보여 줍니다.
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="2" >
<PartitionRange Start="2" End="3" />
</PartitionsAccessed>
</RunTimePartitionSummary>
다른 실행 계획 방법을 사용하여 파티션 정보 표시
실행 계획 방법인 SHOWPLAN_ALL, SHOWPLAN_TEXT 및 STATISTICS PROFILE은 다음과 같은 경우를 제외하고 이 항목에서 설명한 파티션 정보를 보고하지 않습니다. SEEK 조건자의 일부로써 액세스할 파티션이 파티션 ID를 나타내는 계산 열에서 범위 조건자로 식별됩니다. 다음 예에서는 Clustered Index Seek 연산자에 대한 SEEK 조건자를 보여 줍니다. 파티션 2와 3에 액세스하고 seek 연산자는 date_id BETWEEN 20080802 AND 20080902 조건을 충족시키는 행에 필터를 설정합니다.
|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),
SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)
AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)
AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))
ORDERED FORWARD)
분할된 힙의 실행 계획 해석
SQL Server 2008에서는 분할된 힙이 파티션 ID의 논리적 인덱스로 취급됩니다. 분할된 힙에서의 파티션 제거는 파티션 ID의 SEEK 조건자를 사용하는 Table Scan 연산자로 실행 계획에 나타납니다. 다음 예는 제공된 실행 계획 정보를 보여 줍니다.
|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)
콜러케이션된 조인의 실행 계획 해석
같거나 상응하는 파티션 함수를 사용하여 두 개의 테이블이 분할되고 조인의 양쪽 면에서 분할 열을 사용하여 쿼리의 조인 조건에 되면 조인 콜러케이션이 발생합니다. 쿼리 최적화 프로그램은 동일한 파티션 ID가 있는 각 테이블의 파티션이 개별적으로 조인되는 계획을 생성할 수 있습니다. 콜러케이션된 조인은 메모리와 처리 시간이 덜 필요로 할 수 있으므로, 콜러케이션된 조인이 콜러케이션되지 않은 조인보다 더 빠를 수 있습니다. 쿼리 최적화 프로그램에서는 비용 예측을 기준으로 콜러케이션되지 않은 계획 또는 콜러케이션된 계획을 선택합니다.
콜러케이션된 계획에서 Nested Loops 조인은 내부 측면에서 조인된 테이블 또는 인덱스 파티션을 하나 이상 읽습니다. Constant Scan 연산자의 숫자는 파티션 번호를 나타냅니다.
콜러케이션된 조인에 대한 병렬 계획을 분할된 테이블 또는 인덱스에 대해 생성하면 Constant Scan 및 Nested Loops 조인 연산자 사이에 Parallelism 연산자가 표시됩니다. 이 경우 조인 외부 측면의 여러 스레드가 각각 서로 다른 파티션을 읽고 작업합니다.
다음 그림은 콜러케이션된 조인에 대한 병렬 쿼리 계획을 보여 줍니다.
.gif "같은 위치에 배치된 조인 실행 계획")
분할된 개체에 대한 병렬 쿼리 실행 전략
쿼리 프로세서에서는 분할된 개체에서 선택하는 쿼리에 대해 병렬 실행 전략을 사용합니다. 실행 전략의 일부로 쿼리 프로세서에서는 쿼리에 필요한 테이블 파티션과 각 파티션에 할당할 스레드 수를 결정합니다. 대부분의 경우 쿼리 프로세서는 동일하거나 거의 동일한 수의 스레드를 각 파티션에 할당한 다음 전체 파티션에서 병렬로 쿼리를 실행합니다. 다음 단락에서는 스레드 할당에 대해 자세히 설명합니다.
스레드 수가 파티션 수보다 적은 경우 쿼리 프로세서는 각 스레드를 서로 다른 파티션에 할당합니다. 따라서 처음에는 하나 이상의 파티션에 스레드가 할당되지 않습니다. 파티션에서 스레드가 실행을 완료하면 쿼리 프로세서는 각 파티션에 하나의 스레드가 할당될 때까지 이 스레드를 다음 파티션에 할당합니다. 이런 경우에만 쿼리 프로세서가 스레드를 다른 파티션에 다시 할당합니다.
.gif "완료 후 재할당된 스레드 표시")
스레드 수가 파티션 수와 같은 경우 쿼리 프로세서는 각 파티션에 하나의 스레드를 할당합니다. 이때 스레드가 완료되어도 다른 파티션에 다시 할당되지 않습니다.
.gif "각 파티션에 할당된 하나의 스레드 표시")
스레드 수가 파티션 수보다 많은 경우 쿼리 프로세서는 각 파티션에 동일한 수의 스레드를 할당합니다. 스레드 수가 파티션 수의 정확한 배수가 아닌 경우 쿼리 프로세서는 사용 가능한 스레드를 모두 사용하기 위해 일부 파티션에 하나의 스레드를 추가로 할당합니다. 파티션이 하나뿐인 경우 해당 파티션에 모든 스레드가 할당됩니다. 아래 다이어그램에서는 4개의 파티션과 14개의 스레드가 있습니다. 이 경우 각 파티션에 3개의 스레드가 할당되고 총 14개의 스레드를 할당하기 위해 2개의 파티션에 추가 스레드가 하나씩 할당됩니다. 이때 스레드가 완료되어도 다른 파티션에 다시 할당되지 않습니다.
.gif "각 파티션에 할당된 여러 스레드 표시")
위의 예에서는 스레드를 할당하는 간단한 방법을 제시하지만 실제 전략은 더욱 복잡하며 쿼리 실행 시 발생하는 다른 변수들을 고려해야 합니다. 예를 들어 테이블이 분할되어 있고 테이블의 A 열에 클러스터형 인덱스가 있으며 쿼리에 조건자 절 WHERE A IN (13, 17, 25)가 있는 경우 쿼리 프로세서는 각 테이블 파티션이 아니라 이러한 3개의 검색 값(A=13, A=17 및 A=25))에 각각 하나 이상의 스레드를 할당합니다. 이러한 값을 포함하는 파티션에서만 쿼리를 실행하면 되며 이러한 검색 조건자가 모두 동일한 파티션에 있는 경우 모든 스레드가 동일한 테이블 파티션에 할당됩니다.
또 다른 예로 테이블의 A 열에 경계 지점이 (10, 20, 30)인 파티션이 4개 있고 B 열에 인덱스가 있으며 쿼리에 조건자 절 WHERE B IN (50, 100, 150)이 있다고 가정합니다. 이 테이블 파티션은 A 값을 기반으로 하므로 B 값은 임의의 테이블 파티션에서 발생할 수 있습니다. 따라서 쿼리 프로세서는 4개의 각 테이블 파티션에서 3개의 B 값(50, 100, 150)을 각각 검색합니다. 쿼리 프로세서는 이러한 12개의 각 쿼리 검색을 병렬로 실행할 수 있도록 스레드를 균형 있게 할당합니다.
A 열을 기반으로 하는 테이블 파티션 |
각 테이블 파티션에서 B 열 검색 |
|---|---|
테이블 파티션 1: A < 10 |
B=50, B=100, B=150 |
테이블 파티션 2: A >= 10 및 A < 20 |
B=50, B=100, B=150 |
테이블 파티션 3: A >= 20 및 A < 30 |
B=50, B=100, B=150 |
테이블 파티션 4: A >= 30 |
B=50, B=100, B=150 |
최상의 방법
분할된 대형 테이블과 인덱스에서 많은 양의 데이터에 액세스하는 쿼리의 성능을 향상시키려면 다음과 같은 최상의 방법을 권장합니다.
여러 디스크 간에 각 파티션을 스트라이프합니다.
가능하면 충분한 주 메모리가 있는 서버를 사용하여 자주 액세스하는 파티션이나 모든 파티션을 메모리 크기에 맞춰서 I/O 비용을 줄입니다.
쿼리한 데이터가 메모리 크기에 맞지 않을 경우 해당 테이블과 인덱스를 압축합니다. 이렇게 하면 I/O 비용이 줄어듭니다.
빠른 프로세서와 가능한 많은 프로세서 코어가 장착된 서버를 사용하여 병렬 쿼리 처리 기능을 이용합니다.
서버에 충분한 I/O 컨트롤러 대역폭이 있는지 확인합니다.
모든 분할된 대형 테이블에 클러스터형 인덱스를 만들어 B-트리 검색 최적화를 이용합니다.
분할된 데이터로 데이터를 대량 로드할 경우 백서에서 최상의 권장 방법인 "분할된 테이블에 대량 데이터 로드(Loading Bulk Data into a Partitioned Table)"를 따르십시오.
예
다음 예에서는 7개의 파티션이 있는 단일 테이블이 포함된 테스트 데이터베이스를 만듭니다. 이 예에서 쿼리를 실행할 때 이전에 설명한 도구를 사용하여 컴파일 시간과 런타임 계획 모두에 대한 분할 정보를 확인합니다.
[!참고]
이 예에서는 백만 개 이상의 행을 테이블로 삽입합니다. 이 예를 실행하는 데에는 하드웨어에 따라 시간이 몇 분 정도 걸릴 수 있습니다. 이 예를 실행하기 전에 1.5GB 이상의 사용 가능한 디스크 공간이 있는지 확인하십시오.
USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact]
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int,
quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO