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

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

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

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

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

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

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

Так происходит, если не использовать параметр 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 не окончит все текущие запросы на чтение либо запись. А это значит, что блокировка вероятна.

Резюмируя, стоит сказать, что невзирая на заглавие операции — оперативная процедура индексации — она, все же, просит 2-ух короткосрочных блокировок, которые могут вызвать препядствия с блокировками. Преимущество по сопоставлению с классическими автономными операциями индексации состоит в том, что в подавляющем большинстве операций индексации блокировки отсутствуют, так что общий уровень параллелизма оказывается выше. Подробнее об индексировании в оперативном режиме см.«белую книгу» «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.

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.