SQL в вопросах и ответах: Обслуживание журналов и индексов

Вопрос:Я занимаюсь разработкой плана резервного копирования наших баз данных, который помимо прочего предусматривает резервное копирование журнала транзакций. Это позволит нам в случае сбоя восстановить систему, потеряв минимум данных. Исследуя, с какими проблемами мы можем столкнуться, я много раз читал, что нужно быть очень внимательным, чтобы не разорвать цепочку резервных копий журнала. Не могли бы вы объяснить, что это за цепочка и как ее можно разорвать?

Ответ:Это замечательный вопрос, так как очень часто о таких нюансах забывают. Цепочкой резервных копий журнала (иногда ее просто называют цепочкой журнала) называют непрерывный набор резервных копий журнала транзакций, которые охватывают время с момента создания самой последней резервной копии данных (полной или разностной) до момента, на который надо восстановить сервер. Примерная последовательность применения резервных копий в процессе восстановления такова:

• самая свежая полная резервная копия базы данных;
• самая свежая разностная резервная копия базы данных;
• все резервные копии журнала транзакций, созданные позже.

Большинство ИТ-специалистов хранят больше резервных копий журнала транзакций на случай, если какая-то из резервных копий окажется поврежденной, и придется восстанавливать систему, используя не самую свежую резервную копию данных. За более подробной информацией отсылаю к двум моим статьям о резервном копировании и восстановлении, опубликованных в прошлом году в TechNet Magazine: «Understanding SQL Server Backups» и «Recovering from Disasters Using Backups».

Если какая-либо из резервных копий журнала в выбранной последовательности восстановления повреждена или отсутствует, цепочка резервных копий журнала будет нарушена и систему не удастся восстановить дальше этой точки. Если повреждена только одна из резервных копий журнала, можете принудительно восстановить систему, используя параметр WITH CONTINUE_AFTER_ERROR. При этом будут восстановлены и поврежденные записи журнала транзакций, что повлечет за собой повреждение базы данных. Советую сто раз подумать, прежде чем применять этот тип восстановления.

Одна из операций, которая могла привести к отсутствию необходимой резервной копии журнала, — создание «внеочередной» резервной копии (например, чтобы отдать копию разработчику) без обеспечения сохранности цепочки. Эта резервная копия журнала является частью цепочки резервных копий журналов, поскольку содержит записи журнала, созданные с момента создания предыдущей резервной копии журнала.

Так происходит, если не использовать параметр WITH COPY_ONLY, который позволяет создать резервную копию журнала, но при этом следующая резервная копия будет содержать те же записи журнала. Подробнее о том, как избежать прерывания цепочки резервных копия см. в записи «BACKUP WITH COPY_ONLY» с моем блоге.

Более популярный пример нарушения цепочки резервных копий — операция, которая не позволяет создать регулярную резервную копию журнала транзакций. К таким операциям относятся:

• переход на простую модель восстановления и затем обратно к полной модели или модели с неполным протоколированием (BULK_LOGGED);
• создание дампа журнала в версии SQL Server 2005 или более ранней, используя параметр BACKUP LOG … WITH NO_LOG или TRUNCATE_ONLY;
• восстановление базы данных из моментального снимка;

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

И последнее: совершенно ошибочен тот популярный миф, что создание полной или разностной резервной копии нарушает цепочку резервных копий — оно вообще никак не влияет на резервное копирование журнала.

Кластеризуйте индексы

Вопрос:Во многих таблицах в нашей базе данных в SQL Server 2008 нет кластеризованного индекса. Я слышал, что могут быть проблемы с производительностью при работе с перенаправленными записями в связи с необходимостью дополнительных операций ввода-вывода. Скажите, как мне проверить это и что с этим делать?

Ответ:Куча — это таблица, в которой нет кластеризованного индекса. Она по определению неупорядочена. Читателей, которые не знакомы с перенаправленными записями, я отсылаю к статье «Forwarding and forwarded records, and the back-pointer size» (Перенаправление и перенаправленные записи и размер обратного указателя) в моем блоге. Перенаправленные записи в кучах могут вызывать дополнительные случайные операции ввода-вывода при обработке запросов, что в свою очередь ведет к падению производительности.

Самый простой способ проверить, есть ли у вас запросы, обрабатывающие перенаправленные записи, — изучить показания счетчика Forwarded Records/sec у объекта Access Methods. Затем примените функцию динамического управления dm_db_index_physical_stats в режиме DETAILED к некоторым таблицам базы данных — она возвратит число перенаправленных записей в каждой таблице в столбце forwarded_record_count. Подробнее можно почитать в интерактивной справочной системе.

Самый неудачный способ удаления перенаправленных записей — это создать кластеризованный индекс, а затем снова удалить его. Это вызовет автоматическое двукратное перестроение всех некластеризованных индексов в таблице, что является огромной и бесполезной тратой ресурсов. Подробнее см. статью «What happens to non-clustered indexes when the table structure is changed?» “(Что происходит с некластеризованными индексами при изменении структуры таблицы?) в моем блоге.

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

При увеличении размера записей в таблице и недостатке места могут появляться перенаправленные записи. Поэтому есть еще один способ предотвращения образования перенаправленных записей — не изменять размер записей. Это может, к примеру, означать использование значений по умолчанию в столбцах переменной длины.

В SQL Server 2008 есть новая инструкция ALTER TABLE … REBUILD, которая позволяет перестраивать кучи. Она аналогична инструкции ALTER INDEX … REBUIL с той разницей, что последняя перестраивает индексы. Разработчики из Microsoft добавили эту инструкцию, чтобы обеспечить возможность сжатия данных, но она будет служить и нашим задачам. Подробнее можно почитать в интерактивной справочной системе.

Обслуживание индексов

Вопрос:Я изменил наши процедуры обслуживания индексов, так чтобы индексы перестраивались в оперативном режиме, но все равно иногда мы сталкиваемся с блокировкой во время выполнения процедур обслуживания. Почему так получается? Я полагал, что в оперативных операциях индексации блокировки не используются, то есть их быть не должно. Так должно быть или я делаю что-то не так?

Ответ:Это вполне ожидаемое поведение. При инициализации процедуры требуется блокировка общей таблицы (это очень быстрая операция). Она немедленно прерывается. Блокировка немедленно отменяется, но как и любая другая блокировка, она должна быть поставлена в очередь и это будет препятствовать всем новым запросам вносить изменения в таблицу, пока не будет получена и отменена блокировка.

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

В конце процедуры индексации для ее завершения потребуется блокировка схемы — можете считать ее монопольной блокировкой. Она также происходит чрезвычайно быстро и немедленно отменяется. Эта блокировка предотвращает выполнение любых запросов к таблице (на чтение или запись), пока она не получена и не отменена.

И на этот раз невозможно получить блокировку, пока SQL Server не завершит все текущие запросы на чтение или запись. А это означает, что блокировка возможна.

Резюмируя, стоит сказать, что несмотря на название операции — оперативная процедура индексации — она, тем не менее, требует двух краткосрочных блокировок, которые могут вызвать проблемы с блокировками. Преимущество по сравнению с традиционными автономными операциями индексации заключается в том, что в подавляющем большинстве операций индексации блокировки отсутствуют, так что общий уровень параллелизма оказывается выше. Подробнее об индексировании в оперативном режиме см.«белую книгу» «Online Indexing Operations in SQL Server 2005» (Оперативные процедуры индексации в SQL Server 2005).

Сокращение времени обслуживания индексов

Вопрос:Я получил «в наследство» несколько систем, в которых на выполнение регулярных заданий обслуживания индексов уходит много времени, к тому же они вызывают много операций ввода-вывода, но я не выполняю никакой перестройки индексов, потому что они не становятся фрагментированными. Я хотел бы избавиться от этих процедур, так как они не дают никакого выигрыша в производительности. Подскажите, какую стратегию мне использовать?

Ответ:Это довольно популярная проблема. А причина заключается в логике определения, какие индексы нуждаются в перестроении или реорганизации.

Большинство администраторов применяют ко всем индексам в базе данных системную функцию динамического управления dm_db_index_physical_stats (она уже упоминалась ранее), после чего решают, что делать — перестраивать, реорганизовать или ничего не предпринимать. При принятии решения они ориентируются на значения avg_fragmentation_in_percent, page_count и avg_page_space_used_in_percent, отбирая их с помощью предложения WHERE.

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

Большинство представлений динамического управления поддерживают «обработку после предиката», когда обрабатываются только данные, отвечающие условию предиката в предложении WHERE. Однако dm_db_index_physical_stats — функция, а не представление, и она не поддерживает такую возможность. Это означает, что фильтровать надо вручную и запрашивать у функции обработку только тех индексов, которые скорее всего фрагментированы и могут потребовать перестроения или реорганизации.

Я рекомендую проследить за фрагментацией на протяжении нескольких недель. Так вы выявите индексы, которые требуют проверки на фрагментацию, и вам не придется каждый раз проверять все индексы. Получив список таких индексов, создайте таблицу с именем, названием индексов и порогом фрагментации, после которого нужно принимать меры. Может оказаться, что некоторые индексы начинают влиять на производительность при существенно более высоком уровне фрагментации, чем другие. Это будет «контрольная таблица» для управления заданием обслуживания индексов. Процедура должна по циклу проходить по всем индексам в таблице и выполнять системную функцию dm_db_index_physical_stats только по отношению к ним.

Я реализовал такую схему у нескольких клиентов. В некоторых случаях это позволило сократить время обслуживания индексов с часов до 15 минут или даже меньше. Выигрыш достигнут исключительно за счет отказа от выполнения этой функции по отношению к статическим индексам. Вы можете пойти дальше и проследить, как часто индекс перестраиваеться и изменять параметр FILLFACTOR индекса автоматически; это может дать дополнительное сокращение работы, выполняемой заданием обслуживания индексов.

Более подробную информацию о различных методах обслуживания вы найдете в статье «Importance of index maintenance» (Важность обслуживания индексов) в моем блоге, а из статьи «Inside sys.dm_db_index_physical_stats» вы узнаете о происходящем внутри функции dm_db_index_physical_stats.