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

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

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

Сжатие файлов данных должно выполняться еще реже, если должно вообще. И вот почему — сжатие файлов данных вызывает серьезнейшую фрагментацию индексов. Позвольте мне продемонстрировать это на простом скрипте, который вы можете выполнить сами. Скрипт ниже создаст файл данных, создаст таблицу-«наполнитель» размером 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) (смотрите статью «Внутри Storage Engine: GAM, SGAM, PFS и другие карты распределения», английский) чтобы найти самую последнюю страницу, размещенную в файле. Затем она перемещает эту страницу настолько близко к началу файла, насколько это возможно, и снова, и снова повторяет такую операцию. В ситуации выше, это полностью развернуло порядок кластерного индекса, сделав его из полностью дефрагментированного полностью фрагментированным.

Одинаковый код используется в командах DBCC SHRINKFILE, DBCC SHRINKDATABASE, и при автосжатии – они одинаково плохи. И вместе с фрагментацией индекса, сжатие файлов данных генерирует большое количество операций ввода/вывода, активно использует процессорное время и генерирует большое количество записей в журнале транзакций — поскольку все, что оно делает, полностью журналируется.

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

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

Я рекомендую следующий метод:

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

На самом деле вам необходимо обеспечить дополнительное свободное пространство, прежде чем вы сможете сжать старые файлы, но это гораздо более чистый механизм.

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

Итог – пытайтесь избегать запуска сжатия файлов любой ценой!

Автор: Алексей Бородин

Источник