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

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

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

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

Сжатие в операционной системе

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

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

Сжатие таблиц

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

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

Первой попыткой уменьшить размер командой SQL Server было добавление типа данных vardecimal в версию SQL Server 2005 SP2. В типичной транзакционной системе, например крупной системе SAP, нередко используются таблицы, содержащие большое число десятичных значений. Десятичное значение в SQL Server с выбираемой по умолчанию точностью 18 знаков занимает в памяти 9 байт, но очень часто эти столбцы имеют значение NULL или содержат простые значения, такие как 0 или 1. С появлением типа vardecimal в версии Enterprise Edition SQL Server хранит содержимое строк иным способом, и десятичные значения становятся компактнее благодаря формату с переменной длиной. Тип данных vardecimal был внутренним. Невозможно определить столбец или переменную как тип данных vardecimal.

Сжатие строк

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

В версии SQL Server 2008 появилось два типа сжатия таблиц: ROW и PAGE. К сожалению, указанные возможности имеются только в редакции Enterprise Edition, но клиент, о котором идет речь в статье, располагает Enterprise Edition. Хотелось бы, чтобы сжатие таблиц было доступно и в других выпусках.

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

  • Если база данных совершает много операций ввода-вывода, то любые меры, способствующие снижению интенсивности ввода-вывода, обычно благоприятно отражаются на производительности. Например, если удается уменьшить размер строк на 30%, то на 30% снижается объем данных, пересылаемых в процессе ввода-вывода. Очевидно, ситуация может измениться в случае преобладающей нагрузки на процессор, но у большинства моих клиентов системы ориентированы на ввод-вывод.
  • Для таблиц на диске, поскольку в буферах SQL Server хранятся копии страниц базы данных, больше строк данных теперь входит в те же буферы. Это означает, что больше строк размещается в памяти сервера, без сопутствующего увеличения доступной памяти.
  • Обработка каждой страницы сопряжена с увеличением нагрузки на процессор, но возможности большинства серверов ограничиваются операциями ввода-вывода при обилии ресурсов процессора. Мне приходилось наблюдать ситуации, в которых нагрузка на процессор снижалась при сжатии таблиц. Я могу объяснить это только тем обстоятельством, что, несмотря на увеличение затрат на обработку одной страницы, число обрабатываемых страниц уменьшается.

Как реализовано сжатие типа ROW?

  • Первое изменение заключается в уменьшении количества метаданных, связанных с каждой строкой (в частности, сведений о столбцах и их длине).
  • Следующее изменение распознает, что многие столбцы пусты или содержат нулевое значение. Хранение таких столбцов оптимизировано до такой степени, что нулевые значения и NULL вообще не занимают места.
  • Еще одно изменение относится к типу vardecimal. Vardecimal устранен, и для числовых типов используются форматы хранения переменной длины (smallint, int, bigint, decimal, numeric, smallmoney, money, float, real, timestamp/rowversion). Эффективность хранения улучшается благодаря сокращению метаданных и уменьшению размеров некоторых значений, относящихся к дате (datetime, datetime2, datetimeoffset).
  • Строковые значения в целом остаются прежними, но конечные символы заполнения удалены из значений типов char и nchar.
  • Конечные нули удалены из двоичных значений.

Как выяснилось, сжатие типа ROW удивительно эффективно. В большинстве таблиц транзакционной системы сокращение достигало приблизительно 30%.

Применение сжатия типа ROW

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

Важно понять, когда происходит сжатие. Например, рассмотрим таблицу Sales.OrderDetails из тестовой базы данных AdventureWorks (см. экран 1).

 

Таблица Sales.OrderDetails из тестовой базы данных AdventureWorks
Экран 1. Таблица
Sales.OrderDetails из тестовой базы данных AdventureWorks

Основываясь на типах данных, можно сказать, что эта таблица — подходящий кандидат для уменьшения размера с использованием сжатия типа ROW. Вместо внесения изменений в таблицу AdventureWorks создадим другую таблицу в tempdb и будем работать с ней (см. листинг).

Эта процедура не проверяет всю таблицу, а переносит часть строк таблицы в tempdb, а затем сжимает их с помощью запрошенной схемы и выдает отчет о результатах. Результаты экстраполируются на всю таблицу на основе выборки, и «реальные» результаты могут отличаться. Но, как мы обнаружили, это полезный первый шаг (см. экран 2).

 

Результаты сжатия
Экран 2. Результаты сжатия

В данном случае тестовый размер был, в сущности, всей базой данных. Сообщается о сокращении размера с 6,9 Мбайт до 3,7 Мбайт, или на 46%. Предполагается, что, если применяется сжатие ROW, размер таблицы составит 54% первоначального размера. Проверим текущий размер таблицы:

EXEC sp_spaceused N’dbo.SalesOrderDetail’;
GO

Результат показан на экране 3.

 

Текущий размер таблицы
Экран 3. Текущий размер таблицы

А теперь внесем изменения:

ALTER TABLE dbo.SalesOrderDetail
    REBUILD WITH
    (DATA_COMPRESSION = ROW);
GO

EXEC sp_spaceused
N'dbo.SalesOrderDetail';
GO

Результат — на экране 4.

 

Размер таблицы после внесения изменений
Экран 4. Размер таблицы после внесения изменений

Размер равен 3,9 Мбайт; теперь размер таблицы составляет 56% первоначального размера 6,9 Мбайт.

Результаты первого этапа диеты для баз данных

Для нашего клиента, чью базу данных нужно было «посадить на диету», применив сжатие типа ROW ко всем таблицам, было достигнуто уменьшение размера всей базы данных с 3,8 Тбайт до 2,6 Тбайт. Система клиента полностью ориентирована на ввод-вывод, поэтому неудивительно, что производительность заметно повысилась.

Выгодно ли применять сжатие типа ROW ко всем таблицам? Мне пока не приходилось встречать таблицу, для которой сжатие типа ROW приносило что-либо, кроме пользы. Для пользователей редакции Enterprise Edition оптимальным будет выбираемый по умолчанию режим для таблиц на диске.

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

Листинг. Создание и работа с таблицей tempdb
USE tempdb;
GO

CREATE TABLE dbo.SalesOrderDetail
(
        SalesOrderID int NOT NULL,
        SalesOrderDetailID int NOT NULL,
        CarrierTrackingNumber nvarchar(25) NULL,
        OrderQty smallint NOT NULL,
        ProductID int NOT NULL,
        SpecialOfferID int NOT NULL,
        UnitPrice money NOT NULL,
        UnitPriceDiscount money NOT NULL,
    CONSTRAINT PK_dbo_SalesOrderDetail
        PRIMARY KEY (SalesOrderID, SalesOrderDetailID)
);
GO

INSERT dbo.SalesOrderDetail
    (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
     OrderQty, ProductID, SpecialOfferID, UnitPrice,
     UnitPriceDiscount)
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
       OrderQty, ProductID, SpecialOfferID, UnitPrice,
       UnitPriceDiscount
FROM AdventureWorks.Sales.SalesOrderDetail;
GO

SQL Server предоставляет процедуру для количественной оценки предстоящего сокращения. Попробуем это:
EXEC sp_estimate_data_compression_savings
    'dbo', 'SalesOrderDetail', NULL, NULL, 'ROW';
GO