sp_estimate_data_compression_savings (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Возвращает текущий размер запрошенного объекта и оценивает размер объекта для запрошенного состояния сжатия. Сжатие можно оценить для всех таблиц или только для части. К ним относятся кучи, кластеризованные индексы, некластеризованные индексы, индексы columnstore, индексированные представления и секции таблиц и индексов. Объекты можно сжимать с помощью сжатия строк, страниц, columnstore или columnstore. Если таблица, индекс или секция уже сжимаются, можно использовать эту процедуру для оценки размера таблицы, индекса или секции, если она повторно сжимается или хранится без сжатия.

Начиная с 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 процедура системы доступна в База данных SQL Azure и Управляемый экземпляр SQL Azure.

Чтобы оценить размер объекта, если бы он использовал запрошенный параметр сжатия, эта хранимая процедура примеры исходного объекта и загружает эти данные в эквивалентную таблицу и индекс, созданный в 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 и может быть одним из следующих значений: идентификатор индекса, NULL или 0, если object_id является кучей. Чтобы вернуть данные для всех индексов базовой таблицы или представления, укажите значение NULL. При указании NULL необходимо также указать ЗНАЧЕНИЕ NULL для partition_number.

[ @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 также является возможным. data_compression не может иметь значение NULL, если xml_compression имеет значение NULL.

[ @xml_compression = ] xml_compression

Область применения: SQL Server 2022 (16.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure.

Указывает, следует ли вычислять экономию для сжатия XML. xml_compression бит и может иметь значение NULL, 0 или 1. По умолчанию имеет значение NULL.

xml_compression не может иметь значение NULL, если data_compression имеет значение NULL.

Значения кода возврата

0 (успешно) или 1 (сбой).

Результирующий набор

Приведенный ниже результирующий набор содержит сведения о текущем и предполагаемом размере таблицы, индекса или секции.

Имя столбца Тип данных Description
object_name sysname Имя таблицы или индексированного представления.
schema_name sysname Схема таблицы или индексированного представления.
index_id int Идентификатор индекса:

0 = куча;

1 = кластеризованный индекс;

> 1 = некластеризованный индекс
partition_number int Номер секции. Возвращает 1 для несекционированной таблицы или индекса.
size_with_current_compression_setting (КБ) bigint Размер запрошенной таблицы, индекса или секции в текущем состоянии.
size_with_requested_compression_setting (КБ) bigint Предполагаемый размер таблицы, индекса или секции, использующего запрошенный параметр сжатия; и, если применимо, существующий коэффициент заполнения и предполагается, что фрагментация отсутствует.
sample_size_with_current_compression_setting (КБ) bigint Размер образца с текущими настройками сжатия. К этим настройкам относится любая фрагментация.
sample_size_with_requested_compression_setting (КБ) bigint Размер образца, созданного с использованием запрошенных настроек сжатия, и, если применимо, существующего коэффициента заполнения при отсутствии фрагментации.

Замечания

Используйте sp_estimate_data_compression_savings для оценки экономии, которая может возникать при включении таблицы или секции для строки, страницы, columnstore, архива columnstore или сжатия XML. Например, если средний размер строки может быть уменьшен на 40 процентов, можно уменьшить размер объекта на 40 процентов. Но выигрыша можно не получить, поскольку экономия места зависит от коэффициента заполнения и размера строки. Например, если у вас есть строка размером 8 000 байтов, а размер ее уменьшается на 40 процентов, вы по-прежнему можете поместить только одну строку на страницу данных. Нет экономии.

Если результаты выполнения sp_estimate_data_compression_savings в несжатой таблице или индексе указывают на увеличение размера, это означает, что многие строки используют почти всю точность типов данных, а добавление небольших затрат, необходимых для сжатого формата, превышает экономию от сжатия. В этом редких случаях не включите сжатие.

Если таблица уже включена для сжатия, можно оценить sp_estimate_data_compression_savings средний размер строки, если таблица распаковано.

Блокировка общего намерения (IS) приобретается в таблице во время этой операции. Если блокировка IS не может быть получена, процедура будет заблокирована. Таблица сканируется на уровне изоляции, зафиксированной по умолчанию.

Если запрошенный параметр сжатия совпадает с текущим параметром сжатия, хранимая процедура возвращает предполагаемый размер без фрагментации данных и использует существующий коэффициент заполнения для индексов исходного объекта.

Если индекс или идентификатор секции не существует, результаты не возвращаются.

Разрешения

Требуется SELECT разрешение на таблицу и VIEW DEFINITION базу данных, VIEW DATABASE STATE содержащую таблицу и в tempdbней.

Ограничения

До SQL Server 2019 (15.x) эта процедура не применяется к индексам columnstore и поэтому не принимает параметры сжатия данных COLUMNSTORE и COLUMNSTORE_ARCHIVE. Начиная с SQL Server 2019 (15.x), а в База данных SQL Azure и Управляемый экземпляр SQL Azure индексы columnstore можно использовать как в качестве исходного объекта для оценки, так и в качестве запрошенного типа сжатия.

Если метаданные TempDB оптимизированы для памяти, создание индексов columnstore во временных таблицах не поддерживается. Из-за этого ограничения sp_estimate_data_compression_savings не поддерживается с параметрами сжатия данных COLUMNSTORE и COLUMNSTORE_ARCHIVE при включении метаданных TempDB, оптимизированных для памяти.

Кандидат выпуска SQL Server 2022 (16.x) (RC) 0 не оценивает экономию для XML-индексов.

Рекомендации по индексам columnstore

Начиная с SQL Server 2019 (15.x), а в База данных SQL Azure и Управляемый экземпляр SQL Azure sp_estimate_compression_savings поддерживается оценка сжатия архива columnstore и columnstore. В отличие от сжатия страниц и строк, применение сжатия columnstore к объекту требует создания нового индекса columnstore. По этой причине при использовании columnSTORE и COLUMNSTORE_ARCHIVE параметров этой процедуры тип исходного объекта, предоставленного процедуре, определяет тип индекса columnstore, используемого для оценки сжатого размера. В следующей таблице показаны эталонные объекты, используемые для оценки экономии сжатия для каждого типа исходного объекта, если @data_compression параметр имеет значение COLUMNSTORE или COLUMNSTORE_ARCHIVE.

Исходный объект Ссылочный объект
Куча Кластеризованный индекс columnstore
Кластеризованный индекс Кластеризованный индекс columnstore
Некластеризованный индекс Некластеризованный индекс columnstore (включая ключевые столбцы и все включенные столбцы предоставленного некластеризованного индекса, а также столбец секционирования таблицы, если таковой есть)
некластеризованный индекс columnstore Некластеризованный индекс columnstore (включая те же столбцы, что и предоставленный некластеризованный индекс columnstore)
Кластеризованный индекс columnstore Кластеризованный индекс columnstore

Примечание.

При оценке сжатия columnstore из исходного объекта rowstore (кластеризованный индекс, некластеризованный индекс или куча), если в исходном объекте есть столбцы, имеющие тип данных, который не поддерживается в индексе columnstore, sp_estimate_compression_savings завершится ошибкой.

Аналогичным образом, если @data_compression параметр имеет значение NONE, ROWили PAGE исходный объект является индексом columnstore, в следующей таблице описаны используемые эталонные объекты.

Исходный объект Ссылочный объект
Кластеризованный индекс columnstore Куча
некластеризованный индекс columnstore Некластеризованный индекс (включая столбцы, содержащиеся в некластеризованном индексе columnstore в качестве ключевых столбцов, и столбец секционирования таблицы, если таковой включен)

Примечание.

При оценке сжатия rowstore (NONE, ROW или PAGE) из исходного объекта columnstore убедитесь, что исходный индекс не содержит более 32 ключевых столбцов, так как это ограничение, поддерживаемое в индексе rowstore (некластеризованный).

Примеры

А. Оценка экономии с помощью сжатия 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

Следующие шаги