sp_estimate_data_compression_savings (Transact-SQL)
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance
要求されたオブジェクトの現在のサイズを返し、要求された圧縮状態のオブジェクト サイズを推定します。 圧縮は、テーブル全体またはテーブルの一部について評価できます。 これには、ヒープ、クラスター化インデックス、非クラスター化インデックス、列ストア インデックス、インデックス付きビュー、テーブルおよびインデックス パーティションが含まれます。 オブジェクトは、行、ページ、列ストア、または列ストア アーカイブの圧縮を使用して圧縮できます。 テーブル、インデックス、またはパーティションが既に圧縮されている場合は、この手順を使用して、テーブル、インデックス、またはパーティションが圧縮なしで再圧縮または格納されている場合のサイズを見積もることができます。
sys.sp_estimate_data_compression_savings システム ストアド プロシージャは、Azure SQL Database と Azure SQL Managed Instance で使用できます。
SQL Server 2022 (16.x) 以降では、 xml データ型を使用して列の行外 XML データを圧縮できるため、ストレージとメモリの要件を減らすことができます。 詳細については、「CREATE TABLE」および「CREATE INDEX」を参照してください。 sp_estimate_data_compression_savings では、XML 圧縮見積もりがサポートされます。
Note
圧縮と sp_estimate_data_compression_savings は、SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。
要求された圧縮設定を使用する場合にオブジェクトのサイズを見積もるために、このストアド プロシージャはソース オブジェクトをサンプリングし、このデータを tempdbで作成された同等のテーブルとインデックスに読み込みます。 tempdbで作成されたテーブルまたはインデックスは、要求された設定に圧縮され、推定圧縮の節約が計算されます。
テーブル、インデックス、またはパーティションの圧縮状態を変更するには、 ALTER TABLE または ALTER INDEX ステートメントを使用します。 圧縮に関する一般的な情報については、「 Data 圧縮」を参照してください。
Note
既存のデータが断片化されている場合は、インデックスを再構築することで、圧縮を使用しなくてもデータのサイズを削減できる可能性があります。 インデックスの場合、インデックスの再構築中に塗りつぶし係数が適用されます。 これによってインデックスのサイズが増える可能性があります。
構文
sp_estimate_data_compression_savings
[ @schema_name = ] N'schema_name'
, [ @object_name = ] N'object_name'
, [ @index_id = ] index_id
, [ @partition_number = ] partition_number
, [ @data_compression = ] N'data_compression'
[ , [ @xml_compression = ] xml_compression ]
[ ; ]
引数
[ @schema_name = ] N'schema_name'
テーブルまたはインデックス付きビューを含むデータベース スキーマの名前。 @schema_name は sysname で、既定値はありません。 @schema_nameがNULLされている場合は、現在のユーザーの既定のスキーマが使用されます。
[ @object_name = ] N'object_name'
インデックスが存在するテーブルまたはインデックス付きビューの名前。 @object_name は sysname で、既定値はありません。
[ @index_id = ] index_id
インデックスの ID です。 @index_id は int で、次のいずれかの値を指定できます。
- インデックスの ID 番号
NULL0object_idがヒープの場合
ベース テーブルまたはビューのすべてのインデックスの情報を返すには、 NULLを指定します。 NULLを指定する場合は、@partition_numberのNULLも指定する必要があります。
[ @partition_number = ] partition_number
オブジェクト内のパーティション番号。 @partition_number は int で、次のいずれかの値を指定できます。
- インデックスまたはヒープのパーティション番号
NULL1パーティション分割されていないインデックスまたはヒープの場合
パーティションを指定するには、 $PARTITION 関数を指定することもできます。 所有オブジェクトのすべてのパーティションの情報を返すには、 NULLを指定します。
[ @data_compression = ] N'data_compression'
評価する圧縮の種類を指定します。 @data_compression は nvarchar(60)であり、次のいずれかの値を指定できます。
NONEROWPAGECOLUMNSTORECOLUMNSTORE_ARCHIVE
SQL Server 2022 (16.x) 以降のバージョンでは、 NULL も可能な値です。 @xml_compressionがNULLされている場合、@data_compressionはNULLできません。
[ @xml_compression = ] xml_compression
適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance
XML 圧縮の節約を計算するかどうかを指定します。 @xml_compression は ビットであり、次のいずれかの値を指定できます。
NULL(既定)01
@data_compressionがNULLされている場合、@xml_compressionはNULLできません。
リターン コードの値
0 (成功) または 1 (失敗)。
結果セット
テーブル、インデックス、またはパーティションの現在のサイズと推定サイズを提供するために、次の結果セットが返されます。
| 列名 | データ型 | 説明 |
|---|---|---|
object_name |
sysname | テーブルまたはインデックス付きビューの名前。 |
schema_name |
sysname | テーブルまたはインデックス付きビューのスキーマ。 |
index_id |
int | インデックスのインデックス ID:0 = ヒープ1 = クラスター化インデックス>1 = 非クラスター化インデックス |
partition_number |
int | パーティション番号。 パーティション分割されていないテーブルまたはインデックスの 1 を返します。 |
size_with_current_compression_setting (KB) |
bigint | 要求されたテーブル、インデックス、またはパーティションの現在のサイズ。 |
size_with_requested_compression_setting (KB) |
bigint | 要求された圧縮設定を使用するテーブル、インデックス、またはパーティションの推定サイズ。また、必要に応じて既存のフィル ファクターを指定し、断片化がないと仮定します。 |
sample_size_with_current_compression_setting (KB) |
bigint | 現在の圧縮設定を使用したサンプルのサイズ。 このサイズには、断片化が含まれます。 |
sample_size_with_requested_compression_setting (KB) |
bigint | 要求された圧縮設定を使用して作成されるサンプルのサイズ。また、該当する場合は、既存のフィル ファクターと断片化なし。 |
解説
sp_estimate_data_compression_savingsを使用して、行、ページ、列ストア、列ストア アーカイブ、または XML 圧縮に対してテーブルまたはパーティションを有効にしたときに発生する可能性のある節約額を見積もります。 たとえば、行の平均サイズを 40% 小さくできる場合は、オブジェクトのサイズを 40% 小さくすることができます。 これは FILL FACTOR と行サイズに左右されるため、領域を削減できない場合もあります。 たとえば、長さが 8,000 バイトの行があり、そのサイズを 40% 縮小した場合でも、データ ページに収めることができる行は 1 つだけです。 節約はありません。
圧縮されていないテーブルまたはインデックスで sp_estimate_data_compression_savings を実行した結果、サイズが大きくなることが示される場合は、多くの行でデータ型のほぼ全体の精度が使用され、圧縮形式に必要な小さなオーバーヘッドの追加は、圧縮による節約以上であることを意味します。 このまれなケースでは、圧縮を有効にしないでください。
テーブルで既に圧縮が有効になっている場合は、 sp_estimate_data_compression_savings を使用して、テーブルが圧縮されていない場合に行の平均サイズを見積もることができます。
この操作中に、テーブルに対して意図共有 (IS) ロックが取得されます。 IS ロックを取得できない場合、プロシージャはブロックされます。 テーブルは、既定の読み取りコミット済み分離レベルでスキャンされます。
要求された圧縮設定が現在の圧縮設定と同じ場合、ストアド プロシージャは、ソース オブジェクトのインデックスの既存のフィル ファクターを使用して、データの断片化なしで推定サイズを返します。
インデックスまたはパーティション ID が存在しない場合、結果は返されません。
アクセス許可
テーブル SELECT 含むデータベースのテーブル、 VIEW DATABASE STATE 、および VIEW DEFINITION と tempdbに対するアクセス許可が必要です。
制限事項
SQL Server 2017 (14.x) 以前のバージョンでは、この手順は列ストア インデックスには適用されないため、データ圧縮パラメーター COLUMNSTORE および COLUMNSTORE_ARCHIVEを受け入れませんでした。 SQL Server 2019 (15.x) 以降のバージョン、および Azure SQL Database と Azure SQL Managed Instance では、列ストア インデックスを見積もりのソース オブジェクトとして使用することも、要求された圧縮の種類として使用することもできます。
Memory-Optimized TempDB Metadata が有効になっている場合、一時テーブルでの列ストア インデックスの作成はサポートされません。 この制限のため、メモリ最適化 TempDB メタデータが有効になっている場合、COLUMNSTOREおよびCOLUMNSTORE_ARCHIVEデータ圧縮パラメーターではsp_estimate_data_compression_savingsはサポートされません。
列ストア インデックスに関する考慮事項
SQL Server 2019 (15.x) 以降、および Azure SQL Database と Azure SQL Managed Instance では、 sp_estimate_compression_savings では列ストアと列ストアの両方のアーカイブ圧縮の推定がサポートされています。 ページと行の圧縮とは異なり、列ストア圧縮をオブジェクトに適用するには、新しい列ストア インデックスを作成する必要があります。 このため、このプロシージャの COLUMNSTORE オプションと COLUMNSTORE_ARCHIVE オプションを使用する場合、プロシージャに提供されるソース オブジェクトの型によって、圧縮サイズの見積もりに使用される列ストア インデックスの種類が決まります。 次の表は、 @data_compression パラメーターが COLUMNSTORE または COLUMNSTORE_ARCHIVE に設定されている場合に、ソース オブジェクトの種類ごとに圧縮の節約を見積もるために使用される参照オブジェクトを示しています。
| Source オブジェクト | Reference オブジェクト |
|---|---|
| **ヒープ | クラスター化列ストア インデックス |
| クラスター化インデックス | クラスター化列ストア インデックス |
| 非クラスター化インデックス | 非クラスター化列ストア インデックス (指定された非クラスター化インデックスのキー列と含まれる列、およびテーブルのパーティション列 (存在する場合) を含む) |
| 非クラスター化列ストア インデックス | 非クラスター化列ストア インデックス (指定された非クラスター化列ストア インデックスと同じ列を含む) |
| クラスター化列ストア インデックス | クラスター化列ストア インデックス |
Note
行ストア ソース オブジェクト (クラスター化インデックス、非クラスター化インデックスまたはヒープ) から列ストア圧縮を推定する場合、列ストア インデックスでサポートされていないデータ型を持つ列がソース オブジェクトに存在する場合、 sp_estimate_compression_savings はエラーで失敗します。
同様に、 @data_compression パラメーターが NONE、 ROW、または PAGE に設定されていて、ソース オブジェクトが列ストア インデックスである場合、次の表は、使用される参照オブジェクトの概要を示しています。
| Source オブジェクト | Reference オブジェクト |
|---|---|
| クラスター化列ストア インデックス | ヒープ |
| 非クラスター化列ストア インデックス | 非クラスター化インデックス (非クラスター化列ストア インデックスに含まれる列をキー列として含み、含まれる列としてテーブルのパーティション列 (存在する場合) を含む) |
Note
列ストア ソース オブジェクトから行ストア圧縮 (NONE、ROW、または PAGE) を推定する場合は、行ストア (非クラスター化) インデックスでサポートされる制限であるため、ソース インデックスに 32 を超えるキー列が含まれていないことを確認してください。
例
この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。
A. ROW 圧縮による削減額の見積もり
次の例では、ROW圧縮を使用して圧縮されている場合、Production.WorkOrderRouting テーブルのサイズを見積もります。
EXEC sys.sp_estimate_data_compression_savings
'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO
B. PAGE 圧縮と XML 圧縮による節約の見積もり
適用対象: SQL Server 2022 (16.x) 以降のバージョン
次の例では、PAGE圧縮を使用して圧縮し、@xml_compression値が有効になっている場合、Production.ProductModel テーブルのサイズを見積もります。
EXEC sys.sp_estimate_data_compression_savings
'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO