Применение административных представлений и функций 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-запросов. Содержание статьи
Запрос к административным представлениям Очень ценной для решения проблем производительности запросов является информация по кэшированным планам выполнения, которую можно получить с помощью динамических административных представлений (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-формата, проделав следующие манипуляции.
Рис1. Результирующая таблица, столбцы text и query_plan.
Рис2. XML-формат плана выполнения.
Рис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-формата планов выполнения к графическому виду. Административные представления и функции можно успешно использовать при решении проблем производительности запросов: получать и анализировать планы выполнения запросов и статистику по ним, принимать решения об оптимизации конструкции запросов, изменении индексов, статистик и т.д. Однако даже для анализа отдельных планов этот инструмент имеет определённые недостатки и ограничения. А при необходимости анализа эффективности большого количества планов, значимость этих недостатков возрастает. Во-первых, возможно только поштучное ручное получение графического представления интересующего плана, процесс этот кропотливый и не удобный. Но без графического представления, как правило, не обойтись. Без него крайне сложно оценить эффективность плана. При большом количестве запросов, требующих детального анализа, этот недостаток серьёзно увеличивает время и трудозатраты на дополнительные подготовительные работы. Во-вторых, есть ограничение на вывод планов с количеством уровней вложенных элементов более 128. Это ограничение делает невозможным анализ особенно больших планов выполнения, которые с высокой вероятностью могут быть интересны с точки зрения оптимизации. В-третьих, сами по себе запросы, получающие информацию из административных представлений, довольно тяжёлые. При оценке единичных планов в большинстве случаев это будет не существенно. Но при анализе большого количества планов выполнения потребуется запускать этот запрос многократно, что существенно увеличит дополнительную нагрузку, и может негативно повлиять на производительность системы. И последнее, самое главное, этот инструмент только предоставляет информацию, не давая никаких рекомендаций. Поэтому весь анализ и выработка решений по оптимизации ложится на администратора. Это тонкая и кропотливая ручная работа, зачастую очень трудоёмкая. Она требует от специалиста высокой квалификации и опыта. Это особенно важно в случае больших баз данных, потому что при многомиллионных таблицах решение об изменении структуры запросов, изменении структуры существующих индексов, или создании новых, является очень ответственным. Ошибка при этом может иметь очень серьёзные последствия для производительности всей системы. Конечно, почти все перечисленные ограничения и недостатки (кроме высоких требований к квалификации) не так критичны, когда количество оптимизируемых запросов не велико, а база данных не слишком большая. Но, с одной стороны, с ростом объёма базы данных возрастает ответственность и цена ошибки при принятии решений. А с другой стороны, с увеличением количества запросов, в отсутствие средств хотя бы предварительного автоматического анализа, время и трудозатраты на ручную работу и поштучный анализ вырастают кратно, и могут стать буквально не приемлемыми. Компания "СофтПоинт" разработала собственный инструментарий для помощи в оптимизации тяжелых запросов на MS SQL Server 2005/2008,. Помощник используется для оптимизации запросов, как в 1С 7.7, 8.1, так и в произвольных системах работающих на MS SQL Server 2005/2008. При этом инструментарий позволяет не только определять узкие места в запросах с детализацией до «узла» плана выполнения и предикатов, но и рекомендует какой индекс следует добавить, а также дает рекомендации по оптимальному синтаксису и оптимальной структуре запросов. Помощник умеет анализировать временные таблицы, которые в спектре запросов 1С 8.1 могут составлять более 50%. В ближайшее время выйдет статья с реальными примерами оптимизации, следите за новостями на нашем сайте. |
||||||||||||||||
Статья: Применение административных представлений и функций MS SQL 2005 при оптимизации производительности запросов T-SQL | ||||||||||||||||
Перейти на главную страницу компании "Софтпоинт" |
||||||||||||||||