У ваші обов'язки, як DBA, ймовірно входить оптимізація продуктивності, відновлення, настройка прав доступу і тд. Але багато хто схильний забувати про таку важливу операції, як перевірка цілісності БД (DBCC CHECKDB). Ви можете вирішити це завдання просто створивши план обслуговування «» Check Database Integrity Task », проте це всього лише checkdbox.
Як ви бачите, тут ми майже не можемо нічого контролювати, хоча для даної операції існує безліч цікавих ключів. Я думаю, що вам слід більш детально зануритися в DBCC CHECKDB і створити власне, що підходить саме вам, завдання. Основною перевагою власного завдання буде скорочення часу роботи і як наслідок зменшення необхідних ресурсів для даної операції. Так само можуть бути такі переваги як універсальність, керованість, обробка помилок і тд.
Зменшення виведення та збір всіх помилок:
Не важливо де ви запускаєте CHECKDB, завжди запускайте з опцією WITH NO_INFOMSGS. Ця проста опція пригнічує всі інформаційні повідомлення, які просто повідомляють вам як багато рядків в кожній таблиці, якщо вам необхідна ця інформація, ви можете отримати її з DMV, поза командою CHECKDB.
Використовуйте тільки фізичну перевірку даних на продуктивної середовищі:
У більшості випадків, CHECKDB витрачає основний час на логічні перевірки даних. Якщо у вас є можливість провести дану перевірку на достовірній копії даних, то ви можете сфокусуватися тільки на фізичну структуру на вашій продуктивної системі. Під достовірної копією даних я розумію ТІЛЬКИ відновлення БД з backup на іншому сервері.
Такі способи як:
- Група доступності AlwaysOn
- Snapshot по верх database mirroring
- Log Shipping
- І т.д.
Чи не є достовірною копією даних і логічна перевірка на даних технологіях, не дасть достовірний результат щодо продуктивної середовища. Тільки точно така ж копія БД може бути достовірною.
Експерименти з прапорами трасування 2549, 2562, and 2566:
Я знайшов, що прапори трасування 2549 і 2562 можуть поліпшити продуктивність CHECKDB. Знайти опис даних прапорів можна в KB # 2634571. але в цілому:
Trace Flag 2549 (оптимізує процес перевірки з розрахунку, що кожен файл даних БД лежить на своєму власному диску. Прапор можна використовувати коли БД має один файл даних або кожен файл даних лежить на своєму диску, в іншому випадку це може погіршити продуктивність CHECKDB)
Якщо ви зважитеся використовувати дані прапори, то я настійно рекомендую включати їх за допомогою 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 ресурсів, то ви можете зменшити паралелізм декількома способами:
На жаль, Microsoft не планує реалізовувати використання MAXDOP для CHECKDB, хоча їх про це неодноразово просили.
Мої результати:
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.