Применение административных представлений и функций MS SQL 2005 при оптимизации производительности запросов T-SQL   

В MS SQL Server 2005 возможности административных представлений (DMV) и функций (DMF) существенно расширились. В частности, появилась возможность с их помощью получать развёрнутую информацию по кэшированным планам выполнения запросов.

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

Также следует отметить, что, хотя в статье нет упоминаний про 1С 8.1 и 1С 8.2, и не рассматривается специфика запросов 1С 8.1 и 1С 8.2, сделанные общие выводы полностью актуальны для этой платформы, поскольку в информационных системах на её основе, как и в любых других, достаточно регулярно возникают те или иные проблемы с производительностью T-SQL-запросов.

Содержание статьи

• Приведён T-SQL запрос, с помощью которого можно получить развёрнутую информацию по кэшированным планам выполнения. Дано краткое описание используемых в запросе объектов (sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_query_plan).

• Описан алгоритм действий, с помощью которого можно из плана выполнения запроса в XML-формате получить его графическое представление (*.sqlplan).

• Приведены показатели производительности запроса, в котором представление sys.dm_exec_query_stats пересекается с функциями sys.dm_exec_sql_text и sys.dm_exec_query_plan.

• Описано ограничение на вывод планов выполнения с количеством уровней вложенности более 128.

• Сделаны общие выводы о возможностях, предоставляемых описанными административными представлениями и функциями.

Запрос к административным представлениям

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

Например, приведённый ниже запрос вернёт развёрнутую статистику по производительности кэшированных планов выполнения, а также сами планы в XML-формате и тексты соответствующих T-SQL-запросов:

select *

from sys.dm_exec_query_stats AS s1

cross apply sys.dm_exec_sql_text(s1.sql_handle) AS s2

cross apply sys.dm_exec_query_plan(s1.plan_handle) as s3

Краткое описание использованных в запросе объектов:

sys.dm_exec_query_stats - динамическое административное представление (DMV), содержит объединенную статистику производительности для кэшированных планов запросов.

sys.dm_exec_sql_text - динамическая административная функция (DMF), возвращает текст пакета SQL. Входной параметр функции – дескриптор пакета SQL (sql_handle).

sys.dm_exec_query_plan - динамическая административная функция (DMF), возвращает в XML-формате план выполнения пакета SQL. Входной параметр функции - дескриптор плана выполнения (plan_handle).

Получение графического плана

Приведённый запрос возвращает только XML-формат планов выполнения, а для эффективного анализа необходимо графическое представление планов (*.sqlplan). Его можно получить из XML-формата, проделав следующие манипуляции.

• Щелкнуть элемент ShowPlanXML в столбце query_plan результирующей таблицы. При этом в сводной области среды Management Studio откроется план выполнения в формате XML;

• Сохранить открывшийся план выполнения в отдельном файле формата XML, для этого выбрать в главном меню Management Studio элемент "ФайлСохранить как…", сохранить план в xml-файле;

• Изменить расширение полученного файла с XML на sqlplan, в результате должно получить имя файла в формате <имя_файла>.sqlplan. Например: MyPlan.sqlplan;

• Далее полученный файл *.sqlplan нужно открыть в Management Studio, при этом в сводной области отобразится план выполнения в графическом виде:
  

Рис3. Графическое представление плана выполнения запроса.

Получение текста запроса

Саму Transact-SQL конструкцию, которой соответствует полученный план выполнения, при необходимости можно скопировать из поля Text результирующей таблицы, и вставить в новую вкладку сводной области Management Studio. При этом запрос будет вытянут в одну строку. Чтобы привести его к удобочитаемому виду, придётся форматировать вручную.

Показатели производительности запроса к DMV и DMF

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

select *

from sys.dm_exec_query_stats AS s1

cross apply sys.dm_exec_sql_text(s1.sql_handle) AS s2

cross apply sys.dm_exec_query_plan(s1.plan_handle) as s3

На двух разных боевых серверах клиентов показатели производительности данного запроса получились следующие:

 Таблица 1. Показатели производительности запроса.

Рис4. Показатели производительности запроса. 1-ый сервер первый запуск запроса.

Рис5. Показатели производительности запроса. 1-ый сервер повторный запуск запроса.

Рис6. Показатели производительности запроса. 2-ой сервер первый запуск запроса.

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

Ограничение на количество уровней

Следует заметить, функция sys.dm_exec_query_plan не может возвратить планы запросов, содержащие 128 и более уровней вложенных элементов. Это обусловлено тем, что тип данных xml имеет ограничение количества уровней вложенности – не более 128. В ранних версиях SQL Server 2005 это условие препятствует возврату плана запроса и формирует ошибку 6335. В версии с пакетом обновления 2 (SP2) ошибки не возникает, а столбец query_plan возвращает значение NULL.

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

Выводы

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

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

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

Конечно, почти все перечисленные ограничения и недостатки (кроме высоких требований к квалификации) не так критичны, когда количество оптимизируемых запросов не велико, а база данных не слишком большая. Но, с одной стороны, с ростом объёма базы данных возрастает ответственность и цена ошибки при принятии решений. А с другой стороны, с увеличением количества запросов, в отсутствие средств хотя бы предварительного автоматического анализа, время и трудозатраты на ручную работу и поштучный анализ вырастают кратно, и могут стать буквально не приемлемыми.

Компания "СофтПоинт" разработала собственный инструментарий для помощи в оптимизации тяжелых запросов на MS SQL Server 2005/2008,. Помощник используется для оптимизации запросов, как в 1С 7.7, 8.1, так и в произвольных системах работающих на MS SQL Server 2005/2008. При этом инструментарий позволяет не только определять узкие места в запросах с детализацией до «узла» плана выполнения и предикатов, но и рекомендует какой индекс следует добавить, а также дает рекомендации по оптимальному синтаксису и оптимальной структуре запросов. Помощник умеет анализировать временные таблицы, которые в спектре запросов 1С 8.1 могут составлять более 50%. В ближайшее время выйдет статья с реальными примерами оптимизации, следите за новостями на нашем сайте.