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