Статья
09/28/2010

Создание сжатых таблиц и индексов

SQL Server 2008 поддерживает сжатие строк и страниц для таблиц и индексов. Сжатие данных может быть настроено для следующих объектов базы данных.

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

Параметры сжатия таблицы не применяются автоматически к ее некластеризованным индексам. Каждый индекс должен быть установлен индивидуально. Для системных таблиц сжатие недоступно. Таблицы и индексы могут быть сжаты, если для их создания были использованы инструкции CREATE TABLE и CREATE INDEX. Чтобы изменить состояние сжатия таблицы, индекса или секции, используются инструкции ALTER TABLE или ALTER INDEX.

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

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

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

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

Сжатие доступно только в выпусках SQL Server 2008 Enterprise и Developer.
С помощью сжатия можно хранить больше строк в странице, максимальный размер строки таблицы или индекса при этом не изменяется.
Для таблицы нельзя включить сжатие, если сумма максимального размера строки и служебных данных сжатия превышает максимальный размер строки в 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.
При сжатии индексов страницы конечного уровня можно сжать как сжатием строк, так и сжатием страниц. Для страниц, расположенных не на конечном уровне, нельзя использовать сжатие страниц.
Ввиду их размера, типы данных больших значений иногда хранятся отдельно от нормальных данных строк на страницах для особых целей. Сжатие данных недоступно для данных, хранящихся отдельно.

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

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

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

Реализация сжатия

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

Ожидаемая экономия при сжатии

Определить, каким образом сказалось изменение состояние сжатия на таблице или индексе, можно с помощью хранимой процедуры sp_estimate_data_compression_savings. Хранимая процедура sp_estimate_data_compression_savings доступна только в тех выпусках SQL Server, которые поддерживают сжатие данных.

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

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

Разбиение диапазона

При разбиении секций с помощью инструкции 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.

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

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

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

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

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

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

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

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

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

Если массовый импорт данных производится в кучу с включенным сжатием страниц, то при вставке данных операция массового импорта попытается применить к ним сжатие страниц.
Сжатие не затрагивает резервное копирование и восстановление.
Сжатие не затрагивает доставку журналов.
Сжатие данных несовместимо с разреженными столбцами. Поэтому таблица, содержащая сжатые столбцы, не может быть сжата, а разреженный столбец не может быть добавлен в сжатую таблицу.
Включение сжатия может вызвать изменение планов запросов, поскольку данные будут занимать при хранении другое число страниц с другим числом строк на страницу.
Сжатие данных поддерживается в среде SQL Server Management Studio через Мастер сжатия данных.

Запуск мастера сжатия данных

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

Наблюдение за сжатием

Наблюдение за сжатием всего экземпляра SQL Server производится с помощью счетчиков Page compression attempts/sec и Pages compressed/sec в SQL Server, Access Methods Object.

Чтобы получить статистику сжатия страниц по отдельным столбцам, запросите функцию динамического управления sys.dm_db_index_operational_stats.

Примеры

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

CREATE DATABASE TestDatabase
ON  PRIMARY
( NAME = TestDatabase,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO

Переключение на новую базу данных:

USE TestDatabase
GO

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

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

CREATE TABLE T1 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO

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

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

CREATE TABLE T2 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO

В. Установка параметра DATA_COMPRESSION для секционированной таблицы

В следующем примере используется таблица TestDatabase, созданная с помощью кода, предоставленного ранее в этом разделе. Этот пример создает функцию секционирования и схему секционирования, а затем создает секционированную таблицу и указывает параметры сжатия для секций в этой таблице. В этом примере для секции 1 устанавливается сжатие ROW, а для остальных — сжатие PAGE.

Создание функции секционирования:

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

Создание схемы секционирования:

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO

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

CREATE TABLE PartitionTable1 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO

Г. Установка параметра DATA_COMPRESSION для секционированной таблицы

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

CREATE TABLE PartitionTable2 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
  DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO

Д. Изменение таблицы для изменения режима сжатия

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

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

Е. Изменение сжатия для одной секции в секционированной таблице

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

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

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

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

Ж. Изменение режима сжатия для нескольких секций в секционированной таблице

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

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

З. Изменение режима сжатия индекса

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

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2) 
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

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

ALTER INDEX IX_INDEX_1 
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO

И. Изменение режима сжатия одной секций в секционированном индексе

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

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Чтобы создать индекс, использующий различные настройки сжатия для разных секций, используйте синтаксис ON PARTITIONS. В следующем примере создается индекс для секционированной таблицы, в которой применяется сжатие строк для секции 1 индекса и сжатие страниц для секций индекса со 2-ой по 4-ю.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
    DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO

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

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

К. Изменение режима сжатия для нескольких секций в секционированном индексе

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO