Вопросы и ответы по SQL: Большие журналы транзакций, процесс восстановления и другие вопросы

В: Наблюдается странное поведение при резервном копировании. Чем это объяснить? Для обновления данных, используемых нашими разработчиками приложений, время от времени проводится резервное копирование рабочей базы данных размером в 62 ГБ Перед восстановлением новой копии базы данных всегда удаляется старая. Размер восстановленной копии соответствует размеру рабочей базы данных и данные сохраняют прежний вид, хотя процесс восстановления занимает гораздо больше времени, чем процесс резервного копирования. Что происходит? Почему процесс восстановления занимает гораздо больше времени, чем процесс резервного копирования?

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

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

Этап 1 процесса резервного копирования Чтение всех выделенных данных в файлах данных и их запись на устройство резервного копирования.

Этап 2 процесса резервного копирования Выборочное чтение и запись журнала транзакций на устройство резервного копирования.

Количество требуемых записей в журнале транзакций меняется в зависимости от количества, достаточного для восстановления базы данных на определенный момент времени. (Более подробное разъяснение приведено в блоге по адресу sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx).

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

Этап 1 процесса восстановления Создайте файлы базы данных, если они отсутствуют.

Этап 2 процесса восстановления Прочитайте все данные и журнал транзакций из резервной копии и запишите их в соответствующие файлы базы данных.

Этап 3 процесса восстановления Выполните стадию повтора процесса восстановления в журнале транзакций.

Этап 4 процесса восстановления Выполните стадию отката процесса восстановления в журнале транзакций.

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

Во избежание этого не следует удалять существующие файлы до запуска процесса восстановления. Кроме того, включите мгновенную инициализацию для быстрого создания файлов (дополнительную информацию см. в msdn.microsoft.com/­library/ms175935.aspx.)

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

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

О: Это неверное предположение. Действие зеркального отображения сводится к переносу реальных записей журнала транзакций из основной базы данных на зеркальный сервер, где они «воспроизводятся» в зеркальной базе данных. Преобразование, фильтрация или перехват команд T-SQL в зеркальной базе данных не осуществляются.

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

Зеркальное отображение баз данных может рассматриваться как доставка журналов в режиме реального времени (на самом деле, это определении ранее использовалось для данной функции при разработке SQL Server 2005.) При доставке журналов резервное копирование журнала транзакций основной базы данных обычно переносится на сервер-получатель и восстанавливается в базе данных-получателе.

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

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

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

Также нужно принять во внимание другие факторы при выборе одного из двух методов (слишком много факторов для обсуждения в одном разделе «Вопросов и ответов по SQL».) При принятии решения необходимо учесть соответствие факторов вашим требованиям (таких как ограничение потери данных и допустимый простой.)

В:У меня запущен SQL Server 2005 и журнал транзакций одной из баз данных продолжает пополняться. База данных находится в режиме полного восстановления и проводится резервное копирование журнала транзакций. Мне казалось, что при этом журнал транзакций не должен пополняться. Что происходит?

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

Репликация — асинхронное чтение записей журнала транзакций и последующая загрузка транзакций для репликации в отдельную базу данных распространителя. Любые записи в журнале транзакций еще непрочтенные средством чтения журнала не могут быть освобождены. Если рабочая нагрузка создает большой объем записей в журнале транзакций и был установлен существенный интервал времени для запуска средства чтения журнала, журнал транзакций будет пополняться большим количеством записей.

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

В конечном итоге, при запуске пользователем явной транзакции (например, при помощи оператора BEGIN TRAN) и при последующей модификации (например, оператор DLL или действие insert/update/delete) созданные записи журнала транзакций должны быть сохранены до тех пор пока пользователь не зафиксирует либо не произведет откат транзакции. Это означает, что любая последующая запись в журнале транзакций, созданная другими транзакциями, не может быть освобождена, так как журнал транзакций не подлежит выборочному освобождению. Например, если пользователь уходит до окончания транзакции, журнал транзакций будет продолжать пополняться, т.к.число создаваемых записей в журнале транзакций, не подлежащих освобождению, будет продолжать расти.

Чтобы узнать о причине, по которой освобождение журнала транзакций невозможно, зайдите в представление системного каталога sys.databases и изучите раздел log_reuse_wait_desc column.

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

Если причиной является активная транзакция, воспользуйтесь оператором DBCC OPENTRAN для получение подробной информации о транзакции.

DBCC OPENTRAN ('dbname')

В: Я слышал, что использование оператора REPAIR_ALLOW_DATA_LOSS для восстановления данных после их повреждения должно быть крайней мерой. Вместо этого желательно воспользоваться восстановлением при помощи резервных копий. Объясните, почему не рекомендуется пользоваться «восстановлением SQL Server 2005» и почему оно включено в продукт, учитывая его потенциальную небезопасность?

О: Начнем с того, что «Восстановление SQL Server 2005» было написано мною. Проблема с оператором REPAIR_ALLOW_DATA_LOSS (с этого момента просто называемый «восстановлением») состоит в том, что принцип его работы не совсем понятен. Имя варианта восстановления было выбрано с целью привлечения внимания к вероятности потери данных в базе данных. Принцип работы данной функции по восстановлению поврежденной структуры базы данных заключается в удалении поврежденной структуры и дальнейшем исправлении всего остального содержимого базы данных, которое ссылалось на удаленную структуру. Восстановление должно быть крайней мерой в достижении структурной согласованности базы данных и не предполагает сохранение пользовательских данных. Восстановление не пытается любым путем избавиться от пользовательских данных, но также и не пытается сохранить их.

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

Тем не менее, существует ряд проблем с восстановлением, с которыми следует ознакомиться.

• При удалении поврежденных структур восстановление не учитывает ограничения внешнего ключа, что может привести к удалению записей из таблицы, имеющей связи внешнего ключа с другой таблицей. Существует только один способ проверки после выполнения восстановления при помощи DBCC CHECK­CONSTRAINTS.
• Восстановление не учитывает (и не имеет возможности учитывать) присущую бизнес-логику или связи данных, определенных на уровне приложений, которые могут быть повреждены при удалении некоторых данных. Повторюсь, что невозможно обнаружить какое-либо повреждение без прогона проверки целостности, встроенной в приложение.
• Некоторые операции восстановления не реплицируются. Запуск восстановления в издателе или узле в одноранговой топологии может внести несогласованность в топологию, которую необходимо исправлять вручную.

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

В: Недавно я перешел в новую компанию на позицию администратора базы данных, в рамках которой на меня была возложена ответственность за несколько приложений и их серверные базы данных. Одно из приложений имеет очень низкую производительность в проведении обновлений. Я провел некоторые исследования и обнаружил, что каждая таблица, используемая данным приложением, имеет огромное количество индексов. Поспрашивав людей, я выяснил, что предыдущий администратор базы данных имел привычку добавлять индекс каждой колонки таблицы в отдельности дополнительно к другим комбинациям. Неужели все эти индексы необходимы? Как определить какие из них можно удалить? Мы используем SQL Server 2005.

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

Единственным способом определения типа используемых индексов в SQL Server 2000 является использование профилирования и рассмотрение плана запросов. В SQL Server 2005 добавлено новое динамическое административное представление (DMV), которое отслеживает использование индексов — sys.dm_db_index_usage_stats.

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

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

Примеры простого кода для получения периодических снимков динамического административного представления см. в блоге sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx.