圧縮されたテーブルおよびインデックスの作成

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

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

• 圧縮は、SQL Server 2008 の Enterprise Edition および Developer Edition でのみ使用できます。

• 圧縮を使用すると、ページに格納できる行数が増えますが、テーブルまたはインデックスの最大行サイズは変更されません。

• 最大行サイズに圧縮のオーバーヘッドを加えると最大行サイズが 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 2008 は vardecimal ストレージ形式をサポートしていますが、行レベルの圧縮で同じ目的が果たされるので、vardecimal ストレージ形式は推奨されません。 この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。 新しい開発作業では、この機能の使用を避け、現在この機能を使用しているアプリケーションは修正するようにしてください。

圧縮の実装

データ圧縮の実装の概要については、「行の圧縮の実装」および「ページの圧縮の実装」を参照してください。

圧縮による削減量の推定

圧縮状態の変更によるテーブルまたはインデックスへの影響を調べるには、sp_estimate_data_compression_savings ストアド プロシージャを使用します。 sp_estimate_data_compression_savings ストアド プロシージャは、データ圧縮をサポートしている SQL Server のエディションでのみ使用できます。

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

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

• 範囲の分割

  ALTER PARTITION ステートメントを使用してパーティションを分割すると、両方のパーティションに元のパーティションのデータ圧縮属性が継承されます。

• 範囲のマージ

  2 つのパーティションをマージすると、結果として得られるパーティションにマージ先パーティションのデータ圧縮属性が継承されます。

• パーティションの切り替え

  パーティションを切り替えるには、パーティションのデータ圧縮プロパティがテーブルの圧縮プロパティと一致する必要があります。

• 1 つのパーティションまたはすべてのパーティションの再構築

  パーティション テーブルまたはパーティション インデックスの圧縮の変更に使用できる構文には、次の 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」を参照してください。

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

• マージ レプリケーションの場合、パブリケーションの互換性レベルがスキーマ オプションよりも優先され、この互換性レベルによってスクリプトが作成されるスキーマ オブジェクトが特定されます。 互換性レベルの詳細については、「レプリケーション トポロジにおける複数バージョンの SQL Server の使用」を参照してください。

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

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

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

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

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

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

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

  ページの圧縮を使用するヒープにデータを一括インポートする場合、一括インポート操作では、データの挿入時にページの圧縮を使用したデータ圧縮が試行されます。

• 圧縮はバックアップと復元には影響しません。

• 圧縮はログ配布には影響しません。

• データ圧縮はスパース列との互換性がありません。 したがって、スパース列を含むテーブルを圧縮したり、圧縮されたテーブルにスパース列を追加することはできません。

• 圧縮を有効にすると、クエリ プランが変更される可能性があります。データの格納に使用されるページ数とページあたりの行数が異なるためです。

• データ圧縮は、SQL Server Management Studio のデータ圧縮ウィザードによってサポートされます。

データ圧縮ウィザードを起動するには

• オブジェクト エクスプローラーでテーブル、インデックス、またはインデックス付きビューを右クリックして、[ストレージ] をポイントし、[圧縮] をクリックします。

圧縮の監視

SQL Server のインスタンス全体の圧縮を監視するには、SQL Server, Access Methods Object の Page compression attempts/sec カウンターと Pages compressed/sec カウンターを使用します。

個々のパーティションのページの圧縮に関する統計を取得するには、sys.dm_db_index_operational_stats 動的管理関数に対してクエリを実行します。

例

次の例の中には、パーティション テーブルを使用し、ファイル グループを含むデータベースを必要とするものもあります。 ファイル グループを含むデータベースを作成するには、次のステートメントを実行します。

CREATE DATABASE TestDatabase
ON  PRIMARY
( NAME = TestDatabase,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO

新しいデータベースに切り替えるには、次のステートメントを実行します。

USE TestDatabase
GO

A. 行の圧縮を使用するテーブルを作成する

CREATE TABLE T1 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO

B. ページの圧縮を使用するテーブルを作成する

CREATE TABLE T2 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO

C. パーティション テーブルに対して DATA_COMPRESSION オプションを設定する

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO

CREATE TABLE PartitionTable1 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO

D. パーティション テーブルに対して DATA_COMPRESSION オプションを設定する

CREATE TABLE PartitionTable2 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
  DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO

E. テーブルを修正して圧縮を変更する

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

F. パーティション テーブル内の 1 つのパーティションの圧縮を変更する

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

G. パーティション テーブル内の複数のパーティションの圧縮を変更する

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

H. インデックスの圧縮を変更する

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2) 
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

ALTER INDEX IX_INDEX_1 
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO

I. パーティション インデックス内の 1 つのパーティションの圧縮を変更する

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
    DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

J. パーティション インデックス内の複数のパーティションの圧縮を変更する

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

関連項目

参照