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 圧縮見積もりがサポートされます。

要求された圧縮設定を使用する場合にオブジェクトのサイズを見積もるために、このストアド プロシージャはソース オブジェクトをサンプリングし、このデータを tempdbで作成された同等のテーブルとインデックスに読み込みます。 tempdbで作成されたテーブルまたはインデックスは、要求された設定に圧縮され、推定圧縮の節約が計算されます。

テーブル、インデックス、またはパーティションの圧縮状態を変更するには、 ALTER TABLE または ALTER INDEX ステートメントを使用します。 圧縮に関する一般的な情報については、「 Data 圧縮」を参照してください。

Transact-SQL 構文表記規則

構文

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 番号
• NULL
• 0object_idがヒープの場合

ベース テーブルまたはビューのすべてのインデックスの情報を返すには、 NULLを指定します。 NULLを指定する場合は、@partition_numberのNULLも指定する必要があります。

[ @partition_number = ] partition_number

オブジェクト内のパーティション番号。 @partition_number は int で、次のいずれかの値を指定できます。

• インデックスまたはヒープのパーティション番号
• NULL
• 1 パーティション分割されていないインデックスまたはヒープの場合

パーティションを指定するには、 $PARTITION 関数を指定することもできます。 所有オブジェクトのすべてのパーティションの情報を返すには、 NULLを指定します。

[ @data_compression = ] N'data_compression'

評価する圧縮の種類を指定します。 @data_compression は nvarchar(60)であり、次のいずれかの値を指定できます。

• 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 Database、および Azure SQL Managed Instance

XML 圧縮の節約を計算するかどうかを指定します。 @xml_compression は ビットであり、次のいずれかの値を指定できます。

• NULL (既定)
• 0
• 1

@data_compressionがNULLされている場合、@xml_compressionはNULLできません。

リターン コードの値

0 (成功) または 1 (失敗)。

結果セット

テーブル、インデックス、またはパーティションの現在のサイズと推定サイズを提供するために、次の結果セットが返されます。

解説

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 に設定されている場合に、ソース オブジェクトの種類ごとに圧縮の節約を見積もるために使用される参照オブジェクトを示しています。

同様に、 @data_compression パラメーターが NONE、 ROW、または PAGE に設定されていて、ソース オブジェクトが列ストア インデックスである場合、次の表は、使用される参照オブジェクトの概要を示しています。

例

この記事の 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

関連するコンテンツ

• CREATE TABLE (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• sys.partitions (Transact-SQL)
• データベース エンジン ストアド プロシージャ (Transact-SQL)
• Unicode 圧縮の実装