SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2012 自習書データパーティション入門

SQL Server 2012 自習書「No.19 データパーティション入門」（HTML 版）

松本美穂と松本崇博が執筆した完全オリジナル SQL Server 2012 自習書シリーズの「No.19 データパーティション入門」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2013年12月29日]

目次へ | 前のページへ | 次のページへ

4.2　スライディングウィンドウの実装手順

◆ スライディングウィンドウの実装手順

次に、「スライディングウィンドウ」の実装手順を試してみましょう。

スライディングウィンドウを実装する、おおまかな流れは、次のとおりです。

1. 稼動テーブルとまったく同じ範囲のパーティション関数とパーティション構成を作成し、アーカイブテーブルへ適用します（SWTICH 操作は、同じファイルグループ内でのみ有効になるので、これを行っておく必要があります）。

Note： 1つのファイルグループのみでパーティションを実装する場合
複数のファイルグループを作成せずに、1つのファイルグループのみでパーティションを実装する場合は、同じ範囲のパーティション関数を作成する必要はありません。しかし、1つのファイルグループのみで実装する場合は、パーティション単位でのバックアップや、一部のデータファイル破損時のデータ復旧（Step 5 で説明）などを実現できないので、複数のファイルグループでパーティションを実装することをお勧めします。

2. 稼動テーブルと同じパーティションインデックスを、アーカイブテーブル側へも作成します。

3. 稼働テーブルと同じデータ圧縮の設定を、アーカイブテーブル側へも設定します。

稼動テーブルへ新しいパーティションを追加するときは、次の手順で行います（この作業は、アーカイブテーブルに対しても同様に行います）。

4. ファイルグループの追加

5. パーティション構成の変更（NEXT USED ファイルグループの指定）

6. パーティション関数（境界値）の追加（SPLIT RANGE によるパーティション分割）

パーティションの移動には ALTER TABLE .. SWITCH ステートメントを利用します。構文は、次のとおりです。

ALTER TABLE 稼動テーブル
SWITCH PARTITION n TO アーカイブテーブルPARTITION n

◆ Let's Try

それでは、これを試してみましょう。ここでは、前の Step で作成した「t1」テーブル（4年分のパーティション）を稼動テーブルとし、次のように古いパーティションをアーカイブ用の「t1Arch」テーブルへ SWITCH していくシナリオとして、スライディングウィンドウの実装手順を試してみましょう。

◆ 手順1　アーカイブテーブル用のパーティション関数とスキームの作成

スライディングウィンドウを実装する最初の手順は、稼動テーブルとまったく同じ範囲のパーティション関数とパーティション構成を、アーカイブテーブル用に作成することです。SWTICH 操作は、同じファイルグループ内でのみ有効となるので、これを行っておく必要があります。では、これを試してみましょう。

1．稼働テーブル用のパーティション関数「pFunc1」と同じ範囲（3つの境界値）のアーカイブ用の「pFunc1Arch」を作成し、パーティション構成についても、「pScheme1」と同じように「pScheme1Arch」を作成します。

USE pTestDB
go
CREATE PARTITION FUNCTION pFunc1Arch (datetime)
AS RANGE RIGHT FOR
VALUES ('2010/01/01', '2011/01/01', '2012/01/01')
go
CREATE PARTITION SCHEME pScheme1Arch
AS PARTITION pFunc1Arch
TO (fg1, fg2, fg3, fg4)
go

00022

◆ 手順2　アーカイブテーブルの作成

1．次に、前の手順で作成したパーティション構成「pScheme1Arch」を指定して、アーカイブテーブルを「t1Arch」という名前で作成します。

CREATE TABLE t1Arch
( id int IDENTITY(1,1) NOT NULL
,col1 datetime
) ON pScheme1Arch(col1)

◆ 手順3　アーカイブテーブルへパーティションインデックスの作成

1．次に、稼働テーブルへ作成しているパーティションインデックスと同じパーティションインデックスをアーカイブテーブルに対しても作成します。

CREATE CLUSTERED INDEX index_col1
ON t1arch (col1)
ON pScheme1Arch (col1)

◆ 手順4　アーカイブテーブル側のパーティションへデータ圧縮

1．稼働テーブル側でデータ圧縮を設定している場合は、アーカイブ側のパーティションに対しても、同じようにデータ圧縮を設定しておく必要があります。Step 3.3 では、パーティション番号「1」に対してデータ圧縮（行圧縮）を設定していたので、同じように設定します。

ALTER TABLE t1arch
REBUILD PARTITION = 1
WITH ( DATA_COMPRESSION = ROW )

◆ 手順5　新しいファイルグループの追加

1．次に、稼動テーブル用の新しいパーティション（2013年のデータ格納用）のために、新しいファイルグループを「fg5」という名前で追加します。これは、ALTER DATABASE ステートメントを次のように実行します。

USE master
go
ALTER DATABASE pTestDB
ADD FILEGROUP fg5
go
ALTER DATABASE pTestDB
ADD FILE (  NAME = 'fg5'
           , FILENAME = 'C:\test\fg5.ndf', SIZE = 5MB)
  TO FILEGROUP fg5

◆ 手順6　稼動とアーカイブのパーティション構成の変更（NEXT USED の指定）

1．前の手順で追加したファイルグループをパーティションとして使用するには、パーティション構成（pScheme1 と pScheme1Arch）を変更します。これは、ALTER PARTITION SCHEME ステートメントで NEXT USED を利用します。

USE pTestDB
go
ALTER PARTITION SCHEME pScheme1
NEXT USED fg5
go
ALTER PARTITION SCHEME pScheme1Arch
NEXT USED fg5
go

このように NEXT USED を利用すると、パーティションが "次に" 使用するファイルグループを指定することができます。

00023

新しいファイルグループは、のちのアーカイブテーブルへの移動用としても利用するので、アーカイブ側のパーティション構成についても同様に変更しておく必要があります。

◆ 手順7　稼動テーブルとアーカイブのパーティション関数へ範囲を追加（SPLIT）

1．次に、新しいパーティション（2013年のデータ格納用）を作成するために、パーティション関数へ境界を追加します。境界を追加するには、ALTER PARTITION FUNCTION ステートメントで、SPLIT RANGE を利用します。

USE pTestDB
go
ALTER PARTITION FUNCTION pFunc1()
SPLIT RANGE ('2013/01/01')
go
ALTER PARTITION FUNCTION pFunc1Arch()
SPLIT RANGE ('2013/01/01')
go

このように SPLIT RANGE を利用すると、パーティション関数の範囲を分割できるようになります。'2013/01/01' という境界値を指定することで、パーティションは、次の構成（5つのパーティション）へ変わります。

00024

◆ 作成されたパーティション関数の名前と境界の確認

1．SPLIT によってパーティションが分割されたことを確認するには、次のように partition_functions と partition_range_values カタログビューを JOIN します。

USE pTestDB
go
SELECT f.name, r.value, *
FROM sys.partition_range_values r
INNER JOIN sys.partition_functions f
ON r.function_id = f.function_id

00025

パーティション関数の名前と境界値を取得できたことを確認できます。

◆ パーティション番号とファイルグループの対応を確認

1．パーティション番号とファイルグループの対応を確認するには、partition_schemes とdata_spaces、destination_data_spaces の 3つのカタログビューを JOIN して、次のように記述します。

USE pTestDB
go
SELECT
  ps.name As [パーティション構成名]
,ds.name As [ファイルグループ名]
,dds.destination_id As [パーティション番号]
,*
FROM sys.destination_data_spaces dds
  INNER JOIN sys.partition_schemes ps
    ON dds.partition_scheme_id = ps.data_space_id
  INNER JOIN sys.data_spaces ds
    ON dds.data_space_id = ds.data_space_id
ORDER BY partition_scheme_id

00026

◆ パーティション4 とパーティション5 へデータを追加

1．次に、パーティション分割したパーティション4（2012年用）とパーティション5（2013年用）へデータを追加して、パーティションが正しく設定されたことを確認してみましょう。

-- データの追加
INSERT INTO t1 VALUES ('2012/12/31')
INSERT INTO t1 VALUES ('2013/01/01')
-- どのパーティション内のデータか確認
SELECT *, $PARTITION.pFunc1(col1) As [パーティション番号]
FROM t1

00027