MS SQL Server. Оптимизация работы

19 декабря 2012

Предисловие

Платформа 1С:Предприятие 8.x поддерживает работу с несколькими СУБД. Самым используемым является продукт компании Microsoft.

В статье будут рассмотрены распространенные причины неоптимальной работы SQL-сервера и пути их решения на примере MS SQL Server 2008.

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

По опыту, самыми распространенными причинами неоптимальной работы SQL-сервера являются:

  1. Неактуальная статистика о распределении значений и индексов в таблице базы данных.
  2. Устаревание процедурного КЭШа планов запросов.
  3. Высокая фрагментация индексов таблиц.
  4. Периодическая необходимость в перестроении всех индексов таблиц.

Теперь по порядку.

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

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

Достаточно интенсивная работа с базой данных снижает актуальность собранной статистики  что не позволяет оптимизатору создавать оптимальные запросы к БД. Поэтому необходимо проводить обновление статистик. Делается это с помощью следующей команды:

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

Наибольшее влияние статистики распределения данных на производительность СУБД можно увидеть с применением вложенных запросов. Если платформа 1С:Предприятие 8.x сформирует запрос к SQL-базе многоуровневой вложенности, плюс к этому еще и соединением двух вложенных запросов, то можно утверждать, что скорость его работы напрямую зависит от актуального состояния собранной статистики.Для поддержки статистик в актуальном состоянии рекомендуется создать регламентное задание,  которое будет производить обновление статистик в указанное время. Поскольку данный процесс не блокирует таблицы БД, то его можно запускать практически в любое время. Ниже на видео демонстрируется настройка плана обслуживания по обновлению статистик средствами СУБД MS SQL Server 2008.

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

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

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

DBCC FREEPROCCACHE

Ниже Вы можете посмотреть на видео процесс добавления к уже созданному субплану обновления статистик - задачу очистки процедурного кэша.

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

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

Интенсивная работа с базой данных приводит к снижению эффективности использования индексов из-за фрагментации. Поэтому рекомендуется регулярно проводить дефрагментацию индексов. Если необходимо провести дефрагментацию для всех таблиц некоторой базы, то можно использовать следующую команду:

sp_msforeachtable N'DBCC INDEXDEFRAG ([ИМЯ БАЗЫ ДАННЫХ], ''?'')'

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

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

РЕИНДЕКСАЦИЯ ТАБЛИЦ

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

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

Ниже вы можете посмотреть видео по добавлению к существующему плану обслуживания задачи реиндексации таблиц БД.

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

Выводы

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

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


comments powered by Disqus