Сжатие данных в SQL Server (COMPRESSION)

Перевод статьи: Table and Index Compression

Сжатие данных доступно только в SQL Server Enterprise Edition, но начиная с SQL Server 2016 SP1, его добавили во все редакции. Нет так просто ответить на вопрос «будет ли польза от использования сжатия данных в конкретном случае или нет». Предлагаю рассмотреть это более детально.

Плюсы

Сжатие данных позволяет хранить больше строк на одной странице, что в конечном счёте приводит к экономии места. Кроме экономии дискового пространства (не только для самой базы данных, но так же и для backup), мы получает экономию памяти, так как даже в памяти данные хранятся в сжатом виде.

Наиболее полезным будет использовать сжатие данных на так называемых «холодных данных», которые редко используются и, обычно, не хранятся в памяти, а читаются с диска.

Минусы

Сжаты могут быть только данные, которые хранятся в строках (in-row). Это означает, что длинные строки (более 8 Кб) и LOB данные (большие) не могут быть сжаты, так как они хранятся не в строках, а помещаются в специальное хранилище, а в строке остаётся ссылка на них.

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

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

Принцип работы простыми словами

Сжатие на уровне строк: переводит фиксированный тип данных в переменный. Например, int, обычно занимает фиксированно 4 байта, но если вы храните 123, то это значение может поместиться в 1 байт + метаданные на хранение переменной длины.

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

Применить сжатие или нет — это большой вопрос

• Рассмотрите большие таблицы. Вам не следует рассматривать сотни маленьких таблиц, сжатие которых не принесёт пользы.
• Убедитесь, что объекты не фрагментированы перед применение компрессии (используйте sys.dm_db_index_physical_stats). Фрагментация может привести с сильным погрешностям сжатия и вы не получите достаточного выигрыша
• Проверьте как много данных выбранного объекта находится в памяти (используйте sys.dm_os_buffer_descriptors). Если большая часть объекта находится в памяти, то вы не получите выигрыша от применения сжатия, так как они могут быть горячими. Но если такие данные редко используются, то вы всё равно можете получить преимущество от сжатия, сжатые данные будут заниматься меньше объёма памяти.
• Посмотреть как часто используются объекты памяти можно с помощью sys.dm_db_index_operational_stats. Чем чаще объекты используются, тем больше CPU ресурсов будет затрачено.
• Проверьте возможный процент сжатия с помощью sys.sp_estimate_data_compression_savings. Если выигрыш незначительный, не применяйте сжатие, в противном случае вы потратите ваши CPU ресурсы в пустую. Выигрыш от сжатия на уровне строк должен быть хотя 15%, для сжатия на уровне страниц не менее 30%.
• Рассмотрите использование колоночных индексов. Сжатие уровня страниц, обычно, даёт уровень сжатия 50%, но колоночные индексы сжимают значительно сильнее (сжатие доходит до 10х). Колоночные индексы существенно отличаются от обычных, поэтому не применяйте их без изучение принципа их работы.

Вывод

Начиная с SQL Server 2016 сжатие доступно во всех редакциях, что позволяет использовать его в любых приложениях. Кроме сжатия, в SQL Server 2016, стали доступны и другие функции Enterprise Edition — это замечательный подарок от Microsoft.