SQL Server: パーティション分割について

パーティション分割について調べてみたことをまとめます。

公式ドキュメントは[こちら](https://docs.microsoft.com/ja-jp/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15)

# パーティションについて
上記公式ドキュメントによると、以下の3つのメリットがある

- データのサブセットへのアクセスや転送が高速化
- メンテナンス操作（削除やインデックス再構築など）の高速化
- クエリのパフォーマンス改善

# クエリレベルでの実施方法
## ①パーティション関数の作成
データを「どのようにパーティショニングするか」という定義

```sql
CREATE PARTITION FUNCTION PF_Sample(datetime2) --datetime2 : VALUESに指定する値のデータ型
AS RANGE RIGHT --設定している値をパーティションの右側に置くのか、左側に置くのか、という設定。今回は「右」にしており、2021/1/31より昔のデータがpartition_number=1となる。
FOR VALUES('2021/02/01', '2021/03/01', '2021/04/01') --パーティションの区切りとなる値を指定
GO
```
※パーティション関数の定義において、「どのようにパーティショニングするか」（パーティションの区切りとなる値）については、一つ一つ直接値を明示的に指定する必要がある
※VALUESで指定する値の数は、1000がパフォーマンスへのオーバーヘッドの閾値と言われている。上限値としては15000までは作れる。
　参考：[パフォーマンスに関するガイドライン](https://docs.microsoft.com/ja-jp/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15#performance-guidelines)


## ②パーティション構成の作成
「各パーティションのデータをどのファイルグループに配置するか」を定義

```sql
CREATE PARTITION SCHEME PS_Sample
AS PARTITION PF_Sample
ALL TO ([PRIMARY]) --ALL : 全部PRIMARYファイルグループにいれる
GO
```

## ③パーティションテーブルの作成

作成したパーティション構成を指定する。
また、「どのカラムをもとにパーティションを分割するか」を「パーティション分割列」として指定する。

```sql
CREATE TABLE PartitionSample (
	c1 int identity
	,c2 varchar(36)
	,c3 varchar(36)
	,c4 datetime2 default getdate()
	,CONSTRAINT PK_PartitionSample PRIMARY KEY CLUSTERED (C1 ,C4)
	) ON PS_Sample(C4) --C4:パーティション分割列 / パーティショニングキーと呼ばれる
GO
```

### 注意点
主キーやユニーク制約などの一意インデックスに対してパーティショニングする場合、キー項目をパーティション分割列に含める必要がある。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/792e0274-4acc-a7ee-07a4-030b41d330f6.png)

# メリット / デメリット
## メリット

- パーティション単位でデータメンテナンスが可能
 - パーティション単位の再構成 / 再構築
 - パーティション単位の Truncate
- パーティション単位でデータのアーカイブ可能
 - パーティションのスイッチによる別テーブルへのアーカイブ
- 物理 I/O の分散
 - パーティション単位でファイルグループを指定することができるため、データを格納する物理ファイルをパーティション単位に分散させることができる
 - ※ファイルグループを統一（例：All to PRIMARY)していた場合はこの効果は見込めない。
-- パーティション分割列を使用した検索の効率化
 - パーティション分割列を使用した検索によるスキャンが発生した場合、該当するパーティションのみがアクセスされるため、パーティション分割列を使用した検索については、スキャンのオーバーヘッドを抑えることができる

## デメリット

- パーティション分割によるオーバーヘッドの発生
 - 通常のテーブルと比較すると、テーブルの構成が複雑となるため、ミリ秒またはそれ以下の処理時間でのオーバーヘッドは発生する
- パーティションのメンテナンスが必要となる
 - 時系列でパーティショニングした場合、必要となるパーティションが存在しているかのメンテナンスが必要となる
     - 例えば月ごとの値でパーティションの範囲を決めている場合、10年先まで（120個くらい）最初に入れておく、とかはありだが、10年後に再度メンテする必要がでてくる
- インデックスと実データのパーティションを固定化する必要がある
 - インデックスと実データのパーティションの構成が同一になっていないと、Truncate やパーティションのスイッチを行うことができない。
     - ※片方はパーティション化されていて、片方はパーティション化されていない、っていうのでもNG
     - そのため、インデックスを新規に作成する際には、適切なパーティション構成で作成を行う必要がある
- パーティション分割列を変更した場合のオーバーヘッドの発生
 - UPDATE をした際には、DELETE / INSERT により、他のパーティションへのデータ移動をする
 - ※上記の例だとc4をUPDATEした場合がこれに該当。registerDTとかだと基本UPDATEされないため、このオーバーヘッドは気にしなくてもOK

# 用途

- パーティション分割列で検索された場合のスキャン範囲の限定
- パーティション単位のデータの削除
-パーティション単位のデータのアーカイブ
- パーティション単位のデータのメンテナンス (再構成 / 再構築)
- パーティション単位の物理データ格納領域 (ファイルグループ) の分散

# 運用例
### テストデータのINSERT

```sql
set nocount on
insert into PartitionSample (c2, c3, c4)
values (newid(), newid(), dateadd(day, cast(rand() * 100 as int), '2021/1/1'))
go 1000
```

### パーティション分割列で検索された場合のスキャン範囲の限定

```sql
select count(*) from PartitionSample
where c4 between '2021/03/01' and '2021/03/31'
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/3ba5d34f-bea9-2d13-0e50-17fd106157d8.png)

「実際にアクセスされたパーティション」アクセスが発生したパーティション番号
「実際のパーティション数」アクセスが発生したパーティションの数

今回の例だと、パーティション番号3のパーティション1つだけを読み取っているのでIndex Scanの読み取りページ数が通常時よりも削減できていることが分かる。

### パーティション単位のデータの削除

インデックスを一つ作ってみる

```sql
create index ix_PartitionSample_c2 on PartitionSample(c2)
```

作成したインデックスがパーティショニングされるか確認

```sql
select * from sys.dm_db_partition_stats where object_id = object_id('PartitionSample')
```

↓パーティション番号が1から4までバラけているため、特に指定しなくても自動でパーティショニングされていることが分かる。このように、parittion_numberがクラスタ化インデックスと同じ構成のものを「固定されたインデックス」と呼ぶ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/c7eece51-0f3a-7d1b-bbea-e86711579e23.png)

わざとON PRIMARYつけてみる

```sql
create index ix_PartitionSample_c3 on PartitionSample(c3) on [primary]
```

再度確認すると、1つのパーティションしか使われていないインデックスになっている（＝パーティション分割されていない）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/2e5dc5c8-f8a2-c824-5f70-9fd26bedb13d.png)

パーティション指定のTRUNCATEしてみる

```sql
truncate table PartitionSample with (partitions(2)) --2:任意のpartition_number
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/0fc15cd2-1c99-6388-4a30-eab7f8c481ad.png)

失敗する。
パーティショニングされていないインデックスをDROPしてからtruncateすると成功する

```sql
drop index ix_PartitionSample_c3 on PartitionSample
truncate table PartitionSample with (partitions(2))
```

値を入力すると、その値がどのパーティション番号に該当するか返してくれる関数

```sql
select $PARTITION.[PF_Sample]('2021-01-01')
select $PARTITION.[PF_Sample]('2021-02-01')
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/162cf211-5c69-b411-b0fc-fe6f2e7ef197.png)

この関数を使って以下のようにTRUNCATEを書き換えられる

```sql
truncate table PartitionSample WITH (PARTITIONS($PARTITION.[PF_Sample]('2021-01-01'))) --2021/1/1のデータが入っているパーティションの全データをtruncate
```

### パーティション単位のデータの削除（バージョン2016より前の場合）
[ドキュメント](https://docs.microsoft.com/ja-jp/sql/sql-server/sql-server-offline-documentation?view=sql-server-ver15#sql-server-2012-offline-content)によると、TRUNCATE TABLEの構文でwith (partitions())がサポートされていない。

したがって、以下の手順で代替する必要がある。

１．アーカイブ用テーブルを作成 ※パーティションはされておらず、それ以外のスキーマは同じものでもOK

```Sql
CREATE TABLE PartitionSample_Archive (
    c1 int identity
    ,c2 varchar(36)
    ,c3 varchar(36)
    ,c4 datetime2 default getdate()
    ,CONSTRAINT PK_PartitionSample_Archive PRIMARY KEY CLUSTERED (C1 ,C4)
    ) 
GO
```

２．削除したいパーティション番号を特定し、アーカイブ用テーブルにスイッチ（移し替え）

```sql
DECLARE @PartitionNo INT

SELECT @PartitionNo = $PARTITION.[PF_SAMPLE]('2021/04/01')

SELECT @PartitionNo

--この時点で元テーブルからはデータが消える
ALTER TABLE [dbo].[PartitionSample] SWITCH PARTITION @PartitionNo TO [dbo].[PartitionSample_Archive];
```

※同時に2パーティション以上スイッチしたい場合はアーカイブ用テーブルもパーティション化が必要。
※アーカイブ用テーブルをパーティション化しておらず、かつ空じゃないときはスイッチ時に以下のエラーがでるので、空にしてから実施
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/e6e84e37-8886-e5a8-a902-84a45d2fc2a7.png)


いずれにせよ、同一ファイルグループに属している必要はある。SWITCHはファイルグループ内でポインタをつけかえるだけなので。ファイルグループをまたぐとmdf/ndfの物理ファイルが変わってしまうためポインタの移動ができない。

スイッチ実行により指定パーティションのデータがArchive側に移ったことを確認。

```sql
select * from sys.dm_db_partition_stats where object_id = object_id('PartitionSample')
select * from sys.dm_db_partition_stats where object_id = object_id('PartitionSample_Archive')
```

３．アーカイブ用テーブルをTRUNCATE

```sql
TRUNCATE TABLE [dbo].[PartitionSample_Archive]
```

### パーティション単位のデータのメンテナンス

```sql
alter table PartitionSample rebuild partition=2
```

# 既存テーブルをパーティション化したい場合
１．普通のPKのクラスタ化インデックスがPRIMARYファイルグループに存在するとする

```sql
CREATE TABLE Sample (
    c1 int identity
    ,c2 varchar(36)
    ,c3 varchar(36)
    ,c4 datetime2 default getdate()
    ,CONSTRAINT PK_Sample PRIMARY KEY CLUSTERED (C1)
    ) 
GO
```

２．一度PK制約を削除

```Sql
ALTER TABLE Sample DROP CONSTRAINT PK_Sample
```

３．パーティションキー含めた主キーで作り変え

```sql
-- not null制約追加
ALTER TABLE Sample ALTER COLUMN c4 datetime2 not null

--主キー作り変え
ALTER TABLE Sample
ADD CONSTRAINT PK_Sample PRIMARY KEY CLUSTERED (c1, c4) ON PS_Sample(c4) 
```

ということで、既存のテーブルをパーティション化するハードルは高めな印象。PK制約の削除時にデータ量が多いテーブルだと長時間Sch-Mロックがかかるはず。
データ量少なければいけそうだが、そもそもデータ量少ないテーブルにパーティション化するメリットも無いと思われる。

# パーティション関数のALTER
### SPLIT
月ごとに区切った場合、月が枯渇した場合に未来の年月をパーティション関数に追加する必要がでてくる。

```Sql
select * from sys.dm_db_partition_stats where object_id = object_id('PartitionSample')

--レンジを分割する（パーティションの区切りを追加）
ALTER PARTITION FUNCTION [PF_Sample]() SPLIT RANGE ('2021/05/01')

select * from sys.dm_db_partition_stats where object_id = object_id('PartitionSample')
```

パーティション番号が一つ追加されていることが分かる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/3a0b0b57-a288-aaee-928a-77f6e2c04b64.png)
↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/102a543f-2393-9360-8f45-de34bc273f17.png)

### MERGE
パーティションの区切りを削除したい場合はMERGEを使う。

```sql
ALTER PARTITION FUNCTION [PF_Sample]() MERGE RANGE ('2021/02/01')
```

パーティション番号が一つ減る。


# パーティション化の注意事項
パーティション分割列との複合キーになるため、IDの重複が許容される。(だからといってIDに別途UQ制約をつけるとパーティションのメリットを享受できない）

IDENTITYとしておけば基本的には重複発生しないが、SET IDENTITY_INSERT ONの状態でINSERTすると重複レコードを挿入できてしまうので、IDカラムの一意性についてはアプリケーション側で担保する。

# パーティション作成のガイドライン
前提として、パーティションは全テーブルに対して作成するようなものではない。
パーティション作成のメリットを享受したい場合にのみ、パーティションの作成を検討する。

### 注意点
１．パーティションを作成する際は、パーティション関数に指定する領域値が枯渇しないように指定する
　　
一番右側のパーティションのレコード数は常にゼロ。月単位であれば、30年分(12*30=360)くらいを事前に入れておくのはあり。

２．基本的に1テーブルにつき1パーティション関数を用意
SPLITやMERGEを別々に管理できるため。でもこのメリットを享受する必要がなければ1パーティション関数を共通で使う方があり。