【SQL Server】パーティション分割によるデータ管理について

SQL Serverのパーティション分割によるデータ管理について説明します。パーティション分割は、テーブルとインデックスのデータを複数のファイルグループに分割し、データ管理を容易にし、パフォーマンスを向上させます。

SQL Serverのパーティション分割によるデータ管理についてご紹介いたします。

パーティション分割とは、

テーブルとインデックスのデータを複数のファイルグループに分割

し、データ管理を行うこととなります。

データ量が多いテーブルやインデックスを複数のファイルに分割することで、データの管理が容易になり、パフォーマンスの向上が期待できます。

以下、パーティション分割を行う際の手順となります。

1.ファイルグループの作成

分割するデータを格納するファイルグループを作成します。

/*ファイルグループの作成*/
ALTER DATABASE partition_db ADD FILEGROUP [FG1];
ALTER DATABASE partition_db ADD FILEGROUP [FG2];
ALTER DATABASE partition_db ADD FILEGROUP [FG3];
ALTER DATABASE partition_db ADD FILEGROUP [FG4];
ALTER DATABASE partition_db ADD FILEGROUP [FG5];

/*ファイルグループに割り当てるファイルの作成*/
ALTER DATABASE partition_db
ADD FILE (NAME = N'File1', FILENAME = N'C:\テストストレージ\path_to_file1.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB) TO FILEGROUP [FG1];

ALTER DATABASE partition_db
ADD FILE (NAME = N'File2', FILENAME = N'C:\テストストレージ\path_to_file2.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB) TO FILEGROUP [FG2];

ALTER DATABASE partition_db
ADD FILE (NAME = N'File3', FILENAME = N'C:\テストストレージ\path_to_file3.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB) TO FILEGROUP [FG3];

ALTER DATABASE partition_db
ADD FILE (NAME = N'File4', FILENAME = N'C:\テストストレージ\path_to_file4.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB) TO FILEGROUP [FG4];

ALTER DATABASE partition_db
ADD FILE (NAME = N'File5', FILENAME = N'C:\テストストレージ\path_to_file5.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB) TO FILEGROUP [FG5];

2.パーティション関数の作成

次にデータをファイルグループに振り分けるための関数の作成になります。
今回は日付で分割するpf_partition_db_tableというパーティション関数を用意します。

/*パーティション関数の作成*/
CREATE PARTITION FUNCTION pf_partition_db_table (datetime)
AS RANGE RIGHT FOR VALUES ('2025/01/01', '2025/02/01', '2025/03/01', '2025/04/01');

補足ですがRANGE句は、RIGHTとLEFTがあり、以下のようになります。

RIGHTの場合

2025/01/01未満
2025/01/01以上、2025/02/01未満
2025/02/01以上、2025/03/01未満
2025/03/01以上、2025/04/01未満
2025/04/01以上

LEFTの場合

2025/01/01以下
2025/01/02以上、2025/02/01以下
2025/02/02以上、2025/03/01以下
2025/03/02以上、2025/04/01以下
2025/04/02以上

3.パーティション構成の作成

作成したパーティション関数で指定するファイルグループを割り当てます。

/*パーティション構成の作成*/
CREATE PARTITION SCHEME ps_partition_db_table
AS PARTITION pf_partition_db_table
TO ([FG1], [FG2], [FG3], [FG4], [FG5]);

4.パーティションテーブルの作成

最後に対象となるテーブルの作成となります。
今回は日付で分割するため、パーティション分割を行う列【create_date】を指定します。

/*パーティションテーブルの作成*/
CREATE TABLE partition_db_table
(
    id bigint IDENTITY(1,1) NOT NULL,
    name nvarchar(MAX) NOT NULL,
    status tinyint NOT NULL,
    create_date datetime NOT NULL
)
ON ps_partition_db_table(create_date);　←パーティション分割を行う列の定義

以上でパーティション分割でのデータ管理を行う準備は完了となります。

続いて以下のようなデータを挿入した際の参照結果を見ていきましょう。

ファイルグループ	件数
FG1	2000
FG2	2000
FG3	2000
FG4	2000
FG5	2000

まずは、パーティション分割列の【create_date】（='2025/01/01'）を条件にSELECTクエリを実行した実行プランです。

select * from partition_db_table where create_date = '2025/01/01'

実行プラン結果

CPUの推定コスト	0.002357
I/Oの推定コスト	0.0097917
実際にアクセスされたパーティション	2

次にパーティション分割列ではない【status】(=1)を条件にSELECTクエリを実行した実行プランです。

select * from partition_db_table where status = 1

実行プラン結果

CPUの推定コスト	0.0117065
I/Oの推定コスト	0.0490368
実際にアクセスされたパーティション	1..5

「実際にアクセスされたパーティション」が【create_date】を条件にしたSELECTクエリが「2」のみを指しており、【status】を条件にしたSELECTクエリでは「1..5」となりすべてのファイルグループに対してアクセスしていることが分かります。
また、CPU、I/Oの推定コストについてもパーティション分割列を指定したSELECTクエリの方がパフォーマンスが良くなっていることが分かります。

今回は10,000件ほどのデータ量ですが、データ量がもっと大きくなればより顕著にパフォーマンスの違いが出てくると思いますので、大量データを扱う場合は有効な手法であると考えられます。

以上、パーティション分割の簡単な説明となりますが、