SQL Server
테이블 파티션을 사용한 데이터베이스 유지 관리 단순화
Noah Gomez
한 눈에 보기:
- 파티션 테이블 만들기
- 파티션 추가 및 병합
- 분할된 인덱스 생성 및 관리
이 기사의 코드 다운로드: GomezPartitioning2007_03.exe (156KB)
이전에는 데이터베이스 관리자가 수백만 개의 데이터 행이 포함된 테이블을 관리하기 위해 여러 테이블을 만들어야 했습니다. 이러한 테이블을 분할한 뒤 관리자는
많은 쿼리를 실행하는 동안 테이블들을 다시 결합시켜야 했습니다. 파티션을 결합하는 작업에는 분할된 뷰를 만들거나 데이터가 있는 위치를 찾은 다음 다른 저장 프로시저를 실행하여 데이터 집합을 반환하는 데 필요한 파티션만을 찾아내는 래퍼 저장 프로시저를 만드는 작업이 포함됩니다.
이러한 방법은 효과가 있기는 하지만 귀찮은 작업이었습니다. 여러 테이블과 인덱스를 관리하는 작업과 테이블을 다시 결합하는 데 사용된 방법 때문에 관리 및 유지 관리 문제가 자주 발생했었습니다. 또한 여러 테이블을 만들어 데이터를 분할하려면 저장 프로시저, 유지 관리 작업, DTS(데이터 변환 서비스) 작업, 응용 프로그램 코드 및 다른 프로세스에서 분할의 특성을 이해하고 있어야 했기 때문에 유연성이 크게 떨어지는 문제가 있었습니다. 코드를 변경하지 않고 이러한 파티션을 추가하거나 삭제할 수 있도록 하기 위해 일반적으로 이들 요소를 비동적인 방법으로 만들었지만 결과적으로 효율이 떨어졌습니다.
SQL Server™ 2005 Enterprise 및 Developer Edition에서는 단일 테이블에 있는 대용량 데이터를 여러 작은 파티션으로 분할하여 효과적으로 유지 관리할 수 있습니다. 단일 입력 지점을 통해 액세스되는 데이터 세그먼트를 만드는 기능은 기존 방법으로 인한 많은 관리 문제를 줄여 줍니다. 단일 입력 지점(테이블 이름 또는 인덱스 이름)을 사용하면 응용 프로그램 코드에서 여러 데이터 세그먼트가 숨겨지며 관리자나 개발자는 코드 베이스를 조정하지 않고도 필요에 따라 파티션을 변경할 수 있습니다.
다시 말해서 응용 프로그램의 코드를 조정하지 않고서도 복수 파티션을 만들고, 파티션을 이동하고, 기존 파티션을 제거할 수 있을 뿐만 아니라 데이터의 분할 방법까지도 변경할 수 있습니다. 응용 프로그램 코드에서는 계속해서 동일한 기본 테이블이나 인덱스 이름을 호출하기만 하면 됩니다. 따라서 개별 인덱스에 포함된 데이터의 양을 줄일 수 있고, 결과적으로 인덱스에 대한 유지 관리 시간이 단축될 뿐만 아니라, 빈 파티션에 데이터를 로드하여 로드 속도를 높일 수 있습니다.
기술적으로 모든 SQL Server 2005 테이블은 분할됩니다. 즉, 모든 테이블에는 하나 이상의 파티션이 있습니다. SQL Server 2005에서 데이터베이스 관리자는 각 테이블에 추가 파티션을 만들 수 있습니다. 테이블 및 인덱스 파티션은 단일 입력 지점(테이블 이름 또는 인덱스 이름)을 사용할 수 있는 명확히 정의된(hard-defined) 행 수준 파티션(열 단위 분할은 안 됨)으로, 응용 프로그램 코드에서는 입력 지점 뒤에 있는 파티션의 수를 몰라도 됩니다. 파티션은 기본 테이블 및 테이블과 연관된 인덱스에 존재할 수 있습니다.
분할된 테이블 만들기
파티션 함수와 파티션 구성표를 사용하여 기본 단일 파티션 이상으로 확장 가능한 테이블을 만들 수 있습니다. 이러한 개체는 데이터를 특정 세그먼트로 나누고 스토리지 설계에 배치된 데이터 세그먼트의 위치를 제어하는 기능을 제공합니다. 예를 들어 데이터의 사용 기간을 기준으로 하거나 다른 공통 식별자를 사용하여 데이터를 여러 드라이브 배열에 분산시킬 수 있습니다. 테이블은 테이블의 한 열을 기준으로 분할될 수 있으며, 각 파티션에는 다른 파티션에 배치될 수 없는 데이터가 있어야 합니다.
파티션 함수: 테이블을 분할할 때는 먼저 데이터를 여러 세그먼트로 어떻게 나눌 것인지 결정해야 합니다. 파티션 함수는 개별 데이터 행을 여러 파티션으로 매핑하는 데 사용됩니다. 이러한 개별 데이터 행은 text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), 별칭 데이터 형식 또는 CLR(공용 언어 런타임) 사용자 정의 데이터 형식을 제외한 모든 열 형식을 사용하여 매핑할 수 있습니다. 그러나 파티션 함수는 하나의 데이터 행을 하나의 테이블 파티션에만 배치할 수 있으며, 같은 데이터 행을 여러 파티션에 동시에 매핑할 수 없습니다.
테이블을 분할하려면 대상 테이블에서 분할 열을 만들어야 합니다. 이 분할 열은 테이블을 처음 만들 때 테이블 스키마에 포함하거나 나중에 테이블을 변경하면서 추가할 수 있습니다. 이 열에는 NULL 값을 사용할 수 있지만 NULL 값이 포함된 모든 행은 기본적으로 테이블의 맨 왼쪽 파티션에 배치됩니다. 이를 원하지 않는 경우에는 파티션 함수를 만들 때 NULL 값이 테이블의 가장 오른쪽 파티션에 배치되도록 지정할 수 있습니다. 왼쪽 또는 오른쪽 파티션의 사용은 파티션 구성표를 수정하고 파티션을 추가하거나 기존 파티션을 삭제하는 작업과 관련된 설계상의 중요한 결정 사항입니다.
파티션 함수를 만들 때 LEFT 또는 RIGHT 파티션 함수를 선택할 수 있습니다. LEFT 파티션과 RIGHT 파티션은 파티션 구성표에 배치되는 경계 값의 위치가 서로 다릅니다. LEFT 파티션(기본값)에서는 경계 값이 파티션에 포함되지만 RIGHT 파티션에서는 경계 값이 다음 파티션에 배치됩니다.
이 개념을 이해하기 위해 간단한 LEFT 및 RIGHT 파티션을 살펴보겠습니다.
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
첫 번째 함수 Left_Partition에서 값 1, 10 및 100은 첫 번째, 두 번째 및 세 번째 파티션에 각각 배치됩니다. 두 번째 함수 Right_Partition에서는 값이 두 번째, 세 번째 및 네 번째 파티션에 배치됩니다.
분할된 테이블을 만들 때는 되도록 파티션을 균등하게 나누는 것이 좋습니다. 이렇게 하면 파티션에 필요한 공간을 쉽게 파악할 수 있습니다. LEFT와 RIGHT를 사용하면 데이터의 배치 위치가 결정될 뿐만 아니라 파티션의 크기와 파티션에서 만든 인덱스의 크기도 결정됩니다.
다음과 같이 $PARTITION 함수를 사용하여 데이터 값이 배치될 파티션 번호를 결정할 수 있습니다.
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
첫 번째 SELECT 문에서 결과는 2가 됩니다. 두 번째 SELECT 문에서는 3이 반환됩니다.
파티션 구성표: 함수를 만들고 데이터의 분할 방법을 결정한 후에는 개별 파티션을 디스크 하위 시스템의 어느 위치에 만들 것인지 결정해야 합니다. 파티션 구성표는 이러한 디스크 레이아웃을 만드는 데 사용됩니다. 파티션 구성표에서는 파일 그룹을 사용하여 각 파티션을 디스크 하위 시스템에 배치함으로써 개별 파티션의 디스크 스토리지를 관리합니다. 파티션 구성표를 구성하여 모든 파티션을 단일 파일 그룹에 배치하거나, 모든 파티션을 여러 파일 그룹에 배치하거나, 여러 파티션에서 여러 파일 그룹을 공유하도록 설정할 수 있습니다. 데이터베이스 관리자는 이 마지막 방법을 사용하여 디스크 I/O를 유연하게 분산시킬 수 있습니다.
그림 1은 하나 이상의 파일 그룹을 파티션 구성표에 할당하는 몇 가지 방법을 보여 줍니다. 파티션 구성표에 사용할 파일 그룹은 파티션 구성표를 만들기 전에 데이터베이스에 이미 있어야 합니다.
Figure 1 파티션 구성표에 파일 그룹 지정
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
그림 1과 같은 샘플 파티션 함수를 만들고 파티션 구성표를 사용하여 테이블을 만든 경우에는 새로 분할된 테이블에 배치될 개별 데이터 행의 위치를 결정할 수 있습니다. 그런 다음 분할된 테이블에 삽입된 데이터 행의 분포를 볼 수 있습니다. 이 모든 작업을 수행하는 코드는 그림 2와 같습니다.
Figure 2 데이터 행 배치 및 분포 보기
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
분할된 테이블 수정
아무리 철저하게 계획을 세웠더라도 테이블을 만들고 채운 이후에 분할된 테이블을 조정해야 하는 경우가 발생할 수 있습니다. 예를 들어 파티션 구성표가 예상대로 작동하더라도 새 데이터가 누적되어 새 파티션을 추가해야 하거나 많은 양의 분할된 데이터를 동시에 삭제해야 하는 경우가 발생할 수 있습니다. 다행히 분할된 테이블과 기본 분할 구조는 테이블이 활성화되어 데이터가 채워진 이후에도 변경할 수 있습니다.
파티션 추가: 많은 분할 계획에는 나중에 새 파티션을 추가할 수 있는 기능이 포함되어 있습니다. 이 시점은 특정 날짜일 수 있으며 증분 ID 열의 값에 따라 달라질 수도 있습니다. 그러나 미리 계획하지 않았더라도 나중에 새 파티션을 분할된 테이블에 추가할 수 있습니다. 그림 2에서 만든 테이블을 예로 들면, 다음과 같이 500보다 큰 값이 포함된 새 파티션을 이 테이블에 추가할 수 있습니다.
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
파티션 추가 기능은 높은 유연성을 제공합니다. 그림 3은 함수의 왼쪽에 파티션을 추가하는 방법을 보여 줍니다. 이 경우에는 처음 파티션 구성표를 작성할 때 만든 파일 그룹이 모두 사용되고 없으므로 새 파티션을 배치할 위치를 파티션 구성표에 알려야 합니다. 또한 모든 파티션에 대해 PRIMARY 파일 그룹을 사용하고 있더라도 PRIMARY 파일 그룹을 새 파티션에 다시 사용할 것임을 파티션 구성표에 알려야 합니다.
Figure 3 함수의 왼쪽에 파티션 추가
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
두 파티션 병합 SQL Server에서는 데이터를 유지한 채 테이블에서 단일 파티션을 삭제할 수 있습니다. 이 기능은 오래된 활성 데이터를 보관된 데이터에 병합하거나 사용하는 파티션의 수를 줄이려는 경우에 사용되며 분할된 테이블에 대한 관리 작업을 간편하게 만들어 줍니다. 또한 이 기능을 통해 파일 그룹 간에 데이터를 이동하여 파티션을 병합함으로써 특정 드라이브 배열의 여유 디스크 공간을 늘릴 수 있습니다. 그림 4의 코드에서는 동일한 파일 그룹에 있는 파티션 간에 데이터를 이동하는 방법을 보여 줍니다.
Figure 4 파티션 간 데이터 이동
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
단일 파티션 테이블을 분할된 테이블로 이동: 로드 루틴을 실행하는 동안 많은 양의 데이터를 데이터베이스에 로드한 다음 수집하거나 집계한 후 실제 데이터 테이블로 이동해야 하는 경우가 자주 발생합니다. SQL Server 2005 분할 기능을 사용하면 단일 파티션 테이블을 여러 파티션을 가진 테이블로 이동할 수 있습니다. 즉, 데이터를 단일 로드 테이블에 로드해서 데이터를 수정한 다음 개별 데이터 행을 이동할 필요 없이 전체 테이블을 기존 테이블로 이동할 수 있습니다. 이 분할 작업에서는 기본 분할 구조를 변경하지 않고 분할된 테이블만 수정합니다. 그림 5의 코드는 이 작업을 수행하는 방법을 보여 줍니다.
Figure 5 전체 테이블을 기존 테이블로 이동
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
테이블 간 파티션 이동: 오래된 데이터를 별도의 보관 테이블로 이동하는 작업은 일반적인 관리 작업입니다. 보관 프로세스에서는 일반적으로 트랜잭션 로그에 추가 리소스 사용량을 만들 수 있는 일련의 문이 포함됩니다. 그러나 파티션의 소유권을 한 테이블에서 다른 테이블로 전환하면 트랜잭션 로그 오버헤드 없이 대용량 데이터를 간편하게 보관할 수 있습니다. 이 기능을 통해 데이터베이스 관리자는 오래된 데이터 세그먼트를 활성 테이블에서 보관된 테이블로 이동할 수 있습니다. 또한 데이터가 실제로 이동하지는 않기 때문에 개별 데이터 행을 이동할 때보다 많은 시간을 절약할 수 있습니다. 그림 6에서는 이 작업을 수행하는 방법을 보여 줍니다.
Figure 6 오래된 데이터를 보관 테이블로 이동
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
단일 파티션을 사용하여 새 테이블 만들기: 분할된 기존 테이블에 있는 단일 파티션을 분할되지 않은 빈 테이블로 이동할 수 있습니다. 이 기능을 통해 데이터베이스 관리자는 단일 파티션에 대한 인덱스 유지 관리 작업을 수행할 수 있으며 또한 삭제 프로세스를 로깅하지 않고 많은 양의 데이터를 쉽게 삭제할 수 있습니다. 그림 7의 샘플은 파티션을 빈 테이블로 이동한 다음 새 테이블을 사용하여 데이터를 삭제하는 방법을 보여 줍니다.
Figure 7 데이터 이동 및 삭제
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
분할된 인덱스
테이블의 데이터를 분할하는 기능과 함께 분할된 인덱스를 만드는 기능도 제공됩니다. 이 기능을 통해 데이터베이스 관리자는 전체 테이블의 데이터 대신 분할된 데이터를 기반으로 하는 인덱스 구조를 설계할 수 있습니다. 분할된 인덱스를 만들면 분할된 인덱스에 개별 B-트리가 만들어집니다. 인덱스를 분할하면 데이터를 수정, 추가 및 삭제하는 중에 스토리지 엔진에서 보다 쉽게 관리할 수 있는 작은 인덱스를 만들 수 있습니다. 이러한 작은 인덱스는 데이터베이스 관리자가 개별적으로 유지 관리할 수 있기 때문에 대용량 데이터 집합에 대한 인덱스를 보다 효율적으로 유지 관리할 수 있습니다.
분할된 인덱스 만들기: 분할된 인덱스를 만들 때 정렬된 인덱스 또는 정렬되지 않은 인덱스를 만들 수 있습니다. 정렬된 인덱스의 경우 분할된 데이터에 대한 직접 관계를 사용하여 인덱스를 만들고, 정렬되지 않은 인덱스의 경우 다른 파티션 구성표를 선택합니다.
주로 사용되는 방법은 정렬된 인덱스로, 파티션 테이블을 만든 후 다른 파티션 구성표를 지정하지 않고 인덱스를 만들면 자동으로 정렬된 인덱스가 만들어집니다. 정렬된 인덱스를 사용하면 유연하게 추가 파티션을 테이블에 만들 수 있으며 파티션의 소유권을 다른 테이블로 전환할 수도 있습니다. 이러한 기능 때문에 데이터베이스 관리자가 분할된 테이블을 먼저 만드는 것입니다. 또한 테이블의 파티션 구성표를 인덱스에 사용하는 것만으로도 분할 목표를 쉽게 달성할 수 있습니다.
인덱스의 데이터가 테이블의 데이터와 대응되지 않는 테이블에 대한 인덱스를 만들 수 있습니다. 데이터가 분할된 테이블에 있는 경우에는 다양한 방법으로 데이터를 결합할 수 있습니다. 분할된 데이터는 쿼리 최적화 프로그램을 통해 다른 분할된 데이터와 효과적으로 결합할 수 있습니다. 분할되지 않은 테이블을 사용해서도 이 작업을 수행할 수 있습니다. 이 경우에는 단일 파티션 테이블에 대한 분할된 인덱스를 만들 수 있기 때문에 인덱스를 쉽게 유지 관리할 수 있습니다.
그림 8의 코드는 분할된 비클러스터형 인덱스를 분할된 테이블에 만듭니다. 비클러스터형 인덱스는 테이블과 대응되도록 정렬되며 테이블의 분할 열을 비클러스터형 인덱스 키로 사용합니다.
Figure 8 분할된 테이블의 분할된 비클러스터형 인덱스
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
그림 9의 코드에서는 정렬되지 않은 비클러스터형 인덱스를 분할된 테이블에 만듭니다. 이 비클러스터형 인덱스는 다양한 열을 인덱스 키로 사용하며 다른 분할된 테이블에 대한 정렬된 결합에 사용될 수 있습니다.
Figure 9 분할된 테이블의 정렬되지 않은 비클러스터형 인덱스
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
분할된 인덱스 유지 관리: 예전의 데이터베이스 관리자는 수백만 또는 수십억 개의 데이터 행이 포함된 대용량 테이블에 대한 인덱스를 유지 관리하는 데 많은 시간을 빼앗기곤 했습니다. 인덱스가 다시 작성되는 동안 데이터가 잠겨서 유지 관리 작업이 완료되지 않은 상태로 중지되는 경우도 자주 발생했습니다. SQL Server 2005를 사용하는 데이터베이스 관리자는 기본 테이블을 오랜 시간 동안 잠그지 않고도 인덱스 유지 관리를 온라인으로 수행할 수 있습니다. 그러나 사용자가 데이터에 액세스하는 동안 인덱스 유지 관리를 수행할 수 있는 이 방법을 사용하는 경우에도 리소스 사용량 때문에 시스템의 처리 속도가 낮아질 수 있습니다. 따라서 인덱스를 작은 세그먼트로 분할한 다음 작은 파티션에 대한 인덱스 유지 관리를 수행하는 것이 더 효율적인 방법입니다. 예를 들어 하나의 인덱스 파티션에 대한 인덱스 유지 관리를 수행하려면 그림 8의 코드 끝에 다음 코드 조각을 추가하기만 하면 됩니다.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
단일 인덱스 파티션에 대한 인덱스 유지 관리는 오프라인으로 수행해야 하며 인덱스 유지 관리 동안 테이블이 잠길 수 있습니다. 이 문제를 방지하기 위해 단일 파티션을 별도의 파티션으로 이동하고, 인덱스 유지 관리를 수행한 다음 파티션을 기본 테이블로 다시 이동할 수 있습니다. 이 프로세스를 수행하면 파티션이 테이블로 다시 옮겨지고 클러스터형 인덱스가 업데이트되기 때문에 성능 문제가 발생할 수 있지만 전체 테이블이 잠기는 것보다는 심각하지 않으며 필요한 시스템 리소스도 더 작습니다.
요약
위에서 볼 수 있는 것처럼 SQL Server 2005 테이블 분할 기능을 사용하면 응용 프로그램 코드나 SQL Server 프로세스를 다시 작업하지 않고서도 대용량 테이블의 데이터를 유연하게 저장 및 유지 관리할 수 있습니다. 이러한 기능을 통해 SQL Server는 엔터프라이즈 수준의 중요한 데이터베이스에 적합한 플랫폼임을 직접 증명해 보이고 있습니다.
Noah Gomez는 VLDB와 대규모 응용 프로그램을 전문적으로 다루는 Verizon의 수석 SQL Server 개발 DBA입니다. 그는 PASS(Professional Association for SQL Server) 멤버이며 Verizon DBA 팀에서 멀티 테라바이트 VLDB 작업을 통해 2003년에 Winter Corp. Top Ten Grand Prize를 수상한 경력이 있습니다.
© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..