Skip to content

MS_SQLServerCompression

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server データ圧縮

概要

SQL Server は、2008 から、

  • テーブルとインデックスの両方で
  • 「データ圧縮」（行とページの圧縮）

をサポートしている。

なお、「データ圧縮」は、

  • SQL Server 2008 の Enterprise Edition および Developer Edition でのみ使用でき、
  • SSMS（MS_SSMS.md）の「データ圧縮ウィザード」でもサポートされる。

補足（最新化：エディション要件が緩和された）: SQL Server 2016 SP1 以降、
データ圧縮は Standard / Web / Express Edition でも使用できる。
「Enterprise 限定」という制約は現在は当てはまらない。

同時に、圧縮の選択肢も増えている。

種類 導入 概要
行圧縮 / ページ圧縮 2008 本ページで扱う行ストアの圧縮
列ストア（アーカイブ）圧縮 2012 / 2014 列ストア インデックス。分析系で圧縮率・性能とも桁違い
COMPRESS() / DECOMPRESS() 関数 2016 列の値を GZIP で圧縮。LOB 列の格納に使える
XML 圧縮 2022 XML 列・XML インデックスの圧縮
バックアップ圧縮 2008 SQL Server のバックアップ。既定で有効にすることが多い

圧縮対象

テーブル

SQL Server のインデックス

  • クラスタ化インデックス
  • 非クラスタ化インデックスのヒープ

移行メモ（正誤）: 「非クラスタ化インデックスのヒープ」という表現は、
正しくは「クラスタ化インデックスを持たないテーブル、すなわちヒープ」の意。
ヒープはインデックスの一種ではなく、
「クラスタ化インデックスが無い状態のデータ格納形態」を指す。

インデックス

SQL Server のインデックス

  • 非クラスタ化インデックス
  • インデックス付きビュー

パーティション

SQL Server パーティション分割

「パーティション分割」されているテーブルとインデックス。
※ この場合、「パーティション」毎に、別々の圧縮オプションを指定できる。

  • パーティション テーブル
  • パーティション インデックス

行圧縮とページ圧縮

行圧縮

行圧縮は、

SQL Server 2005 SP2 以降で提供された vardecimal データ型と同じような動作
（固定長データ型の利用していない領域を削り、可変長データ型のように扱う）

で、圧縮を行う。

補足（行圧縮が効きやすいデータ）: 行圧縮は
「固定長型に入っている値が、型のサイズより小さい」場合に効く。

例 効果
int 列に 0〜100 の値 4 バイト → 1 バイト
char(50) に 5 文字 末尾の空白を格納しない
datetime / decimal 有効桁のみ格納
NULL / 0 メタデータのみで領域を消費しない

逆に、varchar に隙間なく詰まっているデータには
ほとんど効果がない。

ページ圧縮

圧縮方法

  • プレフィックス圧縮

    • このプレフィックスは、＝列値の先頭から一致するデータパターン。
    • 列毎に、プレフィックスがページのヘッダに移動される。
    • 列の値は、プレフィックス参照に変更される。

  • ディクショナリ圧縮

    • このディクショナリは、＝列値と完全に一致する値のリスト。
    • プレフィックス圧縮の完了後、ディクショナリ圧縮が適用される。
    • ディクショナリ圧縮は、1 つの列に制限されない。
      ページ上の任意の場所を対象とすることができる。

補足（ページ圧縮は行圧縮を含む）: ページ圧縮は
「行圧縮 → プレフィックス圧縮 → ディクショナリ圧縮」の 3 段階で適用される。
つまり、ページ圧縮を指定すれば行圧縮も自動的に効いている。
「行圧縮とページ圧縮の両方を指定する」という選択肢は存在しない。

ページと圧縮の方法

  • リーフ レベル ページ

    • 行圧縮
    • プレフィックス圧縮
    • ディクショナリ圧縮

  • リーフ レベル ページ以外のページ

    • 行圧縮のみが使用される。

注意事項

必要なリソース

実行時

  • CPU リソースを消費して、I/O コストを下げるというもの。

    • CPU コスト: 圧縮・解凍のオーバヘッド
    • ストレージ コスト: I/O とサイズの双方

  • 従って、何を圧縮すべきかどうかを検討する必要がある。

  • 参考

    • 第7回　大規模データ処理における CPU と I/O のバランスをどう考えるか：
      RDBMS でも大規模データをあきらめないためには｜gihyo.jp … 技術評論社
      http://gihyo.jp/admin/serial/01/rdbms/0007

補足（バッファ プール上でも圧縮されたまま）: 重要な性質として、
圧縮されたページはメモリ（バッファ プール）上でも圧縮されたまま保持され、
アクセス時に必要な行だけが展開される。
このため、

  • 同じメモリ量でより多くのデータをキャッシュできる
    （メモリ不足の環境ほど効果が大きい）
  • I/O だけでなく、キャッシュ ヒット率も改善する

という副次効果がある。
現在のサーバは CPU に余裕があることが多いため、
大きなテーブルには積極的に適用してよいというのが実務的な結論。

圧縮時

  • 圧縮には、インデックス再構築の実行と同等の、ディスクの空き容量が必要になる。
  • オンラインのテーブル（ヒープ）の圧縮はシングル スレッドである。

適用先

テーブル・インデックス

  • システム テーブルには適用されない。
  • 「クラスタ化インデックス」には、自動的に反映される。
  • 「非クラスタ化インデックス」には、自動的には反映されない。

サイズ

  • 圧縮をしても、テーブルやインデックスの最大行サイズは変更されない。
  • 最大行サイズが 8,060 バイトを超える場合、テーブルに圧縮を適用できない。
  • サイズが原因で特殊なページに格納された LOB データは圧縮を適用できない。

運用時の考慮点

  • テーブル（ヒープ）の圧縮設定を変更する場合、
    「非クラスタ化インデックス」を再構築して、行識別子ポインタを
    変更する必要がある。

ページ圧縮のトリガ

  • ページ圧縮は、ページがいっぱいになった状態で、
    （INSERT INTO、BULK INSERT を使用して、）
    行追加することによって開始される。

  • 以降の行追加に対しては、基本的にページ圧縮が適用される。

  • 既存のテーブルに適用する場合は、直ちに各ページが評価・再構築される。

圧縮の推定、代替

  • 圧縮による削減量の推定には、sp_estimate_data_compression_savings ストアドを使用できる。
  • データの断片化が進んでいる場合は、インデックス再構築でもサイズ縮小できる可能性がある。

補足（推定の使い方）: 適用前に必ず効果を推定する。

EXEC sp_estimate_data_compression_savings
     @schema_name    = 'dbo',
     @object_name    = 'LargeTable',
     @index_id       = NULL,
     @partition_number = NULL,
     @data_compression = 'PAGE';

圧縮率が小さい（20% 未満など）のに
CPU コストだけ増えるケースもあるため、
ROW と PAGE の両方で試算して比較するとよい。

SQL Serverコンポーネントへの影響

  • 影響の無いもの

    • バックアップ・リストア
    • ログ配布（トランザクション・ログのため）

  • 影響の有るもの

    • クエリ プラン
      ページ数とページあたりの行数が異なるためクエリ プランの変更を伴う。

    • 一括インポート操作と一括エクスポート操作
      エクスポート時は、非圧縮のデータとなるため、操作に圧縮・解凍が伴う。

    • レプリケーション
      以下のレプリケーションの範囲に従って作成するスクリプトを変更する。

      • テーブルまたはインデックスのパーティション構成をレプリケートする。
        パーティション構成のスクリプトを作成する。
      • 圧縮設定のレプリケートする。
        圧縮設定のスクリプトを作成する。

データ圧縮とパーティション

「パーティション分割」と「データ圧縮」の併用によって、
必要な「パーティション」のみ「データ圧縮」するよう選択できる。

圧縮対象のパーティション

下記のような「パーティション」は、
「データ圧縮」しない（若しくは行圧縮のみ適用）。

  • 頻繁にアクセスする「パーティション」
  • データの変更が頻繁に発生する「パーティション」

圧縮対象でないパーティション

下記のような「パーティション」は、
「データ圧縮」する（ページ圧縮を適用）。

  • あまりアクセスしない「パーティション」
  • 確定している過去データの読み取り専用「パーティション」

移行メモ（見出しの取り違え）: 上記 2 つの見出しは内容と逆になっている。
本文のとおり、

  • 前者（頻繁にアクセス・変更される）が圧縮対象でないパーティション
  • 後者（あまりアクセスしない過去データ）が圧縮対象のパーティション

である。原文の見出しを保ちつつ本メモで補足する。

補足（この使い分けが最も費用対効果が高い）: 直近のデータは非圧縮、
過去データはページ圧縮（さらに古ければ列ストア アーカイブ圧縮）という
ホット / ウォーム / コールドの階層化は、
パーティション分割と圧縮を組み合わせる最大の動機である。
併せて過去パーティションを
読み取り専用ファイル グループに置けば、
バックアップ対象からも外せる
（SQL Server のファイル・グループ、
SQL Server のバックアップの段階的リストア）。

圧縮時の注意点

「パーティション」を「データ圧縮」する場合は、以下の点に注意する。

既存の「パーティション」の分割

元の「パーティション」のデータ圧縮属性が、
分割後の双方の「パーティション」に継承される。

既存の「パーティション」のマージ

マージ先「パーティション」のデータ圧縮属性が、
得られる「パーティション」に継承される。

既存の「パーティション」の切替え

「パーティション」のデータ圧縮属性が、
テーブルのデータ圧縮属性と一致する必要がある。

変更時の考慮点

パーティション or テーブル全体

既存の「パーティション テーブル」・「パーティション インデックス」の
圧縮設定の変更（ = 再構築）に使用できる構文には、次の 2 つの構文が利用できる。

  • index_option (Transact-SQL)
    https://learn.microsoft.com/ja-jp/sql/t-sql/statements/alter-table-index-option-transact-sql

    • 参照されているパーティションのみ再構築
    ALTER { TABLE | INDEX } object_name
REBUILD PARTITION = partition_number WITH (DATA_COMPRESSION = <option>)
    • テーブル全体を再構築（パーティションの既存の圧縮設定を使用）
    ALTER { TABLE | INDEX } object_name
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON
PARTITIONS ({ <partition_number_expression> | <range> }), ... )

クラスタ化インデックスの削除

  • オフラインで「パーティション分割」された「クラスタ化インデックス」を削除した場合、

    • 「クラスタ化インデックス」の上位レベルだけが削除されるため、操作は高速だが、
      「パーティション構成」を変更しない限り、ヒープ上のデータ圧縮属性が維持される。
    • これは、「パーティション構成」を変更するか、明示的に
      ALTER TABLE ... REBUILD ... ステートメントを実行、
      再構築することにより解除できる。

  • なお、オンラインで「パーティション分割」された「クラスタ化インデックス」を
    削除した場合、

    • SQL Server によって、自動的に再構築され、データ圧縮属性が解除される。

圧縮の効果

補足（効果の目安）: 一般的な業務データでの圧縮率の目安は以下
（データの内容に大きく依存するため、必ず sp_estimate_... で試算すること）。

圧縮 削減率の目安 CPU 増加の目安
行圧縮 10〜30% 数%
ページ圧縮 30〜60% 10〜30%
列ストア 70〜90% （分析クエリでは総合的に高速化）

参考

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally