김민석 : SQL Server 2005의 Partitioned table 강좌(1)

제 1강 : 파티션드 테이블

이름: 김민석 편지: MINSOUK@HOTMAIL.COM NHN WWW.NHNCORP.COM DBA

테이블을 만들고 데이터를 적제하고 어떻게하면 할당된 PGAE 에 많은 데이터를 넣어서 효율적으로 관리할까? 테이블 사이즈가 40G ~ 80G 가 되고 있는데, 풀스캔을 한번 타면 어떻게 될까? 생각만 해도 아찔한 순간입니다.. 모 사이트의 어떤서버를 통합하니 1 TERA 테이블이 생겼다. 인덱스 리빌딩을 해야 하는데... 이럴경우 관리자가 떠 안아야 할 부담은 거의 절정을 이루게된다. 이러한 대용량 테이블을 보다 효과적으로 관리하기 위해서 SQL 2005 에서는 PARTITIONED TABLE 이라는 기술을 선보이고 있습니다. 모두 아는 기능이고 해서 특별히 시간내기 어렵고, 기능을 실습해 보고 싶은 분을 위해서 간단하게 따라하기 식으로 글을 적어 보았습니다. 이미 다른 상용 DBMS 들은 다양한 테이블 분할방법 (RANGE PARTITIONED TABLE, LIST PARTITIONED TABLE, HASH PARTITIONED TABLE)을 선보이며, 실제로 운영하고 있습니다. 늦은 감이 없지는 않지만, 언제까지 2008년에 제품지원이 중단되는 2000만을 MSSQL 이라고 생각하고 있는 사람들도 우끼는 이야기 라고 생각합니다. 어떤 데이터베이스가 가장 멋진 데이터베이스가 아니라, 해당 제품을 가장 잘 핸들링 하는 사람이 있는 데이터베이스가 가장 좋은 데이터베이스 라고 개인적으로 생각합니다. SQL 2000 엔터프라이즈를 쓰면서 SCHEMA BINDING INDEXED VIEW 도 모르고, MSCS 도 써보지 않았다면?? 혹자는 어려워 못쓸 것이고, 혹자는 들어보지 못해서 못 쓸 것입니다. 간단하게 실습하고 긴요하게 쓰이길 바랍니다. 우리가 꿈꾸고 갈망하던 많은 부분이 MSSQL 2005 를 통해서 해결되어 가고 있고, 더해서 차기 활화산(카트마이)도 멋지게 타고 있으니 열심히 공부해 보도록 하자. 목차 PARTITIONED TABLE      PARTITIONED FUNCTION      PARTITIONED SCHEME      CREATE PARTITIONED TABLE      CHECK PARTITIONED INFORMATION      PARTITION MERGE      PARTITION SPLIT      PARTITION SWITCH EXAMPLE      TO IMPLEMENT AN AUTOMATIC SLIDING WINDOW IN A PARTITIONED TABLE ON SQL 2005      http://msdn2.microsoft.com/en-us/library/aa964122.aspx USE MASTER GO -- DROP DATABASE PDB -- GO /*----------------------------------------------------------------------- PARTITIONED TABLE       PARTITIONED FUNCTION       PARTITIONED SCHEME       CREATE PARTITIONED TABLE       CHECK PARTITIONED INFORMATION       PARTITION MERGE       PARTITION SPLIT       PARTITION SWITCH       * PARTITIONED FUNCTION 분할된 테이블 및 인덱스를 만드는 첫번째 단계로 CREATE PARTITION FUNCTION 이다. -------------------------------------------------------------------------*/ CREATE PARTITION FUNCTION PARTITION_FUNCTION_NAME ( INPUT_PARAMETER_TYPE ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ BOUNDARY_VALUE [ ,...N ] ] ) [ ; ] 기준이 있어야 나누지!! 어떤 기준으로 나눌지를 정의하는 단계이다. 예를 들어 나는 SEQUENCE 를 기준으로 나누기를 원한다라고 생각 했다면, CREATE PARTITION FUNCTION MYRANGEPF1 (INT) AS RANGE LEFT FOR VALUES (1, 100, 1000); 값 COL1 <= 1 COL1 > 1 AND COL1 <= 100 COL1 > 100 AND COL1 <= 1000 COL1 > 1000 CREATE PARTITION FUNCTION MYRANGEPF2 (INT) AS RANGE RIGHT FOR VALUES (1, 100, 1000); 값 COL1 < 1 COL1 >= 1 AND COL1 < 100 COL1 >= 100 AND COL1 < 1000 COL1 >= 1000 /************************* [질문] 내 맘데로 넣으면 넌 어떻게 할꺼야? **************************/ -- 실행 CREATE DATABASE PDB GO USE PDB GO CREATE PARTITION FUNCTION MYRANGEPF3 (INT) AS RANGE LEFT FOR VALUES ( 1, NULL,1000, 100,-100); GO -- DROP PARTITION FUNCTION MYRANGEPF3 결과 경고: 파티션 함수 'MYRANGEPF3'의 범위 값 목록이 값을 기준으로 정렬되어 있지 않습니다. CREATE PARTITION SCHEME 작업 동안 파일 그룹에 파티션을 매핑하면 함수 'MYRANGEPF3'이(가) CREATE PARTITION SCHEME에서 참조된 경우 정렬된 경계 값을 사용합니다. -- 확인 SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE           FUNCTION_ID IN                    (                    SELECT FUNCTION_ID                    FROM SYS.PARTITION_FUNCTIONS                    WHERE NAME = ('MYRANGEPF3')                    ) 결과 FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----------- 65536 1 1 NULL 65536 2 1 -100 65536 3 1 1 65536 4 1 100 65536 5 1 1000 똑똑한 SQL 서버는 이 값들을 정렬해서 순서를 재배열 하여 사용하게 된다. 또한 NULL 은 우선순위 최상으로 소팅되는 되는 것을 알수 있다. 음 나누다 보니 보기 이상한데 하나더 나누어 보고 싶다면 어떻게 할까? 바꾸어 보자! /* 구문정보 ALTER PARTITION FUNCTION PARTITION_FUNCTION_NAME() {     SPLIT RANGE ( BOUNDARY_VALUE )    | MERGE RANGE ( BOUNDARY_VALUE ) } [ ; ] */ 생성된 모든 파티션을 확인 부터 해보자. 개별 파티션 함수에 대한 정보를 얻으려면 SELECT * FROM SYS.PARTITION_FUNCTIONS             --(파티션 함수정보) SELECT * FROM SYS.PARTITION_PARAMETERS               --(매개 변수 정보) SELECT * FROM SYS.PARTITION_RANGE_VALUES             --(경계구성) SELECT * FROM SYS.PARTITION_SCHEMES               --(파티션 구성정보) SELECT * FROM SYS.DATA_SPACES               --(파티션 구성정보) SELECT * FROM SYS.DESTINATION_DATA_SPACES              --(개별 파티션 구성정보) SELECT * FROM SYS.PARTITIONS             --(파티션 정보) SELECT * FROM SYS.TABLES              --(테이블 분할정보) SELECT * FROM SYS.INDEXES              --(인덱스 분할정보) SELECT * FROM SYS.INDEX_COLUMNS               --(인덱스 컬럼정보) -- SPLIT ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (-1000); GO SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE          FUNCTION_ID IN                  (                  SELECT FUNCTION_ID                  FROM SYS.PARTITION_FUNCTIONS                  WHERE NAME = ('MYRANGEPF3')                  ) 결과 FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----------- 65536 1 1 NULL 65536 2 1 -1000 65536 3 1 -100 65536 4 1 1 65536 5 1 100 65536 6 1 1000 알아서 잘 배열 되는 것을 관찰할 수 있다. -- MERGE ALTER PARTITION FUNCTION MYRANGEPF3 () MERGE RANGE (-1000); FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----------- 65536 1 1 NULL 65536 2 1 -100 65536 3 1 1 65536 4 1 100 65536 5 1 1000 정리 PARTITION_RANGE_VALUES + 1 개의 영역이 생기게 되고,기준값은 LEFT, RIGHT 의 지정에 따라 기준값의 위치가 다르게 속하게 되는 것을 알 수 있다. 예를들어 RIGHT 파티션을 만들고 경계값이 NULL 이 있다면, RIGHT 파티션의 경우 기준값을 포함하지 않는 더작은 영역을 만들게 된다. 그러나 NULL 보다 더 작은 값이 없으므로 첫번째 파티션은 비게 된다. DROP PARTITION FUNCTION MYRANGEPF3 GO CREATE PARTITION FUNCTION MYRANGEPF3 (INT) AS RANGE RIGHT FOR VALUES ( NULL, -100, 1, 100, 1000); GO 참고 : 기준값을 정열해서 배치하면 에러 없음 5개로 기준값으로 나누었으니 다음과 같은 6개의 영역이 생긴다. 1                                     값       <     NULL    --                                   첫번째 기준값 보다 작은 값들 (빈파티션) 2    NULL      <=       값      <       -100       -- NULL                포함 기준값보다 더 큰 값들 3    -100       <=       값      <        1                         -- -100                   포함 다음기준값 보다 작은 값들 4    1                      <=     값       <           100                     -- 1                    포함 다음기준값 보다 작은 값들 5    100                   <=     값       <           1000        -- 100                    포함 다음기준값 보다 작은 값들 6                                     값       >=         1000        -- 1000                  포함 더 큰값들 다 /*----------------------------------------------------------------------- PARTITIONED TABLE           PARTITIONED FUNCTION           PARTITIONED SCHEME           CREATE PARTITIONED TABLE           CHECK PARTITIONED INFORMATION           PARTITION MERGE           PARTITION SPLIT           PARTITION SWITCH           * PARTITIONED SCHEME 파일그룹 매핑 구성표 만들기 (필수 선행작업: 파티션 함수 생성) -------------------------------------------------------------------------*/ /* 구문정보 CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) [ ; ] */ ALL 을 지정하면 하나의 파일그룹만을 지정 할 수 있고, 파티션 펑션의 기준값 보다 같거나 많은 개수를 정의해야 하며, 적을 경우 에러를 나타내며 종료 많은경우 NEXT USED 로 정의 됨 -- 실험 CREATE PARTITION SCHEME PSMYSCHEME3 AS PARTITION MYRANGEPF3 TO (FG1,FG2) 결과 메시지 7707, 수준 16, 상태 1, 줄 2 연결된 파티션 함수 'MYRANGEPF3'은(는) 구성표 'PSMYSCHEME3'에 언급된 파일 그룹보다 많은 파티션을 생성합니다. 음~ 파일그룹이 있는지 없는지 검사하지도 않고 바로 에러! -- 설정하려는 파티션 펑션의 기준값 개수 확인 SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE           FUNCTION_ID IN                      (                      SELECT FUNCTION_ID                      FROM SYS.PARTITION_FUNCTIONS                      WHERE NAME = ('MYRANGEPF3')                      ) 65537 1 1 NULL 65537 2 1 -100 65537 3 1 1 65537 4 1 100 65537 5 1 1000 -- 음~ 최소 6개 만들고 더 만들면 다음파티션으로 쓰겠군... CREATE PARTITION SCHEME PSMYSCHEME3 AS PARTITION MYRANGEPF3 TO (FG1,FG2,FG3,FG4,FG5,FG6,FG7) 결과 메시지 208, 수준 16, 상태 58, 줄 1 개체 이름 'FG1'이(가) 잘못되었습니다. 개수를 만족하고 나서 파일 그룹이 실제로 있는지 찾음 해당데이터 베이스에 파일그룹이 없으면 에러가 난다. 여기서 잠깐! 스토리지 이야기도 조금 하고 파일그룹 및 데이터 파일 핸들링 하는 방법에 대해서도 조금만 알아보자. 데이터 베이스에서 파일의 숫자 만큼 쓰래드가 생성 될 수 있기 때문에 파일을 여러개 만드는 것이 좋다. 클러스터 인덱스가 있는 데이터 파일을 파일그룹, 난클러스터 인덱스는 다른 디스크에 위치시키는 것이 절대적으로 유리하다. 파일그룹을 잘게 나누면 파일그룹별로 백업 복구도 가능하고 용량산정을 위해 다른 위치로 옮길때도 옮기기 좋은 단위가 된다는 것을 상기하자! 또한 로그파일[LDF] 는 분리된 다른 위치에 독립적으로 쓰게 하는것이 좋다. I/O 패턴이 다른 파일을 같은 디스크에 배치하는 것은 어떤경우에도 좋은 방법이 되지 못한다. 개인적으로 로그는 300G 10000RPM RAID 1 을 추천하고 싶다. 최대 테이블 사이즈가 300기가를 넘는다면 다른 방법을 구상하도록 한다. 또한 데이터 디스크는 RAID 1+0 를 추천한다. MMORPG 같은 경우나 빈번하고 짧은 트랜젝션이 많은 데이터베이스에서 쓰기스피드가 느린 RAID 5 는 CheckPoint시 트랜잭션 숫자를 감소시키는 주요 범인이 된다. 로컬백업을 한다면 로컬백업이 되는 위치는 백업시간을 단축하기 위해 RAID 0 에 위치시킨다. 두장의 RAID 5 로 구성된 디스크 두장에 백업 하는것 보다 두장의 RAID 0 로 백업한다면 훨씬 더 빠른 백업을 수행 할 수 있다. 그러나 디스크 장애로 잦은 RAID 리빌딩을 하기 싫다면 디스크 에러에 강한 RAID 1+0 로 구성하는 것이 바르다고 할 수 있다. 이런 디스크 문제가 빈번하게 일어난다면 스토리지 시스템을 도입하는 것을 고려해 보기 바란다. 스토리지 시스템은 자체 리드 캐쉬 라이트 캐쉬가 있기 때문에 메모리까지만 받아주면 스토리지 컨트롤러가 알아서 디스크에 쓸것이다. 최신 엔터프라이즈 스토리지는 컨트롤러 캐쉬가 16G에 이른다. -- 파일그룹 생성방법 ALTER DATABASE PDB ADD FILEGROUP FG1 -- 파일그룹에 간단한 파일 추가 ALTER DATABASE PDB ADD FILE ( NAME = PDB_DAT1, FILENAME = 'C:\FG1\PDB_DAT1.ndf') TO FILEGROUP FG1 -- 삭제방법 -- 1, 파일 삭제 ALTER DATABASE PDB REMOVE FILE PDB_DAT1 -- 2, 파일그룹 삭제 ALTER DATABASE PDB REMOVE FILEGROUP FG1 -- 좀더 자세하게 USE master GO ALTER DATABASE PDB ADD FILEGROUP FG1; ALTER DATABASE PDB ADD FILEGROUP FG2; ALTER DATABASE PDB ADD FILEGROUP FG3; ALTER DATABASE PDB ADD FILEGROUP FG4; ALTER DATABASE PDB ADD FILEGROUP FG5; ALTER DATABASE PDB ADD FILEGROUP FG6; ALTER DATABASE PDB ADD FILEGROUP FG7; ALTER DATABASE PDB ADD FILEGROUP FG8; GO -- 해당경로를 미리 만들어 두어야 한다. -- C:\ 드라이브에 FG1,FG2,FG3,FG4,FG5 -- G:\ 드라이브에 FG6,FG7,FG8 ALTER DATABASE PDB ADD FILE (     NAME = PDB_DAT1,     FILENAME = 'C:\FG1\PDB_DAT1.ndf', -- 테스트 이므로     SIZE = 5MB,     MAXSIZE = 100MB,     FILEGROWTH = 5MB ) --, -- 여러개의 파일로 분할 하고 싶은 경우 --( -- NAME = PDB_DAT2, -- FILENAME = 'C:\FG1\PDB_DAT2.ndf', -- SIZE = 5MB, -- MAXSIZE = 100MB, -- FILEGROWTH = 5MB --) TO FILEGROUP FG1 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT2,FILENAME = 'C:\FG2\PDB_DAT2.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG2 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3,FILENAME = 'C:\FG3\PDB_DAT3.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG3 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT4,FILENAME = 'C:\FG4\PDB_DAT4.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG4 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT5,FILENAME = 'C:\FG5\PDB_DAT5.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG5 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT6,FILENAME = 'G:\FG6\PDB_DAT6.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG6 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT7,FILENAME = 'G:\FG7\PDB_DAT7.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG7 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT8,FILENAME = 'G:\FG8\PDB_DAT8.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG8 -- 다시 하던일 계속하자! USE PDB GO CREATE PARTITION SCHEME PSMYSCHEME3 AS PARTITION MYRANGEPF3 TO (FG1,FG2,FG3,FG4,FG5,FG6,FG7,FG8) GO 결과 R1 파티션 구성표 'PSMYSCHEME3'이(가) 작성되었습니다. 파티션 구성표 'PSMYSCHEME3'에서 'FG7'은(는) 다음에 사용되는 파일 그룹으로 표시됩니다. 다음에 사용되는 파일 그룹 다음에 지정된 1 파일 그룹은 무시됩니다. 음~ 우리는 6개의 파일그룹만 필요한데, 더 만들어 두니 바로 다음 파일그룹은 NEXT USED 로 지정되고 나머지들은 무시되는 것이구나. -- 삭제방법 -- DROP PARTITION SCHEME PSMYSCHEME3 -- 해당디비의 상황 EXEC SP_HELPDB N'PDB' EXEC SP_HELPFILEGROUP; EXEC SP_HELPFILE; SELECT * FROM SYS.SYSALTFILES WHERE DBID = DB_ID('PDB') 여기서 잠깐! 데이터 베이스 미러링 같은 경우 피지컬 영역 구성이 다를수 있고 스토리지 용량도 보장 할 수 없고 이런 파티션 스킴을 고치는 작업은 미러링중에 미러 대상서버에 적용되지 않는다. /*----------------------------------------------------------------------- PARTITIONED TABLE         PARTITIONED FUNCTION         PARTITIONED SCHEME         CREATE PARTITIONED TABLE         CHECK PARTITIONED INFORMATION         PARTITION MERGE         PARTITION SPLIT         PARTITION SWITCH         * CREATE PARTITIONED TABLE 준비하는데 수고했습니다. 이제 파티션드 테이블을 만들어 보겠습니다. -------------------------------------------------------------------------*/ -- 두번째 작업에서 힘들게 만들었던 파일스키마 정의에 테이블을 매핑 시켜 만든다. IF OBJECT_ID('DBO.PT_LOG') IS NOT NULL DROP TABLE DBO.PT_LOG GO CREATE TABLE [dbo].[PT_LOG] (              PIDX INT -- 파티션 기준값         , IDX INT IDENTITY(1,1)         , DATA VARCHAR(6000) NULL ) ON PSMYSCHEME3 (PIDX) GO /*----------------------------------------------------------------------- PARTITIONED TABLE         PARTITIONED FUNCTION         PARTITIONED SCHEME         CREATE PARTITIONED TABLE         CHECK PARTITIONED INFORMATION         PARTITION MERGE         PARTITION SPLIT         PARTITION SWITCH         * CHECK PARTITIONED INFORMATION ,PARTITION MERGE, PARTITION SPLIT 데이터 삽입과 파티션 정보확인 및 MERGE SPLIT 해보도록 하겠습니다. NULL 은 어디에 있을까? 궁금하지 않나요? -------------------------------------------------------------------------*/ INSERT INTO PT_LOG (PIDX, DATA) VALUES (NULL, REPLICATE('A',6000)) GO 601 -- 인서트 601번 실행 INSERT INTO PT_LOG (PIDX, DATA) VALUES (-100, REPLICATE('A',6000)) GO 602 INSERT INTO PT_LOG (PIDX, DATA) VALUES (1, REPLICATE('A',6000)) GO 603 INSERT INTO PT_LOG (PIDX, DATA) VALUES (100, REPLICATE('A',6000)) GO 604 INSERT INTO PT_LOG (PIDX, DATA) VALUES (1000, REPLICATE('A',6000)) GO 605 INSERT INTO PT_LOG (PIDX, DATA) VALUES (2000, REPLICATE('A',6000)) GO 606 SET STATISTICS IO ON SET STATISTICS PROFILE ON SELECT COUNT(*) FROM PT_LOG 결과    |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1008],0)))          |--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))                 |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007])) **                    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6))))                        |--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG])) 테이블 'PT_LOG'. 검색 수 6, 논리적 읽기 수 3621, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0. 별표 별표(**) 부분이 어떤 파티션을 읽었는지 보여주눈 부분이다. 상수 조인을 이용한 6회 읽은 것을 알 수 있다. SET STATISTICS IO OFF SET STATISTICS PROFILE OFF 결과 3621 SET STATISTICS IO ON SET STATISTICS PROFILE ON SELECT COUNT(*) FROM PT_LOG WHERE PIDX IS NULL SET STATISTICS IO OFF SET STATISTICS PROFILE OFF 결과    |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))          |--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))                |--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]), WHERE:([PDB].[dbo].[PT_LOG].[PIDX] IS NULL) PARTITION ID:((2))) -- 파티션 아이디 2번에서만 읽은 것을 볼 수 있다. 아무리 봐도 넘 이쁜 넘이다. SET STATISTICS IO ON SET STATISTICS PROFILE ON SELECT COUNT(*) FROM PT_LOG WHERE          PIDX IS NULL OR          PIDX = 1 GROUP BY PIDX HAVING PIDX = 1 SET STATISTICS IO OFF SET STATISTICS PROFILE OFF 결과    |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))          |--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))                |--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]), WHERE:([PDB].[dbo].[PT_LOG].[PIDX]=(1)) PARTITION ID:((4))) SELECT OBJECT_NAME(OBJECT_ID), * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PT_LOG') ORDER BY PARTITION_NUMBER, INDEX_ID; 결과 partition_id   object_id      index_id      partition_number   hobt_id      rows PT_LOG 72057594040680448 53575229 0 1 72057594040680448 0 PT_LOG 72057594040745984 53575229 0 2 72057594040745984 601 PT_LOG 72057594040811520 53575229 0 3 72057594040811520 602 PT_LOG 72057594040877056 53575229 0 4 72057594040877056 603 PT_LOG 72057594040942592 53575229 0 5 72057594040942592 604 PT_LOG 72057594041008128 53575229 0 6 72057594041008128 1211 위와같이 RIGHT TYPE 에서는 NULL 보다 논리적으로 더 작은 것이 없으므로 제일 안쪽 파티션이 항상 비는 것을 알 수 있다. 음~ 배운게 있으니 1211 을 적당하게 다시 나누어 보아야 겠다. 파티션 기준값을 어떻게 나누었는지 다음 쿼리로 확인해 보자. SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE      FUNCTION_ID IN          (          SELECT FUNCTION_ID          FROM SYS.PARTITION_FUNCTIONS          WHERE NAME = ('MYRANGEPF3')          ) 결과 function_id boundary_id parameter_id value ----------- ----------- ----------- ----- 65541 1 1 NULL 65541 2 1 -100 65541 3 1 1 65541 4 1 100 65541 5 1 1000 2000으로 경계값을 더 나누어 보자 위에서 아래와 같은 데이터를 넣었다는것을 우리는 알고 있으므로 -- INSERT INTO PT_LOG (PIDX, DATA) VALUES (2000, REPLICATE('A',6000)) -- GO 606 -- 다음과 같은 쿼리를 실행했다. ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (2000); GO 결과 잘 나누어져 들어갔음을 확인 할 수 있다. FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----- 65541 1 1 NULL 65541 2 1 -100 65541 3 1 1 65541 4 1 100 65541 5 1 1000 65541 6 1 2000 ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (3000); GO 결과 메시지 7707, 수준 16, 상태 1, 줄 1 연결된 파티션 함수 'MYRANGEPF3'은(는) 구성표 'PSMYSCHEME3'에 언급된 파일 그룹보다 많은 파티션을 생성합니다. 우리는 위에서 파티션 스키마를 만들때, 결과 R1 에서 NEXT USED 로 설정된 FG7 까지만 적용이 되고 FG8에는 무시되었던 것을 기억할 것입니다. 이런 결과 값이 나오는 테이블에서 2000 보다 큰 값이 들어갈 영역을 만들려고 했으나, 역시 무시되는 것을 알 수 있습니다. 그럼 NEXT USED로 다음 파일그룹이 사용될 영역만 다시 정의해 주면 될것을 생각할 수 있겠습니다. EXEC SP_HELP N'PT_LOG' 결과 Data_located_on_filegroup ------------------------- PSMYSCHEME3 -- 해당 테이블의 스키마 정보를 이용해서 어떤 파일그룹을 쓰고 있는지 조사 SELECT             A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,             B.DATA_SPACE_ID,             C.NAME FROM             SYS.DATA_SPACES A             INNER JOIN SYS.DESTINATION_DATA_SPACES B                     ON A.DATA_SPACE_ID = B.DATA_SPACE_ID             INNER JOIN SYS.PARTITION_SCHEMES C                     ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID WHERE             C.NAME = 'PSMYSCHEME3' 결과 NAME DATA_SPACE_ID IS_DEFAULT DATA_SPACE_ID NAME ---- ------------- ------------- ------------- ---- FG1 2 0 2 PSMYSCHEME3 FG2 3 0 3 PSMYSCHEME3 FG3 4 0 4 PSMYSCHEME3 FG4 5 0 5 PSMYSCHEME3 FG5 6 0 6 PSMYSCHEME3 FG6 7 0 7 PSMYSCHEME3 FG7 8 0 8 PSMYSCHEME3 -- 해당 디비의 파일그룹 조회 EXEC SP_HELPFILEGROUP 결과 groupname groupid filecount --------- ------- --------- PRIMARY 1 1 FG1 2 1 FG2 3 1 FG3 4 1 FG4 5 1 FG5 6 1 FG6 7 1 FG7 8 1 FG8 9 1 -- 해당 스킴의 파티션펑션 및 TYPE 정보 조회 SELECT             A.BOUNDARY_ID, A.VALUE,             B.NAME,B.TYPE_DESC,B.FANOUT, BOUNDARY_VALUE_ON_RIGHT FROM SYS.PARTITION_RANGE_VALUES A             INNER JOIN SYS.PARTITION_FUNCTIONS B             ON A.FUNCTION_ID = B.FUNCTION_ID             INNER JOIN SYS.PARTITION_SCHEMES C             ON B.FUNCTION_ID = C.FUNCTION_ID WHERE             C.NAME = 'PSMYSCHEME3' 결과 BOUNDARY_ID VALUE NAME TYPE_DESC FANOUT BOUNDARY_VALUE_ON_RIGHT ----------- ---------- --------- ------ ----------------------- 1 NULL MYRANGEPF3 RANGE 8 1 2 -100 MYRANGEPF3 RANGE 8 1 3 1 MYRANGEPF3 RANGE 8 1 4 100 MYRANGEPF3 RANGE 8 1 5 1000 MYRANGEPF3 RANGE 8 1 6 2000 MYRANGEPF3 RANGE 8 1 해당하는 스키마는 PRIMARY 파일 그룹과 FG8을 안쓰고 있다는 것을 알 수 있다. 우리는 FG8을 다음 파티션 그룹으로 쓰기로 하자. -- 용법 ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ] [ ; ] -- 실행 ALTER PARTITION SCHEME PSMYSCHEME3 NEXT USED FG8 GO 결과 명령이 완료되었습니다. -- 확인 SELECT              A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,              B.DATA_SPACE_ID,              C.NAME FROM              SYS.DATA_SPACES A              INNER JOIN SYS.DESTINATION_DATA_SPACES B                         ON A.DATA_SPACE_ID = B.DATA_SPACE_ID              INNER JOIN SYS.PARTITION_SCHEMES C                         ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID WHERE             C.NAME = 'PSMYSCHEME3' groupname groupid filecount --------- ------- --------- BOUNDARY_ID VALUE NAME TYPE_DESC FANOUT BOUNDARY_VALUE_ON_RIGHT FG1 2 0 2 PSMYSCHEME3 FG2 3 0 3 PSMYSCHEME3 FG3 4 0 4 PSMYSCHEME3 FG4 5 0 5 PSMYSCHEME3 FG5 6 0 6 PSMYSCHEME3 FG6 7 0 7 PSMYSCHEME3 FG7 8 0 8 PSMYSCHEME3 FG8 9 0 9 PSMYSCHEME3 잘 적용된것을 확인 할 수 있다. -- 펑션을 수정해 보도록 하자 ! ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (3000); GO 잘 수행되는 것을 확인 할 수 있을 것이다. 이제는 다양한 값을 넣어 보겠습니다. INSERT INTO PT_LOG (PIDX, DATA) VALUES (NULL, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (-99, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (2, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (101, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (1001, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (2001, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (3000, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (3001, REPLICATE('A',6000)) GO 3 SELECT OBJECT_NAME(OBJECT_ID), * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PT_LOG') ORDER BY PARTITION_NUMBER, INDEX_ID; 결과 groupname groupid filecount --------- ------- --------- PT_LOG 72057594041663488 117575457 0 1 72057594041663488 0 PT_LOG 72057594041729024 117575457 0 2 72057594041729024 604 PT_LOG 72057594041794560 117575457 0 3 72057594041794560 605 PT_LOG 72057594041860096 117575457 0 4 72057594041860096 606 PT_LOG 72057594041925632 117575457 0 5 72057594041925632 607 PT_LOG 72057594041991168 117575457 0 6 72057594041991168 608 PT_LOG 72057594042056704 117575457 0 7 72057594042056704 609 PT_LOG 72057594042122240 117575457 0 8 72057594042122240 6 SELECT             $PARTITION.MYRANGEPF3(PIDX) AS 'PARTITION ID',             MIN(PIDX) AS 'MIN',             MAX(PIDX) AS 'MAX',             COUNT(*) AS 'ROWCNT' FROM             PT_LOG GROUP BY $PARTITION.MYRANGEPF3(PIDX) ORDER BY 1 결과 PARTITION ID MIN MAX ROWCNT ------------ ----------- ----------- ----------- 2 NULL NULL 604 3 -100 -99 605 4 1 2 606 5 100 101 607 6 1000 1001 608 7 2000 2001 608 8 3000 3001 6 -- 파티션 머지 ALTER PARTITION FUNCTION MYRANGEPF3 () MERGE RANGE (1000); SELECT             A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,             B.DATA_SPACE_ID,             C.NAME FROM             SYS.DATA_SPACES A             INNER JOIN SYS.DESTINATION_DATA_SPACES B                         ON A.DATA_SPACE_ID = B.DATA_SPACE_ID             INNER JOIN SYS.PARTITION_SCHEMES C                         ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID WHERE             C.NAME = 'PSMYSCHEME3' 결과 FG6 이 없어진 것을 확인 할 수 있다. FG1 2 0 2 PSMYSCHEME3 FG2 3 0 3 PSMYSCHEME3 FG3 4 0 4 PSMYSCHEME3 FG4 5 0 5 PSMYSCHEME3 FG5 6 0 6 PSMYSCHEME3 FG6 7 0 7 PSMYSCHEME3 FG7 8 0 8 PSMYSCHEME3 FG8 9 0 9 PSMYSCHEME3 EXEC SP_HELPFILEGROUP 결과 PRIMARY 1 1 FG1 2 1 FG2 3 1 FG3 4 1 FG4 5 1 FG5 6 1 FG6 7 1 FG7 8 1 FG8 9 1 물리적 파일 그룹은 그대로 이다. DBCC SHOWFILESTATS 결과 1 1 35 22 PDB C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PDB.mdf 3 2 80 1 PDB_DAT1 C:\FG1\PDB_DAT1.ndf 4 3 80 78 PDB_DAT2 C:\FG2\PDB_DAT2.ndf 5 4 80 78 PDB_DAT3 C:\FG3\PDB_DAT3.ndf 6 5 80 78 PDB_DAT4 C:\FG4\PDB_DAT4.ndf 7 6 160 78 PDB_DAT5 C:\FG5\PDB_DAT5.ndf 8 7 160 78 PDB_DAT6 G:\FG6\PDB_DAT6.ndf 9 8 80 79 PDB_DAT7 G:\FG7\PDB_DAT7.ndf 10 9 80 2 PDB_DAT8 G:\FG8\PDB_DAT8.ndf 정확하게 예전과 같이 분할된 것을 볼 수 있다. /*----------------------------------------------------------------------- PARTITIONED TABLE             PARTITIONED FUNCTION             PARTITIONED SCHEME             CREATE PARTITIONED TABLE             CHECK PARTITIONED INFORMATION             PARTITION MERGE             PARTITION SPLIT             PARTITION SWITCH             * PARTITION SWITCH 이제 파티션 이동하기를 해보겠습니다. 대용량 로그 파일이 있을때 DELETE 로 지우면서 세월아 내월아 기다릴 수 없으니 한방에 날릴 수 있도록 다른 파티션으로 이동해 보도록 하겠습니다. 그리고 스키마 정보만 바꾸어서 -------------------------------------------------------------------------*/ EXEC SP_HELPFILEGROUP GO ALTER DATABASE PDB ADD FILEGROUP FG3_AUX; GO -- 폴더를 미리 만들고 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3_AUX,FILENAME = 'C:\FG3_AUX\PDB_DAT3_AUX.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG3_AUX IF OBJECT_ID('DBO.PT_LOG_AUX') IS NOT NULL DROP TABLE DBO.PT_LOG_AUX GO CREATE TABLE [dbo].[PT_LOG_AUX] (                  PIDX INT -- 파티션 기준값              , IDX INT IDENTITY(1,1)              , DATA VARCHAR(6000) NULL ) ON FG3_AUX GO DBCC SHOWFILESTATS ALTER TABLE PT_LOG SWITCH PARTITION 3 TO [PT_LOG_AUX] ; GO 메시지 4939, 수준 16, 상태 1, 줄 1 ALTER TABLE SWITCH 문이 실패했습니다. 테이블 'PDB.dbo.PT_LOG_AUX'은(는) 파일 그룹 'FG3_AUX'에 있고 테이블 'PDB.dbo.PT_LOG'의 파티션 3은(는) 파일 그룹 'FG3'에 있습니다. -- 파일 그룹이 다른 테이블은 스위칭 하지 못한다. IF OBJECT_ID('DBO.PT_LOG_AUX') IS NOT NULL DROP TABLE DBO.PT_LOG_AUX GO CREATE TABLE [dbo].[PT_LOG_AUX] (                  PIDX INT -- 파티션 기준값             , IDX INT IDENTITY(1,1)             , DATA VARCHAR(6000) NULL ) ON FG3 GO ALTER TABLE PT_LOG SWITCH PARTITION 3 TO [PT_LOG_AUX] ; GO -- 성공적으로 스위칭 된다. SELECT * FROM PT_LOG WHERE PIDX = -100 -- 하나도 없다. SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE             FUNCTION_ID IN                          (                          SELECT FUNCTION_ID                          FROM SYS.PARTITION_FUNCTIONS                          WHERE NAME = ('MYRANGEPF3')                          ) SELECT * FROM PT_LOG_AUX -- 결과값이 나온다. DBCC SHOWFILESTATS -- 결국 같은 파일 그룹에서 정의만 다르게 해서 다른 테이블로 빼버리는 형태이다. 결론적으로 부하가 없다는 말입니다. ALTER TABLE PT_LOG_AUX SWITCH TO [PT_LOG] PARTITION 3 ; GO 결과 메시지 4982, 수준 16, 상태 1, 줄 2 ALTER TABLE SWITCH 문이 실패했습니다. 원본 테이블 'PDB.dbo.PT_LOG_AUX'의 CHECK 제약 조건에서 대상 테이블 'PDB.dbo.PT_LOG'의 파티션 3에서 정의한 범위가 허용하지 않는 값을 허용합니다. SELECT OBJECT_NAME(OBJECT_ID), * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PT_LOG') ORDER BY PARTITION_NUMBER, INDEX_ID; 결과 PT_LOG    72057594039042048    2073058421    0    3    72057594039042048    0 실제 파티션이 비어 있음에도 FUNCTION 제약 조건이 단일 테이블에는 적용되어 있지 않기 때문에 다시 스위칭 하는것을 허용하지 않는것 같습니다. 꽁수를 부려라... -- 꽁수 시작 CREATE PARTITION FUNCTION MYRANGEPF3_AUX (INT) AS RANGE RIGHT FOR VALUES ( NULL, -100, 1, 100, 1000); GO CREATE PARTITION SCHEME PSMYSCHEME3_AUX AS PARTITION MYRANGEPF3 ALL TO (FG3) IF OBJECT_ID('DBO.PT_LOG_SRC') IS NOT NULL DROP TABLE DBO.PT_LOG_AUX GO CREATE TABLE [dbo].[PT_LOG_SRC] (                  PIDX INT -- 파티션 기준값              , IDX INT IDENTITY(1,1)              , DATA VARCHAR(6000) NULL ) ON PSMYSCHEME3_AUX(PIDX) GO SET IDENTITY_INSERT PT_LOG_SRC ON INSERT INTO PT_LOG_SRC (PIDX, IDX, DATA)              SELECT PIDX, IDX, DATA FROM PT_LOG_AUX SET IDENTITY_INSERT PT_LOG_SRC OFF ALTER TABLE PT_LOG_SRC SWITCH PARTITION 3 TO [PT_LOG] PARTITION 3 ; GO 원래대로 끼워넣기 성공 파티션 스위치에 대한 감을 잡으셨기를 바랍니다. 다음 기사는 MSDN 에 올라온 글입니다. 관심있는 독자는 읽어보시길 바랍니다. SAN DISK 환경에서 일별로 어마어마한 로그가 쌓인다는 가정하에 2개의 자동화 프로시져를 이용하여 특정일 이후의 데이터를 삭제하는 멋진 로직을 설명 하고 있습니다. SQL2000 에서는 SP_RENAME 을 이용한 일별로그를 분리하곤 하지만 다양한 관리의 부담을 보이고 있으나, 오늘 배운 파티셔닝 기술과 SQL AGENT 로 두개의 프로시져만 번갈아 부르면 자동으로 현재일로부터 과거 몇일까지의 데이터를 저장하고 있는 테이블을 만들 수 있습니다. EXAMPLE             HOW TO IMPLEMENT AN AUTOMATIC SLIDING WINDOW IN A PARTITIONED TABLE ON SQL 2005             http://msdn2.microsoft.com/en-us/library/aa964122.aspx 끝으로 이번에 장가 갑니다. 멀리서나마 축하해 주시면 감사하겠습니다. 또한 김연욱 선배님(SQL MVP), 김대우님(MS DPE팀), 김종균님(TECHDATA), 송혁님(SQL MVP),장홍창님 그리고 많은 우리 SQL OFFLINE STUDY 사람들이 도와줘서 이글을 쓰게 되었습니다. 감사합니다. HTTP://SQLER.PE.KR에 오시면 석이를 만날 수 있습니다. ^^; 부족한 글 끝까지 읽어 주셔서 감사합니다.