Прошу, не путайте сжатие журнала транзакций со сжатием файлов данных. Сжатие журнала необходимо, если ваш журнал вырос сверх допустимых пределов, или при избавлении от избыточной фрагментации виртуальных файлов журнала (смотрите здесь (английский) и здесь (английский) замечательные статьи Кимберли). Тем не менее, сжатие журнала транзакций должно быть редкой операцией и никогда не должно входить ни в одну регулярную программу обслуживания, которую вы выполняете.
Сжатие файлов данных должно выполняться еще реже, если должно вообще. И вот почему — сжатие файлов данных вызывает серьезнейшую фрагментацию индексов. Позвольте мне продемонстрировать это на простом скрипте, который вы можете выполнить сами. Скрипт ниже создаст файл данных, создаст таблицу-«наполнитель» размером 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');
GOavg_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');
GODbId 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Кб, вместо необходимости построения полностью нового индекса в случае выполнения операции перестроения.
Итог – пытайтесь избегать запуска сжатия файлов любой ценой!
Комментарии (24)
jobgemws
08.06.2017 14:05Очень интересная информация-сам на такое натыкался. Причем источник проблемы обнаружить сразу не удавалось.
fishca
08.06.2017 16:54Всегда считал «шринк» обрезкой файла, но никак не сжатием.
minamoto
08.06.2017 17:53Я для переводов всегда стараюсь искать соответствие — как именно сам майкрософт переводит тот или иной термин.
Так что тут именно «Сжатие»:
https://technet.microsoft.com/en-us/library/ms189080.aspx
https://technet.microsoft.com/ru-ru/library/ms189080.aspx
fsou11
08.06.2017 19:16А ведь ещё data compression присутствует.
minamoto
08.06.2017 21:00А оно называется «Сжатие данных».
https://msdn.microsoft.com/ru-ru/library/cc280449.aspx
Вот такая путаница. Поэтому с Майкрософтом нужно быть начеку при переводе терминов. Именно поэтому, при знании английского, лучше пользоваться английскими терминами и английским же интерфейсом.questor
08.06.2017 22:32Хорошо бы первое упоминание термина пояснить в скобках английским оригиналом, чтобы не лезть в исходник статьи
minamoto
08.06.2017 22:51Я думал об этом, но решил, что, если читатель знает английский, я с удовольствием порекомендую ему сразу перейти к оригиналу — это и полезнее и интереснее, а если читатель английского не знает и пользуется русским интерфейсом, то нагружать его сопоставлением терминов смысла не имеет. Было бы неплохо иметь словарик для ключевых терминов — как они переведены на русский — от самой компании Майкрософт, я бы сам им с удовольствием пользовался во время перевода, но я такого не нашел, к сожалению.
Demm21
08.06.2017 22:42А вот интересно, в случае скульных баз 1с все тоже самое?
У меня шринк каждый день идет, реиндекс раз в неделю (в воскр) База 130гб.
Отобрал несколько таблиц (по размеру и кол-ву записей), проверил avg_fragmentation_in_percent — везде примерно 0.03%
И автошринк включен, те все ровно не так как в статье. Может я как-то не так смотрю фрагментацию??minamoto
08.06.2017 22:47+1Это касается всех баз на SQL Server, независимо от их происхождения. Про то, что обслуживающие 1С люди часто рекомендуют включать шринк — я знаю, и это мне очень не нравится, в частности поэтому я перевел статью от уважаемого автора.
Запрос для просмотра фрагментации есть в примерах в статье, так что если им пользуетесь — смотрите верно. То, что фрагментация у вас отсутствует — может быть счастливым стечением обстоятельств — это не значит, что так будет всегда.
Я не буду брать на себя ответственность и обязывать вас исключить шринк из обслуживания и выключить автошринк, потому что с возможными последствиями в виде возможного неконтролируемого роста базы разбираться потом вам, а не мне, но порекомендовать выключить эти опции все же могу, если готовы следить за размером базы и вовремя предпринять действия для контроля над ростом.Demm21
08.06.2017 23:06Завтра попытаю 1сников, может скажут какая самая используемая таблица в базе.
Но в целом выходит рабочий вариант такой: раз в неделю шринк, потом реиндекс и живем неделю.
yukon39
09.06.2017 15:06+2Да, всё то же самое!
Рекомендация по шринку раз в день это из мохнатых методических материалов времен 1С: Предприятия 7.7 и SQL Server 2000. Это тогда уже было вредно, но пользовалось популярностью, т.к. позволяло высвобождать «ненужное» дефицитное место на диске, что очень грело душу на фоне того, что SQL база и так в 1,5-2 раза более объемна, чем файловый dbf-вариант.
Рекомендация разошлась по куче форумов и статей про установку и настройку SQL для 1С, и уже выросло целое поколение специалистов которые бережно хранят это священное знание.
Dreyk
08.06.2017 22:47я бы перевел заголовок как "Почему вам не стоит сжимать файлы данных". "ваши" в данном случае — типичная ошибка — калька с английского языка. На русский это переводится словом "свои", или же вообще опускается
или даже "Не стоит сжимать файлы данных", а если совсем коротко, то "Не сжимайте файлы данных"
minamoto
08.06.2017 22:56Я «ваше» мнение услышал, и с «вашим» мнением не согласен.
Слово «ваше» в данном случае, мне кажется, используется для усиления эффекта принадлежности, и вполне переводимо на русский, также для усиления эффекта. Можно и на английском сказать «Why you should not shrink data files», но смысл слегка меняется.
Мне очень нравится читать статьи Пола, у него весьма выразительная и точная манера письма — как правило, в его статьях нет лишних слов, все они несут какое то значение, и я стараюсь эту выразительность передать, как могу.Dreyk
08.06.2017 22:58+1Можно и на английском сказать «Why you should not shrink data files»
вот как раз так и не говорят. Даже "wash your hands", хотя казалось бы, чьи еще руки можно помыть, зачем акцентировать
Arbane
09.06.2017 13:43Интересно, но вам самому приятно читать? Уже по первым трем строчкам ощущается сильнейший перевод в лоб. Может надо на Хабре добавить кнопку — «Пожаловаться на перевод»?
«Одна из самых моих горячих проблем касается сжатия файлов данных. Несмотря на то, что я владел кодом сжатия, когда работал в Майкрософт, у меня не было шанса переписать его так, чтобы сделать его более приятным. Мне действительно не нравится сжатие.»
«А я любить сжатие!» Ваш перевод не так уж ужасен, но это уже 100500 подобного качества опус.
Мне, например, косноязычные статьи делать стыдно.minamoto
09.06.2017 13:50+2Делаю как могу.
Мне перевод кажется достаточно хорошим — вполне возможно, что я не обладаю достаточным знанием русского, или просто не могу взглянуть на него со стороны, чтобы оценить его недостатки — для этого как раз есть функционал оценки статьи — пока оценки моих переводов плюсовые, поэтому я считаю, что не согласных с вашей оценкой качества больше, чем согласных. Смысла вводить отдельную кнопку для оценки перевода я не вижу — можно оценить качество перевода, поставив оценку статье.
Вы же, судя по тому, что в comment-only режиме — слишком остро оцениваете качество, не давая себе шанса делать ошибки. Попробуйте сделать хотя бы какой-то перевод, возможно ваша категоричность по отношению к чужим работам уйдет.Arbane
13.06.2017 10:00Я в том числе и технический переводчик. Но спорить смысла не вижу.
Оценка качества перевода, говорите? А я думал, что это оценка качества статьи. Статья сама по себе хорошая.
Taciturn
Нет чтобы добиваться автоматической дефрагментации после сжатия.
minamoto
После — это тоже компромиссный вариант. Лучший вариант — это сразу сделать алгоритм, который будет избегать фрагментации по максимуму, но его Полу тоже реализовать не удалось, к сожалению.
navion
Могли бы сделать дефрагментацию индексов при перемещении в новую файловую группу, а не заставлять это делать руками.
minamoto
Как раз при перемещении в новую файловую группу дефрагментация делается. Она не делается, если мы работаем в рамках одной файловой группы. Если вы имеете в виду, что нужно автоматом создавать новую файловую группу и переносить туда индексы — то, ИМХО, выполнение такой операции на автомате имеет больше минусов, чем плюсов.