SQL в вопросах и ответах: Без следа

Такие процессы, как архивирование, восстановление и проверка целостности могут вызывать неожиданное поведение базы данных, но оно оправдано.

Пол С. Рэндал

Трудности восстановления

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

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

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

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

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

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

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

Не перебивай!

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

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

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

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

В крайнем случае, когда создание моментального снимка занимает больше времени, чем ожидалось, и вы приняли решение принудительно завершить процесс DBCC CHECKDB, сразу ничего не произойдет. Придется подождать, пока не завершится восстановление в моментальном снимке, и процесс не среагирует на сигнал завершения. Восстановление прервать нельзя, и в SQL Server в коде аварийного восстановления нет разницы между настоящим аварийным восстановлением в случае неожиданного отключения и при создании моментального снимка.

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

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

Выбирайте правильное время

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

Ответ Всегда, когда нужно определить, когда была удалена таблица, следует проверять трассировку по умолчанию. В ней записываются события DDL (Data Definition Language). Подробнее о трассировках по умолчанию см. статью в электронной документации по SQL Server по адресу https://msdn.microsoft.com/library/ms175513.aspx.

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

Поэтому единственный способ выяснить время удаления таблицы — сделать то, что я называю «итеративный просмотр журнала транзакций». Восстановите копию базы данных на момент времени, когда таблица заведомо существовала. Затем последовательно выполняйте восстановление на определенный момент времени с использованием параметров WITH STOPAT и WITH STANDBY. Перемещайтесь маленькими шажками.  Обнаружив момент, когда таблицы уже нет, восстановите базу данных на момент, непосредственно предшествующий найденному моменту, и вы сможете извлечь данные таблицы.

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

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

Эту функцию можно задействовать для нахождения транзакции, которая удалила нужный вам объект. Затем можно использовать полученный регистрационный номер транзакции в журнал (LSN) для выполнения восстановления командой с параметром WITH STOPBEFOREMARK = 'lsn:<LSN-номер_транзакции>'. При этом будет выполнено восстановление журнала транзакций до, но не включая транзакции удаления таблицы. В этом случае вам не придется пошагово просматривать журнал, как описано ранее. Подробнее об этой функции и ее использовании см. запись в моем блоге по адресу http://www.sqlskills.com/blogs/paul/post/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspx.

Фильтрация событий

Вопрос Теперь, когда в SQL Server 2012 из продукта убрали трассировку, я хотел бы подробнее узнать о расширенных событиях. Не могли бы вы объяснить, почему считается, что расширенные события должны быть легче трассировки.

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

При использовании SQL Trace события генерируются постоянно. Фильтрацию выполняет потребитель событий. Это означает, что на SQL Server возлагается дополнительная нагрузка по генерации всех событий, даже тех, что не будут использованы. Это очень неэффективный процесс.

А вот фильтрация расширенных событий выполняется на самом SQL Server. Механизм расширенных событий оценивает предикаты, заданные при определении сеанса сбора событий. Это означает, что при возникновении события, для сбора основной информации о событии требуется минимальная работа. Это позволяет механизму расширенных событий вычислять предикат. Если результат равен False, событие моментально отбрасывается и механизм событий ничего больше не делает. Это снижает дополнительную нагрузку, связанную со сбором событий, по сравнению с трассировкой.

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

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

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

Пол С. Рэндал (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.