データ圧縮を使用するときの注意点

SQL Azure Database では、データ圧縮（行やPAGE圧縮）をすることができます。
この辺りは、ムッシュの「SQL Database で行/ページ圧縮が利用可能になったようです」で紹介されています。
今回紹介するのは地味につらいけど、あまりちゃんと説明されていない部分の注意点になります。

MSDNで「Data Compression」でツラツラ書かれている中で次のような注意事項があります。

行またはページの圧縮を有効または無効にするために必要なディスク空き容量は、インデックスを作成または再構築するために必要なディスク空き容量と同じです。

とてもしれっと、さりげなく書いていますが、とても重要なのです。
つまり、ストレージに十分な空き容量がないとデータ圧縮を有効にできません。

では十分な空き容量とはなんでしょうか？
対象となるのは、「デーベースファイル」と「トランザクションログファイル」の2つです。
それぞれで十分な空き容量が必要となります。

クラスタ化インデックスを作成するときに必要なディスク領域については、「インデックスのディスク領域の例」と「インデックス操作用のトランザクション ログのディスク領域」で解説されています。

オンラインでのクラスタ化インデックス作成（SORT_IN_TEMPDB=ONに設定されたオンラインインデックスの操作）だと、
元のテーブルが約363MBだとすると
作業中に必要な領域の合計サイズは1058MBで、
作成されたインデックスサイズは453MBになります。
作業途中では約2倍の空き領域が必要に（3倍じゃないのは1058MBにはもともとの容量が含まれているので）なりますね。
ただしTEMPDBが含まれているのでAzure SQL Databaseの場合には、
242MBが含まれるので810MBです。オリジナルの1.3倍ぐらいの追加空き容量があればOK。

さて、トランザクションログはというと「インデックス操作を確実にロールバックできるようにするには、インデックス操作が完了するまでトランザクション ログを切り捨てることができません。」や「SORT_IN_TEMPDB オプションを ON に設定することを検討します。この設定により、インデックス トランザクションが同時実行のユーザー トランザクションから分離されます。インデックス トランザクションは、tempdb データベースのトランザクション ログに格納され、」と書かれています。
SORT_IN_TEMPDBオプションは既定ではOFFなのでクエリで明示的に指定しなければOFFです。

ここがあまり理解できていないのですが、対象のデータベースでページ圧縮以外のトランザクションがなくてもトランザクションログは必要で、必要な容量はおそらくディスクデータ領域の数倍かと思われます。

どうやるのがベスト？

データ圧縮を有効にするには、データ容量とトランザクション容量の両方が必要になることを確認しました。

データ容量については、まぁ仕方ないので増やして対応しましょう。
しかし、トランザクションログだけは制限が入ってるので、対策をする必要があります。
Azure SQL Database の P15 でトランザクションログが1TB、P2でトランザクションログが340GBとなっています。そのため、100GBを超えるテーブルを圧縮するには確実に足りません。

そこで利用するのが、「再開可能なオンラインインデックス再構築」です。

再開可能なオンラインインデックス再構築のポイント

  • 実行中は、更新系クエリに一桁パーセントのオバーヘッドがかかるかも。（参考）
  • インデックス再構築の障害からの回復 (データベースのフェールオーバーやディスク領域の不足など)。（参考）
  • インデックス再構築操作の間はトランザクション ログの切り捨てを有効にします (通常のオンライン インデックス操作に対してこの操作を実行することはできません)。
    • つまり、途中で止めてトランザクションログバックアップがされればトランザクションログが切り捨てされて幸せに！
    • 再開可能な再構築では実行時間の長いトランザクションを開いたままにする必要はなく、この操作の間のログの切り捨てと、より優れたログ領域管理が可能です。
    •  新しい設計では、必要なデータを、再開可能な操作を再開するために必要なすべての参照と共に、データベースに保持しています。
  • 再構築中にReconfigurationが発生してもフェールオーバー後に途中から再開可能。
  • 再構築中の進捗状況がクエリで取得可能。

テーブル圧縮とインデックス圧縮について

ALTER TABLEとALTER INDEXそれぞれでデータ圧縮ができますが、それぞれにどんな意味があって、挙動が違うのでしょうか。
それを知る手掛かりが、MSDNの「圧縮されたテーブルおよびインデックスの作成」で説明されています。

圧縮できる対象のデータベースオブジェクトが次のものです。

  • ヒープとして格納されているテーブル全体。
  • クラスター化インデックスとして格納されているテーブル全体。
  • 非クラスター化インデックス全体。
  • インデックス付きビュー全体。
  • パーティション分割されているテーブルおよびインデックスの場合、パーティションごとに圧縮オプションを構成することができ、オブジェクトの各パーティションを同じ圧縮設定にする必要がありません。

つまり、ヒープテーブルへのALTER TABLEと、非ヒープテーブルのクラスター化インデックスへのALTER INDEXが同じ意味を持っていそうです。

Let’s Try

ムッシュがすでに「SQL Database で再開可能なオンラインインデックス再構築が Public Preview で利用可能となりました」で紹介されています。

そこから学ぶと以下のクエリでよさそうですね。

— 再開可能なオンラインインデックス再構築
ALTER INDEX [PK_T1] ON [dbo].[T1] REBUILD PARTITION = ALL
WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1, DATA_COMPRESSION = PAGE)

SQL Azure

Posted by 大和屋貴仁