MSSQL: снова о дефрагментации и SHRINK

Начнем с хороших новостей. Какое-то время назад я написал статью Дефрагментация таблиц в высоко нагруженных базах данных (MSSQL). За это время я еще больше отшлифовал скрипт на production, и отдел безопасности фирмы, где я сейчас работаю, разрешил выложить его в open source (репо на github). Приглашаю воспользоваться им и писать мне о багах и пожеланиях.

Ниже я приведу краткий update к статье - кое в чем я теперь с ней не согласен. Кроме того, опишу опыт SHRINK - почему его лучше никогда не делать, почему все таки иногда нужно делать и как его готовить.

Update к статье

Во-первых, я отказался полностью от INDEX REORGANIZE. На реальных объемах дождаться этого процесса нереально. Поэтому только REBUILD.

Во вторых, я начал перестраивать и COLUMNSTORE таблицы. Прогресс виден и вычисляется по числу перестроенных ROWSTORE. Однако до сих пор я считаю поддержку COLUMNSTORE таблиц экспериментальной

Я также словил несколько инцидентов, когда перестройка индекса так изменяла план выполнения UPDATE и особенно MERGE (а SQL меняет план выполнения чтобы трекать изменения), что время выполнения запроса сильно деградировало

SHRINK - почему это не надо делать

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

Во-первых, объем данных у вас в базе растет естественным образом, поэтому рано или поздно это место будет заполнено.

Во вторых, пустые страницы не пишутся в FULL BACKUP и не кешируются, так что это дырка от удаленной таблицы ничем вам не мешает.

В большом числе случаев SHRINK делают по психологическим соображениям - пустое место внутри файла видно только DBA, а на диске видно сразу

В каких же случаях все таки приходится делать SHRINK?

SHRINK данных - когда это делают

Для данных SHRINK имеет делать смысл, когда необходимо именно уменьшить размер MDF файла. Например, для того, чтобы на тот же диск поместить другую базу, или облегчить работу внешним storage (которые не знают, что внутри файла есть свободное место). Также для SSD наличие свободного места полезно для времени его жизни.

Как это не надо делать?

Сразу забываем о Tasks -> Shrink -> Database и пользуемся только Tasks -> Shrink -> Files, работая с каждым файлом индивидуально. Общая ошибка здесь - увидеть, что в базе 6Tb свободно 50%, и указать в качестве желаемого размера 4Tb, и нажать OK. Работать это будет в одной транзакции, долго, до момента пока не найдет страницу, которую не может переместить и потом эту транзакцию откатит.

SHRINK надо делать мелкими порциями по несколько гигабайт. Рекомендую создать джоб и выполнять в нем примерно следующее (подправьте скрипт по своему вкусу):

declare @fsize bigint
while datepart(hh,getdate())<22 begin -- only during day hous
  SELECT   @fsize = Size/128.0 --AS [TotalSize in MB]
  FROM sys.database_files  (NOLOCK) where (sys.database_files.name='MyDbFile')
  OPTION (RECOMPILE)
  set @fsize=@fsize-1000
  DBCC SHRINKFILE (N'MyDbFile' , @fsize)
  --waitfor delay '00:02:00' -- when commented, shrinks aggresively
  end

Почему джоб? Потому что вы точно не хотите смотреть на экран несколько недель. Вот история одной базы (размер в терабайтах), где после массового удаления SHRINK работал около месяца.

И естественно, после SHRINK сделайте INDEX REBUILD.

SHRINK лога транзакций

Как правило, размер LDF "high watermark" - верхний уровень прилива, и трогать размер файла не нужно. Исключения - runaway операции и исключительно долгие транзакции.

Если для SIMPLE RECOVERY вам просто стоит пытаться много раз сделать SHRINK (и часто вам может повезти и простого усечения будет достаточно), то для FULL recovery/AlwaysOn многие попытки могут быть безуспешными.

Я рекомендую временно прицепить SHRINK лога в виде скрипта к BACKUP LOG для данной базы - как только BACKUP LOG закончен, то сразу делается попытка уменьшить LDF. Если такой скрипт оставить на день, то он наверняка справится даже с 'неподдающийся' базой.

SHRINK tempdb

Вполне валидная операция (например, для выравнивания размеров файлов), но и самая сложная, так как в tempdb часто заблокированы и не могут быть перемещены страницы. Перезапуск гарантированно решает проблему, но это перезапуск.

Интернет рекомендует до SHRINK сделать:

USE [tempdb]
CHECKPOINT
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE ('ALL') WITH NO_INFOMSGS
DBCC FREESESSIONCACHE WITH NO_INFOMSGS

Но это помогает отнюдь не всегда