Что такое план запроса в sql
Как посмотреть план выполнения запроса в Microsoft SQL Server
Всем привет! Сегодня мы поговорим о том, как посмотреть план выполнения запроса в Microsoft SQL Server, при этом мы рассмотрим несколько способов.
Введение
План выполнения запроса – это набор конкретных действий, выполнение которых приведет SQL запрос к итоговому результату.
Иными словами, план выполнения запроса – это то, как именно будет выполняться пользовательский запрос, т.е. как именно будет осуществляться доступ к исходным данных, в каком порядке, какие конкретные методы будут использоваться для извлечения данных из каждой таблицы, какие конкретные методы будут использованы для вычислений, фильтрации, статистической обработки и сортировки данных.
Более подробно план выполнения запроса мы рассматривали в материале
Сегодня мы с Вами поговорим о том, как посмотреть план запроса и начать его анализировать. Однако сначала обязательно стоит отметить, что существует несколько типов планов запроса.
Типы планов выполнения запроса
Оптимизатор запросов Microsoft SQL Server формирует только один план выполнения для запроса, однако существует несколько типов планов выполнения запроса, которые можно отобразить с помощью SQL Server Management Studio (SSMS).
Предполагаемый план выполнения
Предполагаемый план выполнения (Estimated Execution Plan) – это план, созданный оптимизатором запросов на основе оценок.
При создании предполагаемого плана выполнения сам запрос и в целом пакеты языка Transact-SQL не выполняются, поэтому такой план не содержит фактических метрик использования ресурсов.
Вместо этого предполагаемый план отображает наиболее вероятный план выполнения запроса, которому следовал бы SQL Server при фактическом выполнении запроса, а также этот план отображает расчетное движение строк при выполнении нескольких операторов в плане.
За счет того, что запрос фактически не выполняется, это не создает никакой серьезной задержки перед отображением предполагаемого плана выполнения запроса.
Такой план удобно использовать в тех случаях, когда запрос выполняется долго, а нам необходимо посмотреть план, который собирается использовать SQL Server для данного запроса.
Действительный план выполнения
Действительный план выполнения (Actual Execution Plan) – это план, созданный оптимизатором запросов после фактического выполнения запроса. Иными словами, план становится доступным после выполнения SQL инструкции. Поэтому такой план отображает фактические метрики использования ресурсов.
Примечание! Для того, чтобы иметь возможность просматривать план выполнения запроса пользователи должны обладать соответствующими разрешениями на запуск SQL запроса, для которого создается графический план выполнения. Кроме того, пользователям должно быть предоставлено разрешение SHOWPLAN для всех баз данных, упоминаемых в запросе.
Статистика активных запросов
Статистика активных запросов (Live Query Statistics) – это план, который создаётся в режиме реального времени. Такой план доступен во время выполнения SQL запроса и обновляется каждую секунду, что позволяет нам просматривать динамический план выполнения активного запроса.
Такая возможность позволяет нам анализировать процесс выполнения запроса в режиме реального времени по мере передачи управления от одного оператора плана запроса другому.
Динамический план запроса отображает общий ход выполнения запроса и текущую статистику выполнения на уровне оператора, например, число полученных строк, затраченное время, ход выполнения оператора и т. д. Так как эти данные доступны в режиме реального времени, чтобы их увидеть, не нужно дожидаться завершения запроса, такая статистика бывает полезна для отладки проблем с производительностью запросов. Статистика активных запросов доступна с версии SQL Server 2016.
Примечание! Эта функция предназначена в основном для диагностики. Ее использование может значительно снизить общую производительность запроса.
Как посмотреть план выполнения запроса
Посмотреть план выполнения запроса можно, конечно же, с помощью SQL Server Management Studio. При этом для каждого типа используется свой способ просмотра.
Отображение предполагаемого плана выполнения запроса
Посмотреть предполагаемый план выполнения запроса можно несколькими способами, в частности:
С помощью интерфейса SSMS
В окне создания запроса на панели инструментов нажмите кнопку «Показать предполагаемый план выполнения» (Display Estimated Execution Plan).
В результате откроется вкладка «План выполнения». Сам запрос, как Вы помните, в данный момент выполняться не будет.
С помощью инструкции Transact-SQL
Тот же самый план выполнения можно получить с помощью следующей инструкции языка T-SQL
В результате, когда Вы будете запускать запрос на выполнение, вместо результирующего набора данных Вам будет возвращен XML документ, и если на него щелкнуть, т.е. открыть, то план выполнения запроса будет отображен графически, также как с помощью иконки на панели инструментов.
Чтобы выключить отображение плана необходимо установить данному параметру значение OFF.
Отображение действительного плана выполнения запроса
Фактический план выполнения запроса можно также посмотреть нескольким способами:
С помощью интерфейса SSMS
В окне создания запроса на панели инструментов нажмите кнопку «Включить действительный план выполнения» (Include Actual Execution Plan).
В результате, когда Вы выполните запрос, у Вас дополнительно к результатам добавится вкладка «План выполнения». В данном случае, как Вы понимаете, сам запрос будет выполнен, так как результирующий набор будет сформирован.
С помощью инструкции Transact-SQL
Тот же самый план выполнения можно получить с помощью следующей инструкции языка T-SQL
В результате, после выполнения запроса у Вас отобразится дополнительное окно с планом запроса формате XML. Если кликнуть на этот документ, то план выполнения запроса будет отображен графически.
Чтобы выключить отображение плана, необходимо установить данному параметру значение OFF.
Просмотр динамической статистики запросов
В окне создания запроса на панели инструментов нажмите кнопку «Включить статистику активных запросов» (Include Live Query Statistics).
В итоге в момент выполнения запроса откроется вкладка «Статистика активных запросов», на которой в режиме реального времени можно будет наблюдать ход выполнения запроса в формате плана запроса.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь, материал был Вам полезен, пока!
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
Введение в план выполнения SQL Server
Я почти всегда использую планы выполнения в качестве отправной точки для решения проблем с производительностью запросов SQL.
Планы выполнения
Для большинства запросов, производительность которых мы хотим настроить, оптимизатор запросов SQL server рассматривает множество вариантов, возвращающих требуемые данные. Он оценивает стоимость этих различных подходов и останавливается на одном, который он считает наиболее эффективным. Все эти варианты для получения данных и есть то, что называется планами выполнения запроса.
Большую часть времени этот процесс работает хорошо, и оптимизатор запросов останавливается свой выбор на плане запроса, который быстро возвращает затребованные данные. Однако это не означает, что SQL Server всегда выбирает «наиболее эффективный» план запроса. Оставляя в стороне вопрос о том, что считать «наиболее эффективным» в различных сценариях, скажем, что SQL Server не желает тратить часы, рассчитывая каждый из возможных способов вернуть нам данные, а использует только часть этого времени на поиск плана, который «достаточно хорош».
Проблемы возникают тогда, когда оптимизатор запросов выбирает план, который далеко не очень хороший. Это может вызвать большое число проблем, которые мы будем рассматривать в последующих частях этой серии статей.
Сейчас же мы поучимся получать планы выполнения наших запросов.
Просмотр планов выполнения
Чтобы увидеть план выполнения вашего запроса, выполните команду SET SHOWPLAN_ALL ON. Это даст текстовое представление дерева плана:
Это может выглядеть для вас знакомо, если вы просматривали планы в других средах реляционных баз данных, которые используют аналогичное представление плана в виде дерева. В SSMS у нас есть еще более визуальный вариант. Если щелкнуть по кнопке “Display Estimated Execution Plan” (показать предварительный план выполнения) или выполнить команду SET SHOWPLAN_XML ON, а затем ваш запрос, вы получите графический план выполнения вашего запроса:
Я предпочитаю именно это графическое представление (его я и буду использовать в демонстрационных примерах этой серии), но важно знать, что, как подсказывает последняя команда, вы можете также выполнить щелчок правой кнопкой на этом графическом плане выполнения и выбрать “Show Execution Plan XML”, чтобы увидеть все, что он представляет, в формате XML:
В то время как большинство людей не считают XML легким для просмотра по сравнению с графическим планом выполнения, полезно знать, что иногда вам придется погрузиться в XML, чтобы найти свойства, которые не отображаются в графической версии.
Актуальные планы
До сих пор каждый план, который мы видели был что называется «оценочным» планом выполнения. Название «оценочный» означает, что он содержит только «оценки» того, как много строк будет обработано, на основе внутренних метаданных, которые доступны SQL Server. Вы можете увидеть «актуальный» план выполнения, выбрав соответствующую иконку в SSMS:
Живая статистика запроса (Live Query Statistics)
Живая статистика запроса дает вам лучшее из обоих планов, оценочного и актуального. При включении Live Query Statistics, SQL Server предоставляет оценочный план выполнения, но скорректированный реальной статистикой времени выполнения вверху плана в то время как запрос выполняется в реальном времени.
Живая статистика запроса прекрасна, поскольку она позволяет вам часто увидеть, «где» в плане выполнения запроса проявляется узкое место производительности. Это особенно полезно, если вы новичок в анализе планов выполнения и еще не изучили все обычные значки и операторы, которые могут указать на плохую производительность. Это также полезно, когда ваш запрос выполняется настолько медленно, что вы не в состоянии получить актуальный план выполнения для него (т.к. кажется, что он будет выполняться вечно).
Исторические планы
Расчет планов выполнения обходится недешево, поэтому SQL Server кэширует планы выполнения для повторного использования. Эти кэшированные планы можно увидеть в динамическом представлении (DMV) sys.dm_exec_query_plan:
Важно отметить, что этот набор DMV показывает только те планы, которые еще находятся в кэше, и не показывают статистику актуального плана.
Если у вас включено хранилище запросов (Query Store) для вашей базы данных, вы можете также получит доступ к планам запросов, хранящихся в динамических представлениях Query Store, (или через его графический интерфейс):
Заключение
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
План выполнения запроса в Microsoft SQL Server – что это такое и для чего он нужен
Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы с Вами поговорим о том, что же такое план выполнения запроса в Microsoft SQL Server, узнаем, для чего он нужен и как используется, а также какие виды планов выполнения запросов бывают и почему разработчик T-SQL должен уметь работать с планами запросов.
Введение
Ранее, в предыдущем материале, мы с Вами рассмотрели архитектуру обработки SQL запросов в Microsoft SQL Server, где выяснили, что происходит с момента, когда мы нажали кнопку «Выполнить», т.е. послали SQL запрос на сервер, до того момента, когда мы увидели запрашиваемые данные. Иными словами, SQL Server перед тем, как вернуть нам результат SQL инструкции, выполняет достаточно много сложных различных операций.
Компонент, который отвечает за обработку SQL запросов в Microsoft SQL Server называется Relational Engine.
На входе данный движок принимает текст SQL запроса, а на выходе мы получаем результирующий набор данных.
Текст SQL запроса внутри компонента Relational Engine проходит через несколько этапов обработки. Можно выделить 3 основных этапа, это:
Так вот, результатом этапа Query Optimization является план выполнения запроса, о котором мы сегодня и будем говорить.
Таким образом, план выполнения запроса формируется на этапе Query Optimization, а формируется он, так называемым, многим известным «Оптимизатором запросов».
Более подробно про архитектуру обработки SQL запросов в Microsoft SQL Server можете почитать в материале
План выполнения запроса в Microsoft SQL Server
Оптимизатор запросов – это компонент Microsoft SQL Server, который отвечает за построение плана выполнения запроса.
План выполнения запроса – это набор конкретных действий, выполнение которых приведет SQL запрос к итоговому результату.
Иными словами, план выполнения запроса – это то, как именно будет выполняться пользовательский запрос, т.е. как именно будет осуществляться доступ к исходным данных, в каком порядке, какие конкретные методы будут использоваться для извлечения данных из каждой таблицы, какие конкретные методы будут использованы для вычислений, фильтрации, статистической обработки и сортировки данных.
А все дело в том, что SQL Server может выполнить запрос и получить одни и те же данные разными способами, т.е. набор физических операций в различных условиях будет отличаться.
Это связано с тем, что существует много различных алгоритмов обработки данных, каждый из которых будет работать эффективнее при определённых условиях, а на эти условия оказывает влияние очень много факторов, начиная от конфигурации системы и доступных ресурсов, и заканчивая наличием индексов и количеством данных.
Например, если в запросе участвует несколько таблиц, у SQL Server появляется несколько вариантов последовательного обращения к этим таблицам. Допустим, в запросе участвует 3 таблицы: A, B, C, так вот SQL Server может сначала извлечь данные из A, потом, используя полученные данные, обратиться к B, а затем к C. Но в то же время он может обратиться сначала к B, потом к A, а потом к C, или сначала к C, затем к A, потом к B, и различные другие варианты. В данном случае SQL Server будет перебирать возможные варианты последовательности обращения к таблицам, т.е. искать наиболее эффективный вариант.
Также в процессе этого SQL Server будет искать наиболее эффективный метод соединения данных двух таблиц, дело в том, что в синтаксисе мы пишем JOIN, но физически данные могут быть соединены с использование разных алгоритмов, таких как: NESTED LOOPS, HASH MATCH, MERGE – это физические операторы соединения таблиц.
Кроме этого, есть различные методы для извлечения данных из каждой таблицы, допустим, Вам необходимы только несколько записей с определенными значениями, то SQL Server может использовать индекс, если он существует. Если Вам необходимы все записи в таблице, то SQL Server может пропустить индексы и выполнить сканирование всей таблицы. А если есть несколько индексов с нужными Вам данными, то SQL Server будет выбирать наиболее эффективный индекс.
Таким образом, оптимизатор запросов перед тем, как сформировать итоговый план выполнения запроса, анализирует много различных факторов, и перебирает много различных планов выполнения, чтобы в конечном итоге сформировать оптимальный план. И здесь стоит отметить, что оптимизатор ищет именно оптимальный, так называемый «достаточно хороший план» выполнения запроса, дело в том, что на поиск самого лучшего плана оптимизатору может потребоваться очень много времени, т.е. в результате это может быть неэффективно, так как, допустим, оптимизатор может потратить 1 секунду на поиск самого лучшего плана, который выполнится за 0.1 секунды (т.е. суммарно запрос выполнится за 1.1 секунды), хотя, если оптимизатор не искал бы самый лучший план, он мог, например, за 0.1 секунды найти план, который выполнит запрос за 0.2 секунды (т.е. суммарно запрос выполнится за 0.3 секунды), что в конечном счете будет намного быстрее, чем при самом лучшем плане. Поэтому здесь нужен компромисс и оптимизатор ищет именно оптимальный план выполнения запроса.
В итоге работа оптимизатора заключается как раз в создании оптимального плана выполнения запроса, при котором результаты возвращаются быстрее всего и задействовано меньше всего ресурсов.
Оптимизатор запросов перед тем, как передать найденный, т.е. итоговый план запроса на выполнение, помещает этот план в кэш планов, для случаев, если этот же запрос в ближайшее время будет использован повторно.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
Стадии оптимизации запроса
Как было уже отмечено, работа оптимизатора заключается в поиске оптимального плана выполнения запроса, поэтому процесс оптимизации запроса, включает несколько этапов, в частности:
Simplification
На данном этапе происходит упрощение дерева запроса, например:
Trivial Plan Optimization
Это этап поиска тривиального плана, т.е. если запрос может быть решен единственным способом, то значит, запрос удовлетворяет условию тривиального плана и никакие правила оптимизации применять не стоит.
Full Optimization: Search 0
На этом этапе оптимизатор ищет хороший план за минимальное время. Но, данный этап может быть пропущен, и оптимизатор сразу может перейти к следующему этапу, если запрос не удовлетворяет определенным условиям.
Full Optimization: Search 1
На данном этапе используются дополнительные правила преобразования и некоторые возможные перестановки вариантов соединения данных. Если после генерации плана на этой стадии, план все еще недостаточно хорош, то данная стадия повторяется с целью поиска параллельного плана. После чего два плана сравниваются, и для оценки выбирается лучший из них. Если этот лучший план все еще не проходит внутренние пороги оптимизатора, то управление переходит к следующей стадии.
Full Optimization: Search 2
Это самый последний этап оптимизации, на котором в любом случае будет найден тот или иной план выполнения запроса.
Как определить, какой именно план найден
Если у Вас возникла необходимость узнать, на каком именно этапе оптимизатор остановил поиск плана запроса, то это можно посмотреть в плане запроса в свойствах корневого оператора.
В данном случае необходимо смотреть на свойство Optimization Level (Уровень оптимизации):
Чтобы посмотреть, по какой причине оптимизатор остановил поиск плана запроса, т.е. какой именно план был использован, необходимо смотреть на свойство Reason For Early Termination (Причина преждевременного завершения оптимизации инструкции):
Какие бывают планы выполнения запроса
Оптимизатор запросов Microsoft SQL Server формирует только один план выполнения для запроса, однако существует несколько типов планов выполнения запроса, которые можно отобразить с помощью SQL Server Management Studio (SSMS).
Предполагаемый план выполнения
Предполагаемый план выполнения (Estimated Execution Plan) – это план, созданный оптимизатором запросов на основе оценок.
При создании предполагаемого плана выполнения сам запрос и в целом пакеты языка Transact-SQL не выполняются, поэтому такой план не содержит фактических метрик использования ресурсов.
Вместо этого предполагаемый план отображает наиболее вероятный план выполнения запроса, которому следовал бы SQL Server при фактическом выполнении запроса, а также этот план отображает расчетное движение строк при выполнении нескольких операторов в плане.
За счет того, что запрос фактически не выполняется, это не создает никакой серьезной задержки перед отображением предполагаемого плана выполнения запроса.
Такой план удобно использовать в тех случаях, когда запрос выполняется долго, а нам необходимо посмотреть план, который собирается использовать SQL Server для данного запроса.
Действительный план выполнения
Действительный план выполнения (Actual Execution Plan) – это план, созданный оптимизатором запросов после фактического выполнения запроса. Иными словами, план становится доступным после выполнения SQL инструкции. Поэтому такой план отображает фактические метрики использования ресурсов.
Примечание! Для того чтобы иметь возможность просматривать план выполнения запроса, пользователи должны обладать соответствующими разрешениями на запуск SQL запроса, для которого создается графический план выполнения. Кроме того, пользователям должно быть предоставлено разрешение SHOWPLAN для всех баз данных, упоминаемых в запросе.
Статистика активных запросов
Статистика активных запросов (Live Query Statistics) – это план, который создаётся в режиме реального времени. Такой план доступен во время выполнения SQL запроса и обновляется каждую секунду, что позволяет нам просматривать динамический план выполнения активного запроса.
Такая возможность позволяет нам анализировать процесс выполнения запроса в режиме реального времени по мере передачи управления от одного оператора плана запроса другому.
Динамический план запроса отображает общий ход выполнения запроса и текущую статистику выполнения на уровне оператора, например, число полученных строк, затраченное время, ход выполнения оператора и т. д. Так как эти данные доступны в режиме реального времени, чтобы их увидеть, не нужно дожидаться завершения запроса, такая статистика бывает полезна для отладки проблем с производительностью запросов. Статистика активных запросов доступна с версии SQL Server 2016.
Примечание! Эта функция предназначена в основном для диагностики. Ее использование может значительно снизить общую производительность запроса.
Чем полезен план выполнения запроса разработчику T-SQL
Что такое план выполнения запроса в Microsoft SQL Server и как он формируется, мы рассмотрели, но кто-то может спросить – «это все хорошо, но зачем нам, как разработчикам (или администраторам), это нужно знать?».
Все дело в том, что, посмотрев на план запроса и проанализировав его, мы можем предпринять те или иные действия для увеличения быстродействия запроса.
Иными словами, план выполнения запроса в основном нам требуется для решения проблем с производительностью наших SQL инструкций.
Например, SQL запрос выполняется долго, и чтобы определить, в чем конкретная причина его долгой работы, мы можем посмотреть на план запроса, где мы можем увидеть, что у нас не используется тот или иной индекс, или подходящего индекса просто не существует, или индекс используется, но мы все равно дополнительно обращаемся в кластеризованный индекс, т.е. у нас не покрывающий индекс.
Также в плане запроса мы можем увидеть, что у нас от оператора к оператору идет достаточно большой поток данных, хотя, допустим, в результирующем наборе у нас данных не так много, поэтому мы можем сделать рефакторинг SQL кода, чтобы выполнить необходимую фильтрацию данных как можно раньше, например, предварительно сохранив их во временную таблицу.
В плане выполнения запроса дополнительно отображается информация о так называемой стоимости того или иного оператора, т.е. для каждой операции в плане запроса есть оценка ее нагрузки относительно всего запроса. Иными словами, с помощью плана запроса мы можем сразу увидеть, какие из операторов и, соответственно, операций, самые высоконагруженные в данном запросе. Хотя стоит учитывать то, что это всего лишь предварительная оценка стоимости, и иногда она не соответствует действительности (за счет действий, которые в плане запроса могут не отображаться). Но в любом случае, если какой-нибудь оператор в плане запроса занимает 99% нагрузки, то на него определенно стоит обратить внимание.
Кроме этого, в плане выполнения запроса мы можем найти и много других причин, по которым может тормозить SQL запрос и, как следствие, мы можем предпринять определенные действия, чтобы каким-то образом повлиять на выполнение запроса и, тем самым, увеличить его быстродействие.
Таким образом, с помощью плана выполнения запроса мы можем узнать, как именно выполняется наш SQL запрос, т.е. какие именно операции совершаются, и вся эта информация нужна нам для того, чтобы в случае необходимости мы могли тем или иным образом повлиять на выполнение этого запроса.
На сегодня это все, в следующем материале мы поговорим о том, как посмотреть тот или иной план выполнения запроса в среде SQL Server Management Studio.
Поэтому следите за выходом новых статей в моих группах в социальных сетях: ВКонтакте, Facebook, Одноклассники и Twitter. Подписывайтесь, и Вы не пропустите выход нового материала!