Почему не стоит сжимать файлы данных

Автор: Paul Randal, Why you should not shrink your data files

Одна из моих самых больших «болевых точек» — это сжатие файлов данных. Хотя, когда я работал в Microsoft, я отвечал за код сжатия, я не писал его (так что не вините меня! :-)), и это никогда не считалось достаточно серьёзной проблемой для клиентов, чтобы её исправлять. Но мне действительно не нравится процесс сжатия файлов данных.

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

В любом случае, я не говорю об использовании параметра TRUNCATEONLY — всё, что он делает, — это отсекает неиспользуемое пространство в конце файлов — это совершенно нормально. Я говорю о фактическом запуске алгоритма сжатия.

Примечание переводчика: статьи Пола и Кимберли были написаны в те времена, когда доминировали HDD. Сейчас это уже больше экзотика и основу составляют диски SSD. Такие диски значительно превосходят жёсткие диски по производительности и времени доступа, что снижает негативные последствия сжатия данных. Однако, если сжимать данные часто, это может заметно снизить число циклов перезаписи ячеек памяти, которое ограничено, и приблизить срок исчерпания ресурса диска. Это ещё один аргумент, почему не стоит этим злоупотреблять.

Сжатие файлов данных следует выполнять ещё реже, если вообще выполнять. Вот почему: сжатие файла данных может вызвать массовую фрагментацию индексов (типа «страницы не по порядку», а не типа «пустое пространство»), и это очень дорого (с точки зрения ввода-вывода, блокировок, генерации журнала транзакций). Позвольте мне продемонстрировать это простым скриптом, который вы можете запустить. Скрипт ниже создаст файл данных, создаст «заполняющую» таблицу размером 10 МБ в начале файла данных, создаст «рабочий» кластерный индекс размером 10 МБ, а затем проанализирует фрагментацию нового кластерного индекса.

USE [master];
GO
 
IF DATABASEPROPERTYEX (N'DBMaint2008', N'Version') IS NOT NULL
    DROP DATABASE [DBMaint2008];
GO
 
CREATE DATABASE DBMaint2008;
GO
USE [DBMaint2008];
GO
 
SET NOCOUNT ON;
GO
 
-- Создаём 10 МБ заполняющей таблицы в 'начале' файла данных
CREATE TABLE [FillerTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'filler');
GO
 
-- Заполняем заполняющую таблицу
INSERT INTO [FillerTable] DEFAULT VALUES;
GO 1280
 
-- Создаём рабочую таблицу, которая будет 'после' заполняющей таблицы в файле данных
CREATE TABLE [ProdTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO
 
INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1280
 
-- Проверяем фрагментацию рабочей таблицы
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO
    

avg_fragmentation_in_percent
-----------------------------
0.390625
    

Логическая фрагментация кластерного индекса перед сжатием составляет почти идеальные 0,4%.

Теперь я удалю «заполняющую» таблицу, запущу сжатие, чтобы вернуть пространство, и повторно проанализирую фрагментацию кластерного индекса:

-- Удаляем заполняющую таблицу, создавая 10 МБ свободного места в 'начале' файла данных
DROP TABLE [FillerTable];
GO
 
-- Сжимаем базу данных
DBCC SHRINKDATABASE ([DBMaint2008]);
GO
 
-- Снова проверяем фрагментацию индекса
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO
    

DbId  FileId  CurrentSize  MinimumSize  UsedPages  EstimatedPages
----- ------- ------------ ------------ ---------- ---------------
6     1       1456         152          1448       1440
6     2       63           63           56         56
 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
avg_fragmentation_in_percent
-----------------------------
99.296875
    

Ого! После сжатия логическая фрагментация (страницы не по порядку) составляет почти 100%. Операция сжатия полностью фрагментировала индекс. Повлияет ли это на производительность вашей рабочей нагрузки? Если индекс больше нескольких тысяч страниц, обычно не полностью находится в буферном пуле и часто сканируется, то возможно. В противном случае, вероятно, нет.

Почему это происходит? Операция сжатия файла данных работает с одним файлом за раз и использует битовые карты GAM (см. GAM, SGAM, PFS и другие карты распределения), чтобы найти самую последнюю распределённую страницу в файле. Затем она перемещает её как можно ближе к началу файла и так далее. В приведённом выше случае она полностью изменила порядок кластерного индекса, превратив его из идеально дефрагментированного в идеально фрагментированный.

Один и тот же код используется для DBCC SHRINKFILE, DBCC SHRINKDATABASE и auto-shrink — они одинаково плохи. Помимо внесения фрагментации индексов, сжатие файлов данных также генерирует множество операций ввода-вывода, использует много процессорного времени и создаёт огромное количество журнала транзакций — поскольку всё, что оно делает, полностью логируется.

Сжатие файлов данных никогда не должно быть частью регулярного обслуживания, и вам НИКОГДА, НИКОГДА не следует включать автосжатие. Я пытался убрать его из продукта для SQL 2005 и SQL 2008, когда был в положении, позволяющем это сделать, — единственная причина, по которой оно до сих пор там, — обратная совместимость. Не попадайтесь в ловушку использования плана обслуживания, который перестраивает все индексы, а затем пытается вернуть пространство, необходимое для перестроения индексов, запуская сжатие — это игра с нулевой суммой, в которой вы только генерируете кучу журнала транзакций без реальной выгоды.

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

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

1. Создайте новую файловую группу.
2. Переместите все затронутые таблицы и индексы в новую файловую группу, используя синтаксис CREATE INDEX … WITH (DROP_EXISTING = ON) ON, чтобы переместить таблицы и одновременно удалить их фрагментацию.
3. Удалите старую файловую группу, которую вы всё равно собирались сжать (или сильно сожмите её, если это основная файловая группа).

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

Если у вас абсолютно нет выбора и вам необходимо выполнить операцию сжатия файла данных, помните, что вы вызовете фрагментацию индексов, и вам, возможно, потребуется принять меры для её устранения впоследствии, если это вызовет проблемы с производительностью. Единственный способ устранить фрагментацию индексов без повторного роста файла данных — использовать DBCC INDEXDEFRAG или ALTER INDEX … REORGANIZE. Эти команды требуют только одной дополнительной страницы объёмом 8 КБ, вместо необходимости строить целый новый индекс, как в случае операции перестроения индекса (что, вероятно, приведёт к росту файла).

Суть — старайтесь избегать сжатия файлов данных любой ценой! Меня часто цитируют неправильно, говоря, что «никогда не сжимайте файлы данных» — это не так. Я говорю: «никогда не выполняйте регулярное сжатие любого вида», и если вам абсолютно необходимо выполнить сжатие файла данных, осознавайте проблемы, которые это может вызвать.