파티션 테이블 - 문제점, 주의 사항

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale

23 October 2012

by Dmitri Korotkevitch

 

http://www.simple-talk.com/sql/database-administration/partitioned-tables,-indexes-and-execution-plans-a-cautionary-tale/

 

해당 아티클이 파티션 테이블의 이슈 사항을 잘 정리해 주어서 간단 번역함.

 

 

 

 

SQL Server 2005 에서 Partitioned Tables 기능이 나왔을때 우리는 기대를 했다.
많은 데이터를 최소한의 locking 으로 Switch  할 수 있을 것이라고, 그리고 쿼리 성능에도 좋은 영향을 미칠 것이라고..

그러나 무료 제공하는 점심 같은것은 없었다.  파티션 되면서 더 많은 저장 공간이 요구되었다.

Storage space increase

파티션을에 대한 계획을 할 때 꼭, 클러스터된 인덱스 key가 포함되어야 합니다. 불행이도 파티션되어야 하는 key가 고유한 인덱스가 아닐 수도 있습니다. 이 해결책은 클러스터된 인덱스에 포함시키는 방법입니다.

non-clustered index는 clustered index 의 key point를 가지고 있습니다. 파티션 테이블은 일반적으로 대형이며, 클러스터 된 인덱스 키에 새 열을 추가하면, non-clustered index의 모든 레코드가 증가됩니다.

예를 들어  500,000,000 테이블에  clustered index 에 8 byte 날짜 열이 추가된다고 가정해 보면   
500000000 * 8/1024/1024/1024 =  3.75GB 인덱스당 저장공간이 증가됩니다.

 

이 수치는 스토리지 오버헤드의 저장공간은 포함되지 않습니다.
이 증가된 용량은 백업 시간, 복구 시간도 증가하게되며, 색인 유지 보수 비용도 증가합니다.

불행하게도 이 작업에 대한 해결책은 존재하지 않습니다.

데이터가 13개월을 가지고 있고 12개월 데이터를 select 를 매년 초에 해야 한다고 한다면 당연히 날짜 Data가 파티션 key에 존재해야 하며, 파티션이 슬라이등 되어야 합니다. 그러나, 오히려 작은 단위로 데이터를 삭제 하는것이 더 효율 적입니다.

기존 테이블을 파티션 하기 위해 clustered index에 열을 추가하는 것도 쉽지 않습니다.
그나마 대안으로는 indentity 컬럼이 있을 경우 해당 월이 시작되는 값을 알아내어, 그 key로 파티션 하는 것입니다.
저장 공간 뿐 아니라, 실행계획에 대해서도 살펴봐야 합니다.

 

 

 

Suboptimal execution plans

 

파티셔닝을 구현할 때, 물리적 데이터 레이아웃이 변경됨으로 퀴러의 실행 계획이 예기치 못한 결과를 나타낼 수도 있습니다.
그림 참고)

Listing 1 shows the structure of the Orders table, with clustered index.

USE TestDB
GO
CREATE TABLE dbo.Orders
    (
      Id INT NOT NULL ,
      OrderDate DATETIME NOT NULL ,
      DateModified DATETIME NOT NULL ,
      Placeholder CHAR(500)
        NOT NULL
        CONSTRAINT Def_Data_Placeholder DEFAULT 'Placeholder',
    );
GO

CREATE UNIQUE CLUSTERED INDEX IDX_Orders_Id
ON dbo.Orders(ID);
GO

Listing 1: The Orders table structure

 

Listing 2 inserts 524,288 records into the Orders table and creates a non-clustered index.

DECLARE @StartDate DATETIME = '2012-01-01';

WITH    N1 ( C )
          AS ( SELECT   0
               UNION ALL
               SELECT   0
             )-- 2 rows
,       N2 ( C )
          AS ( SELECT   0
               FROM     N1 AS T1
                        CROSS JOIN N1 AS T2
             )-- 4 rows
,       N3 ( C )
          AS ( SELECT   0
               FROM     N2 AS T1
                        CROSS JOIN N2 AS T2
             )-- 16 rows
,       N4 ( C )
          AS ( SELECT   0
               FROM     N3 AS T1
                        CROSS JOIN N3 AS T2
             )-- 256 rows
,       N5 ( C )
          AS ( SELECT   0
               FROM     N4 AS T1
                        CROSS JOIN N4 AS T2
             )-- 65,536 rows
,       N6 ( C )
          AS ( SELECT   0
               FROM     N5 AS T1
                        CROSS JOIN N2 AS T2
                        CROSS JOIN N1 AS T3
             )-- 524,288 rows
,       IDs ( ID )
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL
                                                     ) )
               FROM     N6
             )
    INSERT  INTO dbo.Orders
            ( ID ,
              OrderDate ,
              DateModified
            )
            SELECT  ID ,
                    DATEADD(second, 35 * ID, @StartDate) ,
                    CASE WHEN ID % 10 = 0
                     THEN DATEADD(second,
                                  24 * 60 * 60 * ( ID % 31 ) + 11200 + ID
                                      % 59 + 35 * ID, @StartDate)
                         ELSE DATEADD(second, 35 * ID, @StartDate)
                    END
            FROM    IDs;
GO  

CREATE UNIQUE NONCLUSTERED INDEX IDX_Orders_DateModified_Id
ON dbo.Orders(DateModified, Id);

 

DECLARE @LastDateModified DATETIME = '2012-06-25';

SELECT TOP 100
        ID ,
        OrderDate ,
        DateModified ,
        PlaceHolder
FROM    dbo.Orders
WHERE   DateModified > @LastDateModified
ORDER BY DateModified ,
        Id;

Listing 3: Query that selects the batch of the records

 

 

 

 

 

DROP INDEX IDX_Orders_DateModified_Id ON dbo.Orders;
DROP INDEX IDX_Orders_Id ON dbo.Orders;
GO

CREATE PARTITION FUNCTION pfOrders(DATETIME)
AS RANGE RIGHT FOR VALUES 
('2012-02-01', '2012-03-01',
'2012-04-01','2012-05-01','2012-06-01',
'2012-07-01','2012-08-01');
GO

CREATE PARTITION SCHEME psOrders 
AS PARTITION pfOrders
ALL TO ([primary]);
GO

CREATE UNIQUE CLUSTERED INDEX IDX_Orders_OrderDate_Id
ON dbo.Orders(OrderDate,ID)
ON psOrders(OrderDate);
GO

CREATE UNIQUE INDEX IDX_Data_DateModified_Id_OrderDate
ON dbo.Orders(DateModified, ID, OrderDate)
ON psOrders(OrderDate);
GO

Listing 4: Table partitioning code

 

 

 

 

 

 

 

 

 

Figure 6 shows how SQL Server actually processes the query.

 파티션되지 않은 테이블은 top 100 조회하면 처음 나오는 값 기준으로 정렬없이 100개를 가져오니까 비교적 효율적.
 만약 파티셔닝을 구현 한다며 key가 변경되고 정렬되어 있는 것 중에 모두 데이터를 찾아야 한다. 즉, 파티션이 8개 되었다고 한다면 100의 데이터가 있는지 보다 파티션 별로 데이터를 찾아서 정렬한다.
물론, 힌트로 고정할 수도 있지만 예상치 않은 결과 이다.

 

 

 

 

Fixing the problem

그림 참고)

 

 

ECLARE @LastDateModified DATETIME = '2012-06-25';

SELECT TOP 100
        ID ,
        OrderDate ,
        DateModified ,
        PlaceHolder
FROM    dbo.Orders
WHERE   DateModified > @LastDateModified
        AND $partition.pfOrders(OrderDate) = 5
ORDER BY DateModified ,
        ID;

 

간혹 이상할 경우 많이 사용하는 방법인데 파티션 범위를 확인하고 지정 파티션을 정해 주는 것이다. (파티션 된 테이블을 조건에 해당하는 데이터를 삭제 할 경우 top 10개로.. 등. 이관시 꼭 실행계획을 확인하고 해당 파티션만 찾게 범위를 지정해 줘야 하는 것이 이 이유이다. )
그러려면 파티션의 총 수를 알아야 한다..  ( ys.partition_range_values)

 

DECLARE @LastDateModified DATETIME = '2012-06-25' ,
    @BoundaryCount INT; 
   
SELECT  @BoundaryCount = MAX(boundary_id) + 1
FROM    sys.partition_functions pf
        JOIN sys.partition_range_values prf
                  ON pf.function_id = prf.function_id
WHERE   pf.name = 'pfOrders';

WITH    Boundaries ( boundary_id )
          AS ( SELECT   1
               UNION ALL
               SELECT   boundary_id + 1
               FROM     Boundaries
               WHERE    boundary_id < @BoundaryCount
             )
    SELECT  part.ID ,
            part.OrderDate ,
            part.DateModified ,
            $partition.pfOrders(part.OrderDate) AS [Partition Number]
    FROM    Boundaries b
            CROSS APPLY ( SELECT TOP 100
                                    ID ,
                                    OrderDate ,
                                    DateModified
                          FROM      dbo.Orders
                          WHERE     DateModified > @LastDateModified
                                    AND $Partition.pfOrders(OrderDate) =
                                                           b.boundary_id
                          ORDER BY  DateModified ,
                                    ID
                        ) part;

Listing 8: Implementation of Step 1 of "Ideal execution plan"

 

Dealing with Cardinality Estimation Errors

 

윗 부분 처럼하면 경계값 에러가 발생할 수 있다. 개선책.
tempdb를 사용한다.

 

CREATE TABLE #T
    (
      ID INT NOT NULL
             PRIMARY KEY
    );

DECLARE @LastDateModified DATETIME = '2012-06-25' ,
    @BoundaryCount INT;

SELECT  @BoundaryCount = MAX(boundary_id) + 1
FROM    sys.partition_functions pf
        JOIN sys.partition_range_values prf
                  ON pf.function_id = prf.function_id
WHERE   pf.name = 'pfOrders';

WITH    Boundaries ( boundary_id )
          AS ( SELECT   1
               UNION ALL
               SELECT   boundary_id + 1
               FROM     Boundaries
               WHERE    boundary_id < @BoundaryCount
             )
    INSERT  INTO #T
            ( ID
            )
            SELECT  boundary_id
            FROM    Boundaries;
    
WITH    Top100 ( ID, OrderDate, DateModified )
          AS ( SELECT TOP 100
                        part.ID ,
                        part.OrderDate ,
                        part.DateModified
               FROM     #T b
                        CROSS APPLY ( SELECT TOP 100
                                                ID ,
                                                OrderDate ,
                                                DateModified
                                      FROM      dbo.Orders
                                      WHERE     DateModified >
                                                   @LastDateModified
                                     AND $Partition.pfOrders(OrderDate) =
                                                    b.id
                                      ORDER BY  DateModified ,
                                                ID
                                    ) part
               ORDER BY part.DateModified ,
                        part.ID
             )
    SELECT  d.Id ,
            d.OrderDate ,
            d.DateModified ,
            d.Placeholder
    FROM    dbo.Orders d
            JOIN Top100 t ON d.Id = t.Id
                             AND d.OrderDate = t.OrderDate
    ORDER BY d.DateModified ,
            d.ID;

DROP TABLE #T;

Listing 10: Using a temporary table in order to improve cardinality estimation

Figure 11: Using temporary table in order to improve cardinality estimation

 

 

Hardcoding the number of Partitions
그림 참고)

 

ECLARE @LastDateModified DATETIME = '2012-06-25';

WITH    Boundaries ( boundary_id )
          AS ( SELECT V.v
               FROM   ( VALUES ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7),
                      ( 8) ) AS V ( v )
             ),
        Top100 ( ID, OrderDate, DateModified )
          AS ( SELECT TOP 100
                        part.ID ,
                        part.OrderDate ,
                        part.DateModified
               FROM     Boundaries b
                        CROSS APPLY ( SELECT TOP 100
                                                ID ,
                                                OrderDate ,
                                                DateModified
                                      FROM      dbo.Orders
                                      WHERE     DateModified >
                                                   @LastDateModified
                                     AND $Partition.pfOrders(OrderDate) =
                                                    b.boundary_id
                                      ORDER BY  DateModified ,
                                                ID
                                    ) part
               ORDER BY part.DateModified ,
                        part.ID
             )
    SELECT  d.Id ,
            d.OrderDate ,
            d.DateModified ,
            d.Placeholder
    FROM    dbo.Orders d
            JOIN Top100 t ON d.Id = t.Id
                             AND d.OrderDate = t.OrderDate
    ORDER BY d.DateModified ,
            d.ID;

Listing 11: Hardcode static number of partitions in order to improve cardinality estimation

Figure 12: Hardcode static number of partitions in order to improve cardinality estimation

 

윗 부분 처럼 하면 사용하고자 하는 메모리보다 더 많은 메모리를 사용하게 된다.  ( 일부 파티션에 데이터가 없는 경우)
파티션 수를 하드 코딩 한다. -> 파티션을 추가 되었을 경우 수정해야 한다.    이론.. ㅜㅜ;;

 

 

Summary

 

테이블 파티셔닝은 다양한 디자인과 성능 문제에 도움을 줄 수 있는 훌륭한 기능이지만, 또한 문제 집합을 만 들 수도 있습니다.
clustered index에 파티션 열을 추가하면 저장공간 및 인덱스 유지 보수 비용이 증가합니다.
다른 물리적 데이터 레이아웃 변경이 될 경우 실행 계획이 변화 될 수 있습니다. 이런 부작용이 있다는것을 알고 적용하기 전에 꼭 Test 해봐야 합니다.

 

==> 그래서 처음 설계 부터 파티션 하게 하거나, 자주 조회되지 않은 과거 테이블이나 이력 테이블을 적용하고 있고
또는 키를 변경하기 보다는 기존 key의 범위로 파티션 key를 사용하고 있다, 물론 조회되는 중요 sp들의 조건 절을 확인한다.
기존 테이블을 파티셔닝 하는것은 항상 조심해야 한다.