SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database V12 Preview のパーティショニングを使ってみる

leave a comment

SQL Database V12 Preview では新しい機能としてテーブルのパーティショニングがサポートされました。
この機能を少し試してみたいと思います。

テーブルのパーティショニング自体は、SQL Server 2005 の Enterprise Edition からサポートされており、V12 になって SQL Database でも利用できるようになりました。
テーブルのパーティショニングをする目的としては、

  1. 複数のファイルグループを使用することでパーティション単位にディスク I/O を分散させる
  2. 特定のパーティションのデータをアーカイブテーブルにスイッチ
  3. アーカイブテーブルへのスイッチ→ アーカイブテーブルTruncate を実施することで、特定パーティションのデータを瞬時に削除
    (DROP PARTITION がないため、一度他のテーブルに移し、Truncate Table する必要があります。)
  4. パーティション単位でインデックスのメンテナンス

というようなことが考えられるかと思います。
上記の 3 つの理由で考えた場合、SQL Database のパーティショニングでは [2] ~ [4] に対応することができます。
V12 の新機能として、DMV の拡充があります。
DMV と合わせて、システムビューも拡充されており、様々な情報が取得できるようになりました。
# Preview なので GA した際には動作が変わる可能性があります。
以下のクエリを実行してみます。

SELECT * FROM sys.filegroups
SELECT * FROM sys.database_files

 
このクエリではファイルグループとデータベースのファイルの情報を取得することができます。
# 本投稿で実行しているクエリは V12 Preview でのみ動作可能なものが大半です。現行のバージョンでは動作しません。
image
SQL Database では、 PRIMARY のファイルグループを使用しており、データファイルとログファイルの 2 ファイルでデータベースが構成されていることが確認できます。
SQL Database では PRIMARY ファイルグループのみがサポートされているため、ファイルグループを追加して、パーティションを分散させるということができません。
それでは、実際にパーティションテーブルを作成してみたいと思います。
V12 ではヒープテーブルが作成できるようになりましたので、今回はヒープで作成してイン佐生。

CREATE PARTITION FUNCTION PF_PTTEST(datetime2(0))
AS RANGE RIGHT FOR
VALUES ('2011-01-01', '2012-01-01', '2013-01-01', '2014-01-01', '2015-01-01', '2016-01-01')
GO
CREATE PARTITION SCHEME PS_PTTEST
AS PARTITION PF_PTTEST ALL TO([PRIMARY])
GO
CREATE TABLE PTTEST
(Col1 uniqueidentifier,
Col2 datetime2(0))
ON PS_PTTEST(Col2)

 
今回は日付を使用したパーティションテーブルを作成しています。
SQL Database では、PRIMARY ファイルグループのみが使用できますので、すべてのパーティションのデータは PRIMARY ファイルグループに格納をするように定義しています。
# 物理的なデータの配置ディスクを分割するのではなく、単一のファイルグループを使用した論理的な分割を行います。
このテーブルに対して以下のクエリでデータを挿入します。

SET NOCOUNT ON
GO
DECLARE @cnt int = 0, @datetime datetime2(0) = '2010-01-01'
BEGIN TRAN
WHILE (@cnt <= 100000)
BEGIN
	INSERT INTO PTTEST VALUES(NEWID(), @datetime)
	SET @cnt += 1
	SET @datetime = DATEADD(d, 1, @datetime)
	IF @datetime >= '2017-01-01' SET @datetime = '2010-01-01'
END
COMMIT TRAN

 
どのようなデータが入っているかを確認してみます。

SELECT
	MIN(Col2),MAX(Col2)
FROM
	PTTEST
SELECT
	$PARTITION.PF_PTTEST(Col2) AS PartitionNo,
	COUNT(*), MIN(Col2), MAX(Col2)
FROM
	PTTEST
GROUP BY
	$PARTITION.PF_PTTEST(Col2)

image
2010-01-01 から 2016-12-31 までのデータがパーティションに分散されて格納されているのが確認できます。
パーティションの構成も確認をしてみたいと思います。

SELECT
	*
FROM
	sys.partition_range_values prv
	INNER JOIN
	sys.partition_functions pf
	ON
	prv.function_id = pf.function_id
	AND
	pf.name = 'PF_PTTEST'

image
設定した構成でパーティションが作成されているのが確認できますね。
それでは、次にパーティションのスイッチをしてみたいと思います。

CREATE TABLE PTTEST_ARCHIVE
(Col1 uniqueidentifier,
Col2 datetime2(0))
ALTER TABLE PTTEST
SWITCH PARTITION 2
TO PTTEST_ARCHIVE

2 番目のパーティションを移動しましたので、2011 年のデータが移動されているはずですね。
先ほどの確認用のクエリを実行して確認をしてみます。
image
2011 年のデータが移動されていますね。
アーカイブ用のテーブルを確認すると、2011 年のデータが存在していることが確認できます。
 

SELECT
	MIN(Col2),MAX(Col2)
FROM
	PTTEST_ARCHIVE

image
今回は移動先は非パーティションテーブルですが、アーカイブされたデータを戻すこともできます。
ここまでの状態で以下のスイッチを実行してみます。
 

ALTER TABLE PTTEST_ARCHIVE
SWITCH TO PTTEST
PARTITION 2

この状態では以下のエラーとなります。

メッセージ 4982、レベル 16、状態 1、行 51
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘v12TEST.dbo.PTTEST_ARCHIVE’ allow values that are not allowed by range defined by partition 2 on target table ‘v12TEST.dbo.PTTEST’.
ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル ‘xxxxxxx’ のチェック制約では、切り替え先テーブル ‘xxxxxxx’ のパーティション 2 で定義された範囲では許可されない値が許可されます。

アーカイブテーブルにはスイッチ先のパーティションに格納しようとしているデータ以外 (2011 年以外のデータ) が入っていることが保障されていません。
パーティションのスイッチで戻す場合には、スイッチ元のテーブルがスイッチ先のパーティションの範囲のデータであることを保証する必要があります。
そこで、以下のチェック制約を付与します。

ALTER TABLE PTTEST_ARCHIVE
ADD CONSTRAINT [CHK_PTTEST_ARCHIVE_01]
CHECK (Col2 BETWEEN '2011-01-01' AND '2011-12-31' AND Col2 IS NOT NULL)
GO

これにより、アーカイブからデータを戻すことが可能となります。
注意するポイントとしては NOT NULL を保証するというところです。
NOT NULL を保証していない場合、以下のエラーとなってしまいます。

メッセージ 4972、レベル 16、状態 1、行 52
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘v12TEST.dbo.PTTEST_ARCHIVE’ allows values that are not allowed by check constraints or partition function on target table ‘v12TEST.dbo.PTTEST’.
ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル ‘xxxxxx’ のチェック制約またはパーティション関数で許可される値が、切り替え先テーブル ‘xxxxxxxx’ のチェック制約またはパーティション関数では許可されません。

 
操作方法としては、通常のパーティションテーブルを触るのと同じ方法で使用することができそうですね。

Share

Written by Masayuki.Ozawa

12月 13th, 2014 at 8:31 pm

Posted in SQL Database

Tagged with

Leave a Reply