Сжатие данных

  • Статья

SQL Server 2014 поддерживает сжатие строк и страниц для таблиц и индексов rowstore, а также архивное сжатие columnstore и columnstore для таблиц и индексов columnstore.

Для таблиц и индексов rowstore используйте функцию сжатия данных, чтобы уменьшить размер базы данных. Помимо экономии места, сжатие данных позволяет повысить производительность при рабочих нагрузках с интенсивным вводом-выводом, поскольку данные хранятся в меньшем количестве страниц и в запросах требуется считывать меньше страниц с диска. Однако для сжатия и распаковки данных при обмене данными с приложениями требуются дополнительные ресурсы ЦП на сервере баз данных. Можно настроить сжатие строк и страниц для следующих объектов баз данных:

  • Для полной таблицы, хранящейся в виде кучи.

  • Для полной таблицы, хранящейся в виде кластеризованного индекса.

  • Для полного некластеризованного индекса.

  • Для полного некластеризованного представления.

  • Для секционированных таблиц и индексов параметры сжатия можно задавать для каждой секции, и разные секции объекта могут иметь различные параметры.

Для таблиц и индексов columnstore все таблицы и индексы columnstore всегда используют сжатие columnstore, и этот режим не настраивается пользователем. Используйте архивное сжатие columnstore, чтобы еще больше сократить размер данных в случаях, когда можно предоставить дополнительное время и ресурсы ЦП для хранения и извлечения данных. Можно настроить архивное сжатие columnstore для следующих объектов базы данных:

  • Вся таблица columnstore или весь кластеризованный индекс columnstore. Таблица columnstore хранится в виде кластеризованного индекса columnstore, поэтому оба подхода приносят одинаковые результаты.

  • Весь некластеризованный индекс columnstore.

  • Для секционированных таблиц columnstore и индексов columnstore можно задать параметр архивного сжатия для каждой секции, и разным секциям не обязательно назначать одинаковый параметр архивного сжатия.

Замечания по использованию сжатия строк и страниц

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

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

  • Сжатие поддерживается не во всех выпусках SQL Server. Дополнительные сведения см. в разделе Функции, поддерживаемые выпусками SQL Server 2014.

  • Для системных таблиц сжатие недоступно.

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

  • Для таблицы нельзя включить сжатие, если сумма максимального размера строки и служебных данных сжатия превышает максимальный размер строки в 8060 байт. Например, таблица со столбцами c1char(8000) и c2char(53) не может быть сжата из-за дополнительных издержек сжатия. При использовании формата хранения vardecimal выполняется проверка размера строки (когда формат включен). При использовании сжатия строк и страниц проверка размера строки выполняется при первичном сжатии объекта, а также при всех последующих вставках и изменениях строк. При использовании сжатия обеспечивается выполнение следующих двух правил.

    • Обновление для типа с фиксированной длиной должно всегда завершаться успешно.

    • Отключение сжатия данных должно всегда выполняться успешно. Даже если сжатая строка умещается на странице (что означает, что она имеет размер меньше 8060 байт), SQL Server не допустит выполнения обновлений, которые не поместятся в строке без сжатия.

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

  • При указании списка секций или секции, выходящей за пределы диапазона, будет сформирована ошибка.

  • Некластеризованные индексы не наследуют свойство сжатия таблицы. Чтобы сжать индексы, необходимо явно задать для них свойство сжатия. По умолчанию при создании индексов для них устанавливается режим сжатия NONE.

  • При создании кластеризованного индекса в куче кластеризованный индекс наследует состояние сжатия кучи, если не указано другое состояние сжатия.

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

    • Массовый импорт данных осуществляется со включенными массовыми оптимизациями.

    • Вставка данных с помощью синтаксиса INSERT INTO ... Синтаксис WITH (TABLOCK) и таблица не содержат некластеризованный индекс.

    • Перестройка таблицы с помощью инструкции ALTER TABLE ... REBUILD с параметром сжатия PAGE.

  • В новых страницах, размещенных в куче в процессе выполнения операций DML, сжатие страниц не будет использоваться до тех пор, пока куча не будет перестроена. Перестройте кучу. Для этого удалите и повторно примените сжатие либо создайте и удалите кластеризованный индекс.

  • Чтобы изменить параметры сжатия кучи, необходимо перестроить все некластеризованные индексы в таблице. Это обеспечивает наличие в них указателей на новые расположения в куче.

  • Включить или отключить сжатие типа ROW или PAGE можно в оперативном или режиме вне сети. Включение сжатия для кучи является однопоточным для операции в сети.

  • Чтобы включить или отключить сжатие строки или страницы, необходимо столько же места на диске, как и для создания или перестройки индекса. Для секционированных данных объем пространства, необходимый для включения или отключения сжатия, можно сократить, выполняя включение или отключение сжатия последовательно для каждой секции.

  • Чтобы определить состояние сжатия секций в секционированной таблице, выполните запрос столбца data_compression из представления каталога sys.partitions.

  • При сжатии индексов страницы конечного уровня можно сжать как сжатием строк, так и сжатием страниц. Для страниц, расположенных не на конечном уровне, нельзя использовать сжатие страниц.

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

  • Таблицы, которые реализовали формат хранения vardecimal в SQL Server 2005, будут сохранять этот параметр при обновлении. К таблице, в которой присутствует формат хранения vardecimal, можно применить сжатие строк. Однако, поскольку сжатие строк является надмножеством формата хранения vardecimal, причин для сохранения данного формата нет. Для десятичных значений не происходит никакого дополнительного сжатия при сочетании формата хранения vardecimal и сжатия строк. Сжатие страниц можно применить к таблице, в которой присутствует формат хранения vardecimal, однако для столбцов данного формата дополнительного сжатия, скорее всего, не произойдет.

    Примечание

    SQL Server 2014 поддерживает формат хранения vardecimal. Однако, поскольку сжатие на уровне строк достигает этих же целей, формат хранения vardecimal является устаревшим. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

Использование Columnstore и архивного сжатия Columnstore
Область применения: SQL Server (с SQL Server 2014 по текущую версию).

Основы

Таблицы и индексы Columnstore всегда сохраняются со сжатием columnstore. Можно еще более уменьшить размер данных columnstore, настроив дополнительное сжатие, именуемое архивным сжатием. Чтобы выполнить архивное сжатие, SQL Server применяет к данным алгоритм сжатия XPress корпорации Майкрософт. Добавьте или удалите архивное сжатие, используя следующие типы сжатия данных.

  • Используйте сжатие данных COLUMNSTORE_ARCHIVE для сжатия данных columnstore с помощью архивного сжатия.

  • Используйте сжатие данных COLUMNSTORE для распаковки архивного сжатия. Эти результирующие данные по-прежнему будут сжаты с использованием сжатия columnstore.

Чтобы добавить архивное сжатие, используйте инструкции ALTER TABLE (Transact-SQL) или ALTER INDEX (Transact-SQL) с параметром REBUILD и DATA COMPRESSION = COLUMNSTORE.

Примеры:

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE ON PARTITIONS (2,4)) ;

Чтобы удалить архивное сжатие и восстановить данные до сжатия columnstore, используйте инструкции ALTER TABLE (Transact-SQL) или ALTER INDEX (Transact-SQL) с параметром REBUILD и DATA COMPRESSION = COLUMNSTORE.

Примеры:

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE ON PARTITIONS (2,4) ) ;

В следующем примере устанавливается сжатие данных columnstore в некоторых секциях и архивное сжатие columnstore в других секциях.

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (  
    DATA_COMPRESSION =  COLUMNSTORE ON PARTITIONS (4,5),  
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1,2,3)  
) ;

Производительность

Сжатие индексов columnstore с использованием архивного сжатия приводит к увеличению времени обработки индекса по сравнению с индексами columnstore, к которым не применяется архивное сжатие. Используйте архивное сжатие только при наличии дополнительного времени и ресурсов ЦП для сжатия и получения данных.

При снижении производительности удается уменьшить объем хранилища, что полезно для данных, доступ к которым выполняется нечасто. Например, если имеется секция для данных за каждый месяц и основная часть активности пришлась на последние месяцы, то можно архивировать предыдущие месяцы, чтобы уменьшить требования к хранилищу.

Метаданные

Следующие системные представления содержат сведения о сжатии данных для кластеризованных индексов.

  • sys.indexes (Transact-SQL) — type столбцы и type_desc включают CLUSTERED COLUMNSTORE и NONCLUSTERED COLUMNSTORE.

  • sys.partitions (Transact-SQL) — data_compression столбцы и data_compression_desc включают COLUMNSTORE и COLUMNSTORE_ARCHIVE.

Процедура sp_estimate_data_compression_savings (Transact-SQL) не применяется к индексам columnstore.

Влияние сжатия на секционированные таблицы и индексы

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

  • При разбиении секций с помощью инструкции ALTER PARTITION обе секции наследуют атрибут сжатия данных исходной секции.

  • При слиянии двух секций результирующая секция унаследует атрибут сжатия данных секции назначения.

  • Для переключения секции свойство сжатия данных секции должно совпадать со свойством сжатия таблицы.

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

    • Следующий синтаксис перестраивает только упоминаемую секцию:

      ALTER TABLE <table_name>   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)

    • Следующий синтаксис перестраивает всю таблицу, используя существующий режим сжатия для всех неупоминаемых секций:

      ALTER TABLE <table_name>   
REBUILD PARTITION = ALL   
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),  
... )

    Для секционированных индексов действуют те же принципы, но используется инструкция ALTER INDEX.

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

    1. Удалить кластеризованный индекс.

    2. Изменить таблицу с помощью параметра REBUILD ... инструкции ALTER TABLE ..., в котором указывается режим сжатия.

    Удаление кластеризованного индекса в режиме вне сети происходит очень быстро, поскольку удаляются только верхние уровни кластеризованных индексов. При удалении кластеризованного индекса в режиме в сети SQL Server должен перестроить кучу два раза — один для первого шага, один для второго.

Влияние сжатия на репликацию

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

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

  • При репликации транзакций параметр схемы статьи определяет, какие из зависимых объектов или свойств должны быть добавлены в сценарий. Дополнительные сведения см. в разделе sp_addarticle.

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

  • Для репликации слиянием уровень совместимости публикаций переопределяет параметры схемы и определяет, какие из объектов схемы будут внесены в сценарий.

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

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

Намерение пользователя Выполнить репликацию схемы секционирования для таблицы или индекса Выполнить репликацию настроек сжатия Действия со сценариями
Выполнить репликацию схемы секционирования и включить сжатие на подписчике для этой секции. True True Создать скрипты для схемы секционирования и для настроек сжатия.
Выполнить репликацию схемы секционирования, но не сжимать данные на подписчике. True Неверно Создать скрипт для схемы секционирования, но не создавать его для настроек сжатия секции.
Не выполнять репликацию схемы секционирования и не сжимать данные на подписчике. Неверно False Не создавать скрипты для настроек секций или сжатия.
Выполнить сжатие таблицы на подписчике при условии, что на издателе были сжаты все секции, но не выполнять репликацию схемы секционирования. False True Проверить, что для всех секций включено сжатие.

Создать скрипт сжатия на уровне таблицы.

Влияние сжатия на другие компоненты SQL Server

Сжатие происходит в подсистеме хранилища, и данные предоставляются большинству других компонентов SQL Server в распакованном состоянии. Это ограничивает влияние сжатия на другие компоненты следующим.

  • Операции массового импорта и экспорта

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

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

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

  • Сжатие не затрагивает резервное копирование и восстановление.

  • Сжатие не затрагивает доставку журналов.

  • Сжатие данных несовместимо с разреженными столбцами. Поэтому таблицы, содержащие разреженные столбцы, нельзя сжать, а разреженные столбцы нельзя добавить в сжатую таблицу.

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

См. также:

Row Compression Implementation
Реализация сжатия страниц
Реализация сжатия Юникода
CREATE PARTITION SCHEME (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
Инструкция CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
Инструкция CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)