データ圧縮

[アーティクル]
06/13/2017

SQL Server 2014 では、行ストアテーブルとインデックスの行とページの圧縮がサポートされ、列ストアテーブルとインデックスの列ストアと列ストアのアーカイブ圧縮がサポートされています。

行ストアテーブルおよびインデックスについては、データベースのサイズを小さくするためにデータ圧縮機能を使用してください。領域を削減するだけでなく、データ圧縮を使用すると、データを格納するページ数が少なくなり、クエリがディスクから読み取る必要のあるページが少なくなるため、大量の I/O が発生する作業のパフォーマンスを向上できます。ただし、アプリケーションとの間でデータが交換される間は、データの圧縮と圧縮解除のためデータベースサーバーで追加の CPU リソースが必要になります。次のデータベースオブジェクトで行とページの圧縮を構成することができます。

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

列ストアテーブルおよびインデックスの場合、すべての列ストアテーブルおよびインデックスで列ストア圧縮が使用されます。これはユーザーが構成できません。データを格納および取得できる CPU リソースと時間に余裕がある場合にデータサイズをさらに小さくするには、列ストアの保存用圧縮を使用します。次のデータベースオブジェクトで列ストアの保存用圧縮を構成することができます。

列ストアテーブル全体またはクラスター化列ストアインデックス全体。列ストアテーブルはクラスター化列ストアインデックスとして格納されるため、どちらの方法を使っても同じ結果になります。
非クラスター化列ストアインデックス全体。
パーティション分割されている列ストアテーブルおよび列ストアインデックスの場合、パーティションごとに保存用圧縮オプションを構成することができ、オブジェクトの各パーティションを同じ保存用圧縮設定にする必要がありません。

行とページの圧縮の使用に関する注意点

行とページの圧縮を使用する際は、次の点に注意してください。

データの圧縮に関する詳細情報は、Service Pack または今後のリリースで予告なしに変更されることがあります。
圧縮は、 SQL Serverのすべてのエディッションで使用できるわけではありません。詳しくは「 Features Supported by the Editions of SQL Server 2014」をご覧ください。
圧縮は、システムテーブルには使用できません。
圧縮を使用すると、ページに格納できる行数が増えますが、テーブルまたはインデックスの最大行サイズは変更されません。
最大行サイズに圧縮のオーバーヘッドを加えると最大行サイズが 8,060 バイトを超える場合、テーブルで圧縮を有効にすることはできません。たとえば、列 c1char(8000) と c2char(53) を含むテーブルは、追加の圧縮オーバーヘッドのため圧縮できません。 vardecimal ストレージ形式を使用する場合は、この形式が有効になると行サイズのチェックが実行されます。行とページの圧縮の場合は、オブジェクトが最初に圧縮されるときに行サイズのチェックが実行され、各行が挿入または変更されるときにもチェックされます。圧縮では、次の 2 つのルールが適用されます。
- 固定長の型に対する更新が常に成功する必要があります。
- データ圧縮の無効化が常に成功する必要があります。圧縮された行がページに収まる場合 (行のサイズが 8,060 バイト未満の場合) でも、圧縮されていないときの行に収まらない更新は SQL Server によって回避されます。
パーティションの一覧を指定する場合は、個々のパーティションの圧縮の種類を ROW、PAGE、または NONE に設定できます。パーティションの一覧を指定しない場合は、すべてのパーティションがステートメントで指定されたデータ圧縮プロパティを使用して設定されます。特に指定しない限り、データ圧縮はテーブルまたはインデックスの作成時に NONE に設定されます。特に指定しない限り、既存の圧縮はテーブルの変更時にも保持されます。
範囲外の一連のパーティションまたは単独のパーティションを指定すると、エラーが生成されます。
テーブルの圧縮プロパティは非クラスター化インデックスに継承されません。インデックスを圧縮するには、インデックスの圧縮プロパティを明示的に設定する必要があります。既定では、インデックスの圧縮設定はインデックスの作成時に NONE に設定されます。
ヒープにクラスター化インデックスを作成する場合、圧縮状態を特に指定しない限り、ヒープの圧縮状態がクラスター化インデックスに継承されます。
ヒープがページレベルの圧縮用に構成されている場合、ページでは、次の方法によるページレベルの圧縮のみが受け入れられます。
- データは一括最適化を有効にして一括インポートされます。
- INSERT INTO ...WITH (TABLOCK) 構文とテーブルに非クラスター化インデックスはありません。
- PAGE 圧縮オプションを指定して ALTER TABLE ...REBUILD ステートメントを実行し、テーブルを再構築する方法
DML 操作の一部としてヒープに割り当てられた新しいページでは、ヒープが再構築されるまで PAGE 圧縮は使用されません。圧縮を解除してから再適用するか、クラスター化インデックスを作成してから削除することで、ヒープを再構築します。
ヒープの圧縮設定を変更するには、テーブルのすべての非クラスター化インデックスを再構築して、ヒープ内の新しい行位置へのポインターを持つようにする必要があります。
ROW または PAGE 圧縮はオンラインまたはオフラインで有効または無効にすることができます。オンライン操作の場合、ヒープに対する圧縮の有効化はシングルスレッドです。
行またはページの圧縮を有効または無効にするために必要なディスク空き容量は、インデックスを作成または再構築するために必要なディスク空き容量と同じです。パーティションデータの場合は、一度に 1 つのパーティションの圧縮を有効または無効にすることによって必要な空き容量を削減できます。
パーティションテーブルのパーティションの圧縮状態を調べるには、sys.partitions カタログビューの data_compression 列に対してクエリを実行します。
インデックスを圧縮する場合、行とページの両方の圧縮を使用してリーフレベルのページを圧縮できます。リーフレベル以外のページでは、ページの圧縮は受け入れられません。
大きな値のデータ型は、そのサイズが原因で、通常の行データとは別に特殊な目的のページに格納される場合があります。データ圧縮は、別個に格納されているデータには使用できません。
SQL Server 2005 で vardecimal ストレージ形式を実装したテーブルは、アップグレード時にその設定を保持します。 vardecimal ストレージ形式を使用するテーブルに行の圧縮を適用することができます。ただし、行の圧縮は vardecimal ストレージ形式のスーパーセットなので、vardecimal ストレージ形式を保持する理由はありません。 vardecimal ストレージ形式と行の圧縮を組み合わせても、10 進値の圧縮は追加されません。 vardecimal ストレージ形式を使用するテーブルにページの圧縮を適用することができます。ただし、vardecimal ストレージ形式の列の圧縮が追加される可能性は低くなります。

注意

SQL Server 2014 では vardecimal ストレージ形式がサポートされていますが、行レベルの圧縮が同じ目標を達成するため、vardecimal ストレージ形式は非推奨とされます。この機能は、Microsoft SQL Server の将来のバージョンで削除されます。新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

列ストアおよび列ストアの保存用圧縮の使用


適用対象: SQL Server (SQL Server 2014 から現在のバージョン)。

基本

列ストアテーブルおよび列ストアインデックスは常に列ストア圧縮を使用して格納されます。保存用圧縮と呼ばれる追加の圧縮機能を構成するによって、列ストアのデータサイズをさらに小さくすることができます。保存用圧縮を使用するには、 SQL Server でデータに対して Microsoft Xpress 圧縮アルゴリズムを実行します。次の種類のデータ圧縮を使用して、保存用圧縮を追加または削除します。

保存用圧縮で列ストアデータを圧縮するには、COLUMNSTORE_ARCHIVE データ圧縮を使用します。
保存用圧縮を解凍するには、 COLUMNSTORE データ圧縮を使用します。この結果として生成されるデータは、引き続き列データの圧縮を使用して圧縮できます。

アーカイブ圧縮を追加するには、 ALTER TABLE (Transact-SQL) または ALTER INDEX (Transact-SQL) を REBUILD オプションと DATA COMPRESSION = COLUMNSTORE と共に使用します。

例:

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE ON PARTITIONS (2,4)) ;

アーカイブ圧縮を削除し、データを列ストア圧縮に復元するには、 ALTER TABLE (Transact-SQL) または ALTER INDEX (Transact-SQL) を REBUILD オプションと DATA COMPRESSION = COLUMNSTORE と共に使用します。

例 :

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE ON PARTITIONS (2,4) ) ;

次の例では、データ圧縮をあるパーティションの列ストアに設定し、列ストアの圧縮を別のパーティションに設定しています。

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (  
    DATA_COMPRESSION =  COLUMNSTORE ON PARTITIONS (4,5),  
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1,2,3)  
) ;

パフォーマンス

保存用圧縮を使用して列ストアインデックスを圧縮すると、列ストアインデックスに保存用圧縮がない場合に比べて実行速度が遅くなります。保存用圧縮は、データを圧縮および取得する時間と CPU リソースに余裕がある場合にのみ使用します。

パフォーマンスは低下しますがストレージが少なくてすむため、頻繁にアクセスしないデータには便利です。たとえば、各月のデータ用のパーティションがある場合、ほとんどアクティビティは最新の月のデータに対して実行されるため、古い月のデータをアーカイブして必要なストレージを削減できます。

Metadata

次のシステムビューには、クラスター化インデックスのデータ圧縮に関する情報が含まれています。

sys.indexes (Transact-SQL) - type 列と type_desc 列には、CLUSTERED COLUMNSTORE と NONCLUSTERED COLUMNSTORE が含まれます。
sys.partitions (Transact-SQL) - data_compression 列と data_compression_desc 列には COLUMNSTORE と COLUMNSTORE_ARCHIVEが含まれます。

プロシージャ sp_estimate_data_compression_savings (Transact-SQL) は、列ストアインデックスには適用されません。

パーティションテーブルとパーティションインデックスへの圧縮の影響

パーティションテーブルとパーティションインデックスでデータ圧縮を使用する場合は、次の点に注意してください。

ALTER PARTITION ステートメントを使用してパーティションを分割すると、両方のパーティションに元のパーティションのデータ圧縮属性が継承されます。
2 つのパーティションをマージすると、結果として得られるパーティションにマージ先パーティションのデータ圧縮属性が継承されます。
パーティションを切り替えるには、パーティションのデータ圧縮プロパティがテーブルの圧縮プロパティと一致する必要があります。
パーティションテーブルまたはパーティションインデックスの圧縮の変更に使用できる構文には、次の 2 種類があります。
- 次の構文では、参照されているパーティションのみが再構築されます。
```
ALTER TABLE <table_name>   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)  
```
- 次の構文では、参照されていないパーティションの既存の圧縮設定を使用して、テーブル全体が再構築されます。
```
ALTER TABLE <table_name>   
REBUILD PARTITION = ALL   
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),  
... )  
```
パーティションインデックスの場合は、ALTER INDEX を使用して同じ原則に従います。
クラスター化インデックスを削除する場合、パーティション構成を変更しない限り、対応するヒープパーティションでデータ圧縮設定が維持されます。パーティション構成を変更すると、すべてのパーティションが圧縮されていない状態に再構築されます。クラスター化インデックスを削除し、パーティション構成を変更するには、次の手順を実行します。
1. クラスター化インデックスを削除します。
2. 圧縮オプションを指定する ALTER TABLE ...REBUILD ... オプションを使用して、テーブルを変更します。
OFFLINE でクラスター化インデックスを削除すると、クラスター化インデックスの上位レベルだけが削除されます。そのため、操作はとても高速です。 ONLINE でクラスター化インデックスを削除すると、 SQL Server によって、ヒープが手順 1. で 1 回、手順 2. で 1 回の計 2 回再構築されます。

レプリケーションへの圧縮の影響

レプリケーションでデータ圧縮を使用する場合は、次の点に注意してください。

スナップショットエージェントで最初のスキーマスクリプトが生成されるときに、新しいスキーマでは、テーブルとインデックスの両方に同じ圧縮設定が使用されます。圧縮をテーブルのみで有効にし、インデックスで無効にすることはできません。
トランザクションレプリケーションの場合、アーティクルスキーマオプションによって、スクリプトを作成する必要がある依存オブジェクトおよびプロパティが特定されます。詳細については、「 sp_addarticle」を参照してください。

ディストリビューションエージェントでは、スクリプトの適用時に下位のサブスクライバーのチェックが行われません。圧縮のレプリケーションが選択されている場合、下位のサブスクライバーに対するテーブルの作成は失敗します。混合トポロジの場合は、圧縮のレプリケーションを有効にしないでください。
マージレプリケーションの場合、パブリケーションの互換性レベルがスキーマオプションをオーバーライドし、この互換性レベルによってスクリプトが作成されるスキーマオブジェクトが特定されます。

混合トポロジの場合、新しい圧縮オプションをサポートする必要がないときは、パブリケーションの互換性レベルを下位のサブスクライバーバージョンに設定してください。サポートする必要があるときは、テーブルをサブスクライバーに作成してから圧縮してください。

次の表に、レプリケーション時に圧縮を制御するレプリケーション設定を示します。

ユーザーの目的	テーブルまたはインデックスのパーティション構成のレプリケート	圧縮設定のレプリケート	スクリプト作成の動作
パーティション構成をレプリケートしてパーティションのサブスクライバーで圧縮を有効にする。	True	True	パーティション構成と圧縮設定の両方のスクリプトを作成します。
パーティション構成をレプリケートするがサブスクライバーでデータ圧縮は実行しない。	True	False	パーティション構成のスクリプトは作成しますが、パーティションの圧縮設定のスクリプトは作成しません。
パーティション構成をレプリケートせず、サブスクライバーでデータ圧縮も実行しない。	False	False	パーティションと圧縮設定のスクリプトを作成しません。
パブリッシャーですべてのパーティションが圧縮される場合はサブスクライバーでテーブルを圧縮するが、パーティション構成はレプリケートしない。	False	True	すべてのパーティションで圧縮が有効になっているかどうかを確認します。テーブルレベルで圧縮のスクリプトを作成します。

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

圧縮はストレージエンジンで行われるので、他のほとんどの SQL Server コンポーネントには、データは圧縮されていない状態で提供されます。このため、他のコンポーネントに対する圧縮の影響は、次のように制限されます。

一括インポート操作と一括エクスポート操作

データをエクスポートする場合、データはネイティブ形式であっても圧縮されていない行形式で出力されます。この結果、エクスポートされたデータファイルのサイズがソースデータより大幅に大きくなる可能性があります。

データをインポートする場合、インポート先のテーブルで圧縮が有効になっているときは、データはストレージエンジンによって圧縮された行形式に変換されます。この結果、圧縮されていないテーブルにデータをインポートする場合と比較して、CPU 使用率が上昇する可能性があります。

ページの圧縮を使用するヒープにデータを一括インポートする場合、一括インポート操作では、データの挿入時にページの圧縮を使用したデータ圧縮が試行されます。
圧縮はバックアップと復元には影響しません。
圧縮はログ配布には影響しません。
データ圧縮は、スパース列と互換性がありません。したがって、スパース列を含むテーブルを圧縮したり、スパース列を圧縮されたテーブルに追加したりすることはできません。
圧縮を有効にすると、クエリプランが変更される可能性があります。データの格納に使用されるページ数とページあたりの行数が異なるためです。

参照

行の圧縮の実装
 ページ圧縮の実装
 Unicode 圧縮の実装
 CREATE PARTITION SCHEME (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)

次の方法で共有

データ圧縮

行とページの圧縮の使用に関する注意点

列ストアおよび列ストアの保存用圧縮の使用

基本

パフォーマンス

Metadata

パーティションテーブルとパーティションインデックスへの圧縮の影響

レプリケーションへの圧縮の影響

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

参照

その他のリソース

次の方法で共有

データ圧縮

行とページの圧縮の使用に関する注意点

列ストアおよび列ストアの保存用圧縮の使用

基本

パフォーマンス

Metadata

パーティション テーブルとパーティション インデックスへの圧縮の影響

レプリケーションへの圧縮の影響

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

参照

その他のリソース

パーティションテーブルとパーティションインデックスへの圧縮の影響