分享方式：

sp_estimate_data_compression_savings (Transact-SQL)

  • 文章

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

傳回所要求物件的目前大小，並估計所要求壓縮狀態的物件大小。 您可以評估整個資料表或數據表部分的壓縮。 這包括堆積、叢集索引、非叢集索引、數據行存放區索引、索引檢視表，以及數據表和索引分割區。 物件可以使用數據列、頁面、數據行存放區或數據行存放區封存壓縮來壓縮。 如果數據表、索引或分割區已經壓縮，您可以使用此程式來估計數據表、索引或分割區的大小，如果它已重新壓縮或儲存而不壓縮。

系統sys.sp_estimate_data_compression_savings預存程式可在 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中使用。

從 SQL Server 2022 （16.x） 開始，您可以使用 xml 資料類型壓縮數據行中的異列 XML 數據，以減少記憶體和記憶體需求。 如需詳細資訊，請參閱 CREATE TABLE 和 CREATE INDEX。 sp_estimate_data_compression_savings 支援 XML 壓縮估計值。

注意

壓縮和 sp_estimate_data_compression_savings 不適用於每個 SQL Server 版本。 如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

若要估計物件的大小，如果是使用要求的壓縮設定，這個預存程式會取樣來源物件，並將此數據載入至 中 tempdb建立的對等數據表和索引。 然後，在中 tempdb 建立的數據表或索引會壓縮至要求的設定，並計算預估的壓縮節省。

若要變更數據表、索引或分割區的壓縮狀態，請使用 ALTER TABLE 或 ALTER INDEX 語句。 如需壓縮的一般資訊，請參閱 數據壓縮。

注意

如果現有的數據已分散，您可以藉由重建索引來減少其大小，而不需使用壓縮。 針對索引，填滿因數將會在索引重建期間套用。 這可能會增加索引的大小。

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

索引的標識碼。 @index_id為 int，而且可以是下列其中一個值：

  • 索引的標識碼
  • NULL
  • 0 如果 object_id 是堆積，則為

若要傳回基表或檢視表之所有索引的資訊，請指定 NULL。 如果您指定 NULL，您也必須指定 NULL @partition_number。

[ @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 為 ，則@data_compression不能NULL為 NULL。

[ @xml_compression = ] xml_compression

適用於：SQL Server 2022 （16.x） 和更新版本、Azure SQL 資料庫 和 Azure SQL 受控執行個體

指定是否要計算 XML 壓縮的節省量。 @xml_compression為位，而且可以是下列其中一個值：

  • NULL (預設值)
  • 0
  • 1

如果 @data_compression 為 ，則@xml_compression不能NULL為 NULL。

傳回碼值

0 (成功) 或 1 (失敗)。

結果集

傳回下列結果集，以提供數據表、索引或數據分割的目前和估計大小。

資料行名稱 資料類型 描述
object_name sysname 數據表或索引檢視表的名稱。
schema_name sysname 數據表或索引檢視表的架構。
index_id int 索引的索引識別碼：

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%。 您可能不會收到節省空間，因為這取決於填滿因數和數據列的大小。 例如，如果您有長度為8,000個字節的數據列，且其大小會減少40%，您仍然可以在數據頁面上只容納一個數據列。 沒有節省。

如果在未壓縮的數據表或索引上執行 sp_estimate_data_compression_savings 的結果表示大小將會增加，這表示許多數據列幾乎會使用數據類型的整個有效位數，而且增加壓縮格式所需的小型額外負荷大於壓縮的節省。 在此情況下，請勿啟用壓縮。

如果數據表已啟用壓縮，您可以使用 sp_estimate_data_compression_savings 來估計未壓縮數據表時數據列的平均大小。

此作業期間會在數據表上取得意圖共用 （IS） 鎖定。 如果無法取得IS鎖定，就會封鎖程式。 數據表會在預設讀取認可隔離等級下掃描。

如果要求的壓縮設定與目前的壓縮設定相同，預存程式會使用來源物件上索引的現有填滿因數，傳回沒有數據片段的估計大小。

如果索引或分割區標識碼不存在，則不會傳回任何結果。

權限

SELECT需要數據表的許可權，VIEW DATABASE STATE以及VIEW DEFINITION包含數據表的資料庫和 上的tempdb許可權。

限制

在 SQL Server 2017 （14.x） 和舊版中，此程式不適用於資料行存放區索引，因此不接受數據壓縮參數 COLUMNSTORE 和 COLUMNSTORE_ARCHIVE。 在 SQL Server 2019 （15.x） 和更新版本中，在 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中，數據行存放區索引可以同時作為估計的來源物件，以及做為要求的壓縮類型。

啟用記憶體優化TempDB元數據時，不支援在臨時表上建立資料行存放區索引。 由於這項限制， sp_estimate_data_compression_savings COLUMNSTORE 啟用記憶體優化TempDB元數據時，不支援和 COLUMNSTORE_ARCHIVE 資料壓縮參數。

數據行存放區索引的考慮

從 SQL Server 2019 （15.x） 開始，在 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中，sp_estimate_compression_savings支持估計數據行存放區和數據行存放區封存壓縮。 不同於頁面和數據列壓縮，將數據行存放區壓縮套用至物件需要建立新的數據行存放區索引。 因此，使用此 COLUMNSTORE 程式的 和 COLUMNSTORE_ARCHIVE 選項時，提供給程式的來源物件類型會決定用於壓縮大小估計的數據行存放區索引類型。 下表說明當 @data_compression 參數設定COLUMNSTORE為 或 COLUMNSTORE_ARCHIVE時，用來估計每個來源物件類型壓縮節省的參考物件。

來源物件 參考物件
**堆 叢集資料行存放區索引
叢集索引 叢集資料行存放區索引
非叢集索引 非叢集數據行存放區索引（包括索引鍵數據行和所提供非叢集索引的任何包含數據行，以及數據表的數據分割數據行，如果有的話）
非叢集數據行存放區索引 非叢集資料行存放區索引（包括與所提供非叢集資料行存放區索引相同的數據行）
叢集資料行存放區索引 叢集資料行存放區索引

注意

從數據列存放區來源物件（叢集索引、非叢集索引或堆積）估計數據行存放區壓縮時，如果源物件中有任何數據行具有數據行存放區索引中不支持的數據類型， sp_estimate_compression_savings 將會失敗並出現錯誤。

同樣地，當@data_compression參數設定為 NONE、 ROW或 PAGE 且來源對像是數據行存放區索引時，下表概述所使用的參考物件。

來源物件 參考物件
叢集資料行存放區索引 堆積
非叢集數據行存放區索引 非叢集索引（包括非叢集數據行存放區索引中包含的數據行作為索引鍵數據行，以及數據表的數據分割數據行，如果有的話，作為包含的數據行）

注意

從數據行存放區來源對象估計資料列存放區壓縮 （NONE、ROW 或 PAGE） 時，請確定來源索引不包含超過 32 個索引鍵數據行，因為這是數據列存放區 （非叢集） 索引中支援的限制。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （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來估計數據表的大小Production.ProductModel，並啟用@xml_compression值。

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

相關內容