http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
번역:박노철(maceo.park@gmail.com)
Q:SS2005 의 파티션 테이블에 쿼리를 날릴 때 어떤 종류의 병렬 계획이 나올 것이며 성능에는 어떤 영향을 미치는가?
A:먼저 파티셔닝에 대한 간략한 배경을 소개하겠다. SS2005의 테이블 파티셔닝은 관리와 가용성 측면에서 많은 향상을 가져왔다. 관리적 측면에서 보자면, 데이터 파티션에 대해서 메타데이터 스위치 인, 아웃을 가능케 했다(sliding window requirements를 지원한다. (뭔소리여? -_-)) 가용성 측면에는 온라인 인덱스 리빌드, 병렬 실행, 파일그룹의 piecemeal 리스토어가 가능하다.
이제 파티션 테이블이 성능에 어떤 영향을 미치는지 살펴보자. 파티셔닝을 사용하는지 아닌지에 관계없이 병렬 계획 선택여부는 CPU갯수, 쿼리 비용, 가용 메모리와 현재 workload 에 의해서 결정된다. 이 글에서 이야기하는 모든 것들은 병렬 계획이 가능할때만 유효한 것들이다.
쿼리가 하나의 파티션을 사용할 때 SS2005는 maxdop에 설정된 수치까지 여러개의 스레드를 사용해서 병렬로 데이터를 읽어올 수 있다. maxdop는 보통 시스템에 설치된 CPU갯수와 같은 값을 의미하는 0으로 설정되어 있다. 쿼리가 두개 이상의 파티션에서 데이터를 읽어올 때 파티션당 단 하나의 스레드만 사용될 수 있다.
만약 파티션 갯수가 maxdop와 같거나 더 적다면 데이터가 한쪽으로 치우침으로 인해서 CXPACKET 대기가 생길 수 있다. 파티션 갯수가 maxdop보다 크면 SS2005는 하나의 스레드가 특정 파티션에서 작업을 끝내면 자동적으로 다음 파티션으로 이동한다. 16개의 파티션을 가지고 있고 maxdop가 8이라면 첫 8개의 스레드는 파티션1-8에 대해서 작동한다. 작업이 끝난 첫번째 스레드는 파티션9에서 돌게 된다.
만약 8개 이상 CPU를 가진 기계라면, 최악의 경우는 두개의 파티션에 걸쳐있는 단일 SELECT문이다. (테이블1 참조) 테이블2에 노란색 강조 부분, 파티션 80,81과 Executes 컬럼의 스레드 숫자를 보면, 노란색 부분 이후의 단계에서 maxdop가 적용될 수 있음에도 불구하고 데이터를 읽어올 때는 파티션당 하나의 스레드만 돌아간다. (녹색 강조 참고)
Table 1: Retrieve 2 weeks of data
SELECT |
SUM(Sales_Qty) as Sales_Qty, |
SUM(Sale_Amt) as Sales_Amount |
FROM SalesDB.dbo.Tbl_Fact_ Sales – Partitioned by week |
WHERE date_id between '20050703' and '20050716' |
Table 2: Set Statistics Profile: MAXDOP = 12
Rows | Executes | StmtText |
1 | 1 | SELECT SUM([Sales_Qty]) [Sales_Qty],SUM([Sale_Amt]) [Sales_Amount] FROM [SalesDB].[dbo].[Tbl_Fact_Sales] WHERE [date_id]>=@1 AND [date_id]<=@2 |
0 | 0 | |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END, [Expr1003]=CASE WHEN [globalagg1012]=(0) THEN NULL ELSE [globalagg1014] END)) |
1 | 1 | |--Stream Aggregate(DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009]), [globalagg1012]=SUM([partialagg1011]), [globalagg1014]=SUM([partialagg1013]))) |
2 | 1 | |--Parallelism(Gather Streams) |
2 | 12 | |--Stream Aggregate(DEFINE:([partialagg1007]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1009]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1011]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt]), [partialagg1013]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt]))) |
20577235 | 12 | |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006])) |
2 | 12 | |--Parallelism(Distribute Streams, Demand Partitioning) |
2 | 1 | | |--Constant Scan(VALUES:(((80)),((81)))) |
20577235 | 2 | |--Index Seek(OBJECT:([SalesDB].[dbo].[Tbl_Fact_Sales].[IX_Tbl_Fact_Sales_SKDteItmStrIDSalQtySalAmtDiscMkd] AS [ss]), SEEK:([ss].[SK_Date_ID] >= (20050703) AND [ss].[SK_Date_ID] <= (20050716)) ORDERED FORWARD PARTITION ID:([PtnIds1006])) |
예를 들어 월단위로 파티션된 테라바이트급 매출 테이블이 있다고 하자. 일반적인 쿼리 패턴이라면, 이달과 전달 또는 이달과 1년전을 비교할 것이다.
Table 3: MONTHLY Partitions
WHERE clause | Partitions | Retrieval Parallelism | |
Single SELECT statement | SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’ | 2 | 1 thread per partition * |
SELECT [ | Select …. Where DateCol between ’10/1/2005’ and ‘10/31/2005 Select …. Where DateCol between ’11/1/2005’ and ‘11/30/2005 | 1 per select | MAXDOP per partition |
Table 4: WEEKLY Partitions: Sales for November 1-15
WHERE clause | Partitions | Retrieval Parallelism | |
Single SELECT statement | SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/15/2005’ | 3 | 1 thread per partition * |
SELECT [ | SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/5/2005’ SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/6/2005’ and ‘11/12/2005’ SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/13/2005’ and ‘11/15/2005’ | 1 per select | MAXDOP per partition |
비록 관리나 가용성 측면에서 어려움이 있긴 하지만 거대한 단일 테이블이 성능상 더 좋은 경우도 있다. 예를 들어 1TB 파티션 테이블에 두개의 파티션을 읽어야 하는 쿼리가 있다고 하면, SS2005는 파티션당 1개의 스레드만 할당한다. 하지만 SS2005는 1TB짜리 단일 테이블에 대해서는 maxdop를 적용하여 데이터를 읽어온다.
Table 5: Monolithic BigSalesTable
WHERE clause | Partitions | Retrieval Parallelism | |
Single SELECT statement | SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’ | N/A | MAXDOP |
파티션과 병렬 실행의 퍼포먼스에 관한 위의 커멘트는 DW, 배치 프로세싱, 리포팅에 적용가능하다. 모든 DW가 병렬 쿼리를 허용하지는 않을 것이다. 병렬 계획은 시스템에 실행되는 쿼리가 몇개 없고 실행 시간을 최소화 하기 위해 가능한 많은 리소스를 사용하기를 위할 때 가장 효과적이다. 만약 DW가 이미 동시성이 매우 높은 환경이라면, 병렬 계획은 throughput이나 응답 시간을 향상시켜주지 않을 것이다. 이미 수많은 단일 스레드 쿼리들이 가용 자원을 대부분 소비하고 있을 것이기 때문이다. 최고의 퍼포먼스를 위해서, 동시성이 높은 OLTP시스템에서도 병렬 계획을 원하지는 않을 것이다.
파티셔닝 정밀도(일별, 주별, 월별)를 결정할 때 사용자들의 일반적인 쿼리 패턴을 고려해야 하며 CPU8개 이상의 시스템에서 최고의 성능을 위해서는 최소한 maxdop개의 파티션을 사용하도록 해야 한다. 테이블 3,4에서 보여준바와 같이 SQL문을 여러개의 단일 파티션 쿼리로 재작성해야 최고의 성능을 얻을 수 있다.
Posted by maceo
즉 넣고 빼는데 시간이 걸리지 않으며 스크립팅도 가능해졌지요. '최신 1년치 데이터만 유지' 같은 것도 굉장히 편해졌으니 말입니다.
