제2의 비엔지니어 인생관을 꿈꾸며

Posted
Filed under MSSQL
MSSQL 의 대용량 데이터 관리를 위한 파티셔 테이블 에 대해서  시나리오 별로 작성해 보았다.
우선 파티셔닝에 대해 왜 필요한지 어떤게 장점이고, 단점인지 뭔지, 뭐하는건지에 대한 내용은 설명되어있지 않다. 간략하게 설명 드리자면, 대용량 데이터 베이스 환경에서, 데이터가 관리되는, 최소규모의 테이블을 물리적 & 논리적 분할을 통해 I/O 및 기타 성능을 높이는데 목적이 있다. 자세한건 구글링을 통해 알아보도로 하고, 다음 포스팅에 다시한번 파티셔닝을 다룰때 이야기 하도록 하자

  테스트를 위한 시나리오 , SQL 풀 쿼리이다.(다운로드)


포스팅의 내용을 살펴보면,
# DB 생성 또는  기존 DB 에 그룹 추가 후 그룹별 파티셔닝하기 (Primary 1개 + 그룹 5개)
   >  쿼리 연습겸 , 3개는 Create DATABASE 로 나머지 2개는 Alter DataBase 로 처리
# 기본 DB 에 , 쿼리로 DB 에 파일 그룹 추가해 보기 (기존에 단일 그룹  > 그룹으로 추가하는 방법을 알수있음)
# 파티셔닝에 필요한  쿼리들을 알수있다. (Partition Function , Partion Scheme  , Create Table , Create Index )
# 파티셔닝  관리에 필요한 명령어 처리(SPLIT , MERGE , SWITCH)
| 자세한 내용은 상황 별로 설정하여 그때 그때 쿼리 및 이미지 주변에 설명에 두었으니, 참고하길 바랍니다.


DB 생성(그룹동시생성)  및 alter 명령어로 그룹추가
CREATE DATABASE partitionDB
ON
Primary  (Name = "partitionDB_PM" , FileName="D:\TEST_DATA\pDB_M.MDF"),  

FileGroup FG1 (name= "partitionDB_FG1" , FileName  ="D:\TEST_DATA\fDB_1.NDF") ,
FileGroup FG2 (name= "partitionDB_FG2" , FileName  ="D:\TEST_DATA\fDB_2.NDF") ,
FileGroup FG3 (name= "partitionDB_FG3" , FileName  ="D:\TEST_DATA\fDB_3.NDF") 


Alter DataBase [partitionDB] add FileGroup [FG4]
Alter DataBase [partitionDB] add File  (Name ="partitionDB_FG4", FileName = "D:\TEST_DATA\fDB_4.NDF") 
To FileGroup  [FG4]


Alter DataBase [partitionDB] add FileGroup [FG5]
Alter Database [partitionDB] add File (Name = "partitionDB_FG5", FileName = "D:\TEST_DATA\fDB_5.NDF") 
To FileGroup [FG5]


use partitionDB 


DBCC SHOWFILESTAT


파티션 함수생성
CREATE PARTITION FUNCTION [PF_For_Date_FG5] (datetime)
AS
RANGE LEFT FOR VALUES
(
            '2017-03-01 00:00:00' ,  -- 'RegDate 값' <= 2017-03-1 
            '2017-06-01 00:00:00' ,  -- 'RegDate 값' <= 2017-06-1 
            '2017-09-01 00:00:00' ,  -- 'RegDate 값' <= 2017-09-1
            '2017-12-01 00:00:00' ,  -- 'RegDate 값' <= 2017-12-1 
            '2018-03-01 00:00:00'    -- 'RegDate 값' <= 2018-03-1                      
  ) 

파티션 펑션  쿼리 확인
select * From sys.partition_functions
select * From sys.partition_range_values





파티션 스키마 생성
CREATE PARTITION SCHEME [PS_For_Date_FG5] 
AS 
PARTITION [PF_For_Date_FG5] To ([FG1], [FG2], [FG3], [FG4], [FG5])
*  스크립 생성하면 오류난다? 왜지? 그룹5개만들도 다 맞는데?
*   2018-03-1 <  이후 데이터가 들어갈곳이없다.
*  그래서 그룹생성시 파티셔닝할 그룹 + 1개(사실은 1개 이상) 그룹을 추가로 만들어야함


[FG6] 파일그룹 추가
ALTER DATABASE [partitionDB] add FileGroup [FG6]
ALTER DATABASE [partitionDB] add File (name = "partitionDB_FG6" , filename = "D:\TEST_DATA\fDB_6.NDF")

DBCC SHOWFILESTATS

| 오잉? 이상하다
| 방금만든 [partion_FG6] 이 Primary 와 같은 FileGroup 1 에 들어가있다 
| To FileGroup 옵션을 빼먹었다. --; 삭제하고다시하자

[FG6]  파일그룹을 만들고 & 물리적 파일을 [FG6] 그룹에 적재 
① [FG6]  파일그룹을 생성
Alter database [partitionDB] remove filegroup [FG6] -- 그룹  삭제
Alter database [partitionDB] remove file [partitionDB_FG6] -- 파일  삭제

ALTER DATABASE [partitionDB] add FileGroup [FG6]
ALTER DATABASE [partitionDB] add File (name = "partitionDB_FG6" , filename = "D:\TEST_DATA\fDB_6.NDF") To Filegroup [FG6]
 만약  그룹(TO Filegroup 그룹명) 으로 생성된 파일그룹 삭제시 ① FILE (*.NDF)를 삭제 후  ② FileGroup 을 순서대로
삭제되어야 한다. (논리적으로 [FG6] 안에 물리 파일인[partitionDB_FG6] 이 있기때문)

DBCC SHOWFILESTATS
 | FileGroup 7번을 정상적으로 할당 받았다



② 파티션 스키마에 [FG6]
CREATE PARTITION SCHEME [PS_For_Date_FG5] 
AS 
PARTITION [PF_For_Date_FG5] To ([FG1], [FG2], [FG3], [FG4], [FG5])


| 또 오류  왜 그러지?
| 방금 만든 [FG6] 를 추가해야한다.
| 기본 [FG1] ~ [FG5] 까지는 파티션 함수에서 지정한 조건에 맞는 데이터가 파티셔닝되고
| 그 외의 조건은 [FG6] 에 들어가도록,
| PARTITION SCHEME 생성시에는 PARTITION FUNCTION 지정한 그룹 갯수 + 1개 이상을 추가 해야 한다.



아래쿼리 정상처리
CREATE PARTITION SCHEME [PS_For_Date_FG5] 
AS 
PARTITION [PF_For_Date_FG5] To ([FG1], [FG2], [FG3], [FG4], [FG5] , [FG6])


테이블 생성 & 인덱스 생성시 파티셔닝 하기
Create table Tbl 
(
      idx int ,  
      RegDate datetime
)  ON PS_For_Date_FG5(regdate)
| 테이블에 인덱스 생성시 파티션용 스키마 적용 하여 테이블 파티셔닝 한다.



CREATE NONCLUSTERED INDEX IDX_DUMMY
ON Tbl (regDate)
ON PS_For_Date_FG5 (RegDate) 
| 위의 테이블 생성시 , 테이블에 인덱스 생성시 파티션용 스키마 적용 하여 테이블 파티셔닝 한다.
| 나는 일부러 넌클러스터 인덱스로 해보았다. 다음 포스팅에 파티션인덱스를 다룰때 3가지 "인덱스없이,넌클러스터,클러스터" 를 해보자


tbl에 더미 데이터 넣기
declare @i int
declare @d datetime
set @i = 1 
set @d = '2017-01-01'
BEGIN TRAN
WHILE @i <= 10000
Begin 
        set @i = @i + 1
        insert Tbl values (@i , dateadd(hh ,  @i , @d) )
End
COMMIT TRAN


분할된 파티션 정보를 그때 그때 눈으로 확인하기위해 아래 프로시져를 생성해두자
Create proc sp_partiton_view  -- drop proc sp_partiton_view
        @tbl varchar(50)
as
select 
        F.Name ,
        G.partition_number  , 
        G.Rows ,  
        E.value  
rom 
sys.destination_data_spaces A
        left outer join sys.indexes B on A.partition_scheme_id = B.data_space_id 
        left outer join sys.tables C  on B.object_id = C.object_id
        left outer join sys.partition_schemes D on B.data_space_id = D.data_space_id 
        left outer join sys.partition_range_values E on E.function_id = D.function_id and E.boundary_id = A.destination_id
        left outer join sys.filegroups F on F.data_space_id = A.data_space_id  
        left outer  join sys.partitions G on G.object_id = C.object_id and  G.index_id = B.index_id 
        
                                                        and G.partition_number = A.destination_id
where C.name = @tbl and B.name is not null order by partition_number asc



위에 만들어지 프로시져를 한번 써서, 파티션별로 들어간 데이터 갯수 눈으로 확인해보자
sp_partiton_view 'tbl'

기존 (파티셔닝기준) 데이터로  파티션 위치 확인
SELECT $PARTITION.[PF_FOR_DATE_FG5] ('2017-06-01') 
SELECT $PARTITION.[PF_FOR_DATE_FG5] ('2018-07-01') 
| '2017-06-01' 날짜로 PF_For_Date_FG5 파티션 평션 적용시켜 insert 하면  몇번째 Partition_number 로 들어갈까?
| '2018-07-01' 날짜로 PF_For_Date_FG5 파티션 평션 적용시켜 insert 하면  몇번째 Partition_number 로 들어갈까?
 결과는 아래와 같다.






파티션 SPLIT ①

" 새로운 파티션 추가 #1
시나리오 :  새로운 그룹 [FG7] 생성 후 2018-06-01 추가 "

추가전, 현재 상태를 먼저 확인해보자
sp_partiton_view 'tbl'

| 현재 파티셔닝된 상태 확인

새로 추가될 그룹 추가 생성
ALTER DataBase [partitionDB] add filegroup [FG7]
ALTER database [partitionDB]  add File (Name = "partitionDB_FG7", FileName = "D:\TEST_DATA\fDB_7.NDF") To FileGroup [FG7]

파티션 스키마에 추가 다음으로 파일그룹 FG7 지정
ALTER PARTITION SCHEME PS_For_Date_FG5 Next USED [FG7]
ALTER PARTITION FUNCTION PF_For_Date_FG5()  SPLIT RANGE('2018-06-01 00:00:00') -- 함수명뒤에 '()' 없으며 구문 오류남
| 파티션 평션에 부합되지않은 [FG6] 에 있던 데이터들 중 추가된 파티션 정보에 부합되는 정보가 있으면
| 그쪽 그룹으로 데이터가 이동하는 작업이(삭제/입력 I/O발생 나머지는 그대로 잔류




추가된 Partition_number 6 : [FG7] 확인
sp_partiton_view 'tbl'
| 새로운 파티션 스키마 정보가 삽입되면서 partition_no:6 에서 -> partition_no : 7 로 밀림

제대로 분할되었는지 테스트 INSERT
insert tbl values(10001 , '2018-04-01') -- 2번  실행
insert tbl values(10001 , '2018-06-01') -- 3번 실행



sp_partiton_view 'tbl'




파티션 SPLIT ②

 새로운 파티션 추가 #2
시나리오 : 기존에 파티션정보에 이미 '2017-01-01' 이 사용중인 [FG1] 를 다른 조건으로 파티셔닝하기
[FG6] 은 기존에 위의 조건 외의 데이터가들어가는 그룹이었다  "
 ALTER PARTITION SCHEME PS_For_Date_FG5 Next USED [FG1]
    ALTER PARTITION FUNCTION PF_For_Date_FG5()  SPLIT RANGE('2018-09-01 00:00:00') 
추가된 Partition_number 6 : [FG7] 확인
sp_partiton_view 'tbl'


insert tbl values(10001 , '2018-07-01') -- 2번 실행
insert tbl values(10001 , '2018-08-01') -- 4번 실행
sp_partiton_view 'tbl'




파티션 SPLIT ③

 새로운 파티션 추가 #3
시나리오 : 새로운 그룹 [FG8] 생성 후 2018-06-01 추가 "
ALTER DataBase [partitionDB] add filegroup [FG8]
ALTER database [partitionDB]  add File (Name = "partitionDB_FG8", FileName = "D:\TEST_DATA\fDB_8.NDF") To FileGroup [FG8]


스키마에 [FG8] 추가하고 범위 지정
ALTER PARTITION SCHEME PS_For_Date_FG5 Next USED [FG8]
ALTER PARTITION FUNCTION PF_For_Date_FG5()  SPLIT RANGE('2018-12-01 00:00:00')
sp_partiton_view 'tbl'

| 기본 그룹의 partitinu_number 가 다시 도 밀려나는걸 볼 수 있다.

insert tbl values(10001 , '2018-11-01') -- 4번 실행
insert tbl values(10001 , '2018-12-01') -- 5번 실행

sp_partiton_view 'tbl'




 파티션 MERGE
> 기존에 분할된 파티션 영역을 합친다.
> '2018-12-01 00:00:00.000' 조건을 가지고 있는 Partition_Num 8 (FG8)을 Merge(합침)
> 합쳤을때 데이터가 파티션 함수 조건에 맞는 데이터가 아니라면 기본 그룹에 INSERT 됨

 ALTER PARTITION FUNCTION PF_For_Date_FG5() MERGE RANGE ('2018-12-01 00:00:00.000')

sp_partiton_view 'tbl'


| Merge 한  파티션에 있던 데이터들('2018-12-01') 이  파티셔닝된 조건에 맞는 파티션이 없으므로,  
기본 파티션된 그룹 partition_number 8 인 [FG6] 에 들어감.
 

중간에 있는 2018-06-01 파티션을 Merge 해보자
ALTER PARTITION FUNCTION PF_For_Date_FG5() MERGE RANGE ('2018-06-01 00:00:00.000')
sp_partiton_view 'tbl'

| Merge 한  파티션에 있던 데이터들('2018-06-01') 이  파티셔닝된 조건에 맞는 파티션이 partition_number 6번에
있으므로, partition_number 6 인 [FG1] 에 들어감.
 



파티션 SWITCH 
> SWITCH 작업 전에 두 테이블이 존재해야 합니다..
> 파티션 SWITCH 조건을 MSDN 을 통해 찾아보면
   ①받는 파티션이 존재하되 비어 있어야 합니다.
   ②분할되지 않은 받는 테이블이 존재하되 비어 있어야 합니다
   ③파티션은 동일한 열에 있어야 합니다. 
   ④원본 및 대상 테이블은 동일한 파일 그룹을 공유해야 합니다. 


파티셔닝된 Tbl 의 구조를 확인하고  같은구조의 tbl_tmp 를 만든다.
(위의 조건을 무시하고 primary 그룹에 그냥 만들어보자)
select * from tbl
select * into tbl_tmp from tbl where idx = -1 -- 스키마만 복사
SWITCH 시작
 ALTER TABLE tbl SWITCH PARTITION 5 TO TBL_TMP


| 오류! SWITCH 하기위해 PARTITION_NUM 6 번 데이터를 tbl_tmp 로 switching 시도했으나 안됨
| ④ 원본 및 대상 테이블은 동일한 파일 그룹을 공유해야 합니다. 항목 위반
< 각각의 테이블 파티션 정보 & 테이블 정보 보기 >
원본 tbl  파티션된 정보보기
SELECT * FROM SYS.PARTITIONS where object_id = object_id('tbl')  
order by index_id  , partition_number 


타켓 tbl_tmp  파티션된 정보보기
SELECT * FROM SYS.PARTITIONS where object_id = object_id('tbl_tmp')  
order by index_id  , partition_number 






 각 테이블이 어떤구조로 있는지 눈으로 확인 

select 'tbl' , * from sys.indexes where object_id = object_id('tbl') 
select 'tbl_tmp' , * from sys.indexes where object_id = object_id('tbl_tmp')




select * From sys.data_spaces where data_space_id = 65601 -- PRIMARY 에 속해있다.
select * From sys.data_spaces where data_space_id = 1 -- 파티션 그룹(PARTITION_SCHEME)에 속해있다


drop table tbl_tmp -- drop 하고 다시생성

스키마 참조하여 만들되,  이번엔 일부러 컬럼명만 다르게 해보았다.
 create table tbl_tmp (idx2 int , regdate2 datetime) on [PS_For_Date_FG5](regdate2)

> 다시 스위칭 시킨다. PARTITION_NUM 5 번에 있는거 4145개 데이터 SWITCH
> 각각의 원본의 PARTITION NUM 과 타깃(TBL_TMP) 파티션 번호를 틀리게 줘봤다

ALTER TABLE tbl SWITCH PARTITION 5 TO TBL_TMP PARTITION 5

| 오류 발생 컬럼명 다르다고 오류



스키마 참조하여 만들되,  이번에는 컬럼명도 같게 MSDN 시키는대로
drop table tbl_tmp
create table tbl_tmp (idx int , regdate datetime) on [PS_For_Date_FG5](regdate)
파티션 번호 다르게 해볼까?  오류 발생
ALTER TABLE tbl SWITCH PARTITION 5 TO TBL_TMP PARTITION 1
> 오류 : 그룹이 다르다고 오류를 뱉길래 조건이 다른 1/6번 그룹은 같은 FG1 얘는될까?

ALTER TABLE tbl SWITCH PARTITION 1 TO TBL_TMP PARTITION 6
> 오류 : 정의한 범위가 테이블 'partitionDB.dbo.TBL_TMP'의 파티션 6에서 정의한 범위에 포함되지 않습니다.
원하는대로 같은 파티션에 SWICH 처리 → [성공]
ALTER TABLE tbl SWITCH PARTITION 5 TO TBL_TMP PARTITION 5


확인 [FG5] 에 있는 데이터들이 모두 TBL_TMP 로 INSERT 되었다.
sp_partiton_view 'tbl'


테이블을 확인해보자
PARTITION_NUMBER 5 에 있던 데이터 1985개 그대로 TBL_TMP 에 들어가있다.
select count(*) From TBL_TMP
| 정확히 이야기하자면 TBL_TMP 테이블의 PARTITION_NUM 5 에 FG5 에 들어가있다.
| PARTITION_NUMBER 5 에 그대로 들어가있다.




SELECT * FROM SYS.PARTITIONS
Where object_id = object_id('tbl_tmp')  Order by index_id  , partition_number 



SELECT * FROM SYS.PARTITIONS 
Where object_id = object_id('tbl')  Order by index_id  , partition_number 



| index_id 가 왜  2개지?  나중에  인덱스에 대해서 포스팅할대 설명하기로하자 급하신분들은 구글링 하시길



PARTITION_NUMBER 5를  삭제하자(삭제는아니고 사실 Merge 이다)
sp_partiton_view 'tbl' -- 삭제 전확인


PARTITION_NUMBER 5를  삭제하자 (Merge)
 ALTER PARTITION FUNCTION PF_For_Date_FG5() MERGE RANGE ('2018-03-01 00:00:00.000')

sp_partiton_view 'tbl' -- 삭제 후 확인 (사실 삭제라기 보다 합쳐젔다)


아차! 테스트하다가 파티션 범위 밖의 데이터를 입력해보는것을 해보지 못하였다.

sp_partiton_view 'tbl'


파티셔닝 조건인 value 에 포함이 안되는 값(날짜) insert
 insert tbl values(10002 , '2018-12-01')  -- 5번 실행


sp_partiton_view 'tbl'



파티션 테이블 에서는 새로 알아야할 명령어 및 개념들이 몇가지 있다.

그리고 인덱스 상관관계 또한 이해해야 할 부분이 있다. 이는, 다음 포스팅때 오늘 실습해본 쿼리를 중심으로 알아보기로 
하겠다.

[##_http://blog.sooli.com/owner/entry/edit/1L%7C2816901162.sql%7C%7C_##]  테스트를 위한 시나리오 , SQL 풀 쿼리이다.(다운로드)



2018/01/31 16:37 2018/01/31 16:37