Регламентные операции на СУБД уровне. MS SQL Server

Общая информация

Наиболее распространенная причина, по которой система работает не оптимально,  - это несвоевременное или некорректное выполнение регламентных операций на СУБД уровне. В особенности важно корректно выполнять регламентные процедуры в масштабных информационных системах, работающих под огромной нагрузкой и обслуживающих параллельно значительное число пользователей. Главная особенность этих систем состоит в том, что обычных действий, которых СУБД выполняет автоматически (в соответствии с обозначенными настройками) оказывается недостаточно для корректной и  эффективной работы.

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

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

Рекомендовано выполнение следующих регламентных операций для MS SQL Server:

• Очистка процедурного КЭШа
• Обновление статистик
• Реиндексация таблиц баз данных
• Дефрагментация индексов

Необходимо постоянно контролировать правильность и своевременность выполнения указанных регламентных процедур.

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

В MS SQL Server план запроса строится на основе статистической информации о распределении значений в таблицах и индексах. Статистическая информация собрана на основе образца (части) данных и будут автоматически обновлена в случае изменения этих самых данных. Этого может оказаться недостаточно в определенных случаях для того, чтобы MS SQL Server корректно и стабильно строил самый оптимальный план исполнения всех запросов.

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

Чтобы гарантировать наиболее правильную работу MS SQL Server оптимизатора  необходимо проводить регулярное обновление статистик баз данных MS SQL.

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

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

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

Оптимальную частоту обновления статистик определяют в зависимости от характера и величины нагрузки на систему. Она определяется экспериментально. Обычно рекомендуется проводить обновление статистик не реже раза в 24 часа.

Указанный выше запрос обновит статистики для всех рабочих таблиц вашей базы данных. В системе же обычно различные таблицы требуют разной частоты обновления. Анализируя планы запроса, устанавливается, какие из таблиц более других  испытывают потребность в обновлении статистик. Далее настраиваются две (иногда более) различных регламентных процедуры: одна для часто обновляемых таблиц, другая -  для всех прочих. Этот подход позволит значительно снизить время, затрачиваемое на обновление статистик, а также влияние самого процесса на работоспособность системы.

Настройки автоматического обновления статистик для MS SQL 2005

Для начала нужно запустить MS SQL Server ManagementStudio и подключиться к  СУБД серверу. Затем открыть папку Management и создать новый план обслуживания:

Теперь создавайте субплан (команда Add Sublan) и называйте его, к примеру, «Обновление статистик». Добавляйте задачу Update Statistics Task, воспользовавшись  панелью задач:

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

Осталось настроить параметры задачи. Нужно два раза кликнуть по задаче в нижнем правом углу окна. В форме, которая появится на экране, необходимо указать имя вашей базы (либо нескольких баз), для которых будет осуществляться процесс обновления статистик. Также вы сможете указать, для каких конкретно таблиц необходимо обновлять статистики (устанавливайте значение All, если не проводили анализ нагрузок).

Обязательно проводить обновление статистик, включив опцию Full Scan.

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

Очистка процедурного КЭШа

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

Однако может возникнуть ситуация, когда MS SQL Server, ориентируясь на старую статистическую информацию, выстроит неоптимальный план запроса. Такой план будет сохранен затем в процедурном КЭШе и использован в случае повторного вызова аналогичного запроса. Если статистика была обновлена, но процедурный кэш не очищен, то SQL Server-ом будет выбран старый (т.е. неоптимальный) план запроса, находящийся в КЭШе, а новый (оптимальный) построен не будет.

В связи с этим, рекомендуется после обновления статистик всегда осуществлять очистку процедурного КЭШа.

Для этого в MS SQL Server выполните указанный ниже SQL запрос:

DBCC FREEPROCCACHE

Запрос этот нужно выполнять сразу же после обновления статистики. Таким образом, частота выполнения очистки КЭШа должна соответствовать частоте обновления статистики.

Настройка очистки КЭШа в MS SQL 2005

Т.к. процедурный КЭШ, как уже упоминалось, нужно очищать после каждого обновления статистики, эту операцию рекомендуем добавить в созданный ранее субплан «Обновление статистик».

Откройте субплан и добавьте в его схему задачу под названием Execute T-SQL Statement Task. Далее соедините задачу с Update Statistics Task при помощи стрелочки.

Текст созданной Вами задачи Execute T-SQL Statement Task должен содержать запрос «DBCC FREEPROCCACHE»:

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

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

Рекомендуется регулярно выполнять дефрагментации индексов. Чтобы провести дефрагментацию всех индексов для всех таблиц базы нужно воспользоваться указанным ниже SQL запросом (имя базы прописав предварительно):

sp_msforeachtable N'DBCC INDEXDEFRAG (<здесь имя базы данных>, ''?'')'

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

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

Настройка дефрагментации индексов для MS SQL 2005

В уже созданном ранее плане обслуживания нужно создать новый субплан с названием, к примеру, «Дефрагментация индексов». Затем добавить туда задачу Reorganize Index Task:

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

Настраивайте задачу, указывая базу данных (либо же несколько баз данных) и выбирая необходимые таблицы. Устанавливайте значение All, если не проводили анализ на определение конкретных таблиц, нуждающихся в процедуре.

Реиндексация таблиц баз данных

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

sp_msforeachtable N'DBCC DBREINDEX (''?'')'

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

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

Настройка реиндексации таблиц для MS SQL 2005

В созданном ранее плане необходимо создать новый субплан, назвав его «Дефрагментация индексов». Далее добавить задачу с названием Rebuild Index Task:

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

И наконец, как и в прошлых случаях, настраивается задача, с указанием базы данных (либо несколько баз данных) и выбором необходимых таблиц. Устанавливайте значение All, если не проводили анализ на определение конкретных таблиц, нуждающихся в процедуре.

Контроль за выполнения регламентных процедур на СУБД уровне

Осуществляйте регулярный контроль над выполнением регламентных процедур на СУБД уровне. Ниже приводится пример контроля над выполнением плана обслуживания MS SQL Server 2005.

Открывайте созданный вами ранее план обслуживания, там выбирайте в меню пункт «View History»:

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

Задачи, выполненные с ошибками, и успешно выполненные задачи будут отмечены характерными иконками. Для задач, которые были выполнены с ошибками, доступен просмотр подробной информации об ошибке.