Настройка и оптимизация регламентных заданий MS SQL Server

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

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

Базовый суточный план обслуживания

Создаем новый План обслуживания и назовем его просто "Ежедневное обслуживание". С Панели элементов натаскаем в Область конструктора необходимые нам задания: "Проверка целостности базы данных", "Обновление статистики", "Выполнение инструкции T-SQL", "Резервное копирование базы данных" и "Очистка после обслуживания". Для задания последовательности выполнения задания соединим блоки схемы стрелочками. Кроме последовательности выполнения можно задать и условие выполнения этой последовательности. Щелкнув правой кнопкой мышки на линии связи, выбираем условие: "Успешное завершение", "Ошибка" или просто "Выполнение". Выполнение заданий имеет смысл только в случае успешного завершения тестирования баз, а Очистку кеша - после Обновления статистики. Остальные задания выполняются независимо от результата выполнения предыдущего задания.

Настроим каждую задачу:

Проверка целостности базы данных

Проверку выполняем для всех баз, включая индексы

Обновление статистики

Для всех баз, для все статистики в режиме полного просмотра.

Выполнение инструкции T-SQL

В окне скрипта указываем команду очистки кеша DBCC FREEPROCCACHE

Резервное копирование базы данных

В большинстве случаев достаточно выполнять ежесуточно Полное копирование для Всех пользовательских баз. Зададим папку для сохранения баз - место хранения бакапов должно быть хотя бы на другом диске, а в идеале вообще на другом дисковом массиве. Если баз много, лучше копировать каждую базу в отдельную папку: ставим птичку "Создавать вложенный каталог для каждой базы данных". Весьма полезным будет "Проверять целостность резервной копии" и "Сжимать резервные копии".

Очистка после обслуживания

Чтобы не засорять дисковое пространство старыми неактуальными резервными копиями, необходимо их периодически подчищать. Указываем в задании, что мы будем чистить файлы резервных копий, папку хранения, расширения файлов и срок хранения копий. В выборе срока хранеия необходимо руководствоваться размером копий, доступного места и здравым смыслом. Думаю, вряд ли копии, старше 2 недель имеют какую либо актуальность при исправном выполнении регулярного Резервного копирования.

В добавок, можно переименовать каждое задание по своему усмотрению – F2 или два щелчка по блоку задания.

Схема алгоритма настроенного Плана обслуживания

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

Еженедельные планы

Свойства вложенного плана

Для борьбы с фрагментацией индексов создадим новый план "Недельное обслуживание" и добавим в него два вложенных плана для заданий "Реорганизация (дефрагментация) индексов" и "Перестроение индекса (Реиндексация таблиц)". Для первого зададим периодичность еженедельно по средам на 3 часа ночи, а для второго по воскресеньям тоже на 3 часа ночи. Таким образом мы получаем фактически дефрагментацию 2 раза в неделю. Если потребности в такой частой дефрагментации индексов нет, то можно, например, задать для дефрагментации расписание еженедельно по воскресеньям, а для реиндексации - каждую 4-ю субботу.

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

Объединение и группировка планов обслуживания

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