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

Давайте исследуем внутренний механизм выбора плана запроса, поймем, почему медленно восстанавливаются резервные копии, и посмотрим, что происходит при восстановлении базы данных, являющейся издателем при репликации.

Пол С. Рэндал

Планируйте свои запросы

Вопрос Когда SQL Server выбирает план запроса для выполнения моих запросов, учитывает ли он данные, которые в данный момент находятся в памяти?

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

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

SQL Server не следит за тем, какие фрагменты таблиц и их индексов находятся в памяти в различные моменты времени. Буферный пул хранит в памяти страницы файлов данных, загруженные из базы данных. Однако SQL Server никаким образом не выполняет автоматическое агрегирование. Например, он не сможет определить, что в памяти находится 50 % второго индекса таблицы X и при этом в памяти находится только 5 % третьего индекса таблицы X.

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

У первого индекса более короткие записи, поэтому на странице файла данных умещается больше строк индекса. Оптимизатор запросов выберет этот индекс. При обращении к требуемым записям индекса, необходимом для выполнения запроса, потребуется считать в память с помощью физического ввода-вывода меньше страниц файла данных, чем при использовании второго индекса. У второго индекса более длинные записи, и на странице файла данных умещается меньше записей. Такая логика называется оптимизацией, основанной на стоимости, и именно так реализован обработчик запросов SQL Server.

Однако, что если значительная часть второго индекса, имеющего больший размер, уже загружена в память, а у первого ничего не загружено в память? Запрос потребует физического ввода-вывода для чтения выбранного индекса в память. Эти будет гораздо медленнее, чем при использовании индекса, который уже находится в памяти. В этом случае план запроса действительно будет не оптимален. Однако оптимизатор запросов никак не может узнать, что содержится в памяти, а это всего лишь пример.

Но даже если принять это в расчет, что будет, если оптимизатор запросов узнает о содержимом памяти, и сгенерирует план, который использует менее эффективный индекс, поскольку он уже загружен в память? План будет оптимальным только до тех пор, пока сохраняется такая ситуация. Если другой запрос загрузит в память более эффективный индекс, то план запроса станет неоптимальным. Как объявить план недействительным, чтобы он снова перекомпилировался?

Как бывший инженер группы разработки SQL Server, я знаю, что сложность реализации объединенного представления с информацией о том, какие таблицы и индексы находятся в памяти, помогающего при выборе плана запроса и объявлении плана недействительным, чрезвычайно велика. Скорее всего, это приведет к нежелательным потерям в производительности ради эпизодического выигрыша, и вряд ли когда-нибудь произойдет.

Если вас интересует, как посмотреть буферный пул, ознакомьтесь с Dynamic Management View (DMV) sys.dm_os_buffer_descriptors и различными запросами, которые я привел в разделе Buffer Pool своего блога SQLskills.

Долгое восстановление резервных копий

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

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

1. Все записи журнала записываются из резервной копии в файл журнала базы данных.
2. Выполняется REDO-часть восстановления (обеспечивается, чтобы все операции зафиксированных транзакций отразились в базе данных).
3. Выполняется UNDO-часть восстановления (обеспечивается, чтобы все операции незафиксированных транзакций не отразились в базе данных).

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

Когда на резервной базе данных начинается восстановление, при наличии undo-файла выполняется еще один этап, предшествующий первым трем. На этом первоначальном этапе берутся все записи журнала транзакций из undo-файла и производится откат соответствующих им изменений. По сути, при этом база данных возвращается в состояние, в котором она была в конце этапа 2. Это состояние базы данных будет таким же, как если бы предыдущую резервную копию журнала восстановили с параметром WITH NORECOVERY, а не WITH STANDBY.

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

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

Следующее резервирование журнала основной базы данных также завершится незадолго до окончания перестроения индекса. При восстановлении во второй базе данных все содержимое undo-файла придется откатывать снова. Затем выполнится восстановление журнала, и сгенерируется еще один большой undo-файл, чтобы откатить второе незафиксированное перестроение индекса.

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

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

Репликация и восстановление поврежденных данных

Вопрос Время от времени у нас появляются поврежденные файлы. Наши резервные копии также бывают поврежденными, так что приходится выполнять операции восстановления. На прошлой неделе одной из баз данных, в которой пришлось восстанавливать информацию, оказалась база данных, публикующая данные для репликации. В Microsoft Books Online написано, что после восстановления поврежденных данных на издателе необходимо переинициализировать базы данных подписчиков. Не могли бы вы объяснить, почему?

Ответ Если вы имеете в виду использование параметра REPAIR_ALLOW_DATA_LOSS операции CHECKDB проверки целостности базы данных (database consistency checking, DBCC) (далее я буду говорить просто «восстановление»), следует не один раз подумать, прежде, чем начать восстанавливать базу данных, являющуюся издателем при репликации. Если это возможно, воспользуйтесь резервными копиями вместо запуска восстановления.

Если вы используете репликацию слиянием, DML-триггеры (Data Manipulation Language, язык манипулирования данными) перехватывают изменения на издателе и преобразовывают их в логические операции. Если вы используете транзакционную репликацию, то изменения на издателе перехватываются механизмом анализа журнала транзакций базы данных. Затем они заносятся в журнал, причем физические операции преобразовываются в логические. В обоих случаях логические операции затем применяются к базам данных, являющихся подписчиками репликации.

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

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

Если в дальнейшем операция вставки добавит записи со значениями кластерного ключа, которые были у записей, удаленных операцией восстановления, то Distribution Agent не сможет применить эти вставки к реплицированным копиям. Операция восстановления не выполнялась в базе данных, являющейся подписчиком, поэтому при попытке выполнить вставку в реплицированную таблицу произойдет нарушение уникальности ключа. Значит, если операция восстановления изменила какую-либо таблицу, публикуемую при репликации, данные подписчиков репликации больше не являются достоверными. Поэтому необходимо их переинициализировать.

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

Пол С. Рэндал (Paul S. Randal) — носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу twitter.com/@PaulRandal.