데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

파티션 분할 시 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];

GO

 

Alter database [SW_TEST] set recovery simple

go

 

 

-- 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]

GO

 

-- 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')

go

 

-- 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);

GO

 

-- 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]

GO

 

-- Set the next used partition

Alter partition scheme [Orders__Scheme] NEXT USED [FG5]

--- Traditional Split

-- Clear Records to demonstrate Log records generated

checkpoint

go

 

-- 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://technet.microsoft.com/en-US/library/ms345146(v=SQL.90).aspx#sql2k5parti_topic17

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1557
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1731
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4572
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1730
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1559
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1539
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7811
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2179
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1242
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1771
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1777
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1472
» 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1858
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1648
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1392
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1161
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2507
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421





XE Login