Описание операторов плана выполнения запроса в Microsoft SQL Server. Какая иконка, что обозначает
Приветствую Вас на сайте Info-Comp.ru! Продолжаем рассматривать план выполнения запроса и сегодня мы поговорим об операторах, которые наиболее часто встречаются в плане запроса, узнаем, что означает тот или иной оператор и как обозначается, т.е. как он выглядит в плане.
Напомню, ранее мы уже рассматривали план выполнения запроса, например, в следующих статьях:
Операторы плана выполнения запроса
Чтобы создать план выполнения запроса, который показывает, как именно будет достигнут результат выполнения SQL инструкции, оптимизатор запросов использует операторы, которые описывают конкретные действия.
Операторы плана запроса делятся на логические и физические:
Результатом плана выполнения запроса является дерево физических операторов, которое как раз и описывает, как именно SQL Server будет выполнять SQL инструкцию, т.е. как именно будет достигнут результат этой SQL инструкции.
План запроса можно посмотреть графически в SQL Server Management Studio, как это делается, я показывал в статье – Как посмотреть план запроса в SQL Server.
Ну а сейчас давайте рассмотрим конкретные операторы, которые наиболее часто мы будем видеть в плане выполнения запроса.
Иконка
Оператор
Описание
Assert
Данный оператор предназначен для проверки условий. Например, он проверяет целостность ссылок или гарантирует, что скалярный вложенный запрос возвращает одну строку.
Для каждой входной строки оператор Assert вычисляет выражение в столбце «Аргумент» плана запроса:
Этот оператор использует поисковые возможности индексов для получения строк из кластеризованного индекса, т.е. выполняет поиск в кластеризованном индексе.
Argument содержит имя кластеризованного индекса и предикат SEEK. Подсистема хранилища использует этот индекс для обработки только тех строк, которые удовлетворяют данному предикату. Также может включаться предикат WHERE, в котором подсистема хранилища вычисляет выражение для всех строк, удовлетворяющих предикату SEEK, но это не является обязательным.
Clustered Index Delete
Оператор удаляет строки из кластеризованного индекса. Если в Argument есть предикат WHERE, то удаляются только строки, удовлетворяющие условиям предиката.
Clustered Index Insert
Оператор вставляет в кластеризованный индекс новые строки. Argument содержит предикат SET, который указывает значение, устанавливаемое для каждого столбца.
Index Scan
Оператор Index Scan предназначен для сканирования всех записей некластеризованного индекса. Если в Argument присутствует необязательный предикат WHERE, то возвращаются только те строки, которые удовлетворяют условию, указанному в этом предикате.
Index Seek
Данный оператор выполняет поиск в некластеризованном индексе.
Argument содержит имя некластеризованного индекса и предикат SEEK. Подсистема хранилища использует этот индекс для обработки только тех строк, которые удовлетворяют данному предикату. Также может включаться предикат WHERE, в котором подсистема хранилища вычисляет выражение для всех строк, удовлетворяющих предикату SEEK.
Поиск в индексе является более эффективной операцией, чем сканирование индекса, однако если в запросе запрашивается большая часть данных индекса, то гораздо быстрее будет один раз просканировать индекс, чем осуществлять поиск каждого значения. Таким образом, Index Seek не всегда эффективнее, чем Index Scan, SQL Server сам определяет, что выбрать в том или ином случае на основе внутреннего порогового значения.
Key Lookup
Данный оператор выполняет поиск данных в кластеризованном индексе. Возникает он, например, тогда, когда происходит получение данных из некластеризованного индекса, однако один из столбцов, указанных в запросе, отсутствует в этом некластеризованном индексе, т.е. в данном случае SQL Server по ключу обращается в кластеризованный индекс за недостающими данными. В большинстве случаев можно выиграть в производительности, избавившись от этого оператора, например, создав покрывающий индекс.
Заметка! Проектирование индексов для оптимизации запросов в Microsoft SQL Server.
RID Lookup
Этот оператор похож на Key Lookup, однако он выполняет поиск данных не в кластеризованном индексе, а в таблице «куче». Иными словами, если Вы видите данный оператор, значит у Вас есть таблица «куча», что в большинстве случаев является менее эффективным способом хранения данных, чем их хранение в кластеризованном индексе.
Compute Scalar
Данный оператор вычисляет выражение и выдает скалярную величину. Затем эту величину можно вернуть в качестве результата или использовать в запросе, например, в предикате фильтра или соединения.
Constant Scan
Этот оператор вводит в запрос одну или несколько константных строк. Он возникает, например, когда мы используем конструктор табличных значений VALUES.
Concatenation
Данный оператор принимает данные с нескольких входов, объединяет их, и возвращает один общий результат. Оператор Concatenation мы можем встретить в плане запроса, когда используем конструкцию UNION ALL.
Filter
Этот оператор принимает входные данные и возвращает только те строки, которые удовлетворяют критерию фильтрации (предикату).
Nested Loops
Это оператор вложенных циклов. Он выполняет логические операции соединения. Иными словами, данный оператор возникает, когда мы соединяем несколько таблиц, при этом один набор данных соединения имеет небольшой размер (обычно менее десяти строк), а другой набор данных сравнительно большой и индексирован по соединяемым столбцам.
Nested Loops встречается достаточно часто, так как является самой быстрой операцией соединения на небольшом объеме данных.
Если оба набора данных будут достаточно большие, то данный способ соединения будет крайне неэффективен.
Заметка! Что нужно знать и уметь разработчику T-SQL. Технологии, языки, навыки.
Hash Match
Данный оператор также возникает при соединении таблиц, однако здесь используется другой алгоритм.
Оператор Hash Match строит хэш-таблицу при помощи вычисления хэш-значения для каждой строки одного набора данных. Затем для каждой строки другого набора данных, с помощью той хэш-функции, он вычисляет хэш-значение и осуществляет поиск совпадений по хэш-таблице.
Такой способ физического соединения данных возникает тогда, когда мы обрабатываем большие, несортированные и неиндексированные наборы данных, при этом он делает это достаточно эффективно.
Merge Join
Еще один способ соединения таблиц. Однако в данном случае требуется, чтобы оба набора данных были отсортированы.
Данный способ соединения наиболее эффективен в тех случаях, когда два набора данных достаточно велики, при этом они отсортированы по соединяемым столбцам (например, если они были получены просмотром отсортированных индексов).
Если оба набора данных велики и имеют сходные размеры, но не отсортированы, то соединение слиянием с предварительной сортировкой и хэш-соединение (Hash Match) имеют примерно одинаковую производительность. Однако хэш-соединения часто выполняются быстрее, если наборы данных значительно отличаются по размеру.
Принцип работы данного оператора следующий: он получает строку из каждого набора входных данных и сравнивает их. Например, для операций внутреннего соединения строки возвращаются в том случае, если они равны. Если они не равны, строка с меньшим значением не учитывается, и из этого набора входных данных берется следующая строка и снова происходит сравнение. Этот процесс повторяется, пока не будет выполнена обработка всех строк, т.е. пока этот, назовем его курсор, не дойдет до конца.
Adaptive Join
Данный оператор появился относительно недавно, и он также предназначен для соединения таблиц. Однако Adaptive Join откладывает выбор метода соединения до завершения сканирования первых входных данных, в результате у SQL Server более точные сведения о том, какой способ соединения будет эффективней: Nested Loops или Hash Match.
Таким образом, во время выполнения план запроса может динамически переключаться на более эффективный алгоритм соединения без перекомпиляции.
Заметка! Статистика в Microsoft SQL Server – что это такое и для чего она нужна.
Index Spool
Оператор Index Spool сканирует входные данные, и помещает их в буфер, который хранится в базе данных tempdb, этот буфер существует только в течение выполнения запроса. При этом для этих временных данных создается некластеризованный индекс, который позволяет использовать поддерживаемый индексами механизм поиска для вывода только строк, отвечающих требованиям предиката SEEK.
Примечание!В большинстве случаев задействование tempdb в запросе отрицательно сказывается на его скорости выполнения, т.е. желательно проанализировать и переписать запрос так, чтобы исключить Spool в tempdb (во всех его проявлениях).
Table Spool
Оператор Table Spool сканирует входную таблицу и помещает копию каждой строки в буфер, который находится в базе данных tempdb и существует только в течение времени жизни запроса.
Spool
Оператор Spool сохраняет промежуточные результаты запроса в базе данных tempdb.
Table Scan
Данный оператор получает строки из таблицы, указанной в столбце Аргумент плана выполнения запроса.
Если предикат WHERE присутствует в столбце Argument, возвращаются только строки, удовлетворяющие условию, указанному в этом предикате.
Sort
Оператор Sort сортирует входящие строки. Сортировка является достаточно трудоемкой операцией, поэтому лучше ее избегать, например, это можно достигнуть путем создания индекса с ключевыми столбцами, перечисленными в том же самом порядке, который использует оператор сортировки.
Top
Оператор Top просматривает входные данные и возвращает только указанное число или процент строк.
Stream Aggregate
Это оператор — статистическое выражение потока, он группирует строки в один или несколько столбцов и вычисляет одно или несколько агрегатных выражений, возвращенных запросом. Данный оператор возникает, когда мы используем GROUP BY и агрегатные выражения.
Parallelism
Оператор Parallelism делит данные на несколько частей для параллельной обработки, тем самым сокращая общее время выполнения запроса.
В большинстве случаев параллельная обработка является эффективной операцией, однако это создает дополнительную нагрузку на процессоры и в некоторых случаях, например, когда большинство запросов на сервере используют параллелизм, она может вызвать снижение общей производительности сервера.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь, материал был Вам полезен, пока!
Справочник по логическим и физическим операторам Showplan
Операторы описывают, как SQL Server выполняет запрос или инструкцию языка DML. Оптимизатор запросов использует операторы для построения плана запроса, чтобы создать результат, заданный в запросе, или произвести операцию, указанную в инструкции DML. План запроса — это дерево физических операторов. Можно просмотреть план запроса с помощью инструкций SET SHOWPLAN, параметров графического плана выполнения в среде SQL Server Management Studioи классов событий SQL Server Profiler Showplan.
Операторы классифицируются как логические и физические.
Логические операторы Логические операторы описывают операции реляционной алгебры, используемые для обработки инструкции. Другими словами, логические операторы описывают на концептуальном уровне, какие действия следует совершить.
Физические операторы Физические операторы реализуют действия, описанные логическими операторами. Каждый физический оператор является объектом или процедурой, выполняющей операцию. Например, некоторые физические операторы обращаются к столбцам и строкам таблицы, индекса или представления. Другие физические операторы выполняют другие операции, такие как вычисления, статистическая обработка, проверка целостности данных или соединения. Физическим операторам соответствует их стоимость.
Физические операторы производят инициализацию, собирают данные и закрываются. Если формулировать более конкретно, физический оператор может ответить на вызов трех следующих методов.
GetNext() : метод GetNext() заставляет физический оператор получить первую или последующую строку данных. Физический оператор может получить много вызовов GetNext() или не получить ни одного.
Когда счетчики ActualRebinds и ActualRewinds присутствуют в выводе инструкции Showplan в формате XML, их значения сравнимы со значениями EstimateRebinds и EstimateRewinds. Когда они отсутствуют, оценочное число строк (EstimateRows) сравнимо с действительным числом строк (ActualRows). Обратите внимание, что действительный графический вывод Showplan отображает нули для действительных повторных привязок и сбросов на начало, когда те отсутствуют.
Связанный счетчик ActualEndOfScans доступен только в том случае, когда вывод инструкции Showplan производится с помощью инструкции SET STATISTICS XML ON. Всякий раз, когда физический оператор достигает конца потока данных, этот счетчик увеличивается на единицу. Физический оператор может достигать конца своего потока данных ни одного, один или несколько раз. Как и в случае с повторными привязками и сбросами на начало, число окончаний просмотров может превышать единицу только в том случае, если оператор находится внутри цикла соединения. Количество окончаний просмотров должно быть меньше или равно сумме количеств повторных привязок и сбросов на начало.
Сопоставление физических и логических операторов
Оптимизатор запросов создает план запроса в виде дерева логических операторов. После этого оптимизатор запросов выбирает наиболее эффективный физический оператор для каждого логического. Оптимизатор запросов использует подход, основанный на стоимости, чтобы определить, с помощью какого физического оператора будет реализован логический.
Обычно логическую операцию можно реализовать с помощью нескольких физических операторов. Однако в редких случаях физический оператор может реализовывать несколько логических операций.
Описание оператора
Этот раздел содержит описания логических и физических операторов.
Каждый раз, когда значок графического плана выполнения имеет желтый круг с двумя стрелками справа налево, это означает, что оператор выполняется параллельно. Дополнительные сведения о параллелизме см. в статье Руководство по архитектуре потоков и задач.
Логические операторы включают в себя Asynchronous, Optimistic, Primary, Read Only, Scroll Locks, Secondary и Synchronous.
первых входных данных, не имеющих соответствий во вторых входных данных;
вторых входных данных, не имеющих соответствий в первых входных данных.
Для любых соединений используйте первые (верхние) входные данные для формирования хэш-таблицы, а вторые (нижние) входные данные — для выполнения пробы хэш-таблицы. Выходные данные совпадают (или не совпадают) в зависимости от типа соединения. Если несколько операций соединения используют один и тот же столбец соединения, эти операции группируются в хэш-группу.
Для операторов Distinct и Aggregate используйте входные данные для формирования хэш-таблицы (удаляя повторяющиеся элементы и вычисляя любые статистические выражения). Когда хэш-таблица будет построена, просмотрите таблицу и выведите все записи.
— Модуль чтения функции с табличным значением XML принимает большой двоичный объект XML как параметр и возвращает набор строк, представляющий узлы XML в том же порядке, что и в документе XML. Другие параметры могут ограничивать возвращаемые XML-узлы подмножеством XML-документа.
Оператор Table-valued Function — это логический и физический оператор.
None
Top N Sort
Оператор Top N Sort аналогичен итератору Sort за исключением того, что требуются только первые N строк, а не весь результирующий набор. Для небольших значений N ядро выполнения запросов SQL Server пытается выполнить операцию сортировки полностью в памяти. Для больших значений N ядро выполнения запросов использует более общий способ сортировки, в котором N не является параметром.
Top
Оператор Top просматривает входные данные и возвращает только указанное число или процент строк, выбранных, возможно, на основе порядка сортировки. Столбец Аргумент может содержать список столбцов, которые проверяются на наличие связей. Планируется использовать оператор Top для соблюдения ограничений на число строк. Top — это логический и физический оператор.
UDX
Расширенные операторы (UDX) реализуют часть операций XQuery и XPath в SQL Server. Все операторы UDX являются и логическими, и физическими операторами.
Расширенный оператор (UDX) FOR XML используется для сериализации реляционных наборов строк, которые он получает на входе, в представление XML в одном столбце BLOB в единственной строке на выходе. Это статистический оператор XML, учитывающий порядок данных.
Расширенный оператор (UDX) XML SERIALIZER — это статистический оператор XML, учитывающий порядок данных. Он получает на входе строки, представляющие XML-узлы или скаляры XQuery в порядке, соответствующем XML-документу, и выдает сериализованный XML BLOB в единственном XML-столбце единственной строки вывода.
Расширенный оператор (UDX) XQUERY STRING вычисляет строковое значение XQuery из входных строк, представляющих собой узлы XML. Это статистический строковый оператор, чувствительный к порядку данных. Он выводит одну строку со столбцами, представляющими скаляр XQuery, содержащий строковое значение входа.
Расширенный оператор (UDX) XQUERY LIST DECOMPOSER является оператором декомпозиции списка XQuery. Для каждой входной строки, представляющей собой узел XML, он создает одну или несколько строк, представляющих скаляр XQuery, содержащий значение элемента списка, если входные данные имеют тип списка XSD.
