Автоматизация обслуживания баз данных в СУБД SQL Server

При постоянном использовании базы данных увеличиваются объемы данных, усложняется функционал, количество пользователей возрастает. Чтобы сохранять производительность базы данных в хорошем состоянии, необходимо постоянно выполнять работы по обслуживанию БД и серверов БД.
Основными действиями по обслуживанию БД являются следующие операции:

1) обслуживание Индексов;

2) обновление Статистики;

3) чистка процедурного Кэша.

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

В качестве системы управления баз данных используется MS SQL Server 2008 R2. База данных: «buh».

Разработка скрипта для резервного копирования баз данных

Одной из важнейших задач для обеспечения защищенности и доступности баз данных организации является резервное копирование (рисунок 1).

Скрипт резервного копирования SQL Server

Рисунок 1 – Скрипт резервного копирования

Этот скрипт создает резервную копию с именем файла buh_YYYYDDMM.bak, где YYYYDDMM – это текущая дата. Дата в имени файла позволит нам создавать каждый день резервное копирование в новом файле.

Разработка скрипта для обслуживания индексов баз данных

Индексы позволяют быстро получить доступ к нужным данным без поиска по всей базе данных. В SQL Server 2008 индексы создаются для таблиц, представлений и столбцов. Создав индекс для таблицы, вы ускоряете поиск данных в таблице.

Со временем без обслуживания индексов накапливаются следующие проблемы:

  • индексы становятся сильно фрагментированными и перемешанными;
  • часть данных строк, когда-то участвовавших, в индексе уже удалена, и из-за этого индекс занимает на диске больше места и требует при выполнении запросов больше операций ввода-вывода.

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

В системе 1С:Предприятие 8 есть встроенные средства для выполнения операции по обслуживанию индексов. Однако эта операция слишком ресурсоемка. Поэтому предлагается выполнять скрипт на языке T-SQL, который определяет наиболее оптимальное решение для каждого из индексов. Выбор оптимального решения основан на рекомендациях Microsoft: если общая фрагментация индекса больше 30%, то выполнять перестроение индексов, в противном случае выполнять менее ресурсоемкую операцию – дефрагментацию. Вследствие чего, значительно снижается длительность выполнения такой операции и требования к ресурсам сервера.

Скрипт автоматически реорганизует или перестраивает все секции индексов в базе данных со средней степенью фрагментации более 10 процентов. На рисунке 2 представлена подготовительная часть скрипта. На рисунке 3 представлена реорганизация и перестроение индексов.

Рисунок 2 – Подготовительная часть скрипта

Рисунок 2 – Подготовительная часть скрипта

Реорганизация и перестроение индексов SQL Server

Рисунок 3 – Реорганизация и перестроение индексов

Скрипт для обновления статистики

Статистика – очень важный механизм в MS SQL Server. Для эффективного выполнения запросов необходимо постоянно поддерживать статистику для каждой из баз данных в актуальном состоянии. Чтобы минимизировать потерю производительности, необходимо вручную запускать команду по обновлению всей статистики в базе данных. Особенно это нужно выполнять сразу же после массовых изменений. В MS SQL Server существует специальная системная процедура, которая будет обновлять только ту статистику, которая требует обновления, тем самым предотвращая ненужные обновления статистики по неизменным срокам: sp_updatestats (рисунок 4).

Скрипт для обновления статистики SQL Server

Рисунок 4 – Скрипт для обновления статистики

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

Процедурный кэш – это область оперативной памяти, зарезервированная для сервера MS SQL Server, в которой содержаться планы выполнения запросов. Если при выполнении запроса подходящий план для него не будет найден, то произойдет компиляция этого запроса и скомпилированный план будет помещен в кэш. Эта операция требует дополнительных ресурсов. Поэтому если на сервере установлен достаточный объем оперативной памяти и в качестве сервера баз данных используется MS SQL Server 2005 и более новые версии, то ручную чистку процедурного кэша следует выполнять при крайней необходимости. Выполнять такую операцию следует в следующих случаях:

  • выполнено обновление статистики для всей базы данных;
  • выполнено изменение индексов (перестроение или дефрагментация) для всей базы данных.

В этих случаях для всех запросов в этой базе данных автоматически будет использована перекомпиляция. Так что имеет смысл освободить оперативную память от уже ненужных планов (рисунок 5).

Чистка процедурного кэша SQL Server

Рисунок 5 – Чистка процедурного кэша

Автоматизация процесса выполнения скриптов.

Чтобы автоматизировать процесс выполнения описанных операций нужно воспользоваться системой SQL Server Agent:

1) Добавить новое Задание (Job) и задать имя «Обслуживание БД [ИмяБазы]»;

2) В задании перейти на вкладку Шаги (Steps) и добавить новый шаг с названием «Обслуживание Индексов». В свойствах шага указать типа= «Transact-SQL script» (по умолчанию). Скопировать скрипт из раздела Обслуживание индексов (заменив [ИмяБазы] на имя конкретной базы данных) и вставить его в поле Команда в шаге задания. Сохранить этот шаг;

3) Добавить второй шаг задания с названием «Обновление статистики». В свойствах шага указать типа= «Transact-SQL script» (по умолчанию). Скопировать скрипт из раздела Обновление статистики (заменив [ИмяБазы] на имя конкретной базы данных) и вставить его в поле Команда в шаге задания. Сохранить этот шаг;

4) Добавить третий шаг задания с названием «Чистка процедурного кэша». В свойствах шага указать типа= «Transact-SQL script» (по умолчанию). Скопировать скрипт из раздела Чистка процедурного кэша и вставить его в поле Команда в шаге задания. Сохранить этот шаг;

5) В задании перейти на вкладку Расписания (Shedules) и нажать кнопку Создать(New). Расписание для задания необходимо составить следующим образом: каждый рабочий день, рано утром в 05:00 (чтобы все операции были выполнены перед началом рабочего дня);

6) Сохранить расписание и сохранить задание;

7) На следующий рабочий день проверить по журналу заданий, что новое задание «Обслуживание БД [ИмяБазы]» успешно выполнилось.

Эти действия нужно выполнить для каждой пользовательской базы данных (на каждую базу, должно быть свое задание со своим расписанием, расписания не должны пересекаться по времени!)

Расскажите друзьям

Вам может быть так же интересно:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *