В ваши обязанности, как DBA, вероятно входит оптимизация производительности, восстановление, настройка прав доступа и тд. Но многие склонны забывать о такой важной операции, как проверка целостности БД (DBCC CHECKDB). Вы можете решить данную задачу просто создав план обслуживания «»Check Database Integrity Task», однако это всего лишь checkdbox.
Как вы видите, здесь мы почти не можем ничего контролировать, хотя для данной операции существует множество интересных ключей. Я думаю, что вам следует более детально погрузиться в DBCC CHECKDB и создать собственное, подходящее именно вам, задание. Основным преимуществом собственного задания будет сокращение времени работы и как следствие уменьшение требуемых ресурсов для данной операции. Так же могут быть такие преимущества как универсальность, управляемость, обработка ошибок и тд.
Уменьшение вывода и сбор всех ошибок:
Не важно где вы запускаете CHECKDB, всегда запускайте с опцией WITH NO_INFOMSGS. Эта простая опция подавляет все информационные сообщения, которые просто сообщают вам как много строк в каждой таблице, если вам необходима данная информация, вы можете получить её из DMV, вне команды CHECKDB.
Так же вы всегда должны использовать опцию WITH ALL_ERRORMSGS, особенно если вы используете SQL Server 2008 RTM или SQL Server 2005 (тогда вы сможете увидеть более 200 строк). Любая CHECKDB операция обрезается 1000 строками в Management Studio и иногда может потребоваться перенаправить вывод в файл. Понимание данного механизма позволит вам получать всю информацию с первого раза и не будет надобности перезапускать процесс.
Используйте только физическую проверку данных на продуктивной среде:
В большинстве случаев, CHECKDB тратит основное время на логические проверки данных. Если у вас есть возможность провести данную проверку на достоверной копии данных, то вы можете сфокусироваться только на физической структуре на вашей продуктивной системе. Под достоверной копией данных я понимаю ТОЛЬКО восстановление БД из backup на другом сервере.
Такие способы как:
- Группа доступности AlwaysOn
- Snapshot по верх database mirroring
- Log Shipping
- И тд.
Не являются достоверной копией данных и логическая проверка на данных технологиях, не даст достоверный результат относительно продуктивной среды. Только точно такая же копия БД может быть достоверной.
Эксперименты с флагами трассировки 2549, 2562, and 2566:
Я нашёл, что флаги трассировки 2549 и 2562 могут улучшить производительность CHECKDB. Найти описание данных флагов можно в KB #2634571, но в целом:
Trace Flag 2549 (оптимизирует процесс проверки из расчёта, что каждый файл данных БД лежит на своём собственном диске. Флаг можно использовать когда БД имеет один файл данных или каждый файл данных лежит на своём диске, в противном случае это может ухудшить производительность CHECKDB)
Trace Flag 2562 (процесс CHECKDB будет запущен в одном батче, что будет более дорогой операцией для tempdb (до 5% от размера проверяемой БД). Это более лучший алгоритм для чтений страниц из БД, который уменьшает latch contention). Данный флаг уже включён в SQL Server 2012, так что начиная с этой версии включать его отдельно не требуется.
Trace Flag 2566 (Если вы до сих пор используете SQL Server 2005 (напоминаю что Microsoft уже не поддерживает данную версию), то вы можете использовать флаг 2566, который представлен в SP2 CU9. Флаг исправляет проблему производительности DATA_PURITY на x64 системах. Вы можете посмотреть подробности KB #945770. Данный флаг не требуется включать на других версиях SQL Server)
Если вы решитесь использовать данные флаги, то я настоятельно рекомендую включать их с помощью DBCC TRACEON, а не через параметры запуска SQL Server. Это даст вам возможность выключить флаги без перезагрузок.
Уменьшение нагрузки на дисковую подсистему (оптимизация tempdb):
DBCC CHECKDB может сильно нагружать tempdb, постарайтесь выделить для данной БД достаточно ресурсов (тестируйте).
Уменьшение нагрузки на дисковую подсистему (snapshot):
Запуская DBCC CHECKDB, современные версии SQL Server создают скрытый snapshot вашей БД на том же диске (или на тех же дисках если вы используете несколько файлов tempdb). Вы не можете контролировать данный механизм, но если вы хотите указать где именно необходимо создавать snapshot, то вы можете сделать свой snapshot на любой диск (доступно только в Enterprise Edition) и запустить DBCC CHECKDB по данному snapshot. Лучше всего пользоваться данным методом в период минимальной активности на запись-обновление вашей БД.
Вы можете ускорить DBCC CHECKDB запустив его в offline mode (с блокировками) используя опцию WITH TABLOCK. Я строго не рекомендую этим пользоваться, так как это значительно ухудшит доступность БД.
Уменьшение нагрузки на CPU:
DBCC CHECKDB запускается в параллельном режиме по-умолчанию, но только если у вас Enterprise Edition. Если у вас недостаточно CPU ресурсов, то вы можете уменьшить параллелизм несколькими способами:
- Используйте Resource Governor начиная с 2008 (доступен только в Enterprise Edition). С помощью данного механизма можно создать ресурсный пул, которому будут выделены ограниченные ресурсы и запустить процесс DBCC CHECKDB в данному пуле
- Используйте флаг 2528 чтобы отключить параллелизм для DBCC CHECKDB
- DBCC CHECKDB не поддерживает команду MAXDOP, но её можно ограничить глобальной настройкой сервера «max degree of parallelism». Будьте осторожны, эта настройка влияет на весь сервер и всю его активность.
К сожалению, Microsoft не планирует реализовывать использование MAXDOP для CHECKDB, хотя их об этом неоднократно просили.
Мои результаты:
Я хотел бы продемонстрировать влияние описанных выше способов на время выполнения DBCC CHECKDB. Тесты производились на БД AdventureWorks2012 и с запуском скрипта, который увеличил размер данной БД до 7 ГБ:
CHECKDB results against 7 GB database
Далее я увеличил размер БД до 70 ГБ и провёл тесты снова:
CHECKDB results against 70 GB database
Главные мысли после тестов:
- Когда я запускал DBCC CKECKDB с логической проверкой на боевом сервере:
- На малых БД опция
NO_INFOMSGSможет существенно снизить время выполнения, когда запускается в SSMS. На больших БД эффект уменьшается. - Оба флага трассировки оказали существенные эффект на производительность DBCC CHECKDB (40%-60% если использовать их совместно)
- На малых БД опция
- Когда я запускал DBCC CKECKDB с логической проверкой на вторичном:
- Я снизил время выполнения на 70-80% на боевой системе.
Хотелось бы продемонстрировать нагрузку на CPU во времяDBCC CHECKDB:
CPU impact during CHECKDB – sample mode
CPU impact during CHECKDB – historical mode
На больших БД результаты могут отличаться, поэтому вам обязательно надо проводить своё собственное тестирование.
Заключение:
DBCC CHECKDB очень важная и часто недооцененная задача DBA. Не совершайте ошибки других DBA.
Основано на статье.
*** *** *** *** *** *** *** *** *** *** *** ***
Полезные Скрипты
Рубрика Проверь себя
Ссылка на наш канал YouTube
