21 Май
Работа установленного сервера баз данных MS SQL Server во многом определяется тем, насколько грамотно и регулярно проводятся на нем регламентные задания и процедуры. От выполнения этих работ зависит стабильность и производительность работы баз данных. Регулярное выполнение регламентных работ входит в Обслуживание сервера MS SQL Server.
Выполнение регламентных работ проводятся штатными средствами самого сервера SQL без необходимости писать специальные скрипты, хотя и не исключает их использование. Вопрос сводится к грамотному подходу в настройке и использовании этих средств. Обслуживание должно быть максимально незаметным для пользователей, оптимальное время выполнения – это ночное время.
Основные регламентные работы на сервере MS SQL:
- Проверка целостности базы данных
- Обновление статистики
- Очистка процедурного кэша
- Реорганизация (дефрагментация) индексов
- Восстановление индекса (Реиндексация таблиц)
- Резервное копирование баз
Назначение и периодичность регламентных процедур
Проверка целостности базы данных
Любые регламентные работы имеет смысл только со “здоровой” базой данных, а для этого необходимо для проверки размещения и структурной целостности таблиц и индексов предварительно провести Проверку целостности базы данных.
Время выполнения: непосредственно перед выполнением основных регламентных операций, т.е. не реже 1 раза в сутки.
Обновление статистики
Опираясь на статистические данные, SQL-сервер подбирает оптимальный план запросов. Однако данные статистики не всегда оказываются актуальными на требуемый момент.
Рекомендованный период: не реже 1 раза в сутки.
Очистка процедурного кэша
Для обеспечения лучшей производительности системы при обработке запроса кеширует данные плана запроса, на случай если такой запрос повторится, а план его известен. Но иногда это может и помешать оптимальному выполнению запроса, если статистика обновилась, а новый оптимальны план для нее построен не будет. Для выполнения Очистки процедурного кэша необходимо выполнить следующий SQL запрос:
DBCC FREEPROCCACHE
Время выполнения: сразу после обновления статистики в одном задании (т.е. не реже раза в сутки).
Дефрагментация индексов
Так же как и фрагментация файлов при частом их изменении, приводит к снижению производительности файловых операций, так и фрагментация индекса, возникающая при большой нагрузке на СУБД, приводит к снижению производительность системы в целом. При общем уровне фрагментации индекса базы более 25% наблюдается резкое падение производительности сервера баз данных.
Рекомендованный период: не реже 1 раза в неделю, а при большой нагрузке и раз в сутки.
Реиндексация таблиц БД
Реиндексация позволяет существенно повысить производительность системы в целом. Во время реиндексации выполняется полное перестроение индексов таблиц. Поскольку индексы формируются заново, то после реиндексации смысла проводить дефрагментацию индексов просто нет.
Поскольку операция проводится только в монопольном режиме и при выполнении блокирует таблицы баз MS SQL, то логично проводить ее в нерабочее время, например ночью. Все остальные операции проводятся в фоновом режиме без монопольного захвата таблиц.
Рекомендованный период: не реже 1 раза в неделю.
Резервное копирование баз
Своевременное копирование баз данных – залог крепких нервов администратора сервера. Периодичность и тип резервного копирования определяется прежде всего интенсивностью изменений в базах и критичностью данных. По-хорошему, Резервное копирование и восстановление баз данных – это тема для отдельной статьи.
Рекомендуемый период: не реже 1 раза в сутки.
Настройка регламентных работ
Настройка регламентных работ на SQL-сервере проводим в MS SQL Server Management Studio. Подключаемся к сервер и заходим в папку “Управление -> Планы обслуживания”. Создать план обслуживания можно “вручную” или при помощи мастера, часто получается комбинация этих способов.
Обновление статистики и Очистку процедурного кэша делаем в одном плане, например раз в сутки на час ночи. Обновление статистики делаем при помощи мастера для всех баз, открываем полученное задание и добавляем с Панели элементов еще один элемент «Задача “Выполнение инструкции T-SQL”». Открыв двойным щелчком, прописываем в него скрипт для очистки кеша, а затем соединяем стрелочкой для указания правильной последовательности выполнения.
Задачи Дефрагментация индексов и Реиндексация таблиц – это по-сути, некоторым образом две взаимоисключающие задачи, поскольку обе выполняют дефрагментацию индексов таблиц баз данных. Поэтому Реиндексацию согласно рекомендации можем проводить раз в неделю в воскресенье ночью, а Дефрагментацию среди недели. Можно сроки цикличности варьировать, можно разделить объекты на группы и задавать частоту заданий отдельно для каждой. В любом случае необходимо руководствоваться здравым смыслом и степенью нагрузка на базы и их таблицы. Для того, что бы просмотреть, какие операции и с какой периодичностью требуются индексу, необходимо периодически проверять физическую статистику индекса: правой кнопкой мыши на базе данных и переходим в Отчеты -> Стандартные отчеты -> Физическая статистика индекса.
Имеет смысл объединить эти задания в один План обслуживания (например, назвав его «Индексы»), но для каждого создать отдельный Вложенный план со своим Расписанием вложенного плана.
Оптимизация выполнения регламентных работ
В самом простом виде каждое задание можно настроить в виде отдельного Плана обслуживания с индивидуальным расписанием. Однако, куда более разумнее группировать задания в общие планы обслуживания. Группировка заданий может быть выполнена по разным признакам: общему расписанию (ежедневные задачи или еженедельные), по последовательности или зависимости выполнения и по другим критериям.
Для наиболее часто изменяемых таблиц можно настроить периодичность регламентных заданий чаще, для всех остальных стандартно раз в сутки. Такой подход позволит распределить выполнение операций во времени, снизив нагрузку на сервер во время их проведения, и в тоже время повысить актуальность и производительность работы системы.
Более детально об оптимизации регламентных работ – в нашей следующей статье.
