Чтобы поддерживать быстродействие СУБД Microsoft SQL Server, рекомендуется регулярно его обслуживать. Для этого планируйте работы:

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

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

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

•переиндексация таблиц

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

Microsoft SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, чтобы СУБД стабильно строила наиболее оптимальный план выполнения всех запросов.

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

Чтобы гарантировать максимально правильную работу оптимизатора Microsoft SQL Server, рекомендуется регулярно обновлять статистики базы данных. Для обновления статистик по всем таблицам базы данных в редакторе запросов выполните SQL-запрос:

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

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

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

•индекс не системный;

•измененных строк больше 1000;

•с момента последнего обновления прошло больше двух дней.

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

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

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

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

DBCC FREEPROCCACHE

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

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

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

Рекомендуется регулярное выполнение дефрагментации индексов. Для этого в редакторе запросов выполните SQL-запрос:

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

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

Переиндексация таблиц

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

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

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

После переиндексации не требуется дефрагментация индексов. Для фрагментированных индексов (больше 30%) рекомендуется выполнять перестроение, для индексов с низкой фрагментацией (менее 30%) – реорганизацию индекса.

© Компания Directum, 2024