Регламентные работы на сервере MS SQL Server

Работа установленного сервера баз данных MS SQL Server во многом определяется тем, насколько грамотно и регулярно проводятся на нем регламентные задания и процедуры. От выполнения этих работ зависит стабильность и производительность работы баз данных. Регулярное выполнение регламентных работ входит в Обслуживание сервера MS SQL Server.

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

Основные регламентные работы на сервере MS SQL:

  1. Проверка целостности базы данных
  2. Обновление статистики
  3. Очистка процедурного кэша
  4. Реорганизация (дефрагментация) индексов
  5. Восстановление индекса (Реиндексация таблиц)
  6. Резервное копирование баз

Назначение и периодичность регламентных процедур

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

Любые регламентные работы имеет смысл только со "здоровой" базой данных, а для этого необходимо для проверки размещения и структурной целостности таблиц и индексов предварительно провести Проверку целостности базы данных.

Время выполнения: непосредственно перед выполнением основных регламентных операций, т.е. не реже 1 раза в сутки.

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

Опираясь на статистические данные, SQL-сервер подбирает оптимальный план запросов. Однако данные статистики не всегда оказываются актуальными на требуемый момент.

Рекомендованный период: не реже 1 раза в сутки.

Очистка процедурного кэша

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

DBCC FREEPROCCACHE

Время выполнения: сразу после обновления статистики в одном задании (т.е. не реже раза в сутки).

Дефрагментация индексов

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

Рекомендованный период: не реже 1 раза в неделю, а при большой нагрузке и раз в сутки.

Реиндексация таблиц БД

Реиндексация позволяет существенно повысить производительность системы в целом. Во время реиндексации выполняется полное перестроение индексов таблиц. Поскольку индексы формируются заново, то после реиндексации смысла проводить дефрагментацию индексов просто нет.

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

Рекомендованный период: не реже 1 раза в неделю.

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

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

Рекомендуемый период: не реже 1 раза в сутки.

Настройка регламентных работ

Создаем План обслуживания

Настройка регламентных работ на SQL-сервере проводим в MS SQL Server Management Studio. Подключаемся к сервер и заходим в папку "Управление -> Планы обслуживания". Создать план обслуживания можно "вручную" или при помощи мастера, часто получается комбинация этих способов.

Обновление статистики и Очистку процедурного кэша делаем в одном плане, например раз в сутки на час ночи. Обновление статистики делаем при помощи мастера для всех баз, открываем полученное задание и добавляем с Панели элементов еще один элемент «Задача "Выполнение инструкции T-SQL"». Открыв двойным щелчком, прописываем в него скрипт для очистки кеша, а затем соединяем стрелочкой для указания правильной последовательности выполнения.

Обновление статистики и Очистка процедурного кэша

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

Имеет смысл объединить эти задания в один План обслуживания (например, назвав его «Индексы»), но для каждого создать отдельный Вложенный план со своим Расписанием вложенного плана.

Оптимизация выполнения регламентных работ

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

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

Более детально об оптимизации регламентных работ – в нашей следующей статье.