Использование сжатия данных для рабочей области Reviewer в SQL Server

Сжатие строк и страниц для таблиц и индексов позволяет вам сохранить место на диске для хранения данных за счет уменьшения размера базы данных. Сжатие данных имеет недостаток - увеличение загрузки процессора, поскольку при осуществлении доступа данные должны быть сжаты и распакованы. Вы не можете использовать сжатие данных для системных таблиц и только версии SQL Server 2012 Enterprise и Developer поддерживают сжатие данных.

Вы можете настроить сжатие данных на следующем:

• Кластеризованные таблицы
• Накапливающие таблицы (накапливающими являются таблицы без кластеризованного индекса)
• Некластеризованные индексы
• Индексированные представления
• Индивидуальные разделы разделённой таблицы или индекса

Существуют три формы сжатия данных, которые вы можете использовать в SQL Server 2012: сжатие на уровне строк (row-level), сжатие в юникоде (unicode) и сжатие на уровне страниц (page-level).

Более подробно см. в Накапливающие (Heaps) таблицы (Таблицы без кластеризованного индекса) в библиотеке MSDN.

Сжатие на уровне строк (row-level)

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

Сжатие на уровне строк использует для экономии пространства следующие стратегии:

• Хранение типов числовых данных фиксированной длины и типов данных CHAR таким образом, как будто бы они являются типами данных переменной длины
• Не сохранение значений NULL или 0
• Сокращение метаданных, требуемых для хранения данных

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

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

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

USE [REVDB]
ALTER TABLE [rev].[TableA] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)

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

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

Более подробно о сжатии данных на уровне строк см. в Выполнение сжатия строк (Row Compression Implementation) в библиотеке MSDN.

Сжатие в юникоде (unicode)

Сжатие в юникоде (unicode) активирует средства базы данных для сжатия значений в юникоде (unicode), сохраняемых в сжатых объектах страниц или строк. Вы можете использовать сжатие в юникоде (unicode) для типов данных фиксированной длины nchar(n) и nvarchar(n). Сжатие в юникоде (unicode) в случае необходимости используется автоматически при включении сжатия строк и страниц.

Более подробно о сжатии в юникоде (unicode) см. в Выполнение сжатия строк в юникоде (Unicode Compression Implementation) в библиотеке MSDN.

Сжатие на уровне страниц (page-level)

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

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

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

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)

Например, чтобы перестроить все разделы таблицы rev.TableA базы данных REVDB с использованием сжатия страниц, примените следующий запрос:

USE [REVDB]
ALTER TABLE [rev].[TableA] REBUILD PARTITION = ALL
WITH(DATA_COMPRESSION = PAGE)

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

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

Более подробно о сжатии данных на уровне страниц см. в Выполнение сжатия страниц (Row Compression Implementation) в библиотеке MSDN.

Если таблицы или индексы разбиваются, вы можете настроить сжатие для каждого раздела. Если вы разбиваете раздел с помощью оператора ALTER PARTITION, новые разделы наследуют атрибут сжатия данных исходного раздела. Если вы объединяете два раздела, результирующий раздел имеет атрибут сжатия целевого раздела. Хотя сжатие позволяет сохранить больше строк на странице, оно не изменяет максимальный размер строки таблицы или индекса. Вы не можете включить таблицу для сжатия, если максимальный размер строки и её размер в сжатом виде превышают 8060 байт. Настройка сжатия по умолчанию для индексов – NONE и вы должны указать свойство сжатия для индексов при их создании. Некластеризованные индексы не наследуют свойство сжатия таблицы, но кластерные индексы, созданные на базе накапливающих (heap) наследуют статус сжатия накапливающего индекса. Сжатие данных применяется только для источника, поэтому когда вы экспортируете данные из сжатого источника, SQL Server будет выводить данные в несжатом формате строки. Импорт данных без сжатия в целевую таблицу, включенный на сжатие, будет сжимать данные.

Более подробно о сжатии данных см. в Сжатие данных (Data Compression) в библиотеке MSDN.

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

Шаги:

1. Запустите SQL Server Management Studio.
2. Щелкните правой кнопкой мыши индекс который вы хотите сжать, выберите Хранилище (Storage) и щелкните Управление сжатием (Manage Compression).

   Откроется диалоговое окно Добро пожаловать в Мастер сжатия данных.

3. Щелкните Далее (Next).

   Откроется диалоговое окно Выбрать тип сжатия (Select Compression Type).

4. Выберите тип сжатия.

   Вы можете использовать один и тот же тип сжатия для всех разделов или выбрать один – для строки, для страницы или никакого (none) для каждого раздела.

5. Щёлкните Вычислить (Calculate), чтобы определить разницу между текущим используемым пространством и пространством, которое должно быть занято после сжатия.

   Откроется диалоговое окно Выбрать выходные параметры (Select An Output Option).

6. Выберите, нужно ли создавать скрипт, выполнить ли операцию немедленно или выполнить операцию в соответствии с графиком.
7. Click Next.
8. Click Finish.

Более подробно о Мастере сжатия данных см. в Справка по Мастеру сжатия данных (Data Compression Wizard Help) в библиотеке MSDN.

Оценка сжатия

Лучшим способом определить преимущества сжатия для объекта является использование процедуры сохранения sp_estimate_data_compression_savings. Эффективность сжатия зависит от такого фактора, как уникальность данных. Процедура сохранения sp_estimate_data_compression_savings доступна для SQL Server 2012 только в версии Enterprise.

Синтаксис процедуры сохранения выглядит так:

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'

Например, для настройки оценки эффективности сжатия с использованием сжатия строк для таблицы rev.TableA базы данных REVDB, выполните следующую инструкцию Transact-SQL:

USE REVDB;
GO
EXEC sp_estimate_data_compression_savings 'rev', 'TableA', NULL, NULL,
'ROW';
GO

Для настройки оценки эффективности сжатия с использованием сжатия страниц для той же таблицы, выполните следующую инструкцию Transact-SQL:

USE REVDB;
GO
EXEC sp_estimate_data_compression_savings 'rev', 'TableA', NULL, NULL,
'PAGE';
GO

Для получения дополнительной информации о том, как оценить экономию сжатия, см. sp_estimate_data_compression_savings (Transact-SQL) в библиотеке MSDN.

Вы можете использовать приведённый ниже скрипт SQL для пользовательских таблиц и индексов rev, и для генерации инструкций SQL для настройки сжатия страниц для таблиц и индексов.

--Verify REV Schema Storage
USE [revdb]
GO
--List all tables
SELECT USER_NAME(o.uid) [owner], o.name,o.id,o.type,o.status
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'rev'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
--List all indexes
SELECT USER_NAME(o.uid) [owner], OBJECT_NAME(i.id) [table], i.name [index],o.type [type]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'rev'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO
--Table page compression
--Example:
ALTER TABLE REVDB.REVTABLELINE 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
--Generate script to set table page compression:
SELECT 'ALTER TABLE ' + USER_NAME(o.uid) + '.' + o.name + ' REBUILD WITH (DATA_COMPRESSION = PAGE);' [TXTSQL]
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'rev'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
--Index page compression
--Example:
ALTER INDEX R125_pk 
ON REVDB.REVTABLELINE
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
--Generate script to set index page compression:
SELECT 'ALTER INDEX ' + i.name + ' ON ' + USER_NAME(o.uid) + '.' + OBJECT_NAME(i.id) +
       ' REBUILD WITH ( DATA_COMPRESSION = PAGE );' [TXTSQL]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'REV'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

Сжатие и TDE

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

Более подробно см. в SQL Server 2012 – Прозрачное шифрование данных (TDE) в библиотеке MSDN.

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

Более подробно см. в SQL Server 2012 – Сжатие данных в библиотеке MSDN.