トランザクションログの圧縮とデータファイルの圧縮を混同しないでください。 ジャーナルが許容限度を超えて成長した場合、または仮想ログファイルの過度の断片化を解消する場合は、ログ圧縮が必要です（Kimberlyの素晴らしい記事（ こちら （英語）およびこちら （英語）を参照））。 ただし、トランザクションログの圧縮はまれな操作であり、実行する定期的なメンテナンスプログラムの一部であってはなりません。
データファイルの圧縮は、実行される頻度が低い場合でも、実行する必要があります。 その理由は次のとおりです。データファイルを圧縮すると、 深刻なインデックスの断片化が発生します。 自分で実行できる簡単なスクリプトでこれを示しましょう。 以下のスクリプトは、データファイルを作成し、データファイルの先頭に10MBの「フィル」テーブルを作成し、「本番」10MBクラスターインデックスを作成してから、新しいクラスターインデックスの断片化を分析します。
USE [master]; GO IF DATABASEPROPERTYEX (N'DBMaint2008', N'Version') IS NOT NULL DROP DATABASE [DBMaint2008]; GO CREATE DATABASE DBMaint2008; GO USE [DBMaint2008]; GO SET NOCOUNT ON; GO -- -"" 10 CREATE TABLE [FillerTable] ( [c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'filler'); GO -- - INSERT INTO [FillerTable] DEFAULT VALUES; GO 1280 -- , "" CREATE TABLE [ProdTable] ( [c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'production'); CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]); GO INSERT INTO [ProdTable] DEFAULT VALUES; GO 1280 -- SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats ( DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED'); GO
avg_fragmentation_in_percent ----------------------------- 0.390625
圧縮前のクラスターインデックスの論理的な断片化は、理想的な0.4％に近いです。
ここで、フィルテーブルを削除し、圧縮を開始して領域を解放し、クラスターインデックスの断片化を再度確認します。
-- -, 10 DROP TABLE [FillerTable]; GO -- DBCC SHRINKDATABASE ([DBMaint2008]); GO -- SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats ( DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED'); GO
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ----- ------- ------------ ------------ ---------- --------------- 6 1 1456 152 1448 1440 6 2 63 63 56 56 DBCC execution completed. If DBCC printed error messages, contact your system administrator. avg_fragmentation_in_percent ----------------------------- 99.296875
わあ！ 圧縮後、論理的な断片化はほぼ100％です。 圧縮操作によりインデックスが完全に断片化され、範囲スキャンを伴うすべての前方参照I / O操作が単一ページI / O操作であることを保証することにより、そのインデックス内の範囲を効率的にスキャンする機会を奪います。
なぜこれが起こったのですか？ データファイルの圧縮操作は、一度に1つのファイルで機能し、グローバル配布マップ（GAM）（記事「ストレージエンジン内：GAM、SGAM、PFS、およびその他の配布マップ」 、英語を参照）を使用して、ファイルにある最新のページを見つけます。 次に、このページをファイルの先頭にできるだけ近づけて、そのような操作を何度も繰り返します。 上記の状況では、これによりクラスターインデックスの順序が完全に拡張され、完全に最適化され、完全に断片化されました。
同じコードがDBCC SHRINKFILE、DBCC SHRINKDATABASEコマンドで使用されており、自動圧縮でも同様に不適切です。 また、インデックスの断片化とともに、データファイルの圧縮により多数の入出力操作が生成され、プロセッサ時間が積極的に使用され、トランザクションログに多数のエントリが生成されます。
データファイルの圧縮は、定期的なメンテナンスの一部であってはならず、自動圧縮を有効にしないでください。 私がこれを達成できる立場にあったときに、SQL Server 2005とSQL Server 2008から彼を除外しようとしました-まだ存在する唯一の理由は、後方互換性を確保するためです。 すべてのインデックスを再構築し、圧縮を開始してインデックスの再構築によって占有されたスペースを解放しようとするメンテナンスプランを作成するというintoに陥らないでください-これはゼロサムゲームです。パフォーマンス。
それでは、いつ圧縮を開始する必要があるのでしょうか？ たとえば、非常に大規模なデータベースのほとんどを削除し、データベースが大きくなる可能性が低い場合、または削除する前にファイルをクリアする必要がある場合はどうでしょうか。
次の方法をお勧めします。
- 新しいファイルグループを作成する
- CREATE INDEX ... WITH構文...（DROP_EXISTING = ON）ONを使用して、関連するすべてのテーブルとインデックスを新しいファイルグループに移動して、テーブルを移動し、同時に断片化を削除します。
- とにかく圧縮しようとしていた古いファイルグループを削除します（または、これがプライマリファイルグループである場合は最大に圧縮します）
実際、古いファイルを圧縮する前に追加の空き領域を提供する必要がありますが、これははるかにクリーンなメカニズムです。
絶対に選択の余地がなく、ファイル圧縮操作を開始する必要がある場合は、インデックスの断片化が発生するという事実に備えてください。パフォーマンスの問題が発生する場合は、後で削除する手順を実行する必要があります。 データファイルを拡張せずにインデックスの断片化を削除する唯一の方法は、DBCC INDEXDEFRAGまたはALTER INDEX ... REORGANIZEを使用することです。 これらのコマンドには、再構築操作の場合に完全に新しいインデックスを作成する代わりに、8Kbの追加の1ページサイズが必要です。
結論-ファイルの圧縮を開始することは絶対に避けてください！