[SQL]Partition Table 的實作筆記

2012-12-25

最近為了讓一些大型資料庫的效能能夠變得更好，因此希望配合 SQL Server Enterprise 版本來測試相關 Partition Table 的功能，因此把相關實驗環境整理下來。

1. 環境預備

-- 建立測試資料庫
create database [PartitionTest]
GO

-- 建立 4 個 FILEGROUP 
alter database [PartitionTest] add filegroup [PartitionTestFG201201];
alter database [PartitionTest] add filegroup [PartitionTestFG201202];
alter database [PartitionTest] add filegroup [PartitionTestFG201203];
alter database [PartitionTest] add filegroup [PartitionTestFG999999];

use [PartitionTest]
GO
declare @path nvarchar(128);
declare @PTR int ;
declare @ID  varchar(2)
declare @SQL VARCHAR(1000)

select @path=physical_name from sys.database_files where file_id =1

-- 每個 FileGroup 至少要指定一個檔案
set @PTR = 1
while @PTR < 4
begin
  set @ID = RIGHT('00'+LTRIM(STR(@PTR)),2 )
  set @SQL = 'alter database [PartitionTest] add file ( name=[PartitionTestFile2012'+@ID+'_1], filename="'+@path+'PartitionTestFile2012'+@ID+'_1.ndf", size=10MB ) to filegroup [PartitionTestFG2012'+@ID+']' ;
  exec( @SQL )
  set @PTR += 1
end 
set @SQL = 'alter database [PartitionTest] add file ( name=[PartitionTestFile999999_1], filename="'+@path+'PartitionTestFile'+@ID+'_1.ndf", size=10MB ) to filegroup [PartitionTestFG999999]' ;
exec( @SQL )

2. 以日期區間建立 Partition Function，並建立 Partition Schema 根據 Partition Function 的值放到對應的檔案群組上

-- 按照日期區分, 懶得考慮每月最後一天是哪一天, 先用字串混過去囉
create partition function pfPartitionTest(nvarchar(8)) 
    as range left for values (N'20120131', N'20120231', N'20120331');

-- 依據 Partition Function 建立 Partition Schema
create partition scheme psPartitionTest
as partition pfPartitionTest
TO ([PartitionTestFG201201], [PartitionTestFG201202], [PartitionTestFG201203], [PartitionTestFG999999]);

3. 為了方便後續檢查 Partition Table 的資料是否有按照 Partition 存放，因此建立一個用來查詢 Partition 使用狀況的 UDF

-- 建立一個檢查用的 Function
create function PartitionInfo( @tablename sysname ) returns table
as return
select 
OBJECT_NAME(p.object_id) as TableName
,p.partition_number as PartitionNumber
,prv_left.value as LowerBoundary
,prv_right.value as  UpperBoundary
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,fg.name as FileGroupName
,CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18,2)) AS UsedPages_MB
,p.row_count as Rows
from sys.dm_db_partition_stats p
inner join sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
inner join sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf ON ps.function_id = pf.function_id
inner join sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
inner join sys.filegroups fg ON fg.data_space_id = dds.data_space_id
left  join sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
left  join sys.partition_range_values prv_left  ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
where
 p.object_id = OBJECT_ID(@tablename) and p.index_id < 2

4. 建立測試用的 Table，這裡要特別注意幾點，如果我們的 Table 要建立 Primary Key 或者是 Unique Index 的時候，一定要包含 Partition 分類的欄位；而如果我們的 Table 又有 Cluster Index 的時候，則後續如果要將資料做 Switch 的時候，則目的 Table 也要有 Cluster Index，因此這裡我先把這兩個狀況合併，直接建立一個 CLUSTER Index 的 Primary Key。

USE [PartitionTest]
GO

-- 建立 Table 一開始就使用 Partition Schema
CREATE TABLE [dbo].[A](
    [A01] [nvarchar](4) NOT NULL,
    [A02] [nvarchar](8) NOT NULL,
    [A03] [nvarchar](10) NULL,
    [A04] [nvarchar](10) NULL,
 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 
(
    [A02] ASC,
    [A01] ASC
)
) ON psPartitionTest(A02)

5. 放入測試資料，透過 UDF 的資料表，我們可以看到資料有放在三個 Partition 上各有兩筆

-- 放些 Demo 資料
insert into A values
( '0001', '20120101' , '1', '1' ),
( '0002', '20120102' , '2', '2' ),
( '0003', '20120201' , '3', '3' ),
( '0004', '20120201' , '4', '4' ),
( '0005', '20120301' , '5', '5' ),
( '0006', '20120302' , '6', '6' )


-- 利用 UDF 來檢查 Partition 資料存放狀況
select * from dbo.PartitionInfo('dbo.A')

6. 接下來我們要模擬在切割出一個 Partition 出來單獨放 2012/04/01 ～2012/04/31 的資料，因此我們要建立一個新的檔案群組，並且調整 Partition Function，讓 Partition Schema 會將資料放到這個新的檔案群組上

declare @path nvarchar(128);
declare @ID  varchar(2)
declare @SQL VARCHAR(1000)

select @path=physical_name from sys.database_files where file_id =1

-- 產生一個四月的檔案群組
set @ID = '04'
set @SQL = 'alter database [PartitionTest] add filegroup [PartitionTestFG2012'+@ID+']' ;
exec( @SQL )
-- 產生一個四月的資料檔案
set @SQL = 'alter database [PartitionTest] add file ( name=[PartitionTestFile'+@ID+'_1], filename="'+@path+'PartitionTestFile'+@ID+'_1.ndf", size=10MB ) to filegroup [PartitionTestFG2012'+@ID+']' ;
exec( @SQL )
-- 變更 Partition Schema 指定新的資料群組是 PartitionTestFG201204
set @SQL = 'alter partition scheme [psPartitionTest] next used [PartitionTestFG2012'+@ID+']' ;
-- 變更 Partition Function 
exec( @SQL )
set @SQL = 'alter partition function [pfPartitionTest]() split range ( 2012'+@ID+'31 )';
exec( @SQL )

7. 接著我們測試放入四月的資料看是否會放到正確的檔案群組上

-- 放入四月份資料
insert into A values
( '1001', '20120401' , 'X', 'Y' ),
( '1002', '20120402' , 'X', 'Y' ),
( '1003', '20120403' , 'X', 'Y' ),
( '1004', '20120404' , 'X', 'Y' )

-- 檢查資料
select * from dbo.PartitionInfo('dbo.A')

8. 接下來就是比較麻煩的刪除資料，我們預計使用 Alter Table Switch 的方式，但因為我們原本建立的 Table 有建立 Cluster Index，因此這裡我們可以用兩個方式

A. 單獨建立一個 Table 跟要移出資料的 Partition 在同一個檔案群組上, EX：

-- 建立一個同樣跟三月份資料同在一個檔案群組的 Table
CREATE TABLE MOCK_A_PartitionTestFG201203(
    [A01] [nvarchar](4) NOT NULL,
    [A02] [nvarchar](8) NOT NULL,
    [A03] [nvarchar](10) NULL,
    [A04] [nvarchar](10) NULL,
    PRIMARY KEY CLUSTERED (    [A02] ASC,    [A01] ASC)
) ON PartitionTestFG201203
GO

-- 把資料搬移到仿造的 Table 上
alter table A switch partition 3 to MOCK_A_PartitionTestFG201203

B. 或者是建立一個使用同樣 Partition Schema 的另外一個 Table，此時就可以對照的 Partition ID 相互移轉了

-- 建立一個假的 A Table 來刪除資料搬移用，但此時是原 Table 使用相同的 Partition Schema
CREATE TABLE MOCK_A(
    [A01] [nvarchar](4) NOT NULL,
    [A02] [nvarchar](8) NOT NULL,
    [A03] [nvarchar](10) NULL,
    [A04] [nvarchar](10) NULL,
    PRIMARY KEY CLUSTERED (    [A02] ASC,    [A01] ASC)
) ON psPartitionTest(A02)
GO

-- 既然兩個 Table 都有相同的 Schema , 那麼就相同的 Partition ID 去相互搬移
alter table A switch partition 1 to MOCK_A partition 1

9. 如果按照上述的方式，假設我們已經把 2012/01 月份的資料都移出了，此時我們可能想要回收之前所建立的檔案群組，則要注意處理的先後順序，一定要先刪除資料檔案之後，才可以刪除檔案群組。

use [PartionTest]
GO
-- 把 Partition 1 給取消
alter partition function pfPartitionTest() merge range ( 20120131 )

select * from [dbo].[PartitionInfo]('dbo.A')

-- 依序刪除，先刪除檔案再刪除 FG
use [master]
GO
alter database [PartitionTest] remove file [PartitionTestFile201201_1]
alter database [PartitionTest] remove filegroup PartitionTestFG201201

後記：應該一般會使用到的狀況和基本功能都應該涵蓋了，但因為手邊的設備還不夠有力作壓力測試和相關效能測試，或許等以後有比較多資料的時候，再來提供相關測試數據跟大家來做分享。

回首頁

五餅二魚工作室

James Fu 的技術學習之路

[SQL]Partition Table 的實作筆記

系列文章

標籤雲