Что такое план запроса
План выполнения запроса в 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. Подписывайтесь, и Вы не пропустите выход нового материала!
Содержание
Создание планов запросов
Данная система управления базой данных может предлагать один или несколько механизмов для возврата плана для данного запроса. В некоторых пакетах есть инструменты, которые генерируют графическое представление плана запроса. Другие инструменты позволяют установить специальный режим для соединения, чтобы СУБД возвращала текстовое описание плана запроса. Другой механизм для получения плана запроса включает запрос таблицы виртуальной базы данных после выполнения запроса, который необходимо изучить. В Oracle, например, этого можно достичь с помощью оператора EXPLAIN PLAN.
Графические планы
Пользовательский интерфейс позволяет исследовать различные атрибуты операторов, задействованных в плане запроса, включая тип оператора, количество строк, потребляемых или производимых каждым оператором, и ожидаемую стоимость работы каждого оператора.
Текстовые планы
Текстовый план, приведенный для того же запроса на скриншоте, показан здесь:
Это указывает на то, что механизм запросов выполнит сканирование индекса первичного ключа в таблице Employee и поиск совпадений по индексу первичного ключа (столбец ContactID) в таблице контактов для поиска совпадающих строк. Результирующие строки с каждой стороны будут показаны оператору соединения вложенных циклов, отсортированы, а затем возвращены в качестве набора результатов для соединения.
Чтобы настроить запрос, пользователь должен понимать различные операторы, которые может использовать база данных, и какие из них могут быть более эффективными, чем другие, при этом обеспечивая семантически правильные результаты запроса.
Настройка базы данных
Просмотр плана запроса может предоставить возможности для новых индексов или изменения существующих индексов. Это также может показать, что база данных неправильно использует существующие индексы (см. Оптимизатор запросов ).
Настройка запроса
Оптимизатор запросов не всегда выбирает наиболее эффективный план запроса для данного запроса. В некоторых базах данных можно просмотреть план запроса, найти проблемы, а затем оптимизатор запросов даст подсказки, как его улучшить. В других базах данных можно попробовать альтернативы для выражения того же запроса (другие запросы, возвращающие те же результаты). Некоторые инструменты запросов могут генерировать встроенные подсказки в запросе для использования оптимизатором.
Некоторые базы данных, такие как Oracle, предоставляют таблицу планов для настройки запросов. Эта таблица плана вернет стоимость и время выполнения запроса. Oracle предлагает два подхода к оптимизации:
RBO постепенно устаревает. Чтобы использовать CBO, необходимо проанализировать все таблицы, на которые ссылается запрос. Для анализа таблицы администратор баз данных может запустить код из пакета DBMS_STATS.
Другие инструменты для оптимизации запросов включают:
Как посмотреть план выполнения запроса в 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 для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь, материал был Вам полезен, пока!