Azure SQL Database Hyperscale で Preview として利用できるようになったファイルの縮小について at SE の雑記

今まで Hyperscale では、ファイルの縮小 (SHRINK) を実行することができず、一度割り当てられたデータファイルの領域については縮小をすることができませんでした。

そのため、一時的にでもデータファイルのストレージが拡張されてしまうと、ストレージを縮小することができず、ストレージコストを削減するためには、新しく作成したデータベースにファイルをコピーするというような新規 DB の作成によるデータファイルの使用済み領域の削減を検討する必要がありました。

これを改善するための機能として、2024-07 に Public Preview: Shrink for Azure SQL Database Hyperscale のアナウンスがあり、Hyperscale でもデータファイルの縮小が可能となりました。

Contents

1 Hyperscale のストレージコスト
2 未使用領域の確保状況の確認
- 2.1 データベースのデータファイルの使用領域と未使用領域の状態
- 2.2 テーブルの使用領域と未使用領域の状態
3 データファイルの圧縮方法
4 SHRINKFILE の効果

Hyperscale のストレージコスト

Hyperscale は 100TB までのデータベースをサポートしており、ストレージの料金については、Azure SQL Database の価格から確認することができます。

2024-09-08 時点では、\43.604/GB/月となっており、データファイルの拡張は 10GB 単位で実施されます。

そのため、一度ファイルの拡張が行われると、\436.04 のストレージコストが加算されることになります。

TB クラスのデータベースだとデータベースのストレージコストも高くなり、コスト削減にはストレージコストの削減も重要となってきます。

未使用領域の確保状況の確認

未使用領域がどれだけ確保されているかですが、冒頭に紹介したアナウンス内にクエリが記載されています。

そのクエリを変更しているのが以下のクエリとなります。

SELECT
	*
	, SUM(AllocatedSpaceMB) OVER() AS TotalAllocatedMB
	, SUM(UsedSpaceMB) OVER() AS TotalUsedSpaceMB
FROM
(
	SELECT 
	 file_id AS FileId 
	 , name
	 , size * 8 / 1024 AS AllocatedSpaceMB  
	 , ROUND(CAST(FILEPROPERTY(name, 'SpaceUsed') AS float) * 8 / 1024 ,3) AS UsedSpaceMB 
	 , ROUND((size-CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)) * 8 / 1024 ,3) AS FreeSpaceMB
	 , ROUND(max_size * 8 / 1024, 3) AS MaxSizeMB
	 , ROUND(CAST(size - FILEPROPERTY(name, 'SpaceUsed') AS float)*100/size,3) AS UnusedSpacePercent
	 , 'DBCC SHRINKFILE(' + CAST(file_id AS varchar(2)) + ', TRUNCATEONLY)' AS Shrink_TruncateOnly
	 , 'DBCC SHRINKFILE(' + CAST(file_id AS varchar(2)) + ', ' + CAST(ROUND(CAST(FILEPROPERTY(name, 'SpaceUsed') AS float) * 8 / 1024 + 1024 ,-2) AS varchar(20)) + ') WITH WAIT_AT_LOW_PRIORITY ' AS Shrink_Command
	FROM sys.database_files
	WHERE type_desc = 'ROWS' 
) AS T
ORDER BY FileId

SELECT 
	ROUND(CAST(SUM(reserved_page_count) AS float) * 8 / 1024, 3) AS ReservedPageMB,
	ROUND(CAST(SUM(used_page_count) AS float) * 8 / 1024,3) AS UsedPageMB
FROM sys.dm_db_partition_stats

このクエリを実行すると、次のような情報を取得できます。

未使用領域について、確認をするポイントは 2 つあるかと思います。

データベースのデータファイルの使用領域と未使用領域の状態

ひとつ目が「データベースのデータファイルの使用領域と未使用領域の状態」です。

上記の結果であれば、割り当てられているデータファイルのサイズ (TotalAllocatedMB) は、253,952 MB (253 GB) となります。しかし、実際に使用されているサイズについては 28,096.626 MB (28 GB) となっており、225 GB が未使用の状態となっています。

今後、この領域を使用する可能性があるのであれば、割り当てていて良いかと思いますが、臨時の作業 (例: データ調査用にコピーテーブルの作成) により、一時的に肥大化したものであり、直近で使用する予定が無いのであれば、このデータファイルの割り当ては無駄な部分がある状態となります。

2024-09-08 時点の GB 辺りの単価は、\43.604 となっていますので、この領域のコストを減らすことができると、\9,810.9/月の削減となります。

テーブルの使用領域と未使用領域の状態

ふたつめが「テーブルの使用領域と未使用領域の状態」です。

先ほどの例は、データベースのファイル単位で使用状況を確認 (一つ目のクエリの実行結果) することで、どれだけ未使用の領域があるかを家訓していましたが、今回の確認内容は二つ目のクエリの実行結果を使用するものとなります。

ワークロードによっては次の画像のようなデータベースファイルの使用状況となることがあります。

一つ目のクエリの実行結果では、割り当て領域が 225,280 MB (225 GB) 、使用済みの領域が 224,594.875 MB (224 GB) となっています。

この情報を見ると、使用済みの領域が多いように見えますが、テーブル単位で集計した情報 (二つ目のクエリの実行結果) を確認してみると、割り当て済み領域 (ReservedPageMB) は、224,575.305 MB(224 GB)、使用済み領域 (UsedPageMB) が 28,075.102 MB (28 GB) となっています。

データファイルの使用状況として確認した場合は、未使用領域は無いように見えるのですが、テーブル単位の使用状況で集計した場合には、未使用領域があることが確認できます。

このような割り当てが起こるのは、テーブルの断片化や Bulk Insert の高速挿入が行われた場合です。テーブル用の領域として割り当てられているが、未使用なページが多いというような状況の確認については、二番目のクエリを実行することで確認ができます。

この観点で確認した場合、196 GB が未使用である可能性があり、先ほどと同様の計算をすると、未使用領域を解放することができると、\8,546 のコストを錯塩できる可能性があります。

データファイルの圧縮方法

データファイルの圧縮は DBCC SHRINKFILE を使用して実行することになりますが、いくつかのポイントをまとめておきたいと思います。

コピーしたデータベースを使用して検証を行う

Hyperscale はデータベースのコピーを高速に作成 (復元) することができるのが機能の特徴としてあります。

データファイルの圧縮により、ストレージを縮小することができるかの確認については、コピー環境を作成して事前に効果測定をするのが良いのではないでしょうか。

TRUNCATEONLY で縮小ができるかを確認する

データファイルの使用状況によるのですが、DBCC SHRINKFILE を TRUNCATEONLY オプションで実行してファイルが縮小できるのであれば、それが処理時間を短くしてファイル縮小を行う最善の方法となります。

本投稿のクエリの実行結果の「Shrink_TruncateOnly」として出力しているコマンド (例: DBCC SHRINKFILE(1, TRUNCATEONLY)) を使用して実行するケースとなります。

TRUNCATEONLY

ファイル末尾のすべての空き領域がオペレーティングシステムに解放されますが、ファイル内でのページの移動は行われません。データファイルは、最後に割り当てられたエクステントを限度として圧縮されます。

上記はドキュメントの TRUNCATEONLY の説明となります。

TRUNCATEONLY を指定することでデータの移動を発生させることなく未使用領域の解放を行うことができます。

指定したファイルの後方が空き領域になっていないとファイルの縮小を行うことはできませんが、ファイルの後方に大量の空き領域があるケースでは TRUNCATEONLY を指定して実行することで、瞬時に処理を完了することができます。

縮小を行う際には TRUNCATEONLY を指定して効果があるのかを最初に確認し、それで効果があるのであれば、「データ移動を伴うファイルの縮小」(TRUNCATEONLY を指定しないファイルの縮小) は見送るというのも選択肢として視野に入れることができます。

データ移動を伴うファイルの縮小

TRUNCATEONLY を指定して実行してもファイル縮小の効果が無かった場合、データ移動を伴うファイルの縮小 (ファイル末尾の情報を前に移動させることでファイル後方の空き領域を確保する) を検討する必要があります。

クエリの実行結果でいうと「Shrink_Command」として出力しているコマンド (例: DBCC SHRINKFILE(1, 123300) WITH WAIT_AT_LOW_PRIORITY) となります。

このパターンのファイルの縮小を実行する場合、インデックスの再構成 (REORGANIZE) または再構築 (REBUILD) と組み合わせて実行を検討する必要があります。

単純に DBCC SHRINKFILE を実行してファイルの縮小が実行できれば良いのですが、ファイルの縮小が実行できない場合、インデックスのメンテナンスを実行して「テーブルとして割り当てられている領域の解放」を行ってからでないと、SHRINKFILE を実行しても効果がないケースがあります。

そのため、データ移動を伴うファイルの縮小は、

DBCC SHRINKFILE を NOTRUNCATE を指定せずに実行する

または、

インデックスの再構成 / 再構築を実行
完了後にDBCC SHRINKFILE を NOTRUNCATE を指定せずに実行する

のどちらの実行が効果があるかを確認するのが良いかと思います。

SHRINKFILE の実行の進捗については、次のようなクエリで確認をすることができます。

SELECT command, wait_type, percent_complete, event_info
FROM sys.dm_exec_requests 
OUTER APPLY sys.dm_exec_input_buffer(session_id, 0)
WHERE command LIKE 'DBCC%'

本投稿で記載した圧縮のクエリ生成では「WITH WAIT_AT_LOW_PRIORITY」のオプションを指定して、データ移動を伴うファイル圧縮を実行しています。

そのため、ファイル圧縮は低優先のロックで実行されており、1 分間のロック競合が発生した場合は、SHRINKFILE 側が終了されます。

SHRINKFILE は再実行すると途中から実行されますのでエラーが出た場合は何回か再試行をさせながら最後まで処理を完了させるようにします。

SHRINKFILE で実際に使用領域が解放されるのは最後のフェーズとなりますので、最後のフェーズにたどり着かないとデータ移動のみが実行され、ファイルサイズの縮小が行われません。

SHRINKFILE の効果

今回準備したデータベースは、253,952 MB (253 GB) 割り当てられているが、実際の使用サイズは 28,096MB (28GB) となっている状態でした。

このデータベースを縮小すると、次のようになりました。

データベースに割り当てられているファイルサイズは 31,200 MB(31 GB) となり、実際の使用サイズは 28,082 MB (28GB) となっています。

割り当て領域が 253 GB → 31 GB まで減少しましたので、222 GB の削減となりました。GB 単価が \43.604 の場合は、\9,680/月の削減となり、年額で \116,160 削減できることになります。

SHRINKFILE を実行するとデータの断片化が発生し、アクセス効率とのトレードオフとなる点はありますが、ストレージコストの削減としての効果が期待できるケースがあります。

ストレージコストが大きい場合には、SHRINKFILE によるデータファイルのサイズ削減は検討してもよいのではないでしょうか。

SE の雑記