Данная статья представляет собой описание основных операций, отображаемых в планах выполнения запросов СУБД MS SQL Server.
Index Seek
Поиск по некластеризованному индексу. В большинстве случаев является хорошим для производительности, так как представляет собой прямой доступ SQL Server к требуемым строкам данных. Однако это вовсе не означает, что он всегда работает быстро, например, если он возвращает большое число строк, то по производительности он будет практически равен Index Scan.
Index Scan
Сканирование некластеризованного индекса. Обычно наличие этой операции плохо отражается на производительности, поскольку она предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке. Но бывают исключения, например, применение директивы TOP, ограничивающей число возвращаемых записей; если возвращать всего несколько строк, то операция сканирования будет выполняться достаточно быстро, и вы не сможете получить лучшую производительность, чем ту, которую уже имеете, даже если вы попытаетесь перестроить запрос/индексы, чтобы добиться операции Index Seek.
RID Lookup
Поиск идентификатора записи, является узким местом производительности запроса. Но это легко исправить: если вы видите этот оператор, это означает, что у вас отсутствует кластеризованный индекс на таблице. По крайней мере, вы должны добавить кластеризованный индекс, и тут же получите некоторый рост производительности для большинства ваших запросов.
Key Lookup
Поиск ключей. Возникает, когда SQL Server предполагает, что он с большей эффективностью может использовать некластеризованный индекс, а затем перейти к кластерзованному индексу для поиска оставшихся значения строк, которые отсутствуют в некластеризованном индексе. Это не всегда плохо: обращение SQL Server к кластеризованному индексу для извлечения недостающих значений довольно эффективный метод по сравнению с необходимостью создавать и поддерживать совершенно новые индексы.
Однако, если все, что нужно SQL Server от операции Key Lookup, это единственный столбец данных, гораздо проще добавить этот столбец в ваш существующий некластеризованный индекс. Размер индекса увеличится на один столбец, но SQL Server сможет избежать необходимости обращаться к двум индексам для извлечения всех необходимых данных и это в целом окажется более эффективным решением.
Сортировка является одной из наиболее дорогих операций, которые могут быть в плане выполнения, поэтому лучше избегать ее, насколько это возможно.
Простой способ избежать оператора сортировки – иметь данные, хранящиеся в предварительно упорядоченном виде. Это может быть выполнено созданием индекса с ключевыми столбцами, перечисленными в том же самом порядке, который использует оператор сортировки.
Если SQL Server должен выполнить сортировку одних и тех же данных в одном и том же порядке несколько раз в плане выполнения, то еще одним выходом является разбиение запроса на несколько этапов при использовании временных индексированных таблиц для сохранения данных между этапами. В таком случае, если вы будете повторно использовать временную таблицу в плане выполнения вашего запроса, то вы получите чистую экономию.
Spool
Спулы бывают разных типов, но большинство из них можно сформулировать как операторы, которые сохраняют промежуточную таблицу результатов в tempdb.
SQL Server часто использует спул для обработки сложных запросов, преобразуя данные во временную таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск.
Для ускорения выполнения запроса можно попытаться найти способ его перезаписи таким образом, чтобы избежать спула. Если это не получается, использую метод «разделяй и властвуй» для временных таблиц, который может также заменить спул, обеспечивая больший контроль по сравнению с тем, как SQL Server записывает и индексирует данные в tempdb.
Merge Join
Соединение слиянием. Редко встречаются в реальных запросах, как правило, являются наиболее эффективными из операторов логического соединения.
Оптимизатор выбирает использование соединение слиянием, когда входные данные уже отсортированы или SQL Server может выполнить сортировку данных с относительно небольшой стоимостью.
Операция неприменима, если входные данные не отсортированы.
Nested Loops Join
Соединение вложенными циклами. Встречаются очень часто. Выполняют довольно эффективное соединение относительно небольших наборов данных.
Соединение вложенными циклами не требует сортировки входных данных. Однако производительность можно улучшить при помощи сортировки источника входных данных; SQL Server сможет выбрать более эффективный оператор, если оба входа отсортированы.
Операция неприменима, если данные слишком велики для хранения в памяти.
Hash Match Join
Операция используется всегда, когда невозможно применить другие виды соединения. Она выбираются оптимизатором запросов по одной из двух причин:
При первом сценарии трудно оптимизировать выполнение запроса, если только не найти способа соединять меньшие объемы данных.
При втором же сценарии, если есть некоторый способ получить данные в упорядоченном виде до соединения, типа предопределенного порядка сортировки в индексе, то возможно, что SQL Server выберет вместо этой операции более быстрый алгоритм соединения.
Операторы Hash Match Join достаточно эффективны тогда, когда не сбрасывают данные в tempdb.
Parallelism
Операторы параллелизма обычно считаются хорошими вещами: SQL Server дробит ваши данные на множество частей для асинхронной обработки на множестве процессоров, сокращая общее время работы, требуемое для выполнения вашего запроса.
Однако параллелизм может стать плохим, если большинство запросов используют его. При параллелизме процессоры по-прежнему выполняют тот же самый объем работы, что и без него, тем самым отнимая ресурсы у других запросов, которые могут быть запущены, плюс накладывается дополнительная нагрузка на SQL Server по дроблению и последующему объединению всех данных из множества нитей выполнения.
Если параллелизм является узким местом производительности, можно рассмотреть вопрос об изменении порогового значения стоимости для настройки параллелизма, если оно установлено слишком низким.
Stream Aggregate
Статистическое выражение потока. Группирует строки в один или несколько столбцов и вычисляет одно или несколько агрегатных выражений (пример: COUNT, MIN, MAX, SUM и AVG), возвращенных запросом. Выход этого оператора может быть использован последующими операторами запроса, возвращен клиенту или то и другое. Оператору Stream Aggregate необходимы входные данные, упорядоченные по группируемым столбцам. Оптимизатор использует перед этим оператором оператор Sort, если данные не были ранее отсортированы оператором Sort или используется упорядоченный поиск или просмотр в индексе.
Оператор Compute Scalar вычисляет выражение и выдает вычисляемую скалярную величину. Затем эту величину можно вернуть пользователю или сослаться на нее в каком-либо запросе, а также выполнить эти действия одновременно. Примерами одновременного использования этих возможностей являются предикаты фильтра или соединения. Всегда возвращает одну строку. Часто применяется для того, чтобы конвертировать результат Stream Aggregate в ожидаемый на выходе тип int (когда Stream Aggregate возвращает bigint в случае с COUNT, AVG при типах столбцов int).
Concatenation
Оператор просматривает несколько входов, возвращая каждую просмотренную строку. Используется в запросах с UNION ALL. Копирует строки из первого входного потока в выходной поток и повторяет эту операцию для каждого дополнительного входного потока.
Filter
Оператор просматривает входные данные и возвращает только те строки, которые удовлетворяют критерию фильтрации (предикату).
Описание операторов плана выполнения запроса в 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 для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь, материал был Вам полезен, пока!
Русские Блоги
Оптимизация буфера таблиц SQL Server
Эта серия принадлежитСовместное использование случаев оптимизации производительности SQL ServerТема
Оператор буфера, который появляется в плане выполнения, часто имеет очевидные проблемы с производительностью, что также означает, что могут быть проблемы с проектированием и кодированием базы данных, поэтому в этой статье конкретно представлен этот оператор.
Введение катушки
Например, следующий демонстрационный оператор использует TempDB для создания тестовой таблицы:
Затем откройте фактическое выполнение и выполните следующий запрос, чтобы получить информацию о заказе и общие продажи каждого клиента.
Вы можете увидеть следующие результаты:
Как видно на рисунке, SQL Server просканировал таблицу заказов и отсортировал ее по идентификатору клиента. Затем используйте Table Spool для кэширования результатов. Это обеспечивает прямой доступ к этим кэшированным данным в последующих операциях (таких как вычисление общего числа), и в то же время, поскольку кэшированные значения уже отсортированы, вторичной сортировки можно избежать.
Если вы отметите 3 катушки стола в плане выполнения, как показано на рисунке ниже, вы увидите, что список вывода такой же. Чтобы проиллюстрировать одну вещь, хотя Table Spool появляется в плане выполнения три раза, на самом деле это один и тот же Spool / cache. SQL Server создается только один раз и используется все время. Тот, у кого «Node на рисунке ниже, является первым созданным пулом таблиц, который является верхним в плане выполнения, а два в правом нижнем углу имеют «Master Node что указывает на то, что это ссылочный узел The Table Spool с >
Технически подразделяются на два типа операторов катушки: Eager Spool (первое изображение в этой статье) и Lazy Spool (демонстрационный пример выше). Разница между ними заключается в способе заполнения данных:
К другим необычным операторам спула относятся: спул количества строк, спул некластеризованного индекса.
Отступление: SQL Server также использует Spool для достижения » Halloween Protection «Проще говоря, местоположение данных, которые необходимо изменить, изменилось. Такое маловероятное событие действительно произошло во время работы автора, и для его предотвращения обычно использовался контроль транзакций. Это проблема компьютерного поля, а не SQL Server. Уникально то, что инженеры IBM впервые обнаружили, что большинство зрелых СУБД в значительной степени избегали. Что касается контента, связанного с SQL Server, пожалуйста, обратитесь к блогу MSDN:Halloween Protection
Кратко проанализируем план выполнения выше.Для удобства описания операторы помечены в порядке выполнения плана выполнения:
First Step：
Шаг 1. Из таблицы «Заказы» просканируйте кластерный индекс (поскольку таблица имеет только один кластерный индекс), чтобы прочитать всю информацию о OrderId, CustomerID, Total.
Шаг 2: Поскольку вам нужно сгруппировать в соответствии с идентификатором клиента и использовать вычисление оконной функции (sum () over ()), а идентификатор клиента не входит в ключ кластеризованного индекса, требуется дополнительная сортировка.
Шаг 3: операция сегментации, разделите данные на несколько групп, потому что оконная функция должна сгруппировать CustomerID.
Шаг 4: Буфер таблиц и Ленивая буферизация, эта операция создает временную таблицу в TempDB и сохраняет данные, возвращенные операцией сегмента, на основе различных групп в этой временной таблице.
Second Step：
Шаг 1. Повторно используйте созданный ранее пул таблиц.
Шаг 2. Для данных таблицы Spool используйте оператор агрегирования потока, чтобы суммировать данные по группам.В этом примере Sum вычисляет значение Total по группам и возвращает «строку» в качестве вывода.
Шаг 3. Рассчитайте набор результатов предыдущего шага. Если вы запросите ключевое слово «Compute Scalar» для плана выполнения XML, вы увидите, что он выполняет случай, когда операция:
Шаг 4: Вычислите данные, полученные с помощью скалярного оператора, используйте оператор вложенного цикла и снова сопоставьте буфер таблиц на третьем шаге и верните набор результатов, который соответствует условиям.
Шаг 5: Снова используйте операцию вложенного цикла для двух последних наборов результатов, сопоставляя их построчно.
Удар катушки
Первоначальный замысел Spool хорош, но обычно он включает в себя рабочую таблицу, которую можно найти, заключив оператор, который необходимо запустить, с помощью команды «set statistics io on / off».
Рабочая таблица в системе OLTP означает, что используются неэффективные операции ввода-вывода, такие как TempDB (проще говоря, чтение и запись на диск).
Кроме того, для Eager Spool после того, как требуемые данные запрашиваются из результатов в первый раз и помещаются в TempDB, последующее использование осуществляется напрямую из Spool без прохождения исходного индекса в таблице. Если набор данных Spool слишком большой, очень низкий Эффективный.
Кроме того, поскольку спул фактически делает копию данных и сохраняет ее в TempDB, проблема с пространством может усугубиться.
Оптимизировать катушку
Из приведенной выше серии примеров мы знаем, что, во-первых, поскольку оператор выполняет сложные вычисления и требует одновременного выполнения нескольких вызовов, Spool вводится для оптимизации. Чтобы оптимизировать приведенный выше оператор (рекомендуется записывать некоторую информацию, например as Set Statistics IO information) Начнем с плана выполнения, сначала находим самую дорогую часть, оператор «сортировки». Из описания видно, что этот оператор предназначен для сортировки CustomerID. Из определения таблицы мы знаем, что у этой таблицы только один кластерный индекс. Итак, давайте добавим некластеризованный индекс к CustomerID. Так как Total используется в SELECT, добавьте итог в столбец Include.
Выполните инструкцию еще раз, на этот раз все выглядит хорошо, оператор сортировки отсутствует, но спул все еще есть, и из статистики ввода-вывода значение то же самое. Он все еще не идеален.
Но после многих попыток я обнаружил, что многие методы не так эффективны, как этот, поэтому я создал таблицу orders1 с указанным выше оператором, чтобы создать тестовую среду, то есть я скопировал копию среды и сравнил ее:
Откройте план выполнения, а затем сразу выполните вышеуказанный оператор. Этот метод можно использовать для сравнения накладных расходов двух операторов. Небольшой процент накладных расходов означает, что в большинстве случаев это лучше. Ниже представлена реализация этой машины:
Есть основания полагать, что даже если есть операция катушки, она не обязательно неэффективна. Поскольку я удалил катушку, переписав оператор следующим образом, но при совместном выполнении он был не так хорош, как приведенный выше оператор, который только настраивал индекс без перезаписи:
подводить итоги
В этой статье я сделал несколько выводов:
1. Вы не можете просто полагаться на одно «значение», чтобы судить. В случае одинаковой статистики ввода-вывода вы можете рассмотреть возможность заимствования других методов для оценки оптимизированного решения.
2. Я всегда подчеркивал, что перед тем, как изменить указатель, сначала проверьте, можно ли переписать предложение. Я попытался переписать приведенное выше, но, насколько мне известно, я не нашел способа улучшить этот пример, просто переписав его. Поэтому подумайте об изменении индекса позже.
3. Я видел много случаев, когда читал книги, и оконные функции действительно значительно повысили эффективность во многих средах, поэтому специалистам-практикам SQL Server рекомендуется отдавать приоритет оконным функциям.
4. Смелая гипотеза и тщательная проверка.
Кроме того, оптимизация буфера обычно выполняется путем оптимизации индекса для замедления или удаления. Однако наиболее важно обратить внимание на конкретные вопросы.
