sp_estimate_data_compression_savings (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure
Возвращает текущий размер запрошенного объекта и оценивает размер объекта для запрошенного состояния сжатия. Сжатие можно оценить для всех таблиц или только для части. К ним относятся кучи, кластеризованные индексы, некластеризованные индексы, индексы columnstore, индексированные представления и секции таблиц и индексов. Объекты можно сжимать с помощью сжатия строк, страниц, columnstore или columnstore. Если таблица, индекс или секция уже сжимаются, можно использовать эту процедуру для оценки размера таблицы, индекса или секции, если она повторно сжимается или хранится без сжатия.
Хранимая sys.sp_estimate_data_compression_savings процедура системы доступна в База данных SQL Azure и Управляемый экземпляр SQL Azure.
Начиная с 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 и может быть одним из следующих значений:
- Идентификатор индекса
NULL0Значение object_id, если object_id куча
Чтобы вернуть сведения для всех индексов для базовой таблицы или представления, укажите NULL. При указании NULLнеобходимо также указать NULL для @partition_number.
[ @partition_number = ] partition_number
Номер секции в объекте. @partition_number является int и может быть одним из следующих значений:
- номер секции индекса или кучи
NULL1для непартиментированного индекса или кучи
Чтобы указать секцию, можно также указать функцию $PARTITION . Чтобы вернуть сведения для всех секций объекта- владельцев, укажите NULL.
[ @data_compression = ] N'data_compression'
Указывает тип вычисляемого сжатия. @data_compression — nvarchar(60) и может быть одним из следующих значений:
NONEROWPAGECOLUMNSTORECOLUMNSTORE_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(по умолчанию)01
@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 (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 для оценки экономии, которая может возникать при включении таблицы или секции для строки, страницы, 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 2017 (14.x) и более ранних версиях эта процедура не применяется к индексам columnstore, поэтому не принимает параметры COLUMNSTORE сжатия данных и COLUMNSTORE_ARCHIVE. В SQL Server 2019 (15.x) и более поздних версиях, а также в База данных SQL Azure и Управляемый экземпляр SQL Azure индексы columnstore можно использовать как в качестве исходного объекта для оценки, так и в качестве запрошенного типа сжатия.
Если метаданные TempDB оптимизированы для памяти, создание индексов columnstore во временных таблицах не поддерживается. Из-за этого ограничения sp_estimate_data_compression_savings параметры сжатия данных не поддерживаются при COLUMNSTORE_ARCHIVE COLUMNSTORE включении метаданных TempDB, оптимизированных для памяти.
Рекомендации по индексам 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 (некластеризованный).
Примеры
Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.
А. Оценка экономии с помощью сжатия ROW
В следующем примере оценивается размер Production.WorkOrderRouting таблицы, если она сжимается с помощью ROW сжатия.
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 включено.
EXEC sys.sp_estimate_data_compression_savings
'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO