MS SQL Server. Оптимизация работы [Статья] - Разработка на платформах .NET Framework и 1С:Предприятие

Опубликовано: 07.10.2017

Предисловие

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

Microsoft SQL Server 2014 - Introductory Tutorial

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

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

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

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

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

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

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

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

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

MS SQL Server 2008. Обновления статистик

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

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

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