SQL Server では、DBCC SHRINKFILE という DBCC コマンドを使用することで、データファイル / ログファイルのサイズを圧縮することができます。
データベースのファイルを配置しているドライブの空き容量の不足や、大量の一時的なデータを投入後に、データの削除を行った後にデータベースの物理ファイルが過剰に増加している場合に、ファイルサイズを小さくするというような場合に使用するもので、一般的には日常的に使用するものではありません。
しかし、何らかの理由によってデータベースのファイル (mdf / ndf / ldf) が肥大化し、DBCC SHRINKFILE の実行が必要となるケースもあるのではないでしょうか。
本投稿では、DBCC SHRINKFILE を使用して物理データファイルを圧縮し、サイズを削減する際にはどのような動作になっているのかをまとめておきたいと思います。
Contents
ファイル削減のための 2 つのモード
DBCC SHRINKFILE を実行する際には、次の二つのモードがあり、データファイルの状態に応じて、どちらのモードを使用するかが変わります。
- TRUNCATEONLY によるファイル圧縮
- データ移動を伴うファイル圧縮
データファイルの末尾に空きがある場合には「TRUNCATEONLYによるファイル圧縮」が最適なモードです。
データファイルの末尾には空きがないが、データファイル全体でみると、ところどころに空きがあるようなケースでは、「データ移動を伴うファイル圧縮」を使用することで、データファイルの空きを確保することができます。
今回は次のようなデータを使用して動作を見ていきたいと思います。
DROP TABLE IF EXISTS T1 DROP TABLE IF EXISTS T2 DROP TABLE IF EXISTS T3 CREATE TABLE T1(C1 INT IDENTITY PRIMARY KEY,C2 VARCHAR(1000)) CREATE TABLE T2(C1 INT IDENTITY PRIMARY KEY,C2 CHAR(1000)) SET NOCOUNT ON GO DECLARE @CNT INT = 1 BEGIN TRAN WHILE(@CNT <= 10000) BEGIN INSERT INTO T1 VALUES(NEWID()) INSERT INTO T2 VALUES(NEWID()) SET @CNT += 1 END CREATE TABLE T3(C1 INT IDENTITY PRIMARY KEY,C2 VARCHAR(1000)) INSERT INTO T3 VALUES(NEWID()) COMMIT TRAN DROP TABLE T2
クエリで実行している内容はシンプルで、T1 と T2 に交互にデータの挿入を行い、最後に T3 に 1 レコードのみ挿入を行います。
その後、T3 にレコードを挿入した後に T2 を削除し、データに空きを作るというクエリです。
というようなレコードの配置となっているものが、
というような構成となるイメージです。
(イメージのため、T3 が実際のデータファイルの配置として末尾になっているかというとそういうわけではないのですが)
データベースのファイル構成としては、次のようになっており、シンプルな構成としています。
TRUNCATEONLY によるファイル圧縮
それでは、最初に TRUNCATEONLY によるデファイル圧縮を実施してみたいと思います。
TRUNCATEONLY を使用したデータ圧縮のクエリは次のようになります。
DBCC SHRINKFILE (N'SHRINK_TEST' , 0, TRUNCATEONLY)
SHRINK_TEST というデータファイル (mdf) を TRUNCATEONLY で圧縮するというクエリです。
今回は SHRINK 前後のファイルサイズを取得できるように、次のようなクエリで実行してみます。
SELECT name, size, size * 8 AS size_kb FROM sys.database_files WHERE type = 0 DBCC SHRINKFILE (N'SHRINK_TEST' , 0, TRUNCATEONLY) SELECT name, size, size * 8 AS size_kb FROM sys.database_files WHERE type = 0
実行結果が以下になるのですが、ファイルサイズは変わっていませんね。
TRUNCATEONLY の説明には次のように記載されています。
TRUNCATEONLY
ファイル末尾のすべての空き領域をオペレーティング システムに渡します。
ただし、ファイル内でのページの移動は行われません。
データ ファイルは、最後に割り当てられたエクステントを限度として圧縮されます。
TRUNCATEONLY は最後に割り当てられたエクステントを限度として圧縮を行うため、データファイルの最後尾が使用されていない領域となっている必要があります。
そのため、データファイルの物理的なレイアウトの末尾が未使用となっていない場合は、圧縮により、解放可能なデータ領域が存在せず、データファイルの状態によっては圧縮をしても効果がないケースがあります。
上記のようなデータファイルの構造となっている場合は、TRUNCATEONLY を実行することで、
となり、データファイルの物理的なファイルサイズを圧縮することができるのですが、ファイルの後ろに空き領域が確保されていない場合は、TRUNCATEONLY を使用してデータファイルの圧縮を行っても、効果はありません。
しかし、TRUNCATEONLY は、データの移動は発生せずに圧縮することが可能なため、高速に処理を行うことができます。
データファイルの圧縮を行おうとした場合に、最初に TRUNCATEONLY で実施し、データファイルの末尾の解放で、データファイルの圧縮ができるかを試し、TRUNCATEONLY で思ったより圧縮できていない場合に、「データ移動を伴うデータ圧縮」を使用するという考えでいるのが良いのではないでしょうか。
データ移動を伴うファイル圧縮
それでは次にデータ移動を伴うふぃある圧縮を見てみましょう。
現状、レイアウトのイメージとしては次のようになっています。
T2 で使用していた領域がところどころ空き領域として利用可能な状態になっています。
このような状態になっている場合、データ移動を伴うデータ圧縮を実行することでデータファイルを圧縮することができます。
データ移動を伴うデータ圧縮のイメージは下図のようなものとなります。
データの格納を行っているデータページを空きページの領域に移動することで、データファイルの末尾に未使用な領域の確保を行います。
データの移動が完了し、データファイルの末尾に空きができたらその領域を解放することで、データファイルの物理サイズを小さくするというような動作が内部では行われています。
これらのデータ移動操作はトランザクションログに記録されています。
select [Parent Transaction ID],[Transaction Name], Operation,Context,AllocUnitName,[Page ID],[Log Record Length] from sys.fn_dblog(null, null)
次の画像は、一部を抜粋したものとなります。
SwapPage/DeallocTempSwapPage というトランザクション名のトランザクションが記録されており、SwapPage のトランザクションでは、T1 のページの操作が行われていることが確認できますね。この部分がデータの移動となるかと思います。
データの移動では、空き領域のページに対して、既存のページのデータを移動させるという操作が行われており、後方にあったページ全体が前方の空き領域に移動されるという動作が行われているようです。
新しいページに対して INSERTが行われていますが、8KB 程度のログレコードのサイズとなっているようですので、ページ内の全レコードが新しいページに対して移動 (スワップ) されることで、空き領域の確保が行われているようです。
ページの移動の操作についても、トランザクションログに記録が行われているため、データ圧縮時にはトランザクションログのサイズについても注意をしておく必要がありそうです。
データ移動を伴うデータ圧縮でポイントとなるのはドキュメントの次の記載です。
ページは、ファイルの保持領域内の空き領域に移動されます。 たとえば、10 MB のデータ ファイルで、8 target_size を指定した DBCC SHRINKFILE 操作を実行すると、ファイルの末尾 2 MB 内にあるすべての使用ページがファイルの先頭 8 MB にある未割り当てページに移動されます。 DBCC SHRINKFILE では、格納されているデータ サイズ以下に、ファイルを圧縮することはできません。 たとえば、10 MB のデータ ファイルのうち 7 MB が使用されている場合、target_size を 6 にして DBCC SHRINKFILE ステートメントを実行しても、ファイルは 7 MB にまでしか圧縮できず、6 MB にはなりません。
圧縮する際には、目標となるサイズを指定しますが、指定したサイズによって、どの程度、ファイルの末尾にあるデータを前方に移動するかが決まります。
そのため、大きく圧縮を行おうとした場合、大量のデータ移動が発生し、処理に時間がかかる可能性があります。
データファイルの圧縮は一つのトランザクションとして実行されているようで、途中で中断した場合、それまでの移動は無効になり、データの格納状態は初期状態となるようです。
500 GB のデータを 200 GB に圧縮する場合、最初から 200GB に設定するのではなく、500 GB -> 350 GB、350 GB -> 200 GB のようにサイズを少しずつ刻んで圧縮を行った方がよいケースもあるのではないでしょうか。
データ移動を伴うファイル圧縮による性能への影響
データ移動をともなうデータ圧縮ですが、データ格納を行っているページの移動が発生します。
これにより、ページの断片化が発生します。
ページの断片化の種類ですが、ページ密度の現象ではなく、ページの連続性の損失に伴うページ / エクステントスキャンフラグメンテーションにつながってきます。
DBCCSHRINKFILE によるファイル圧縮は、ページの移動により、データの連続性が低下するということは動作の基本として覚えておくと良いか。
システム稼働中のデータファイルの圧縮
DBCC SHRINKFILE を実行する場合に一番気になるのはシステム稼働中に実施しても良いかではないでしょうか。
ドキュメントには次のように記載されています。
ファイルの圧縮中に他のユーザーはデータベースで作業できます。データベースをシングル ユーザー モードにする必要はありません。 システム データベースを圧縮するために、SQL Server インスタンスをシングル ユーザー モードで実行する必要はありません。
データファイルの圧縮は、オンラインで実施できる操作として記載されています。
基本的な操作についてはオンライン操作ということで問題はないのですが「データ移動を伴うファイル圧縮」については、移動対象のページに対して、瞬間的に「LOCK_X_PAGE」が設定されます。
データ移動を伴うファイル圧縮で、移動対象となったページについては、移動タイミングで瞬間的にページに対しての排他ロックの取得が発生しますので、移動対象となるページのロック保持については少し意識しておく必要があるかと思います。
「どのテーブルのページが移動されるのか？」を見極めるのは難しく、どのオブジェクトに対してロックがかかっていなければ問題ないかという判断は厳しいのですが、移動対象のページに対しての瞬間的な排他ロックだけは意識しておくと良いのではないでしょうか。
オブジェクト全体にはロックはかかっていないように見えますので、同時実行性の低下は局所的なものかとは思いますが。
まとめ
データファイルの圧縮ですが、「どの領域が圧縮されるのか？」「目標サイズを指定した場合、どのようにページが移動して空き領域が作成されるのか？」が基本動作を理解する上でのポイントとなるのではないでしょうか。
基本動作のイメージができれば、様々な情報から動作を追うことはできるかと思います。