データ圧縮：キャパシティ プランニングとベスト プラクティス
SQL Server 技術資料
著者: Sanjay Mishra
寄稿者: Marcel van der Holst 、 Peter Carlin 、 Sunil Agarwal
テクニカル レビューアー: Stuart Ozer 、 Lindsey Allen 、 Juergen Thomas 、 Thomas Kejser 、 Burzin Patel 、 Prem Mehra 、 Joseph Sack 、 Jimmy May 、 Cameron Gardiner 、 Mike Ruthruff 、 Glenn Berry (SQL Server MVP) 、 Paul S Randal (SQLskills.com) 、 David P Smith (ServiceU Corporation)
発行: 2009 年 5 月
対象: SQL Server 2008
要約 : SQL Server 2008 のデータ圧縮機能は、データベース内のデータを圧縮するのに役立ちます。また、データベース サイズを削減するのに役立つこともあります。データ圧縮には、領域の節約以外にもメリットがあります。データを圧縮するとデータの格納先となるページ数が減少するため、クエリがディスクから読み取る必要があるページ数が減少し、大量の I/O を必要とするワークロードのパフォーマンスが向上します。しかし、アプリケーションとの間でデータが交換される際、データを圧縮および圧縮解除するための CPU リソースがデータベース サーバーで新たに必要となります。したがって、圧縮するテーブルを決める際はワークロードの特性を理解することが重要です。
はじめに
Microsoft® SQL Server® 2008 データベース ソフトウェアの データ圧縮 機能は、データベースのサイズを削減したり、大量の I/O を必要とするワークロードのパフォーマンスを向上させたりするのに役立ちます。しかし、アプリケーションとの間でデータが交換される際、データを圧縮および圧縮解除するための CPU リソースがデータベース サーバーで新たに必要となります。したがって、圧縮するテーブルを決める際はワークロードの特性を理解することが重要です。このホワイト ペーパーでは、次の点についてのガイダンスを提供します。
- 圧縮対象のテーブルとインデックスを決める方法
- テーブルの圧縮に必要なリソースを推定する方法
- データ圧縮によって解放された領域を再利用する方法
- 標準的なワークロードでの、データ圧縮によるパフォーマンスへの影響
データ圧縮について
SQL Server 2008 では、行の圧縮とページの圧縮という 2 つのレベルのデータ圧縮が提供されます。 行の圧縮 を使用すると、固定長データ型を可変長ストレージ形式で格納することによって、データを行に格納する際の効率を高めることができます。圧縮後の行では、圧縮後の列 1 つあたり 4 ビットを使用して、列内のデータ長を格納します。すべてのデータ型で、NULL 値と値 0 の格納に必要な領域はこの 4 ビットのみです。
ページの圧縮 は行の圧縮のスーパーセットです。ページの圧縮では、データが効率よく行に格納されるだけでなく、データの冗長性を最小限に抑えることによってページ内の複数行の格納が最適化されます。ページの圧縮では、プレフィックスの圧縮と辞書の圧縮が使用されます。 プレフィックスの圧縮 では、各ページのすべての行の特定の列で、列値の先頭に現れる共通パターンが探されます。 辞書の圧縮 では、各ページのすべての列および行で完全に一致する値が探されます。辞書とプレフィックスのどちらでも、型は意識されず、すべての列値はバイトの集まりとして認識されます。
データ圧縮機能の詳細については、 SQL Server オンライン ブック を参照してください。 SQL Server Storage Engine ブログ (英語) もデータ圧縮の内部に関する優れたリソースです。
データ圧縮機能は、SQL Server 2008 の Enterprise エディションと Developer エディションで提供されます。圧縮後のテーブルまたはインデックスを含むデータベースを、他のエディションで使用することはできません (復元、アタッチ、および他のどのような使い方をすることもできません)。データベースで圧縮が使用されているかどうかを確認するには、動的管理ビュー (DMV) sys.dm_db_persisted_sku_features を照会します。圧縮対象と圧縮方法 (行の圧縮かページ圧縮か) を確認するには、カタログ ビュー sys.partitions. の data_compression_desc 列を照会します。
圧縮対象を決める
SQL Server 2008 では、データ圧縮を非常に柔軟に使用できます。行やページの圧縮は、テーブル、インデックス、インデックス付きビュー、またはパーティションのレベルで構成することができます。以下に、データ圧縮の適用に関する柔軟性の例をいくつか示します。
- 一部のテーブルを行圧縮し、他の一部のテーブルをページ圧縮し、残りのテーブルは圧縮しない。
- ヒープまたはクラスター化インデックスはページ圧縮するが、その非クラスター化インデックスは圧縮しない。
- 1 つのインデックスを行圧縮し、別のインデックスは圧縮しない。
- あるテーブルの一部のパーティションを行圧縮し、他の一部のパーティションをページ圧縮し、残りのパーティションは圧縮しない。
このような柔軟性があることがかえって圧縮対象の決定を困難にします。ここでは、圧縮対象を決めるのに役立つガイドラインをいくつか提供します。この決断に影響を及ぼす要因の一部を以下に示します。
- 領域節約量の推定
- アプリケーション ワークロード
領域節約量の推定
ストアド プロシージャ sp_estimate_data_compression_savings を使用すると、テーブルおよびそのインデックスを圧縮することによって節約される領域の量を推定できます。このストアド プロシージャは、データのサンプルを採取し、それを tempdb 内で圧縮することによって機能します。データベース内で特に大きいテーブルやインデックスの領域節約量を推定し、大幅に領域が節約されるテーブルとインデックスのみ圧縮を検討します。
ストアド プロシージャ sp_estimate_data_compression_savings では、一度に 1 つずつテーブルの領域節約量が推定されます。 「 Whole Database - Data Compression Procs 」 (「データベース全体 - データ圧縮プロシージャ」、英語) および「Procedure used for applying Database Compression to Microsoft SAP ERP system 」 (「マイクロソフト SAP ERP システムにデータ圧縮を適用するのに使用されるプロシージャ」、英語) という 2 つのブログ記事が示すように、このストアド プロシージャを含むようにスクリプトを作成して、データベース内のすべてのテーブルおよびインデックスの領域節約量を推定することができます。数千個のテーブルやインデックスを持つデータベース (SAP ERP データベースなど) では、データ圧縮によるデータベース全体での節約量を推定するのに長時間かかる場合があります。
データとデータ型
テーブルを圧縮することによって節約される領域の量は、テーブルに格納されている "データ" によって決まります (なにしろ "データ" 圧縮なので)。圧縮によってサイズが大幅に削減されるデータもあれば、そうでないデータもあります。以下のパターンのデータが格納されているテーブルは、圧縮によってサイズが大幅に削減されます。
- 数値データ型または固定長文字データ型の列で、割り当てられたバイト数すべてを必要としない値がほとんどの場合 (たとえば、1000 未満の値がほとんどの整数列)
- NULL 許容列で、多数の行でその列の値が NULL の場合
- 繰り返し出現するデータ値またはプレフィックス値が大量にデータに含まれている場合
圧縮してもあまりメリットが得られないデータ パターンもいくつか以下に示します。
- 数値データ型または固定長文字データ型の列で、特定のデータ型用に割り当てられたバイト数すべてを必要とする値がほとんどの場合
- 繰り返し出現するデータがあまりない場合
- 繰り返し出現するデータがあっても、そのデータのプレフィックスは繰り返し出現しない場合
- データが行の外に格納されている場合
- FILESTREAM データ
テーブルまたはパーティションは、 IN_ROW_DATA 、 LOB_DATA 、 ROW_OVERFLOW_DATA という 3 つのアロケーション ユニットを持つことができます。 LOB_DATA アロケーション ユニットおよび ROW_OVERFLOW_DATA アロケーション ユニットに格納されているデータは圧縮されません。 IN_ROW_DATA アロケーション ユニットに格納されているデータのみが圧縮されます。この 3 つのアロケーション ユニットそれぞれに格納されているデータの量を知るには、 付録 B を使用してください。
FILESTREAM(英語) データは、データベースの外 (NTFS ボリューム上の FILESTREAM データ コンテナ内) に格納されます。このデータは圧縮されません。
アプリケーション ワークロード
圧縮後のページはディスク上で圧縮状態が保たれ、メモリに読み込まれるときも圧縮状態のままです。次の条件のいずれかを満たす場合、データが圧縮解除されます (ページ全体ではなく、対象となるデータ値のみ) 。
- クエリ応答の一環としてフィルタ処理、並べ替え、結合のためにデータが読み取られる場合
- アプリケーションによってデータが更新される場合
圧縮状態のページが圧縮解除状態でメモリ内にコピーとして残ることはありません。データの圧縮解除では CPU を消費します。ただし、データを圧縮すれば、データによって使用されるページ数が減少するため、以下の 2 つが削減されるメリットもあります。
- 物理 I/O: ワークロードの観点から考えると物理 I/O は高コストなので、物理 I/O の削減がもたらす節約は、多くの場合、データの圧縮および圧縮解除に伴う追加の CPU コストを上回ります。 物理 I/O が削減される理由は 2 つあります。１ つはディスクから読み取られるデータやディスクに書き込まれるデータ量が減少すること、もう 1 つはより多くのデータをバッファー プール メモリ内にキャッシュしておくことができるようになることです。
- 論理 I/O (データがメモリ内にある場合): 論理 I/O は CPU を消費するため、論理 I/O が削減されると、データの圧縮および圧縮解除に伴う CPU コストが相殺される場合があります。
論理 I/O と物理 I/O が最も大幅に削減されるのは、テーブルまたはインデックスがスキャンされる場合です。(読み取りまたは書き込みのための) 単一参照が実行される場合は、圧縮による I/O の削減は小規模です (圧縮によって、より多くの要求が同じページを対象とするようになり、それが物理 I/O の削減につながる場合にのみ、削減が生じます)。
通常、行の圧縮の CPU オーバーヘッドは最小限で済みます (経験値では、一般に 10% 以下です)。行の圧縮によって領域の節約がもたらされ、システムが CPU 使用率の 10% 上昇に対応できる場合は、すべてのデータを行圧縮するべきです。たとえば、 SAP ERP NetWeaver 7.00 Business Suite 7(英語) 以上では、すべてのテーブルに対して行の圧縮が使用されます。
ページの圧縮では、行の圧縮よりも CPU オーバーヘッドが大きくなる可能性があるため、ページ圧縮の対象を決めるのは行圧縮の対象を決めるよりも困難です。ページの圧縮に関する一般的なガイドラインを以下にいくつか示します。
- システムの動作を正確に把握できるように、まずは使用頻度の低いテーブルおよびインデックスから圧縮します。
- CPU に余裕がない場合、徹底的なテストなしではページの圧縮を使用しないでください。
- フィルタ処理、結合、集計、並べ替えなどのクエリ操作は圧縮解除されたデータに対して実行されるため、このような操作のコストはデータを圧縮しても効果がありません。複雑な処理を伴う操作にコストが左右されるクエリ (複数のテーブル結合操作や複雑な集計操作を伴うクエリなど) では、データ圧縮によってパフォーマンスや CPU 使用率が大幅に変化する可能性はほとんどありません。こうした複雑なクエリは、通常、データ ウェアハウス アプリケーションで使用されますが、他のアプリケーションでも使用されることがあります。アプリケーションの CPU 時間が主に複雑なクエリで占有されている場合、ページの圧縮は CPU にそれほど影響を及ぼさないでしょう。そのようなシナリオでは、領域の節約がデータ圧縮の主な判断要因となるでしょう。
- 大規模なデータ ウェアハウスのワークロードのほとんどでは、大量のスキャンが必要となり、また、通常、ストレージが非常に重要視されます。データ ウェアハウスや大規模データ マートでは、CPU に余裕があれば、以下のようにオブジェクトを個別に評価するのではなく、データベース内のすべてのオブジェクトをページ圧縮することをお勧めします。
圧縮対象を決めるために行うもっと詳かいアプローチとして、それぞれのテーブルやインデックスのワークロード特性を分析する作業があります。この分析は、次の 2 つの指標に基づいて行います。
- U: 特定のテーブル、インデックス、またはパーティションへの操作の総数に対する、そのオブジェクト (テーブル、インデックス、またはパーティション) への更新操作数の割合。U の値が小さいほど (つまり、そのテーブル、インデックス、またはパーティションの更新頻度が低いほど)、そのオブジェクトはページの圧縮の対象に適していることになります。
- S: 特定のテーブル、インデックス、またはパーティションへの操作の総数に対する、そのオブジェクト (テーブル、インデックス、またはパーティション) へのスキャン操作数の割合。S の値が大きいほど (つまり、テーブル、インデックス、またはパーティションのスキャン頻度が高いほど)、そのオブジェクトはページの圧縮の対象に適していることになります。
U: オブジェクトに対する更新操作数の割合
U を計算するには、DMV sys.dm_db_index_operational_stats の統計を使用します。U は、あるテーブルまたはインデックスに対して実行されたすべての操作 (スキャン、DML、参照) の総数に対する、そのテーブルまたはインデックスに対して実行された更新操作数の割合です (パーセント表現)。次のクエリを実行すると、データベース内のそれぞれのテーブルおよびインデックスの U がレポートされます。
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
i.partition_number AS [Partition],
i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
i.leaf_update_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Update] ASC
S: オブジェクトに対するスキャン操作数の割合
S を計算するには、DMV sys.dm_db_index_operational_statsの統計を使用します。 S は、あるテーブルまたはインデックスに対して実行されたすべての操作 (スキャン、DML、および参照) の総数に対する、そのテーブルまたはインデックスに対して実行されたスキャン操作数の割合です (パーセント表現)。つまり、S は、テーブルまたはインデックスがスキャンされる頻度を表します。次のクエリを実行すると、データベース内のそれぞれのテーブル、インデックス、およびパーティションの S がレポートされます。
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
i.partition_number AS [Partition],
i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
i.range_scan_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) AS [Percent_Scan]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Scan] DESC
DMV sys.dm_db_index_operational_stats内のカウンタは、メタデータキャッシュ内のテーブルおよびインデックスの操作統計を反映しています。統計を使用する場合は時間枠を慎重に検討してください。圧縮をあまり使いたくない場合は、 CPU 使用率が最も高い期間 (月末処理中など) のパフォーマンス統計を使用します。
アクティブなテーブルやインデックスで反映される統計値は、SQL Server サービスが再開された時点、またはデータベースが開かれた時点から累積されています。あまりアクティブでないオブジェクトでは、この DMV 内に操作の統計がない場合があります。しかし、あまりアクティブでないオブジェクトでも、サイズが大きくて推定領域節約量が多い場合は、ページ圧縮の対象に適していることがあります。
めったに使用されない追加専用 (テーブルの最後に挿入が行われる) テーブルへの挿入に関しては、ページ圧縮によるオーバーヘッドはあまり大きくありません。このようなテーブルは、S が低くてもページの圧縮に適している場合があります。 このようなテーブルの例には、ログ テーブルや監査テーブルがあります。ログ テーブルや監査テーブルは、いったん書き込まれたらめったに読み取られません。これらのテーブルは、ページの圧縮の対象に適しています。
例
ユーザーががこうした指標をどのように使用してページ圧縮の対象とするテーブルを決めたかを示す例をご紹介しましょう。このユーザーは、平均 CPU 使用率が約 20% のサーバー上で OLTP データベースを運用していました。CPU の余裕が多いこと、データベース サイズの大幅な増加が予定されていたこと、およびストレージのコストが、データ圧縮を行う動機となりました。このユーザーは、領域の節約量と、データベース内で特に大きいテーブルの指標 U、S を計算し、S が 75% を超え U が 20% 未満のテーブルをページ圧縮の対象としました。表 1 は、行とページの推定節約量、S と U の値、および行圧縮とページ圧縮のどちらを行うかの決断を示しています。
テーブル |
節約量: 行 (%) |
節約量: ページ (%) |
S |
U |
決断 |
メモ |
T1 |
80% |
90% |
3.80% |
57.27% |
行圧縮 |
S が低く、U が非常に高い。行の節約量とページの節約量に差がない。 |
T2 |
15% |
89% |
92.46% |
0% |
ページ圧縮 |
S が非常に高い。 |
T3 |
30% |
81% |
27.14% |
4.17% |
行圧縮 |
S が低い。 |
T4 |
38% |
83% |
89.16% |
10.54% |
行圧縮 |
U が高い。 |
T5 |
21% |
87% |
0.00% |
0% |
ページ圧縮 |
追加専用テーブル。 |
T6 |
28% |
87% |
87.54% |
0% |
ページ圧縮 |
S が高く、U が低い。 |
T7 |
29% |
88% |
0.50% |
0% |
ページ圧縮 |
追加が 99% 。 |
T8 |
30% |
90% |
11.44% |
0.06% |
ページ圧縮 |
追加が 85%。 |
T9 |
84% |
92% |
0.02% |
0.00% |
行圧縮 |
行の節約量と ページの節約量がほぼ等しい。 |
T10 |
15% |
89% |
100.00% |
0.00% |
ページ圧縮 |
読み取り専用テーブル。 |
表 1: 圧縮対象を決める
表 1 に示す指標に基づいて、テーブル T2、T5、T6、T7、T8、および T10 をページ圧縮することに決めました。データベース内の他のすべてのテーブルは、行圧縮されました。この計画に従って、 50% の領域節約を実現し、CPU 使用率が約 10% 上昇しました。
圧縮を計画する: ワークスペース、CPU、および I/O を推定する
テーブルはALTER TABLE… REBUILDステートメントを使用して圧縮され、インデックスはALTER INDEX … REBUILD ステートメントを使用して圧縮されます。テーブルまたはインデックスの圧縮には、ワークスペース、CPU、および I/O が必要で、インデックスの再構築と同じメカニズムが使用されます。ここでは、クラスター化インデックスを圧縮するのに必要であると推定されるリソースについて説明します。また、比較のために、同じインデックスを圧縮されていない状態で再構築するのに必要なリソースについても説明します。必要なリソースは、以下の要因によって決まります。
- 圧縮の対象となるのがヒープ、クラスター化インデックス、非クラスター化インデックスのいずれであるか
- SORT_IN_TEMPDB オプションを ON に設定するかどうか
- ONLINE オプションを ON に設定して圧縮操作を実行するかどうか
- 単純復旧モデル、一括ログ復旧モデル、完全復旧モデルのいずれを使用するか
ワークスペース
以下の場所に空きワークスペースが必要です。
- ユーザー データベース
- トランザクション ログ
- tempdb
必要なワークスペースを圧縮の開始前に推定し、高いコストを伴う可能性のあるデータベース ファイルの自動拡張や、ディスク領域の不足による圧縮の失敗を回避できるだけの空き領域を確保します。各ファイル グループ内で使用可能な空き容量を確認するには、 付録 C のスクリプトを使用してください。
ユーザー データベース内に必要なワークスペース
ユーザー データベースには、以下の空きワークスペースが必要です。
- 圧縮後のテーブルまたはインデックス用
- ONLINE オプションを ON に設定し SORT_IN_TEMPDB オプションを OFF に設定してヒープまたはクラスター化インデックスを圧縮する場合の マッピング インデックス 用 (SORT_IN_TEMPDB は ON に設定することをお勧めします。マッピング インデックス用に必要なワークスペースについては、tempdb のところで説明します)
テーブルの圧縮中は、圧縮が成功しコミットされるまで、圧縮前のテーブルと圧縮後のテーブルが共存します。テーブルまたはインデックスの圧縮後は、圧縮前のテーブルが削除され、その領域はファイル グループに解放されます。圧縮後のテーブル サイズを推定するには、 sp_estimate_data_compression_savings の出力を使用します。
トランザクション ログ領域
トランザクション ログ領域に必要な量は、ONLINE が ON と OFF のどちらに設定されているか、および使用される復旧モデル ( 完全復旧モデル、一括ログ復旧モデル、または単純復旧モデル ) によって決まります。
tempdb 内に必要なワークスペース
tempdb データベースでは、ONLINE が ON に設定されている場合、以下の空きワークスペースが必要です。
- マッピング インデックス 用: 古いブックマークを新しいブックマークにマップし同時実行の DML トランザクションを可能にするために使用される内部構造です。 SORT_IN_TEMPDB が ON に設定されている と 、これは tempdbに格納されます。
- バージョン ストア用: これは、同時実行の DML 操作がある場合にのみ使用されます。サイズは、実行中の変更量、および長時間かかる DML トランザクションの実行時間の長さによって決まります。
I/O
一般に、I/O はワークスペースの使用量に比例します。
CPU
平均すると、行圧縮にかかる CPU 時間はインデックスの再構築にかかる CPU 時間の 1.5 倍、ページ圧縮にかかる CPU 時間はインデックスの再構築にかかる CPU 時間の 2 ～ 5 倍です。たとえば、付録 A に記載しているハードウェアを使用し、ONLINE を OFF に設定した場合、インデックスの再構築には 1 GB あたり 41 CPU 秒、行圧縮には 1 GB あたり 48 CPU 秒、ページ圧縮には 1 GB あたり 182 CPU 秒かかりました。ONLINE 操作には、より多くの CPU が必要です。 パフォーマンスはデータベースおよびハードウェアの特性によって変わるため、これらの数値は厳密な値ではなく概算値です。
再構築と圧縮は並列処理でき、複数の CPU を利用することができます。MAXDOP オプションの使用例は、この ブログ (英語) で紹介されています。次の 2 点に注意が必要です。
- SQL Server は、主要な列の統計を使用して、処理を複数の CPU に分散します。そのため、主要な列に一意値が比較的少ないインデックスを作成、再構築、または圧縮する場合、またはデータがごく少数の主要なキー値に大きく偏っている場合は、複数の CPU は有効ではありません。このような場合、並列処理の効果は限定的なものとなります。
- ONLINE を ON に設定してヒープを圧縮または再構築する場合、1 つの CPU が使用されます。しかし、SQL Server はまずテーブルをスキャンする必要があり、このスキャンは並列処理されます。テーブルのスキャンが完了すると、ヒープの圧縮処理の残りの部分はシングル スレッドで実行されます。
データ圧縮に必要なリソースの概要
表 2 は、クラスター化インデックスの圧縮に必要なワークスペース、CPU、および I/O の概要を、同じインデックスが圧縮されていない状態で再構築に必要なワークスペース、CPU、および I/O と比較して示したものです。使用した指標は以下のとおりです。
- X = 圧縮 (または再構築) 前のページ数
- P = 圧縮後のページ数 (P < X)
- Y = 新しいページ数または (同時実行アプリケーションによって) 更新されたページ数 (ONLINE の場合にのみ該当)
- M = マッピング インデックスのサイズ (TEMPDB の容量計画に関するホワイト ペーパーに記載されているガイドラインに基づき推定)
- C = 圧縮されていないインデックスの再構築にかかる CPU 時間
|
|
ワークスペース |
I/O |
CPU |
||||
|
TEMPDB |
ユーザー DB |
ユーザー DB のトランザクション ログ |
TEMPDB |
ユーザー DB |
ユーザー DB のトランザクション ログ |
||
|
OFFLINE かつ BULK_LOGGED 復旧モデルまたは SIMPLE 復旧モデル |
|||||||
|
再構築 |
0 |
X |
~0 |
0 |
X+2X |
~0 |
C |
|
圧縮 |
0 |
P |
~0 |
0 |
X+2P |
~0 |
1.5C ～ 5C |
|
OFFLINE かつ FULL 復旧モデル |
|||||||
|
再構築 |
0 |
X |
X |
0 |
X+X |
X |
~C |
|
圧縮 |
0 |
P |
P |
0 |
X+P |
P |
1.5C ～ 5C |
|
ONLINE かつ FULL 復旧モデル |
|||||||
|
再構築 |
M+Y |
X+Y |
2X+Y |
M+4Y |
X+X+Y |
2X+Y |
~2C |
|
圧縮 |
M+Y |
P+Y |
2P+Y |
M+4Y |
X+P+Y |
2P+Y |
3C ～ 10C |
表 2: クラスター化インデックスの圧縮に必要なワークスペース、CPU、および I/O の概要
表 2 からわかる重要な点をいくつか以下に示します。
- 圧縮なしの再構築よりも圧縮の方が、ワークスペースと I/O の使用量は少なくなりますが、CPU の使用量は多くなります。ワークスペースや I/O の削減は、結果として得られる構造のサイズが小さくなることによって実現されます。
- リソース使用量が最も少ないのは、OFFLINE かつ一括ログ復旧モデルまたは単純復旧モデルで実行している場合です。オフラインの一括操作には、既存のデータの読み込みと、新しい圧縮後のデータの書き込みが含まれます。書き込まれるログは最小限に抑えられます (割り当てのみがログ記録されます)。
- BULK_LOGGED 復旧モデルまたは SIMPLE 復旧モデルを使用できない (が OFFLINE を使用する) 場合は、圧縮後のデータを完全復旧モデルで完全にログ記録するために追加の I/O が必要となります。一括ログ復旧モデルと単純復旧モデルでもログ領域は予約されるため、(一括ログ復旧モデルと比べて) 追加のワークスペースは割り当てられません。
- 多くの運用システムは一括ログ モードまたは単純モードでは実行できません。また、多くのシステムでは、OFFLINE 圧縮によるダウンタイムを発生させるわけにいきません。 ONLINE 操作には、OFFLINE 操作の約 2 倍の CPU が必要です。
圧縮の方法とタイミング
圧縮の方法とタイミングについての重要な決断には、次のようなものがあります。
- オンラインかオフラインか: ONLINE の値を ON と OFF のどちらに設定するかは、データベース上で他に何が同時に実行されているかによって決まります。OFF の場合は ON の場合よりも高速で圧縮が行われ、必要なリソースも少なくて済みますが、OFF に設定すると、圧縮操作中テーブルがロックされます。 SQL Server オンライン ブック で説明されている、オンライン操作の制約に注意してください。
- テーブル、インデックス、またはパーティションを一度に 1 つずつ圧縮するか、多数を同時に圧縮するか: ほとんどの場合、テーブル、インデックス、またはパーティションを一度に 1 つずつ圧縮することをお勧めします。複数の圧縮を同時に行う場合は、それぞれの圧縮に必要なワークスペース、I/O、および CPU (前述) を合計した量のワークスペース、I/O、および CPU がなければなりません。空き領域が不足する危険性と、データ ファイルを拡張する必要があることに注意してください。また、圧縮の終了時にデータ ファイルに大量の未使用領域が残る可能性があることにも注意してください。十分なリソース (ワークスペース、I/O、および CPU) があり、未使用領域を再利用する効率的なメカニズム (後述の「データ圧縮によって解放された領域を再利用する」参照) も存在する場合は、複数の圧縮を同時に実行しても問題ないでしょう。
- テーブルを圧縮する順序: 圧縮対象の一連のテーブルとインデックスを決めたら、まずその中で最も小さなオブジェクトから圧縮します。小さなオブジェクトを圧縮するのに必要なワークスペースは少量で済み、小さなオブジェクトを圧縮すると、データ ファイルの領域が解放され、その後、その領域はより大きなオブジェクトを圧縮するためのワークスペースとして使用できます。このアプローチを使用すると、圧縮処理中に追加のディスク領域が必要となる可能性を最小限に抑えることができます。
- SORT_IN_TEMPDB を ON と OFF のどちらに設定するか: ON に設定することをお勧めします。ON にすると、tempdb の領域がマッピング インデックスに利用されるので、ユーザー データベース内の必要なワークスペースがより少なくて済みます。
テーブルまたはインデックスの圧縮による悪影響
テーブルまたはインデックスを圧縮する際は、以下に示す 2 つの悪影響があることを知っておく必要があります。
- 圧縮は再構築を伴うため、テーブルまたはインデックスから断片化が除去されます。
- ヒープを圧縮するときに、そのヒープに非クラスター化インデックスがあると、そのインデックスは次のように再構築されます。
o ONLINE が OFF に設定されている場合、非クラスター化インデックスは 1 つ
ずつ再構築されます。
o ONLINE が ON に設定されている場合、すべての非クラスター化インデックスが
同時に再構築されます。
圧縮されていないヒープ用の領域は非クラスター化インデックスの再構築が完了するまで解放されないため、非クラスター化インデックスの再構築に必要なワークスペースを考慮する必要があります。
圧縮後のデータを操作する
ここでは、圧縮後のデータが変更されると (新しい行が挿入されたり、行が削除または更新されたりすると) どうなるかについて説明します。
新しく挿入される行
行の圧縮では、新しく挿入される行も行圧縮されます。ページの圧縮では、新しく挿入される行は行圧縮またはページ圧縮されます。どちらの圧縮方法が使用されるかは以下の要因によって決まります。
- テーブルの編成: ヒープかクラスター化インデックスか
- 新しい行の挿入方法と挿入場所
表 3 は、圧縮後のテーブルに新しく挿入される行の圧縮状態をまとめたものです。
|
テーブル 編成 |
テーブルの圧縮設定 |
|
|
ROW |
PAGE |
|
|
ヒープ |
新しく挿入される行は行圧縮されます。 |
以下の場合、新しく挿入される行がページ圧縮されます。 · ページ圧縮により新しい行が既存のページに配置される · 新しい行が TABLOCK を指定した BULK INSERT によって挿入される · 新しい行が、INSERT INTO ... (TABLOCK) SELECT ... FROM を使用して挿入される 上記以外は行圧縮されます。 |
|
クラスター化インデックス |
新しく挿入される行は行圧縮されます。 |
ページ圧縮により新しい行が既存のページに配置される場合、新しく挿入される行はページ圧縮されます。それ以外の場合は、ページがいっぱいになるまで行圧縮されます。ページの圧縮はページ分割の前に試みられます。 |
表 3: 圧縮後のヒープまたはクラスター化インデックスに新しく挿入される行の圧縮
* 結果として生じる行圧縮されたページは、ページ圧縮によってヒープの再構築を実行することによりページ圧縮することができます。
** ページ圧縮では、テーブル内のすべてのページが実際にページ圧縮されるとは限りません。ページがページ圧縮されるのは、そのページにおける領域節約量が内部で定義されたしきい値を超える場合のみです。
圧縮後の行を更新または削除する
行圧縮後のテーブルまたはパーティション内の行を更新すると、常に行圧縮後の形式に保たれます。ページ圧縮後のテーブルまたはパーティション内の行を更新した場合、列プレフィックスやページ辞書は必ずしも再計算されるとは限りません。ページ圧縮後の特定のページに対する変更数が内部で定義されたしきい値を超えると、列プレフィックスとページ辞書が再計算されます。
補助的なデータ構造の処理
アプリケーションによってテーブル内のデータが操作される (INSERT、UPDATE、DELETE、CREATE/REBUILD INDEX など) 場合、データのサブセットを一時的に保持するいくつかの補助的なデータ構造が SQL Server によって作成されることがあります。こうしたデータ構造のいくつかを以下に示します。
- トランザクション ログ
- マッピング インデックス
- バージョン ストア
- 並べ替えページ
圧縮後のテーブル内のデータが操作される場合にこうした補助的な構造が圧縮されるかどうかは、データ構造の種類、およびテーブルで使用されるデータ圧縮の種類によって決まります。表 4 は、圧縮後のテーブルが操作される場合の補助的なデータ構造の圧縮特性をまとめたものです。
テーブル圧縮の種類 |
トランザクション ログ |
クラスター化インデックスを再構築するためのマッピング インデックス |
クエリ用の並べ替えページ |
バージョン ストア (分離レベルSI または RCSI) |
行圧縮 |
行圧縮 |
圧縮なし |
圧縮なし |
行圧縮 |
ページ圧縮 |
行圧縮 |
圧縮なし |
圧縮なし |
行圧縮 |
表 4: 圧縮後のテーブル内のデータが変更される場合の補助的データ構造の圧縮特性
ページ圧縮後のインデックスでは非リーフ ページは行圧縮される
行圧縮後のテーブルまたはパーティション内の行を更新すると、常に行圧縮後の形式に保たれます。ページ圧縮後のテーブルまたはパーティション内の行を更新した場合、列プレフィックスやページ辞書は必ずしも再計算されるとは限りません。ページ圧縮後の特定のページに対する変更数が内部で定義されたしきい値を超えると、列プレフィックスとページ辞書が再計算されます。
ページ圧縮後の (クラスター化または非クラスター化) インデックスでは、リーフ レベルのページはページ圧縮されますが、非リーフ ページはページ圧縮ではなく行圧縮されます (付録 D 参照)。 これは効率上の理由によるものです。
- インデックス内の非リーフ ページの数は比較的少なく、こうしたページをページ圧縮することによって実現される領域の節約は比較的わずかなものです。
- 非リーフ ページはリーフ ページよりもはるかに頻繁にアクセスされます。非リーフ ページを (ページ圧縮ではなく) 行圧縮すると、非リーフ ページにアクセスするたびにそのページを圧縮解除しなくて済み、コストが節約されます。
データ圧縮によって解放された領域を再利用する
データ圧縮が完了すると、節約された領域がそれぞれのデータ ファイルに解放されます。しかし、その領域はファイル システムには領域は解放されません。データ圧縮の一環としてファイル サイズが自動的に削減されることはありません。次のように、ファイルのサイズを削減することによって領域をファイル システムに解放するための選択肢はいくつかあります。
選択肢 1 : 解放された領域を再利用しないで、将来のデータ増加に備えて空き領域をファイル グループ内に残しておくことができます。これは、既存のファイル グループ内のデータ量が将来増加することが予想されるデータベース向けの単純な選択肢です。この選択肢は、パーティション分割され、各パーティションが別々のファイル グループに割り当てられているテーブルには向いていません。ディスク領域を節約するためには、古い読み取り専用パーティションを圧縮する必要があります。
選択肢 2 : DBCC SHRINKFILE (または DBCC SHRINKDATABASE) を使用する選択肢もありますが、データベース ファイルを圧縮するとファイル内容の断片化が進みます。DBCC SHRINKFILE はシングル スレッドで実行されるため、完了に長時間かかる場合があることにも注意してください。テストでは、クラスター化インデックスをページ圧縮した後のファイル グループ内の空き領域は約 68% でした。DBCC SHRINKFILE を実行すると、断片化は 100% になりました。
図 1: DBCC SHRINKFILE を実行した後のデータベースの空き領域と断片化率
ALTER INDEX … REORGANIZE を実行すると、断片化は 0 に減りました。REBUILD (これを使用すると、新しいインデックスが作成されてから既存のインデックスが削除されるので、より多くのワークスペースがファイル グループ内に必要となり、データ ファイルが再び拡張されます) ではなく REORGANIZE (追加のデータ ファイル領域を必要としません) を使用してください。
選択肢 3 : 1 つのファイル グループ内のすべてのテーブルを圧縮する場合は、以下の操作を行います。
- 新しいファイル グループを作成します。
- 圧縮中にテーブルおよびインデックスを新しいファイル グループに移動します。
- テーブルにクラスター化インデックス (ヒープを移動する場合は、選択肢 4 を参照) を作成 (既に存在する場合は再作成) し、新しいファイル グループに移動する必要があります。これを 1 つの操作内で行います。既にクラスター化インデックスがある場合は、CREATE CLUSTERED INDEX コマンドの DROP_EXISTING オプションを使用します。以下に例を示します。
CREATE UNIQUE CLUSTERED INDEX [PK_TRADE]
ON [TRADE_BULK] ([T_ID] ASC)
WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_Data2]
- 古いファイル グループ内のすべてのテーブルとインデックスが圧縮され新しいファイル グループに移動されたら、ファイル システムに領域を解放するために古いファイル グループと古いファイル グループ内のファイルを削除することができます。
- この方法に関して注意が必要な点があります。テーブルの同じファイル グループ内に LOB_DATA アロケーション ユニットがある場合は、この方法を使用しても LOB_DATA は新しいファイル グループに移動されません (別のファイル グループ内にクラスター化インデックスが再作成される際に、IN_ROW_DATA アロケーション ユニットと ROW_OVERFLOW_DATA アロケーション ユニットのみが移動されます)。したがって、古いファイル グループは完全には空にならないため、削除できません。
選択肢 4 : 1 つのファイル グループ内のすべてのテーブルを圧縮する場合、もう 1 つ方法があります。新しいファイル グループ内に空のテーブルを作成し、それを圧縮し、INSERT … SELECT を使用して新しいテーブルにデータをコピーする方法です。
-- 新しいファイル グループ内に新しい空のテーブルを作成します。
ALTER DATABASE [TestDB] MODIFY FILEGROUP FG_COMP DEFAULT;
SELECT * INTO [Tab1] FROM [Tab] WHERE 1 = 2;
-- 新規作成した空のテーブルを圧縮します。
ALTER TABLE [Tab1] REBUILD WITH (DATA_COMPRESSION = PAGE);
-- テーブルに適切なインデックスを作成します。
-- 新しいテーブルにデータをコピーします。
INSERT INTO [Tab1] WITH (TABLOCK) SELECT * FROM [Tab]
-- 挿入されるデータは、挿入時に圧縮されます。
-- 対象のテーブルがヒープの場合は、TABLOCK を指定する必要があります。
-- トレース フラグ 610 を使用すると、最小ログ記録を有効にできる場合があります。
-- LOB_DATA アロケーション ユニットもコピーされます。
-- 古いテーブルを削除し、新しいテーブルに古いテーブルと同じ名前を付けます。
-- このようにしてすべてのテーブルがコピーされたら、古いファイル グループを削除します。
データを圧縮した場合のアプリケーション パフォーマンス
前述のとおり、データ圧縮を行うと論理 I/O および物理 I/O は削減されますが、CPU 使用量は増加します。ここでは、データを圧縮した状態でのいくつかのワークロードとそのパフォーマンスについて説明します。
ワークロード 1: 大量の DML 操作を伴う OLTP アプリケーション
図 2 は、あるユーザーが大量の DML (INSERT、UPDATE、および DELETE) 操作を伴う OLTP アプリケーションで実現したパフォーマンスを示しています。圧縮なしの場合、行の圧縮を行った場合、ページの圧縮を行った場合のそれぞれについて、4 種類のビジネス トランザクションの平均応答時間が測定されました。
図 2: データ圧縮を行った場合の OLTP ワークロードのパフォーマンス
図 2 が示すように、このワークロードでは、行の圧縮を行った場合、4 つ目のビジネス トランザクションを除くすべてのビジネス トランザクションで、圧縮なしの場合と同様またはそれを上回るパフォーマンスが実現されました。しかし、ページの圧縮を行った場合、すべてのビジネス トランザクションは、圧縮なしの場合や行の圧縮を行った場合よりも完了に時間がかかりました。こうしたテストに基づいて、このユーザーは、このアプリケーションのすべてのテーブルおよびインデックスで行の圧縮を使用することに決めました。
ワークロード 2: 大規模なクエリを使用するレポート アプリケーション
図 3 は、あるユーザーがレポート アプリケーションで実現したパフォーマンスを示しています。テスト環境では、圧縮なしのデータとページ圧縮後のデータのそれぞれについて 8 つのクエリの応答時間が測定されました。
図 3: データ圧縮を行った場合レポート ワークロードのパフォーマンス
図 3 が示すように、このワークロードでは、ページの圧縮を行った場合、すべてのクエリで、圧縮なしの場合を上回るパフォーマンスが実現されました。応答時間が半分以下に短縮されたクエリもありました。こうしたテストに基づいて、このユーザーでは、このアプリケーションの運用にページの圧縮を採用することを計画しています。
圧縮後のインデックスを再構築する
圧縮後のインデックスを再構築するには、同じインデックスを圧縮していない状態で再構築するより時間がかかります。テーブルが圧縮されていても (非クラスター化) インデックスが圧縮されていなければ、そのインデックスを再構築するのに追加の処理時間は必要ありません。
圧縮後のインデックスを再構築するには、インデックス ページを圧縮解除してから、圧縮と同時にインデックスを再構築する必要があります。これには、長い時間と多くの CPU リソースが必要です。図 4 は、クラスター化インデックスの再構築にかかった CPU 使用率と時間を示しており、図 5 は、非クラスター化インデックスの再構築にかかった CPU 使用率と時間を示しています。
図 4: クラスター化インデックスの構築にかかった CPU 使用率と時間
図 5: 非クラスター化インデックスの構築にかかった CPU 使用率と時間
ページ圧縮後のインデックスを再構築する場合の CPU 使用率は他の場合よりも大幅に高くなっていることに注目してください。CPU 使用率が高くなるのは、ページ圧縮後のインデックスを再構築する場合、(リーフ レベルの) 各ページの圧縮情報 (プレフィックスと辞書) が再計算されるためです。
BULK INSERT とデータ圧縮の併用
圧縮後のテーブルにデータを一括読み込みするには、読み込みの実行中にデータを圧縮する必要があります。そのため、BULK INSERT は、圧縮後のテーブルに対して実行する方が実行時間が長くなります。図 6 は、データ圧縮を行った場合とデータ圧縮なしの場合について、ヒープに対する BULK INSERT のパフォーマンスを示しています。最初の 3 つのデータ ポイントは、TABLOCK オプションを指定しない場合の BULK INSERT のパフォーマンスを示しています。TABLOCK オプションを指定しない場合、行圧縮を行った場合とページ圧縮を行った場合の圧縮後のヒープ テーブルのサイズが同じであることに注目してください。ページ圧縮後のヒープに対する BULK INSERT の実行中に TABLOCK オプションを使用しないと、新しく挿入されるページはページ圧縮ではなく行圧縮されます (「圧縮後のデータを操作する」の「新しく挿入される行」にある表 3 参照)。確認するには、付録 E のクエリを使用します。
図 6: ヒープへの BULK INSERT (データ圧縮を行った場合とデータ圧縮なしの場合)
図 6 の最後のデータ ポイントは、(ページ圧縮後のヒープに対して) TABLOCK オプションを指定した BULK INSERT のパフォーマンスを示しています。TABLOCK オプションを指定すると、ほぼすべてのページが読み込み中にページ圧縮されるので、テーブル サイズがより小さくなることに注目してください。したがって、ページ圧縮後のヒープにデータを読み込む際は、忘れずに TABLOCK ヒントを使用してください。
ページ圧縮後のヒープに対して INSERT … SELECT 操作を実行する場合も同様で、新しいページが確実に圧縮されるように、必ず TABLOCK ヒントを使用してください。
BULK INSERT の実行後に CREATE CLUSTERED INDEX を実行する
多くの一括読み込みシナリオでは、通常、データが読み込まれた後に、クラスター化インデックスの作成が行われます。スライディング ウィンドウのシナリオでは、通常、新しいデータが空のステージング テーブルに読み込まれ、その後、パーティション分割されたテーブル内の空のパーティションへの切り替えを行う準備を整えるためにステージング テーブルにクラスター化インデックス (および他の適切なインデックスと制約) が作成されます。空のテーブルにデータを読み込んだ後にクラスター化インデックスを作成する予定があり、データの圧縮も必要な場合、次のように複数の選択肢があります。
- 選択肢 1: 圧縮されていないヒープへの BULK INSERT を実行した後で、CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE) を実行する。データは圧縮されていないヒープに読み込まれるため、他の 2 つの選択肢よりも短時間で読み込みを行うことができます。この選択肢を使用すると、クラスター化インデックスを作成するのと同時にデータを圧縮できるので、合計時間は短縮されます。しかし、選択肢 3 よりも多くの空き領域がユーザー データベース内に必要となります。インデックスが作成されている間、圧縮されていないヒープと圧縮後のクラスター化インデックスが同時にユーザー データベース内に存在する必要があるためです。
- 選択肢 2: ページ圧縮後のヒープへの BULK INSERT を実行した後で、CREATE CLUSTERED INDEX を実行する。データはヒープに読み込まれるため、選択肢 3 よりも短時間で読み込みを行うことができます。しかし、ヒープは圧縮されているため、選択肢 1 よりは読み込みに時間がかかります (データは読み込み中に圧縮されます)。また、インデックスが作成されている間、ヒープとクラスター化インデックスがユーザー データベース内に存在する必要があるので、選択肢 3 よりも多くの空き領域が必要です。ただし、ヒープとクラスター化インデックスはどちらも圧縮されているため、必要な空き領域は選択肢 1 よりは少なくて済みます。
- 選択肢 3: ページ圧縮後のクラスター化インデックスへの BULK INSERT を実行する。データはクラスター化インデックスに読み込まれ、読み込み中に圧縮されるため、この選択肢は他の 2 つよりも実行時間が長くなります。ですが、すべてのタスク (読み込み、圧縮、クラスター化インデックスの作成) が同時に完了します。クラスター化インデックスの作成やデータの圧縮を後処理として行う必要がないため、ユーザー データベース内に追加の空き領域は必要となりません。
図 7 は、データの一括読み込み、クラスター化インデックスの作成、およびデータの圧縮に必要な時間を示しています。 図 8 は、これらのタスクに必要なワークスペースを示しています。
図 7: データの一括読み込み、クラスター化インデックスの作成、およびデータの圧縮に必要な時間 (単純復旧モデル、ONLINE = OFF、SORT_IN_TEMPDB = ON)
図 8: データの一括読み込み、クラスター化インデックスの作成、およびデータの圧縮に必要なワークスペース (単純復旧モデル、ONLINE = OFF、SORT_IN_TEMPDB = ON)
データ圧縮とパーティション操作
パーティション分割を行うと、データの圧縮を選択する際の柔軟性が高まります。テーブル内の各パーティションに異なる圧縮設定を指定できます。ただし、パーティション操作 (切り替え、分割、マージなど) と圧縮設定の関係に注意が必要です。
切り替え
パーティションを切り替えるには、切り替え元と切り替え先の圧縮設定が同じである必要があります。パーティション切り替え操作の切り替え先は、必ず、空のパーティション (またはテーブル) です。空のパーティションまたはテーブルの圧縮設定の変更はメタデータのみに対する操作になるため、非常に簡単です。したがって、切り替えコマンドの実行前には、必要に応じて、切り替え先の圧縮設定を確認および変更してください。
分割
新しいパーティションは、分割元のパーティションのデータ圧縮プロパティを継承します。
マージ
2 つのパーティションをマージすると、実質的に 2 つのパーティション間の境界が削除されます。その結果、1 つのパーティションが削除され、そのパーティション内のデータがすべてもう一方のパーティションに移動されます。したがって、パーティションをマージするには、マージ元のパーティション (削除されるパーティション) とマージ先のパーティション (削除されるパーティションのデータの移動先となるパーティション) が必要です。マージ先パーティションの圧縮プロパティは維持されます。
マージ先パーティションの圧縮設定が NONE の場合、マージ元パーティションからマージ先パーティションに移動されるデータはマージ操作中に圧縮解除されます。マージ先パーティションの圧縮設定が ROW の場合、マージ元パーティションからマージ先パーティションに移動されるデータはマージ操作中に行圧縮されます。マージ先パーティションの圧縮設定が PAGE の場合、表 5 に示すように、マージ元パーティションからマージ先パーティションに移動されるデータは行圧縮 (テーブルがヒープの場合) またはページ圧縮 (テーブルがクラスター化インデックスの場合) されます。
マージ先パーティションの圧縮設定 |
マージ元パーティションからマージ先パーティションに移動されるデータの状況 |
NONE |
データはマージ中に圧縮解除される |
ROW |
データはマージ中に行圧縮される |
PAGE |
- ヒープ : データはマージ中に行圧縮される - クラスター化インデックス : データはマージ中にページ圧縮される |
表 5: パーティションのマージとデータ圧縮
パーティションのマージ操作中、マージ元パーティションとマージ先パーティションは、使用されるパーティション関数に基づいて識別されます。図 9 の例は、LEFT パーティション関数を使用する場合と RIGHT パーティション関数を使用する場合について、パーティションのマージの圧縮動作を示しています。
図 9: データ圧縮とパーティション操作
大規模データ ウェアハウスにおけるパーティション分割では、個々のパーティションを読み取り専用としてマークしてバックアップの必要性を最小限に抑えることができるように、個々のファイル グループ全体を各パーティション専用にする場合があります。このような場合、DBCC SHRINKFILE の使用を避けるため、パーティションが最初に圧縮された状態で読み込まれることが重要です (「データを圧縮した場合のアプリケーション パフォーマンス」参照)。ヒープの場合、これを行うには、TABLOCK ヒントを使用してパーティションを一括読み込みする必要があります。また、クラスター化インデックスの場合、これは、圧縮後のクラスター化インデックスが (後で作成されるのではなく) データが読み込まれる際に用意されており、ファイル グループ内に未使用の空き領域が残されている必要があることを意味します。
データ圧縮と透過的なデータ暗号化
透過的なデータ暗号化 (TDE) も SQL Server 2008 の非常に役立つ機能の 1 つです。TDE を使用すると、アプリケーションに一切変更を加えることなくデータベース内のデータをストレージ レベルで暗号化することができます。これに関して、「データ圧縮は暗号化されたデータベースでどのように機能するのか」という質問をよく受けます。
TDE とデータ圧縮は相互に影響しません。図 10 は、TDE を使用した場合と使用しない場合のそれぞれについて、クラスター化インデックスの圧縮にかかる時間の長さと、ページ圧縮によって節約される領域の量を示しています。図 10 が示すように、圧縮にかかる時間の長さと節約される領域の量のどちらの面でも、TDE がデータ圧縮に及ぼす影響は (あるとしても) ささいなものです。
図 10: データ圧縮と透過的なデータ暗号化の併用
TDE を使用すると、ページはディスクに書き込まれる際に暗号化され、ディスクからメモリに読み込まれる際に暗号化解除されます。データの圧縮 (および圧縮解除) はメモリ内のページに対して実行されるため、データ圧縮は、常に、暗号化されていないデータに対して実行されます。そのため、データ圧縮の効果と効率は TDE の影響を受けません。
まとめ
データ圧縮には複数のメリットがあります。データを圧縮するとディスク領域が節約されます。また、特定のワークロードのパフォーマンス向上に役立つ場合があります。しかし、データ圧縮には、データを圧縮および圧縮解除するため CPU 使用率が上昇するというデメリットもあります。したがって、圧縮の方針を決定する前にテーブルでのワークロード特性を把握しておくことが重要です。データ圧縮では、圧縮のレベル (行またはページ) と圧縮できるオブジェクト (テーブル、インデックス、パーティション) に柔軟性があります。そのため、データとワークロードの特性に基づいて細かく調整することができます。
データ圧縮のもう 1 つの重要なメリットは、アプリケーションに意識させることなく機能し、他の SQL Server 機能 (TDE、バックアップの圧縮など) と適切に連携することです。
このホワイト ペーパーに記載した結果は、テストに使用したデータとハードウェアに基づきます。結果は、使用するデータ、ワークロード、およびハードウェアによって異なります。圧縮対象のテーブルとインデックスを決める際は、徹底的なテストを実行してください。
関連情報:
https://www.microsoft.com/japan/sqlserver/: SQL Server Web サイト
https://technet.microsoft.com/ja-jp/sqlserver/: SQL Server TechCenter
https://msdn.microsoft.com/ja-jp/sqlserver/: SQL Server デベロッパー センター
このホワイト ペーパーはお役に立ちましたか? フィードバックをお寄せください。 1 (役に立たなかった) ～ 5 (非常に役に立った) の 5 段階で評価してください。また、その評価の理由もお知らせください。以下に例を示します。
- 評価が高いのは、例が適切、図がわかりやすい、説明が明快といった理由からですか。
- 評価が低いのは、例が少ない、図がわかりにくい、説明があいまいといった理由からですか。
このようなフィードバックをお寄せいただくと、今後のホワイト ペーパーの品質向上につながります。
付録 A: テストで使用したハードウェアとソフトウェア
(ユーザーのテスト環境で実行された図 2 と図 3 のテストを除く) すべてのテストは、以下のハードウェア環境とソフトウェア環境で実行されました。
サーバー
以下のハードウェアを搭載した DELL R805
- 2 ソケットのクアッド コア
- 2.20 GHz の AMD Opteron プロセッサ 2354
- 32 GB の RAM
ストレージ
EqualLogic の iSCSI ストレージ
3 つのディスク シェルフ – それぞれ 14 個の SAS ドライブを収容 (各ドライブのスペックを以下に示します)
· 300 GB
· 15K RPM
· RAID 1+0
ソフトウェア
- 64 ビット版の Windows Server 2008 Enterprise オペレーティング システム
- 64 ビット版の SQL Server 2008 Enterprise
付録 B: アロケーション ユニットの種類ごとのデータ量
テーブルまたはパーティションは、 IN_ROW_DATA、LOB_DATA、ROW_OVERFLOW_DATA という 3 つのアロケーション ユニットを持つことができます。通常、テーブル内のデータのほとんどは IN_ROW_DATA アロケーション ユニットに格納されています。行のサイズやテーブル オプションによっては、一部のデータが行の外 ( ROW_OVERFLOW_DATA アロケーション ユニットまたは LOB_DATA アロケーション ユニット) に格納される場合もあります。3 つのアロケーション ユニットそれぞれに格納されているデータの量を確認するには、次のスクリプトを使用してください。
-- このスクリプトは、何等保証もない現状有姿のままで提供されるものです。
SELECT OBJECT_NAME(p.object_id) AS Object_Name
, i.name AS Index_Name
, ps.in_row_used_page_count AS IN_ROW_DATA
, ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA
, ps.lob_used_page_count AS LOB_DATA
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE OBJECTPROPERTY (p.[object_id], 'IsUserTable') = 1
付録 C: データベース ファイル内の空き領域
-- このスクリプトは、何等保証もない現状有姿のままで提供されるものです。
SELECT
a.file_id,
LOGICAL_NAME = a.name,
PHYSICAL_FILENAME = a.physical_name,
FILEGROUP_NAME = b.name,
FILE_SIZE_MB = CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)),
SPACE_USED_MB = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),
FREE_SPACE_MB = CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2))
FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b
ON a.data_space_id = b.data_space_id
付録 D: ページ圧縮後のインデックスで非リーフ ページが行圧縮されることを確認する
リーフ レベルのページはページ圧縮されているが、非リーフ ページはページ圧縮ではなく行圧縮されていることを確認するには、ページ圧縮後の (クラスター化または非クラスター化) インデックスに対して次のクエリを使用します。
SELECT
o.name, ips.index_type_desc, p.partition_number, p.data_compression_desc,
ips.index_level, ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), object_id(<インデックス名>), NULL, NULL, 'DETAILED') ips
JOIN sys.objects o ON o.object_id = ips.object_id
JOIN sys.partitions p ON p.object_id = o.object_id
ORDER BY ips.index_level
ページ圧縮後のクラスター化インデックスに対してこのクエリを実行すると、出力は次のようになります。
名前 |
index_type_desc |
partition_number |
data_compression_desc |
index_level |
page_count |
compressed_page_count |
TRADE_BULK |
CLUSTERED INDEX |
1 |
PAGE |
0 |
370508 |
370502 |
TRADE_BULK |
CLUSTERED INDEX |
1 |
PAGE |
1 |
830 |
0 |
TRADE_BULK |
CLUSTERED INDEX |
1 |
PAGE |
2 |
5 |
0 |
TRADE_BULK |
CLUSTERED INDEX |
1 |
PAGE |
3 |
1 |
0 |
表 6
出力の index_level 列と compressed_page_count 列に注目してください。リーフ ページ (index_level = 0) はほぼすべてページ圧縮されていますが、非リーフ ページ (index_level >=1) は 1 つもページ圧縮されていない (非リーフ ページの compressed_page_count 列は 0 である) ことがわかります。
付録 E: テーブル、インデックス、またはパーティションでページ圧縮後のページ数を確認する
次のクエリを使用して、テーブル、インデックス、またはパーティションでページ圧縮後のページ数を確認することができます。
SELECT
o.name, ips.index_type_desc, p.partition_number, p.data_compression_desc,
ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), object_id(<テーブル名>), NULL, NULL, 'DETAILED') ips
JOIN sys.objects o ON o.object_id = ips.object_id
JOIN sys.partitions p ON p.object_id = o.object_id
ページ圧縮後のヒープに対して BULK INSERT を実行してから上記のクエリを実行すると、出力は次のようになります。
名前 |
index_type_desc |
partition_number |
data_compression_desc |
page_count |
compressed_page_count |
TRADE_BULK |
HEAP |
1 |
PAGE |
501574 |
0 |
表 7
data_compression_desc 列と compressed_page_count 列に注目してください。sys.partitions の data_compression_desc 列は、特定のテーブル、インデックス、またはパーティションのデータ圧縮設定 (メタデータ) を示します。ページ圧縮後のページの実際の数を取得するには、動的管理関数 (DMF) sys.dm_db_index_physical_stats で compressed_page_count 列を使用します。この出力では、ヒープのデータ圧縮設定が PAGE であるにもかかわらず、新しく読み込まれたページはページ圧縮されていないことがわかります。
TABLOCK を指定した BULK INSERT をページ圧縮後のヒープに対して実行してからこのクエリを実行すると、出力は次のようになります。
名前 |
index_type_desc |
partition_number |
data_compression_desc |
page_count |
compressed_page_count |
TRADE_BULK |
HEAP |
1 |
PAGE |
370658 |
370656 |
表 8
ほぼすべてのページがページ圧縮されていることがわかります。ページ圧縮後のヒープにデータを読み込む場合、またはページ圧縮後のヒープへの INSERT … SELECT 操作を実行する場合は、忘れずに TABLOCK ヒントを使用してください。
注: compressed_page_count 列が表示されるのは、sys.dm_db_index_physical_stats DMF で DETAILED モードが使用された場合のみです。 (既定値の) LIMITED モードが使用された場合、この列の出力は NULL になります。DETAILED モードを使用すると、テーブル内のすべてのページがスキャンされ、大規模テーブルの場合はかなりの時間がかかるため、DETAILED モードを使用する場合は注意が必要です。DETAILED モードを使用する場合は、パラメータ object_id に値を指定することをお勧めします。NULL は指定しないでください。object_id に NULL を指定すると (無効なオブジェクトや存在しないオブジェクトへの参照は NULL に変換されます)、データベース内のすべてのテーブルのすべてのページがスキャンされます。これは、パフォーマンスに大きな影響を及ぼす可能性があります。
付録 F: 関連リンク
SQL Server ストレージ エンジン チームのブログ (英語)
SQL Server 上での SAP アプリケーションの実行に関するブログのデータ圧縮に関する記事 (英語)
SAP ソリューション向けの SQL Server 2008 テクノロジ (英語)
SQL Server のデータ圧縮に関する HP のホワイト ペーパー
SQL Server のデータ圧縮に関する Unisys のホワイト ペーパー (英語)
SQL Server のデータ圧縮に関する NetApp のホワイト ペーパー (英語)
データ圧縮に関する Linchi Shea のブログ記事 (英語)
データ圧縮に関する Paul Nielsen のブログ記事 (英語)
データ圧縮に関する Kalen Delaney のブログ記事 (英語)
データ圧縮に関するマイクロソフト導入事例 (英語)