SQL Server ベースのデータベースで空き領域が少なくなった場合に、ストレージの増強を行わなくても「インデックスの再構成 (REORGANIZE)」で対応できることがあります。
実際に SQL Database で再構成をした環境が次の画像となりますが、再構成をすることで、最大のストレージサイズを変更しなくても、195 MB の容量を 6.67 GB まで空き領域を確保することができています。
対象となるインデックスの調査
最初に再構成を実行するメンテナンス対象のインデックスの調査をする必要があります。
調査には、このようなクエリ を使用することができます。
このクエリは「インデックス (テーブル) 内の未使用領域」が高いインデックスを抽出するものなり次のような情報を取得することができます。
「unused_page_MiB」が「インデックス内の未使用領域」となります。
この領域が多いインデックスに対してインデックスの再構成を実行することで、空き領域を効率的に削減することができます。
インデックスの再構成は「既存領域を使用したインデックスのオンラインメンテナンス」となり、インデックスの再構築 (REBUILD) とは異なり、現在の使用領域を活用しながら領域の最適化が実行されます。
そのため、空き領域が少ない状態でも処理を実行することができ、インデックスサイズと同等の空き領域が存在しなくても領域の解放を行うことができます。
空き領域が少ない場合には、ストレージサイズの増強を検討する必要がありますが、未使用領域が大きい場合には、インデックスの再構成で再利用可能な領域を確保して、データの格納を行うことができます。
今回使用したクエリであれば、インデックスのメンテナンスを行うための ALTER ステートメントも生成していますので、メンテナンスを実行するインデックスが確認できれば出力されているステートメントを実行することで、インデックスの再構成をすることができます。
インデックスの再構成時の状況の確認
インデックス再構成を実行した後の状況確認ですが、次のようなクエリで確認ができます。
DECLARE @index_id INT = 1;
DECLARE @partition_number INT = 72;
SELECT *,
-- SQL Database Only
(SELECT TOP 1 allocated_storage_mb - used_storage_mb FROM sys.dm_db_resource_stats ORDER BY end_time DESC) AS storage_unused_MiB
FROM
(
SELECT
i.name,
ps.partition_number,
(ps.reserved_page_count - ps.used_page_count) * 8 / 1024 AS unused_page_MiB,
ps.reserved_page_count * 8 / 1024 AS reserved_page_MiB,
ps.used_page_count * 8 / 1024 AS used_page_MiB,
reserved_page_count,
used_page_count
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i
ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.index_id = @index_id
AND ps.partition_number = @partition_number
) AS T
LEFT OUTER JOIN
(
SELECT
last_wait_type,
wait_resource,
reads,
writes,
percent_complete
FROM sys.dm_exec_requests
WHERE command = N'DBCC'
) AS T2
ON 1 = 1;
変数の設定内容はメンテナンスするインデックスに合わせて変更をする必要がありますが、実行すると次のような情報を取得できます。
順調にインデックスのメンテナンスが実行できていれば「unused_page_MiB」が減少し、これによりストレージの未使用領域の解放も行われます。
既に割り当てられているストレージの領域 (実ファイルサイズ) は縮小されないため、ストレージ上の使用サイズも減少させたい場合には、DBCC SHRINKFILE を実行する必要があります。
ファイルサイズは変更せず、ファイル内の使用領域を削減し、再利用可能な領域を増やすことが主目的であれば、インデックスの再構成で対応ができます。
再構成はシングルスレッドで動作するため、処理に時間はかかりますが、既存の領域を活用しながら処理が実行できますので、既に空き領域が少ない場合には、再構築は実行できなくても再構成により空き領域を確保できる可能性が高いです。