DBCC CHECKDB для очень больших баз данных

DBCC CHECKDB представляет собой команду на T-SQL, которая осуществляет проверку логической и физической целостности всех объектов заданной базы данных. Большинство администраторов баз данных (DBA), вероятно, не особенно задумываются о регулярности запуска DBCC CHECKDB — до тех пор, пока их базы данных не становятся очень большими. С некоторого момента, когда размеры вашей базы данных существенно вырастут, вы начнете сталкиваться с различными проблемами при запуске DBCC CHECKDB. Например, время, необходимое для выполнения процесса DBCC CHECKDB, может стать реальным препятствием. Помимо этого, может не найтись свободного пространства для снимков данных, создаваемого в ходе выполнения DBCC CHECKDB.

Ваше задание должно быть организовано достаточно гибко, чтобы, помимо решения проблем с очень большими базами данных (VLDB), оно могло различать новые базы данных, удаленные или иные недоступные базы данных, находящиеся в offline-режиме, или, например, зеркальные отображения баз данных. Чтобы научиться управляться со всем этим, я могу предложить простое решение, использующее концепцию «задание для администратора и исполнителя», Admin/Worker Job. Далее в статье будет рассмотрено задание администратору и задание исполнителю, а также параметр @VLDB. Все сценарии из данной статьи исполняются как на SQL Server 2008, так и на SQL Server 2005.

Задание администратору

Листинг 1 содержит сценарий ежедневного обслуживания сервера под названием ServerDailyMaintenance.txt. При исполнении этого сценария создается задание Admin Job для службы SQL Server Agent. Это задание администратору является единственным заданием, которое действительно планируется к запуску, а оно порождает или модифицирует и, собственно, запускает выполнение задания для исполнителя.

На экране 1 показан основной шаг сценария Admin Job, который запускает хранимую процедуру msdb.dbo.mnt_DBCC.

Листинг 2 содержит описание хранимой процедуры mnt_DBCC. Эта процедура идентифицирует имеющиеся в наличии базы данных и приступает к построению задания исполнителю, Maintenance_DBCC_CHECKDB.

На экране 2 показаны параметры процедуры mnt_DBCC и их возможные значения, включая сведения о предназначении каждой величины.

Чтобы работать только с системными базами данных (model и master), параметр @system_only должен быть задан равным 1. Чтобы выполнить команду DBCC CHECKDB только с настройкой physical_only, следует присвоить значение 1 параметру @physical_only. Для большинства баз данных следует придерживаться значения для параметров @system_only и @physical_only. Для категории VLDB вам, возможно, потребуется присвоить значение 1 параметру @VLDB. Если вы зададите это значение (1) для @VLDB, то вам придется определиться и со значением для параметра @days. Параметр @days игнорируется, если @VLDB равен 0. Значение для @VLDB означает, что будет выполняться стандартная команда DBCC CHECKDB. Если вы присваиваете параметру @VLDB значение 1, то процедура mnt_DBCC вызывает на исполнение хранимую процедуру mnt_DBCC_VLDB, код которой представлен в листинге 3. На экране 3 представлен список параметров этой хранимой процедуры и возможных значений.

Задание исполнителю

Задание исполнителю создается и запускается из задания администратору (Admin Job) и называется Maintenance_DBCC_CHECKDB. Каждый шаг задания исполнителя содержит последующий шаг проверки на ошибки. На экране 4 изображен пример выполнения некоего шага задания исполнителю для базы данных AdventureWorks, для которого значение параметра @VLDB равнялось 1, а значение параметра @days было равно 7.

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

Как работает параметр @VLDB?

Если значение этого параметра задано равным 0, то Worker Job запускает простой процесс DBCC CHECKDB. «Волшебство» случится, если сделать @VLDB равным 1. Определить для вашего окружения подходящее значение параметру @days поможет подбор и тестирование.

Когда параметру @VLDB присвоено значение 1, хранимая mnt_DBCC осуществляет вызов процедуры mnt_DBCC_VLDB, которая инспектирует все таблицы пользователя, системные таблицы, индексированные представления и внутренние таблицы в базе данных и определяет размер каждой. После этого все таблицы разбиваются на N групп, число которых совпадает со значением, заданным параметру @days. Цель была в том, чтобы распределить таблицы по группам по возможности равномерно, чтобы выровнять ночную нагрузку на сервер. Каждая группа таблиц получает свой номер (VLDB_GROUP) в зависимости от величины параметра @days. Номер группы, с которой следует работать сегодня, определяется по формуле:

VLDB_Group = DATEDIFF (dd, N'01–01–2009', GETDATE ())% @days

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

Чтобы показать, как работает параметр @VLDB, рассмотрим пример.

Предположим, что у вас есть 10 таблиц и вы задали параметру @days значение 2. Хранимая процедура локализует таблицы и определяет их размеры; затем помещает самую большую по размеру таблицу в группу с номером (VLDB_Group = 0); следующую по величине — в группу 1 (VLDB_Group = 1), затем возвращаемся к группе и так, циклически, до тех пор, пока все таблицы не будут сгруппированы. При запуске запроса

SELECT DATEDIFF (dd, N’01–03–2009’, GETDATE ())% 2 

в результате будет получен 0 или 1. Если сегодня — 0, то завтра будет 1, послезавтра — 0, и так далее. Каждая группа таблиц будет обработана, без запоминания сведений о том, к каким группам относятся какие таблицы. А что случится, если вторая по размерам таблица сегодня увеличится и станет самой большой таблицей завтра? Эта таблица будет пропущена, так как завтра она переместится в группу 0. Что если таблицы 1 и 2 одновременно вырастут или уменьшатся, но так, что их относительные размеры останутся прежними? В этом случае обе таблицы будут своевременно обработаны. Чаще всего наибольшая таблица всегда будет наибольшей; вторая по величине останется второй и так далее; таким образом, ни одна таблица никогда не будет пропущена, так как относительные размеры таблиц не меняются. Если же вы хотите гарантировать, что ни одна из таблиц никогда не будет пропущена в цикле обработки, то вам все же придется модифицировать код с сохранением имен в какой-то специальной таблице. Сохраните, например, группы в первый день цикла и обращайтесь к этой таблице в течение всего периода. Кроме того, нужно будет проверять, что никакие таблицы не были удалены либо добавлены со времени выборки первого дня. Теперь изменение размеров таблиц — не проблема (в том числе если наибольшая на сегодня таблица завтра станет второй по величине).

При параметре @VLDB, установленном равным 1, создается также задание исполнителя, которое при каждом запуске также выполняет и DBCC CHECKALLOC, и DBCC CHECKCATALOG (как показано на экране 4).

Инструкция по применению

Задание DBCC CHECKDB, использующее описанный метод Admin/Worker Job, представляется довольно разумным, гибким и не требует дополнительного сопровождения: вы можете выполнить его на базе данных любого размера. Задание выполняется автоматически со всеми базами данных, которые вы добавили, за исключением ранее удаленных. Кроме того, код задания идентифицирует базы данных — зеркальные отображения (mirrored) и те, что находятся в состоянии offline по какой-либо другой причине. Вы можете определить, следует ли выполнять задание только для системных или только для физических баз данных. И, главное, вы можете развернуть нагрузку DBCC CHECKDB на любое количество дней, которое будет задано.

Давид Поль Жиро — администратор баз данных в Microsoft, где он поддерживает сотни систем SQL Server для служб Xbox Live и Zune. Имеет сертификаты MCITP: Database Administrator и MCITP: Database Developer по SQL Server 2008 и 2005, а также MCDBA по SQL Server 2000