圧縮されたテーブルおよびインデックスの作成
SQL Server 2008 は、テーブルとインデックスの両方で行とページの両方の圧縮をサポートしています。次のデータベース オブジェクトのデータ圧縮を構成することができます。
ヒープとして格納されているテーブル全体。
クラスター化インデックスとして格納されているテーブル全体。
非クラスター化インデックス全体。
インデックス付きビュー全体。
パーティション分割されているテーブルおよびインデックスの場合、パーティションごとに圧縮オプションを構成することができ、オブジェクトの各パーティションを同じ圧縮設定にする必要がありません。
テーブルの圧縮設定は、非クラスター化インデックスに自動的に適用されるわけではありません。各インデックスを個別に設定する必要があります。圧縮は、システム テーブルには使用できません。テーブルとインデックスは、CREATE TABLE および CREATE INDEX ステートメントを使用して作成されている場合に圧縮できます。テーブル、インデックス、またはパーティションの圧縮状態を変更するには、ALTER TABLE または ALTER INDEX ステートメントを使用します。
.gif "注意") 注 |
|---|
既存のデータが断片化されている場合は、インデックスを再構築することで、圧縮を使用しなくてもインデックスのサイズを削減できる可能性があります。インデックスの再構築中に、インデックスの FILL FACTOR が適用されます。これによってインデックスのサイズが増える可能性があります。詳細については、「FILL FACTOR」を参照してください。 |
行とページの圧縮の使用に関する注意点
行とページの圧縮を使用する際は、次の点に注意してください。
圧縮は、SQL Server 2008 の Enterprise Edition および Developer Edition でのみ使用できます。
圧縮を使用すると、ページに格納できる行数が増えますが、テーブルまたはインデックスの最大行サイズは変更されません。
最大行サイズに圧縮のオーバーヘッドを加えると最大行サイズが 8,060 バイトを超える場合、テーブルで圧縮を有効にすることはできません。たとえば、列 c1 char(8000) および c2 char(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 の使用」を参照してください。
混合トポロジの場合、新しい圧縮オプションをサポートする必要がないときは、パブリケーションの互換性レベルを下位のサブスクライバー バージョンに設定してください。サポートする必要があるときは、テーブルをサブスクライバーに作成してから圧縮してください。
次の表に、レプリケーション時に圧縮を制御するレプリケーション設定を示します。
ユーザーの目的 |
テーブルまたはインデックスのパーティション構成のレプリケート |
圧縮設定のレプリケート |
スクリプト作成の動作 |
|---|---|---|---|
パーティション構成をレプリケートしてパーティションのサブスクライバーで圧縮を有効にする。 |
True |
True |
パーティション構成と圧縮設定の両方のスクリプトを作成します。 |
パーティション構成をレプリケートするがサブスクライバーでデータ圧縮は実行しない。 |
True |
False |
パーティション構成のスクリプトは作成しますが、パーティションの圧縮設定のスクリプトは作成しません。 |
パーティション構成をレプリケートせず、サブスクライバーでデータ圧縮も実行しない。 |
False |
False |
パーティションと圧縮設定のスクリプトを作成しません。 |
パブリッシャーですべてのパーティションが圧縮される場合はサブスクライバーでテーブルを圧縮するが、パーティション構成はレプリケートしない。 |
False |
True |
すべてのパーティションで圧縮が有効になっているかどうかを確認します。 テーブル レベルで圧縮のスクリプトを作成します。 |
他の 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_50.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO
新しいデータベースに切り替えるには、次のステートメントを実行します。
USE TestDatabase
GO
A. 行の圧縮を使用するテーブルを作成する
次の例では、テーブルを作成して圧縮を ROW に設定します。
CREATE TABLE T1
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO
B. ページの圧縮を使用するテーブルを作成する
次の例では、テーブルを作成して圧縮を PAGE に設定します。
CREATE TABLE T2
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO
C. パーティション テーブルに対して DATA_COMPRESSION オプションを設定する
次の例では、このセクションで示したコードを使用して作成された TestDatabase テーブルを使用します。この例では、パーティション関数およびパーティション構成を作成してからパーティション テーブルを作成し、そのテーブルのパーティションに対して圧縮オプションを指定します。この例では、パーティション 1 は ROW 圧縮用に構成され、残りのパーティションは PAGE 圧縮用に構成されます。
パーティション関数を作成するには、次のステートメントを実行します。
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 オプションを設定する
次の例では、例 C で使用したデータベースを使用します。この例では、連続しないパーティション用の構文を使用してテーブルを作成します。
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. テーブルを修正して圧縮を変更する
次の例では、例 A で作成した非パーティション テーブルの圧縮を変更します。
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
F. パーティション テーブル内の 1 つのパーティションの圧縮を変更する
次の例では、例 C で作成したパーティション テーブルの圧縮を変更します。REBUILD PARTITION = 1 構文を使用すると、パーティション番号 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. パーティション テーブル内の複数のパーティションの圧縮を変更する
REBUILD PARTITION = ... 構文を使用すると、パーティションを 1 つだけ再構築できます。複数のパーティションを再構築するには、複数のステートメントを実行するか、次の例を実行してすべてのパーティションを再構築し、未指定のパーティションで現在の圧縮設定を使用する必要があります。
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO
H. インデックスの圧縮を変更する
次の例では、例 A で作成したテーブルを使用し、列 C2 のインデックスを作成します。
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
パーティションごとに異なる圧縮設定を使用するインデックスを作成するには、ON PARTITIONS 構文を使用します。次の例では、インデックスのパーティション 1 に行の圧縮を、パーティション 2 から 4 までにページの圧縮を使用するパーティション テーブルのインデックスを作成します。
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. パーティション インデックス内の複数のパーティションの圧縮を変更する
REBUILD PARTITION = ... 構文を使用すると、パーティションを 1 つだけ再構築できます。複数のパーティションを再構築するには、複数のステートメントを実行するか、次の例を実行してすべてのパーティションを再構築し、未指定のパーティションで現在の圧縮設定を使用する必要があります。
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