파티션 분할 시 I/O 최소화 하기

SQL Server/SQL Server Tip

파티션 분할 시 I/O 최소화 하기

SungWookKang 2015. 7. 22. 10:35

파티션 분할 시 I/O 최소화 하기

Version : SQL Server 2005, 2008, 2008R2, 2012

SQL Server에서 파티션의 장점은 대용량 데이터를 관리할 때 데이터 기준(일반적으로 날짜를 많이 사용함)에 따라 파티션 그룹을 분할하고 데이터를 검색 또는 입력, 삭제를 할 때 파티션 그룹 단위로 이루어지므로 많은 성능 이점이 있다.

일반적으로 날짜 시간 값의 범위는 오른쪽 파티션을 권장한다. 자세한 내용은 다음 링크를 참고 한다.

Partitioned Tables and Indexes in SQL Server 2005 :

http://technet.microsoft.com/en-US/library/ms345146(v=SQL.90).aspx#sql2k5parti_topic17

파티션 사용시 오른쪽 파티션(RANGE RIGHT PARTITION) 또는 왼쪽 파티션(RANGE LEFT PARTITION)이 비어 있지 않은 경우 파티션을 추가하는 작업에 따른 리소스 사용과 성능 관계를 알아 보자.

이번 포스트는 SQL Server Premier Field Engineer Blog의 내용을 참고하였으며 필자가 읽고 이해한 내용을 정리하였다. 번역의 오류나 기술적 오류의 가능성이 있으므로 원문을 참고하길 바란다.

다음 시나리오는 테이블의 범위가 RANGE LEFT PARTITION으로 날짜와 시간 열을 기준으로 분리되어 있다.

다음 예제 스크립트는 기간에 따른 파티션 함수를 생성 한다.

CREATE PARTITION FUNCTION [Orders__Function](datetime) AS RANGE LEFT FOR VALUES

(N'2012-12-31 23:59:59.997', N'2013-03-31 23:59:59.997', N'2013-06-30 23:59:59.997')

파티션의 범위를 표로 정리하면 다음과 같다.

Partition	Partition 1	Partition 2	Partition 3	Partition 4
Data Range	<= 2012-12-31	<= 2013-03-31	<= 2013-06-30	> 2013-06-30

다음 스크립트는 파일 그룹을 생성, 파티션 함수 생성, 그리고 데이터를 입력하여 파티션에 데이터를 입력하는 시나리오이다.

/*****************************************************

Step 1: Scenario SETUP

*************************************************/

-- Add Filegroups

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG1];

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG2];

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG3];

ALTER DATABASE [SW_TEST] ADD FILEGROUP [FG4];

Alter database [SW_TEST] set recovery simple

-- Add Files

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'C:\SQL_DATA\PartitionTest_1.ndf') TO FILEGROUP [FG1]

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_2', FILENAME = N'C:\SQL_DATA\PartitionTest_2.ndf') TO FILEGROUP [FG2]

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_3', FILENAME = N'C:\SQL_DATA\PartitionTest_3.ndf') TO FILEGROUP [FG3]

ALTER DATABASE [SW_TEST] ADD FILE ( NAME = N'PartitionTest_4', FILENAME = N'C:\SQL_DATA\PartitionTest_4.ndf') TO FILEGROUP [FG4]

-- Create partition function

CREATE PARTITION FUNCTION [Orders__Function](datetime) AS RANGE LEFT FOR VALUES

(N'2012-12-31 23:59:59.997', N'2013-03-31 23:59:59.997', N'2013-06-30 23:59:59.997')

-- Create partition Scheme

CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders__Function] TO

([FG1],[FG2],[FG3],[FG4])

-- Create table

CREATE TABLE [dbo].[Orders](

[OrdDate] [datetime] NOT NULL,

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Addr] varchar(100) NOT NULL)

-- Partition the table

CREATE UNIQUE CLUSTERED INDEX IX_Orders

ON [Orders](OrdDate asc,ID asc)

ON [Orders__Scheme] (OrdDate);

-- Insert rows into partitions (partition 4 in this case)

declare @i int

set @i = 1

declare @date Datetime

while (@i < 1000)

begin

set @date = dateadd(mi,@i,'2012-11-01 10:17:01.000')

--insert into testtable values (@date)

insert into [Orders] values (@date, 'Denzil')

insert into [Orders] values (dateadd(month,3,@date), 'Denzil')

insert into [Orders] values (dateadd(month,6,@date), 'Denzil')

insert into [Orders] values (dateadd(month,9,@date), 'Denzil')

set @i = @i+1;

end

-- Check the rowcount in each partition

select $PARTITION.[Orders__Function](Orddate) as PartionNum,COUNT(*) as CountRows from Orders

Group by $PARTITION.[Orders__Function](Orddate)

다음과 같이 각 파티션에 999개의 행이 입력되어 있는 결과를 확인 할 수 있다.

다음 스크립트는 테이블의 인덱스정보와 파티션, 그리고 각 파티션에 입력된 행의 정보를 확인할 수 있다.

SELECT

t.name as TableName,i.name as IndexName,

p.partition_id as partitionID,

p.partition_number,rows, fg.name

FROM sys.tables AS t

INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)

INNER JOIN sys.partitions AS p ON (t.object_id = p.object_id and i.index_id = p.index_id)

INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)

INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)

WHERE (t.name = 'Orders') and (i.index_id IN (0,1))

이제 파티션 분할을 실행 하면 해당 파티션 분할에서 999행에 대한 각 레코드가 삭제 된 후 새 파티션에 삽입된 것을 알 수 있다.

--- We now want to SPLIT a non-empty partition, so preparing for that

-- Add new Filegroup and file

ALTER DATABASE SW_TEST ADD FILEGROUP [FG5];

ALTER DATABASE SW_TEST ADD FILE

( NAME = N'PartitionTest_5', FILENAME = N'C:\SQL_DATA\PartitionTest_5.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

TO FILEGROUP [FG5]

-- Set the next used partition

Alter partition scheme [Orders__Scheme] NEXT USED [FG5]

--- Traditional Split

-- Clear Records to demonstrate Log records generated

checkpoint

-- Select to demonstrate that there are no log records for that table

select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)

where AllocUnitName= 'dbo.Orders.IX_Orders'

group by Operation

order by count(*) desc

다음 스크립트를 실행하여 비어 있지 않은 파티션을 분할하고 각 행을 삭제한 다음 새로 만든 파티션에 삽입된 기록을 확인할 수 있다.

-- Split the non-empty partition

ALTER PARTITION FUNCTION Orders__Function() SPLIT RANGE ('2013-09-30 23:59:59.99')

-- Show how many log records generated, there is data movement Deletes followed by inserts

select Operation,AllocUnitName,count(*) as NumLogRecords from fn_dblog(NULL,NULL)

where AllocUnitName= 'dbo.Orders.IX_Orders'

group by Operation,AllocUnitName

order by count(*) desc

또한 메타데이터 정보에서 새로 추가된 파티션(파티션 번호5)에는 이전에 할당된 파티션4의 번호가 부여되었다.

SELECT

t.name as TableName,i.name as IndexName,

p.partition_id as partitionID,

p.partition_number,rows, fg.name

FROM sys.tables AS t

INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)

INNER JOIN sys.partitions AS p ON (t.object_id = p.object_id and i.index_id = p.index_id)

INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)

INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)

WHERE (t.name = 'Orders') and (i.index_id IN (0,1))

이렇게 비어있지 않은 파티션을 분할 하게 되면 데이터가 삭제되고 새로 입력되는 과정에서 많은 I/O가 발생하며 SQL Server의 성능은 느려진다.

효율적으로 파티션 전환을 하기 위해 마지막 파티션과 동일한 파일그룹에서 임시 스테이징 테이블을 만들어 스테이징 테이블을 마지막 파티션으로 전환한다. 이 작업은 메타데이터 작업이기 때문에 몇 초 안에 완료 된다.

파티션 분할 시 데이터 이동을 방지하고 작업을 병합하려면 가장 왼쪽 또는 오른쪽 파티션을 유지하는 것이 좋다. 그러나 파티션이 비어있지 않거나 데이터가 실수로 파티션에 채워진 경우에는 아웃 파티션 스위치를 사용하여 분할 및 병합을 할 수 있다.

[참고자료]

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/08/13/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i-split-it-with-minimal-io-impact.aspx
Partitioned Tables and Indexes in SQL Server 2005 :

http://technet.microsoft.com/en-US/library/ms345146(v=SQL.90).aspx#sql2k5parti_topic17

파티션 관리 툴 : http://sqlpartitionmgmt.codeplex.com/

2013-08-19 / 강성욱 / http://sqlmvp.kr

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Suspect_pages 테이블 이해 및 관리 (0)	2015.07.22
SSRS SocketException (0)	2015.07.22
대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) (0)	2015.07.22
기본 추적(default tace) 활성화 및 로그 확인 (0)	2015.07.22
SQL Server ALTER TABLE syntax diagrams (0)	2015.07.22

현재글파티션 분할 시 I/O 최소화 하기

Data Science Lab