sp_estimate_data_compression_savings (Transact-SQL)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

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

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

注意

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

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

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

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

注意

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

Transact-SQL 語法慣例

語法

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

索引的標識碼。 index_id為 int，而且可以是下列其中一個值：如果object_id為堆積，則為索引、NULL 或 0 的標識碼。 若要傳回基表或檢視表之所有索引的資訊，請指定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 受控執行個體。

指定是否要計算 XML 壓縮的節省量。 xml_compression位，可以是 NULL、0 或 1。 預設值是 NULL。

如果 data_compression 為 NULL，則xml_compression不能為 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 2019 （15.x） 之前，此程式不適用於數據行存放區索引，因此不接受數據壓縮參數 COLUMNSTORE 和 COLUMNSTORE_ARCHIVE。 從 SQL Server 2019 （15.x） 開始，在 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中，數據行存放區索引可以同時作為估計的來源物件，以及做為要求的壓縮類型。

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

SQL Server 2022 （16.x） 候選版 （RC） 0 不會估計 XML 索引的節省。

數據行存放區索引的考慮

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

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

注意

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

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

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

注意

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

範例

A. 使用 ROW 壓縮估計節省成本

下列範例會使用ROW壓縮來估計數據表的大小Production.WorkOrderRouting。

USE AdventureWorks2022;
GO
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值。

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

相關內容