Как осуществляется сжатие базы данных в MS SQL?

Сжатие данных в MS SQL Server может быть выполнено с помощью функции, называемой **данных компрессия (data compression)**. Она позволяет уменьшить размер данных с помощью различных алгоритмов сжатия, которые применяются к строкам и страницам данных. Это может помочь освободить место на диске и улучшить производительность запросов.

### Основные способы сжатия

1. **Row Compression (Сжатие строк)**:
   - Уменьшает объем хранения данных за счет оптимизации хранения фиксированных и переменных типов данных.
   - Сокращает размер строк путем удаления лишних пробелов и ненужных байтов, которые не используются.

2. **Page Compression (Сжатие страниц)**:
   - Затрагивает всю страницу данных (обычно 8 КБ) и использует более сложные алгоритмы, такие как сжатие последовательностей и уникальных значений, что может значительно уменьшить размер страниц.
   - Включает в себя все преимущества `Row Compression` и добавляет дополнительный уровень сжатия.

3. **Columnstore Compression**:
   - Используется для столбцовых хранилищ данных (Columnstore Indexes) и позволяет достигать значительного снижения объема данных за счет сжатия на уровне столбцов.

### Как применять сжатие

- **Сжатие таблиц**: Вы можете применить компрессию к каждой конкретной таблице с помощью команды T-SQL. Например:
  ```sql
  ALTER TABLE [TableName]
  REBUILD WITH (DATA_COMPRESSION = PAGE);
  ```

- **Сжатие индексов**: Компрессию также можно применять к индексам отдельно:
  ```sql
  ALTER INDEX [IndexName] ON [TableName]
  REBUILD WITH (DATA_COMPRESSION = ROW);
  ```

- **Сжатие всей базы данных сразу**: К сожалению, MS SQL Server не поддерживает сжатие всех таблиц и индексов базы данных одновременно с одной командой. Вам нужно будет перебирать каждую таблицу и индекс.

- **Скрипт для массового сжатия**: Вы можете написать скрипт, который переберет все таблицы и индексы и применит к ним компрессию. Примерно так:
  ```sql
  DECLARE @TableName NVARCHAR(256);
  DECLARE TableCursor CURSOR FOR
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

  OPEN TableCursor;
  FETCH NEXT FROM TableCursor INTO @TableName;

  WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(MAX);
      SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION = PAGE);';
      EXEC sp_executesql @SQL;
      FETCH NEXT FROM TableCursor INTO @TableName;
  END

  CLOSE TableCursor;
  DEALLOCATE TableCursor;
  ```

### Рекомендации

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

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

<blockquote>Но при нынешних ценах на дисковое пространство попытка добыть пару лишних мегабайт сжатием - крайне неразумное занятие. Дополнительные процессорные ресурсы на него стоят куда как дороже. </blockquote> люто плюсую. <br/> <br/> Но если прямо очень нужно, то советую вместо сжатия средствами БД, попробовать сжать средствами файловой системы (например btrfs - zstd, 16 уровней сжатия), можно поиграть с разным размером кластера, что может сильно повлиять на результат... особенно если в базе данных соседние поля с одинаковыми данными, а движок их упаковывает независимо. <br/> <br/> Скорость записи в базу данных на сжатом диске сильно упадет, особенно если делать большой размер кластера (так как это увеличивает степень сжатия), но вот скорость чтения, особенно с hdd, даже может подрасти (особенно при хорошей степени сжатия), но должно много всего совпасть. <br/> НАСТОЯТЕЛЬНО рекомендую файлы индексов не сжимать, за исключением случаев, когда они целиком и полностью влезают в оперативную память и запись в базу данных не производится. <br/> <br/> ОБЯЗАТЕЛЬНОЕ тестирование всей конструкции на реальных данных перед запуском в продакшен, иначе можно получить проблему, и конечно же бакапы, без них ничего делать даже не начинай. <br/> <br/> p.s. наилучшее сжатие можно получить, если грамотно его реализовать на стороне самого приложения, ведь его разработчик знает, где какие данные как лежат, как их можно эффективно перераспределить и главное, есть библиотеки типа того же zstd, когда можно держать несколько словарей для сжимаемых данных, специально собранных под свои наборы данных,.. отличный пример сжатие xml/json файлов, где теги/атрибуты могут занимать до 90% пространства,.. и при маленьком размере сжимаемого куска, словарь на них будет в каждом куске свой.. а вот общий словарь для всего пакета файлов позволит на порядок сократить их объем. <br/> <br/> p.p.s. само собой, замена xml/json на правильно созданный protobuf исключит эту проблему в зачатке

Я просоединяюсь к совету выше. На тему того что самое эффективное уплотение информции <br/> можно сделать на уровне разработки приложения. <br/> <br/> Я-бы предложил не сжимать всю базу а <b>проанализировать, какие таблицы и какие поля занимают<br/>
80% </b> всего пространства. <i>(Процены я взял с головы по принципу Паретто. Вы можете взять любой<br/>
процент. Можно 90 или 70 не суть важно. Важно чтобы не закапыватья м мелочах.)</i> <br/> <br/> Из опыта других БД. (Не MS-SQL). Часто бывало что потребителем места были BLOB-поля где <br/> лежали какие-то несуразные и никому не нужные документы. Аттачменты. Картинки. Копии <br/> email из переписок с пользователем и многое другое. Были ситуации когда причиной роста <br/> БД были старые архивные записи в таблице которые почему-то были забыты. Они должны <br/> были удаляться но из за бага не удалялись. <br/> <br/> Хорошая практика в данном случае - <b>убрать из БД все длинные текстовые документы<br/>
или положить их в gzip</b> на уровне самого приложения например. Обычно такие поля <br/> не участвуют напрямую в операциях OLTP и их сжатие ни на что особо не влияет. <br/> <br/> Почти все современные БД имеют очень хорошую плотность информации на мегабайт <br/> и если админ что-то там шринковал или уплотнял - то это носит временную меру. Через <br/> некоторое время БД возвращается к той плотности как и была раньше вследствие <br/> updates например. <br/> <br/> По поводу ROW level/block level сжатия. Я не специалист в MS_SQL, но обычно это надо <br/> предварительно тестировать под нагрузкой на PROD для всех DBMS в общем то. <br/> Велика вероятность падения производительности а это, сами понимаете слишком <br/> большая цена за экономию. И диски в наше время значительно дешевле скажем чем 10 лет назад.