Наверное, каждый 1С-ник задавался вопросом "что быстрее, соединение или условие в ГДЕ?" или, например, "сделать вложенный запрос или поставить оператор В()"?

После чего 1С-ник идет на форум, а там ему говорят - надо смотреть план запроса. Он смотрит, и ничего не понимая, навсегда забрасывает идею оптимизации запросов через планы, продолжая сравнивать варианты простым замером производительности.

В результате, на машине разработчика запрос начинает просто летать, а затем в боевой базе при увеличении количества записей все умирает и начинаются жалобы в стиле "1С тормозит". Знакомая картинка, не правда ли?

В данной статье я не дам вам исчерпывающих инструкций по чтению планов запроса. Но я постараюсь объяснить доходчиво - что это такое и с какой стороны к ним подойти.

Более того, я не считаю себя хорошим оптимизатором запросов, поэтому, в статье весьма вероятны фактологические косяки. Ну тут пусть гуру меня поправят в каментах. На то мы тут и сообщество, чтобы помогать друг-другу, верно?

Если вы уже умеете читать планы запросов, то, наверное, стоит пропустить статью. Тут будет самое простое и с начала начал. Статья ориентирована на тех разработчиков, которые пока еще не выяснили, что это за зверь - план запроса.

Как работает компьютер

А начну я издалека. Дело в том, что компьютеры, к которым мы привыкли, они не такие уж и умные. Вы же наверняка помните первые уроки информатики, или младшие курсы ВУЗа? Помните сортировку массивов пузырьком там, или чтение файла построчно? Так вот, принципиально нового ничего не изобретено в современных реляционных СУБД.

Если на лабораторках вы считывали строчки из файла, а потом записывали их в другое место, то вы уже примерно представляете, как работает современная СУБД. Да, разумеется, там все намного (совсем намного) сложнее, но - циклы они и в Африке циклы, чтение диска все еще не стало быстрее чтения ОЗУ, а алгоритмы O(N) все еще медленнее алгоритмов O(1) при увеличении N.

Давайте представим, что к вам, простому 1С-нику пришел человек и говорит: "смотри, дружище, надо написать базу данных. Вот тут файл, в нем строчки какие-нибудь пиши. А потом оттуда читай". Представим, что отказаться вы не можете. Как бы вы решали эту задачу?

А решали бы вы ее точно так же, как решают ее ребята из Microsoft, Oracle, Postgres и 1С. Вы бы открыли файл средствами вашего языка программирования, прочитали бы оттуда строки и вывели бы их на экран. Никаких принципиально отличных алгоритмов, от тех, что я уже описал - мир не придумал.

Представьте, что у вас есть 2 файла. В одном записаны контрагенты, а в другом - договоры контрагентов. Как бы вы реализовывали операцию ВНУТРЕННЕЕ СОЕДИНЕНИЕ? Вот прямо в лоб, без каких-либо оптимизаций?

Контрагенты

Договоры

	IDКонтрагента	НомерДоговора

Давайте сейчас для простоты опустим нюансы открывания файлов и чтения в память. Сосредоточимся на операции соединения. Как бы вы его делали? Я бы делал так:

Для Каждого СтрокаКонтрагент Из Контрагенты Цикл Для Каждого СтрокаДоговор Из Договоры Цикл Если СтрокаДоговор.IDКонтрагента = СтрокаКонтрагент.ID Тогда ВывестиРезультатСоединения(СтрокаКонтрагент, СтрокаДоговор); КонецЕсли; КонецЦикла; КонецЦикла;

В примере ф-я ВывестиРезультатСоединения просто выведет на экран все колонки из переданных строк. Ее код здесь не существенен.

Итак, мы видим два вложенных цикла. Внешний по одной таблице, а потом во внутреннем - поиск ключа из внешней простым перебором. А теперь, внезапно, если вы откроете план какого-нибудь запроса с СОЕДИНЕНИЕМ в любой из 1С-ных СУБД, то с довольно высокой вероятностью увидите там конструкцию "Nested Loops". Если перевести это с языка вероятного противника на наш, то получится "Вложенные циклы". То есть, в "плане запроса" СУБД вам объясняет, что вот тут, для "соединения" она применила алгоритм, описанный выше. Этот алгоритм способен написать любой школьник примерно 7-го класса. И мощные боевые СУБД мирового уровня применяют этот алгоритм совершенно спокойно. Ибо в некоторых ситуациях - он лучшее, что есть вообще.

И вообще, чего это я сразу с "соединения" начал. Давайте предположим, что вам нужно просто найти контрагента по наименованию. Как бы вы решали эту задачу? Вот есть у вас файл с контрагентами. Напишите алгоритм. Я напишу его вот так:

Для Каждого СтрокаКонтрагент Из Контрагенты Цикл Если СтрокаКонтрагент.Имя = "Иванов" Тогда ВывестиРезультат(СтрокаКонтрагент); КонецЕсли; КонецЦикла;

Нет, ну серьезно, а как еще его можно написать? А никак по сути. Если неизвестно в каком порядке лежат записи в таблице, то придется пересмотреть ее всю, как ни крути. На языке планов запроса это называется Scan. Сканирование. Полный просмотр данных и ничего больше.

Индексы

А как же мы можем ускорить поиск данных в таблице? Ну правда, всё время пересматривать всё - это же зло какое-то.

Вспомним картотеку в поликлинике или библиотеке. Как там выполняется поиск по фамилии клиента? В деревянных шкафчиках стоят аккуратные карточки с буквами от А до Я. И пациент "Пупкин" находится в шкафчике с карточкой "П". Просматривать подряд все прочие буквы нет необходимости. Если мы отсортируем данные в нашей таблице и будем знать, где у нас (под какими номерами строк) находятся записи на букву "П", то мы существенно приблизимся к быстродействию тетеньки из регистратуры. А это уже лучше, чем полный перебор, не так ли?

Так вот, слово "Индекс" в данном контексте означает (опять же, в переводе с языка вероятного противника) "Оглавление". Чтобы быстро найти главу в книге, вы идете в оглавление, находите там название главы, потом смотрите номер страницы и идёте сразу на эту страницу.

Когда базе данных нужно найти запись в таблице, она идет в оглавление, смотрит на название контрагента, смотрит на номер записи, под которой он лежит, и идет в нужную область файла данных сразу за этой записью.

В виде кода это может выглядеть примерно так:

Индекс = Новый Соответствие; // бла-бла НомерЗаписи = Индекс["Иванов"] ВывестиРезультат(ТаблицаКонтрагентов[НомерЗаписи]);

Известно, что чудес не бывает, поэтому, память под Соответствие "Индекс", а также поиск в самом соответствии - это небесплатные операции. Но они намного дешевле, чем прямой перебор всех данных. Ах, да, это Соответствие придется постоянно поддерживать в актуальном состоянии при добавлении или изменении основных данных.

Теперь давайте подумаем, а как бы вы реализовывали сам этот индекс? Можно хранить записи в файле данных сразу в отсортированном виде. И все бы ничего, но, во-первых, искать надо каждый раз по разным полям, а во-вторых, если в уже заполненную от А до Я таблицу пользователь захочет вставить запись на букву М? А ведь он захочет, я вас уверяю.

Вспомним, как вообще ведется запись в файл.

Fseek(file, position); // переход к нужному адресу write(file, dataArray, dataLength); // запись dataLength байт из массива dataArray

Если адрес position указывает куда-то в середину файла, и на этом месте есть данные, то они затираются новыми. Если нужно вставить что-то в середину файла (и массива в памяти в том числе) то нужно в явном виде "подвинуть" все, что находится после position, освободив место, а уже потом писать новые данные. Как вы понимаете, "подвижка" данных это опять же циклы и операции ввода/вывода. То есть, не так уж быстро. Ничего в компьютере "само" не происходит. Все по команде.

Вернемся к индексу. Пользователь хочет вставить что-то в середину. Хочешь не хочешь, а придется двигать данные, либо исхитряться с хранением данных в "страницах", связанных между собой в список. Физически писать в конец, или в пустое место, но как будто в середину таблицы. И потом еще обновлять в оглавлении номера записей. Они же теперь сдвинулись и индекс показывает не туда куда нужно. Вы, наверное, слышали, что индексы в БД ускоряют поиск, но замедляют вставку и удаление. Теперь, вы знаете, почему это так.

Ну так вот, мы еще не решили проблему поиска по разным полям. Мы же не можем хранить данные в файле в разном порядке. Одному пользователю по имени, а другому, скажем - по дате. Причем одновременно. Как бы вы решали эту задачу? По-моему, решение очевидно - нужно хранить отдельно данные и отдельно оглавления, отсортированные по нужным полям. Т.е. в базе данные лежат, как придется, но рядышком мы создадим файлик, где записи отсортированы по имени. Это будет индекс по полю "Имя". А еще рядышком будет другой такой же файлик, но отсортированный по полю "Дата". Для экономии места мы будем хранить в индексах не все колонки основной таблицы, а только те, по которым выполнена сортировка (чтобы быстро тут искать, находить номер записи и моментально прыгать к ней, чтоб прочитать остальные данные).

Ребята, которые пишут взрослые СУБД тоже не придумали ничего лучше. Индексы в БД устроены именно так. Все данные из таблицы лежат отсортированными рядышком в отдельной сущности. По сути, индекс, это просто еще одна таблица. И места она занимает пропорционально размеру основной таблицы, что логично. Да, там еще есть разные ухищрения, типа сбалансированных деревьев и всякого такого, но смысл не сильно меняется.

Кстати, если записывать данные в основную таблицу сразу упорядоченными, то можно не делать отдельно хранимый индекс и считать индексом саму таблицу с данными. Здорово, правда? Такой индекс называют "кластерным". Логично, что поле, по которому отсортированы записи в таблице должно стараться монотонно нарастать. Вы же помните про вставку в середину, верно?

Планирование выполнения запроса

Представьте, что у вас таблица в пять миллионов записей. И есть у нее индекс. Надо быстренько найти запись со словом "Привет". А еще представьте, что у вас такая же таблица, но с тремя записями. И тоже надо найти "Привет". Какой способ поиска выбрать? Открыть файл индекса, пробежаться по нему двоичным поиском, найти нужный номер записи, открыть файл основной таблицы, перейти к записи по ее номеру, прочитать ее? Или запустить цикл от одного до трех, проверив каждую запись на соответствие условию? Современный компьютер циклы от одного до трех выполняет просто ужас, как быстро.

Чтобы принять решение, планировщик запроса должен понимать, с чем имеет дело. Он оперирует такой штукой, как статистика. Статистика включает в себя количество записей по таблицам, распределение данных по колонкам, селективность и прочее и прочее. Это все подсказки планировщику о том, какой способ сбора данных будет быстрее. Не самым быстрым из возможных, а хотя бы достаточно быстрым с некоторой вероятностью. И у планировщика ограничено время на принятие решения. мы же хотим быстро получить данные, а не ждать, пока он там планирует себе.

Вот тут уже я бы не стал браться за работу по написанию планировщика, не защитив предварительно диссертацию. Как он там работает и как умудряется делать это вполне сносно - не знаю. Поэтому, ограничимся документацией СУБД. Из нее следует, что на основании статистики планировщик строит несколько возможных вариантов пошагового выполнения запроса, а потом выбирает из них наиболее подходящий. Например, первый попавшийся. Тоже ведь эвристика, разве нет?

"Что мне сделать сначала" - думает планировщик: "обойти всю таблицу А, отобрав записи по условию, а потом соединить с таблицей Б вложенными циклами, или же найти индексом все подходящие записи таблицы Б, а уже потом пробежаться по таблице А"? Каждый из шагов имеет определенный вес или стоимость. Чем больше стоимость, тем сложнее выполнять. В плане запросов всегда написана стоимость каждого из шагов, которые выполнил движок СУБД, чтобы собрать результаты запроса.

Устройство оператора плана

Каждый шаг плана запроса реализован в виде некоторого объекта, на вход которого подается одно множество записей, а на выходе получается другое. Если представить это в виде кода, то получится, что оператор плана запросов представляет собой реализацию абстрактного интерфейса с одним методом:

Interface IQueryOperator { DataRow GetNextRow(); }

для тех кто не понял, что тут написано, поясню. Каждый оператор плана запросов имеет метод "ДайСледующуюЗапись". Движок СУБД дергает оператор за этот метод и при каждом таком дергании добавляет полученную запись к результату запроса. Например, оператор фильтрации записей на входе имеет всю таблицу, а на выходе - только те, которые удовлетворяют условию. Далее, выход этого оператора подается на оператор, например, ПЕРВЫЕ 100, а далее на оператор агрегации (СУММА или КОЛИЧЕСТВО), которые точно так же, внутри инкапсулируют всю обработку, а на выход выдают запись с результатом.

Схематично это выглядит так:

ВсеДанные ->Фильтр(Имя="Петров")->Первые(100)->Аггрегация(КОЛИЧЕСТВО)

Когда вы откроете план запроса, то увидите кубики, соединенные стрелочками. Кубики - это операторы. Стрелочки - направление потоков данных. Данные бегут по стрелочкам от одного оператора к другому, сливаясь в конце в результат запроса.

Каждый оператор имеет некие параметры: количество обработанных записей, стоимость, количество операций ввода/вывода, использование кэшей и прочее и прочее. Все это позволяет судить об эффективности выполнения запроса. Scan таблицы, пробежавший миллион записей и выдавший две на выходе - это не очень хороший план запроса. Но лучше планировщик ничего не нашел. У него не было индекса, чтобы поискать в нем. А может, наврала статистика и сказала, что в таблице три записи, а на самом деле туда успели написать миллион штук, но статистику не обновили. Все это предмет для разбирательства инженером, который изучает запрос.

План запроса - это пошаговый отладчик запроса. Вы пошагово смотрите, что именно, какой алгоритм (в буквальном смысле) применила СУБД, чтобы выдать результат. Примеры самих алгоритмов вы видели - они чрезвычайно сложны, ведь там есть циклы и условия. Даже порой несколько циклов вложены, вот ведь ужас. Важно понимать, какие процессы происходят внутри каждого оператора. Какой алгоритм применялся к массиву записей в процессе выполнения и сколько он работал.

Конкретные операторы, встречающиеся в планах запроса и их внутреннее устройство я планирую рассмотреть в следующей статье. Спасибо за то, что прочитали до конца!

План выполнения SQL-запроса, или план запроса, - это последовательность шагов или инструкций СУБД, необходимых для выполнения SQL-запроса. На каждом шаге операция, инициировавшая данный шаг выполнения SQL-запроса, извлекает строки данных, которые могут формировать конечный результат или использоваться для дальнейшей обработки. Инструкции плана выполнения SQL-запроса представляются в виде последовательности операций, которые ВЫПОЛНЯЮТСЯ СУБД ДЛЯ предложений SQL SELECT, INSERT, delete и update. Содержимое плана запроса, как правило, представляется древовидной структурой и включает в себя следующую информацию:

• порядок соединения источников данных (таблиц, представлений и т.п.);
• метод доступа для каждого источника данных;
• методы соединения источников данных;
• операции ограничения выбора данных, сортировки и агрегирования;
• стоимость и кардинальность каждой операции;
• возможное использование секционирования и параллелизма. Информация, предоставляемая планом выполнения SQL-запроса, позволяет разработчику увидеть, какие подходы и методы выбирает оптимизатор для выполнения SQL-операций.

Интерпретация плана выполнения SQL-запроса

Визуализация плана выполнения SQL-запроса зависит от инструментов и средств разработки, которые могут как входить в состав СУБД, запрос которой представляет интерес для анализа, так и являться отдельными коммерческими или свободно распространяемыми программными продуктами, не имеющими прямого отношения к конкретному производителю СУБД. Использование того или иного инструмента визуализации плана выполнения запроса, как правило, существенно не влияет на восприятие того, что описывает представленный план запроса. Определяющей в процессе анализа того, каким путем пойдет оптимизатор при выполнении конкретного запроса, является способность верно интерпретировать информацию, которая представлена в плане запроса.

Как уже упоминалось, план SQL-запроса имеет древовидную структуру, которая описывает не только последовательность выполнения SQL-операций, но также и связь между этими операциями. Каждый узел дерева плана запроса - это операция, например сортировка, или метод доступа к таблице. Между узлами существует взаимосвязь родитель-потомок. Отношения родитель-потомок регулируются по следующим правилам:

• родитель может иметь одного или нескольких потомков;
• потомок имеет только одного родителя;
• операция, не имеющая родительской операции, является вершиной дерева;
• в зависимости от метода визуализации плана SQL-запроса потомок располагается с некоторым отступом относительно родителя. Потомки одного родителя располагаются на одинаковом расстоянии от своего родителя.

Рассмотрим более подробно информацию, представляемую планом выполнения SQL-запроса. Приведенные примеры выполнены в среде СУБД Oracle. В качестве инструмента выполнения запросов и визуализации плана SQL-запросов был использован Oracle SQL Developer. Фрагмент плана SQL-запроса представлен на рис. 10.11.

I Id I Operation

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK PRODUCT INFORMATION

SELECT STATEMENT SORT ORDER BY NESTED LOOPS NESTED LOOPS TABLE ACCESS FULL INDEX UNIQUE SCAN TABLE ACCESS BY INDEX ROWID

Рис. 10.11. Фрагмент плана выполнения SQL-запроса в среде СУБД Oracle

Используя правила отношения операций плана запроса, можно определить следующее их формальное описание.

Операция 0 - корень дерева плана запроса. Корень имеет одного потомка: операция 1.

Операция 1 - операция имеет одного потомка: операция 2.

Операция 2 - операция имеет двух потомков: операция 3 и операция 6.

Операция 3 - операция имеет двух потомков: операция 4 и операция 5.

Операция 4 - операция не имеет потомков.

Операция 5 - операция не имеет потомков.

Операция 6 - операция не имеет потомков.

Взаимодействие родитель-потомок между операциями плана запроса представлено на рис. 10.12.

Операции, выполняемые в плане запроса, можно разделить на три типа: автономные, операции не связанного объединения и операции связанного объединения (рис. 10.13).

Автономные		Операции несвязанного		Операции связанного
операции		объединения		объединения

Рис. 10.12.

Рис. 10.13.

Автономные операции - это операции, которые имеют не более одной дочерней операции.

Правила следования, по которым выполняются автономные операции, можно сформулировать следующим образом.

• 2. Каждая дочерняя операция выполняется только один раз.
• 3. Каждая дочерняя операция возвращает свой результат родительской операции.

На рис. 10.14 представлен план следующего запроса:

SELECT o.order_id ,о.order_status FROM orders о ORDER BY о.order_status

Данный запрос содержит только автономные операции.

Учитывая правила следования автономных операций, последовательность их выполнения будет следующая.

• 1. В соответствии с правилом следования автономных операций № 1 первой будет выполнена операция с ID = 2. Выполняется последовательное чтение всех строк таблицы orders.
• 2. Далее выполняется операция с ID = 1. Выполняется сортировка строк, возвращаемых операцией с ID = 2, по условию предложения сортировки ORDER BY.
• 3. Выполняется операция с ID = 0. Возвращается результирующий набор данных.

Операции несвязанного объединения

Операции несвязанного объединения - это операции, которые имеют более одной независимо выполняемой дочерней операции. Пример: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

Правила следования, по которым работают операции несвязанного объединения, можно сформулировать следующим образом.

• 1. Дочерняя операция выполняется перед родительской операцией.
• 2. Дочерние операции выполняются последовательно, начиная с наименьшего значения ID операции в порядке возрастания этих значений.
• 3. Перед началом работы каждой следующей дочерней операции текущая операция должна быть выполнена полностью.
• 4. Каждая дочерняя операция выполняется только один раз независимо от других дочерних операций.
• 5. Каждая дочерняя операция возвращает свой результат родительской операции.

На рис. 10.15 представлен план следующего запроса:

SELECT o.order_id from orders о UNION ALL

SELECT oi.order_id from order_items oi

Данный запрос содержит операцию несвязанного объединения UNION all. Остальные две операции являются автономными.

Рис. 10.15. Операции несвязанного объединения, план запроса

	1 SELECT STATEMENT I

Учитывая правила следования операций несвязанного объединения, последовательность их выполнения будет следующей.

• 1. В соответствии с правилами 1 и 2 следования операций несвязанного объединения первой будет выполнена операция с ID = 2. Выполняется последовательное чтение всех строк таблицы orders.
• 2. В соответствии с правилом 5 операция с ID = 2 возвращает считанные на шаге 1 строки родительской операции с ID = 1.
• 3. Операция с ID = 3 начнет выполняться, только когда закончится операция с ID = 2.
• 4. После окончания выполнения операции с ID = 2 начинает выполняться операция с ID = 3. Выполняется последовательное чтение всех строк таблицы order_items.
• 5. В соответствии с правилом 5 операция с ID = 3 возвращает считанные на шаге 4 строки родительской операции с ID = 1.
• 6. Операция с ID = 1 формирует результирующий набор данных на основе данных, полученных от всех ее дочерних операций (с ID = 2 и ID = 3).
• 7. Выполняется операция с ID = 0. Возвращается результирующий набор данных.

Таким образом, можно отметить, что операция независимого объединения последовательно выполняет свои дочерние операции.

Операции связанного объединения

Операции связанного объединения - это операции, которые имеют более одной дочерней операции, причем одна из операций контролирует выполнение остальных. Пример: nested loops, update.

Правила следования, по которым работают операции связанного объединения, можно сформулировать следующим образом.

• 1. Дочерняя операция выполняется перед родительской операцией.
• 2. Дочерняя операция с наименьшим номером операции (ID) контролирует выполнение остальных дочерних операций.
• 3. Дочерние операции, имеющие общую родительскую операцию, выполняются, начиная с наименьшего значения ID операции в порядке возрастания этих значений. Остальные дочерние операции выполняются НЕ последовательно.
• 4. Только первая дочерняя операция выполняется один раз. Все остальные дочерние операции выполняются несколько раз либо не выполняются совсем.

На рис. 10.16 представлен план следующего запроса:

FROM order_items oi, orders о

WHERE o.order_id= oi.order_id

AND oi.product_id>100

AND о.customer_id between 100 and 1000

Данный запрос содержит операцию связанного объединения NESTED LOOPS.

I Id I Operation

		SELECT STATEMENT |

Рис. 10.16. Операции связанного объединения, план запроса

Учитывая правила следования операций связанного объединения, последовательность их выполнения будет следующей.

• 1. В соответствии с правилами 1 и 2 следования операций связанного объединения первой должна быть выполнена операция с ID = 2. Однако операции с 1D = 2 и 1D = 3 являются автономными, и в соответствии с правилом 1 следования автономных операций первой будет выполнена операция с ID = 3. Выполняется просмотр диапазона индекса ORDCUSTOMERIX по условию: о. customer id between 100 and 1000.
• 2. Операция с ID=3 возвращает родительской операции (с Ш=2) список идентификаторов строк Rowld, полученных на шаге 1.
• 3. Операция с ID = 2 выполняет чтение строк в таблице orders, в которых значение Rowld соответствует списку значений Rowld, полученных на шаге 2.
• 4. Операция с ID = 2 возвращает считанные строки родительской операции (с ID = 1).
• 5. Для каждой строки, возвращаемой операцией с ID = 2, выполняется вторая дочерняя операция (с ID = 4) операции nested loops. То есть для каждой строки, возвращаемой операцией с ID = 2, выполняется полный последовательный просмотр таблицы order_items с целью найти соответствие по атрибуту соединения.
• 6. Шаг 5 повторяется столько раз, сколько строк возвращает операция с ID = 2.
• 7. Операция с ID = 1 возвращает результаты работы родительской операции (с ID = 0).
• 8. Выполняется операция с ID = 0. Возвращается результирующий набор данных.

В зависимости от сложности анализируемого запроса план его выполнения может иметь достаточно сложную структуру, что на первый взгляд кажется затруднительным для его интерпретации. Методичное выполнение описанных выше правил и декомпозиция операций позволят достаточно эффективно проанализировать план выполнения SQL-запроса любой сложности. Рассмотрим пример запроса, который формирует список клиентов, количество купленных ими товаров и их общую стоимость:

SELECT с. cust_first_name customer_name,

COUNT(DISTINCT oi.product_id) as product_qty,

SUM(oi.quantity* oi.unit_price) as total_cost FROM oe.orders о INNER JOIN customers c ON

о.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. cust_first_name

Последовательность операций плана данного запроса представлена на рис. 10.17.

SELECT STATEMENT I

SORT GROUP BY ЇГ

	TABLE ACCESS FULL
	INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWIDd
TABLE ACCESS FULL

Рис. 10.17. План запроса, последовательность выполнения операций

Опишем возможный подход к интерпретации плана выполнения 80Ь-запроса, представленного на рис. 10.17. Данный подход включает в себя два основных этапа: декомпозиция операций на блоки и определение порядка выполнения операций.

На первом этапе необходимо выполнить декомпозицию выполняемых операций на блоки. Для этого находим все операции объединения, т.е. операции, которые имеют более одной дочерней операции (на рис. 10.17 это операции 2, 3 и 4), и выделяем эти дочерние операции в блоки. В результате, используя пример на рис. 10.17, получаем три операции объединения и семь блоков операций.

На втором этапе определяется последовательность выполнения блоков операций. Для этого необходимо применить правила следования операций, описанные выше. Выполним ряд рассуждений по вопросу выполнения каждой операции относительно ее идентификационного номера (Ш).

Операция Ш = 0 - автономная и является родительской для операции сШ = 1.

Операция Ю = 1 тоже автономная; является родительской для операции Ш = 2 и выполняется перед операцией Ю = 0.

Операция ГО = 2 - операция несвязанного объединения и является родительской для операций Ю = 3, Ю = 8. Операция ГО = 2 выполняется перед операцией ГО = 1.

Операция ГО = 3 - операция связанного объединения, является родительской для операций ГО = 4, ГО = 7. Операция ГО = 3 выполняется перед операцией ГО = 2.

Операция ГО = 4 - операция связанного объединения, является родительской для операций ГО = 5, ГО = 6. Операция ГО = 4 выполняется перед операцией ГО = 3.

Операция ГО = 5 - автономная операция, выполняется перед операцией ГО = 4.

Операция ГО = 6 - автономная операция, выполняется перед операцией ГО = 5.

Операция ГО = 7 -автономная операция, выполняется после выполнения блока операций «С».

Операция ГО = 8 - автономная операция, выполняется после блока операций «Е».

На основе проведенных рассуждений и правил следования сформулируем последовательность выполняемых операций:

• 1. Первой выполняется автономная операция ГО = 5, см. правила следования операций связанного объединения. Выполняется последовательное чтение всей таблицы.
• 2. Результат операции ГО = 5 - считанные строки таблицы - передается операции ГО = 4.
• 3. Выполняется операция ГО = 4: для каждой строки, возвращенной операцией ГО = 5, выполняется операция ГО = 6. То есть выполняется сканирование диапазона индекса по атрибуту соединения. Получение списка идентификаторов строк Яоу1с1.
• 4. Результат операции ГО = 4 передается операции ГО = 3. То есть передается список идентификаторов строк Кош1с1.
• 5. Выполняется операция ГО = 3: для каждого значения 11оу1с1, возвращенного в результате работы блока операций «С», выполняется операция ГО = 7, т.е. выполняется чтение строк таблицы по заданному списку идентификаторов строк ИтмЫ, полученных после выполнения операции Ш = 4.
• 6. Выполняется автономная операция ГО = 8 - последовательное чтение всей таблицы.
• 7. Выполняется операция несвязанного объединения ГО = 2: выполняется соединение хэшированием результатов работы блоков операций «Е» и «Е».
• 8. Результат операции ГО = 2 передается операции ГО = 1.
• 9. Выполняется операция несвязанного объединения ГО = 1: выполняется агрегирование и сортировка данных, полученных в результате работы операции ГО = 2.
• 10. Выполняется операция ГО = 0. Возвращается результирующий набор данных.

Правила следования, сформулированные для основных типов операций, применимы для большинства планов выполнения БСГО-запроса. Однако существуют конструкции, используемые в БСГО-запросах, которые предполагают нарушение порядка выполнения операций, описанных в правилах следования. Такие ситуации могут появляться в результате использования, например, подзапросов или предикатов антисоединения. В любом случае процесс интерпретации плана выполнения БСГО-запроса не предполагает только использование ряда правил, которые обеспечат именно максимально верный анализ того, что собирается делать оптимизатор при выполнении 8СГО-запроса. Очередной БСГО-запрос - это всегда индивидуальный случай; и то, как он будет выполнен в СУБД, зависит от множества факторов, среди которых версия СУБД, версия и тип операционной системы, на которой развернут экземпляр СУБД, используемая аппаратная часть, квалификация автора 80Ь-запроса и т.д.

SQL Server .

Планы запросов

Когда сервер SQL Server выполняет запрос , сначала требуется определить наилучший способ выполнения. Для этого нужно рассчитать, как и в каком порядке обращаться к данным и соединять их, как и когда выполнять вычисления и агрегации и т. д. За это отвечает подсистема, которая называется Query Optimizer ( Оптимизатор запроса ). Оптимизатор запроса использует статистические данные о распределении данных, метаданные , относящиеся к объектам в базе данных, информацию индекса и другие факторы для вычисления нескольких возможных планов выполнения запроса. Для каждого из этих планов Оптимизатор запроса предполагает его стоимость на основе статистики по этим данным и выбирает план с минимальными затратами ресурсов на выполнение. Конечно, SQL Server не вычисляет всех возможных планов для каждого запроса, поскольку для некоторых запросов сами эти вычисления могут отнять больше времени, чем выполнение наименее эффективного из всех планов. Следовательно, SQL Server использует сложные алгоритмы, чтобы найти план выполнения с разумной стоимостью, близкой к минимально возможной. После того, как план выполнения сгенерирован, он хранится в буферном кэше (на что SQL Server выделяет большую часть своей виртуальной памяти). Затем план выполняется тем способом, который Оптимизатор запроса сообщает ядру базы данных (компоненту database engine ).

Примечание . Планы выполнения в буферном кэше могут быть повторно использованы при выполнении такого же или аналогичного запроса. Следовательно, планы выполнения хранятся в кэше максимально возможное время. Дополнительную информацию о кэшировании планов выполнения см. в официальном документе под названием: "Batch Compilation, Recompilation , and Plan Caching Issues in SQL Server 2005" (Проблемы компиляции и рекомпиляции пакетов, а также кэширования планов в SQL Server 2005) на странице http://www.microsoft.com/ technet/prodtechnol/sql/2005/recomp.mspx .

Сможет ли Query Optimizer ( Оптимизатор запросов ) сгенерировать эффективный план для конкретного запроса, зависит от следующих аспектов:

• Индексы . Подобно оглавлению в книге, индекс базы данных позволяет быстро найти определенные строки в таблице. В таблице может быть не один индекс. Благодаря наличию в таблице индексов, Оптимизатор запросов SQL Server может оптимизировать доступ к данным, выбрав для использования подходящий индекс. Если индексы отсутствуют, у Оптимизатора запросов остается только один вариант, который заключается в сканировании всех данных, имеющихся в таблице, в поиске нужных строк. Далее в этой лекции приводится информация о том, как работают индексы и как их разрабатывать и проектировать.
• Статистика распределения данных :SQL Server хранит статистику о распределении данных. Если эта статистика отсутствует или устарела, Оптимизатор запросов не сможет вычислить эффективный план выполнения запроса. В большинстве случаев, статистические данные генерируются и обновляются автоматически. Далее в этой лекции рассказывается о том, как генерируются статистические данные и как можно управлять статистикой.

Как видите, генерирование плана выполнения запросов - это функция , немаловажная для производительности SQL Server , поскольку эффективность плана выполнения запроса определяет, будет ли время его выполнения измеряться в миллисекундах, секундах или даже минутах. Планы выполнения запросов, которые показали низкую скорость выполнения, можно проанализировать, чтобы определить, имеется ли индекс , устарели ли данные статистики или просто SQL Server выбрал не самый эффективный план (такое случается не очень часто).

Примечание . Конечно, возможно, что неэффективно выполненный запрос выполнялся в соответствии с хорошим планом. В этих случаях дело не в оптимизации запроса . Скорее всего, проблема кроется совсем в другом, например, в проекте запроса, конфликте доступа к данным, операций ввода/вывода, памяти, использования ЦПУ, сетевых ресурсов и т. п. Чтобы получить дополнительную информацию по этим проблемам, рекомендуем ознакомиться с официальным документом " Troubleshooting Performance Problems in SQL Server 2005" (Поиск и решение проблем с производительностью в SQL Server 2005), который доступен по следующей ссылке: http://www.microsoft.com/ technet/prodtechnol/sql/2005/tsprfprb.mspx .

Знакомимся с планами выполнения запросов

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio). Нажмите кнопку New Query (Создать запрос), чтобы открыть окно нового запроса, и измените контекст выполнения на базу данных Adventure Works , выбрав ее из раскрывающегося списка Available Databases (Доступные базы данных).
2. Выполните следующую инструкцию SELECT . Код этого примера имеется в файлах примеров под именем Viewing Query Plans .sql .
3. Чтобы вывести на экран план выполнения для этого запроса, нажмите комбинацию клавиш (Ctrl+L) или выберите из меню Query (Запрос) команду Display Estimated Execution Plan (Показать предполагаемый план выполнения). План выполнения показан на следующем рисунке.

   При генерировании предполагаемого плана запроса запрос на самом деле не выполняется. Он только оптимизируется Оптимизатором запроса. Эта особенность Оптимизатора запросов является преимуществом, когда приходится иметь дело с запросами, которые имеют продолжительные рабочие циклы , ведь для того, чтобы увидеть план выполнения запроса, нет необходимости выполнять сам запрос. Графическое представление плана выполнения запроса читается справа налево и сверху вниз. Каждый значок в плане представляет один оператор, а данные, изменяемые между этими операторами, обозначены стрелками. Толщина стрелок соответствует объему данных, которые передаются между операторами. Мы не будем углубляться в подробности и объяснять значение каждого оператора; расскажем только о тех из них, которые показаны в данном плане выполнения запроса.

   • SQL Server обращается к данным при помощи операции Clustered Index Scan (Просмотр кластеризованного индекса ). Это сканирование представляет собой реальную операцию доступа к данным и подробно рассматривается далее.
   • Данные переходят к оператору Sort (Сортировка), который сортирует данные на основе предложения ORDER BY .
   • Данные пересылаются клиенту.

   Мы рассмотрим самые важные операторы, которые использует SQL Server, когда будем изучать индексы и соединения. Полный список операторов можно найти в Электронной документации SQL Server 2005, тема "Пиктограммы графического представления плана выполнения".

   Стоимость в процентах под пиктограммой каждого оператора показывает процент от общей стоимости запроса, представленного на графической схеме. Это число поможет вам понять, какая операция использует при выполнении больше всего ресурсов. В нашем случае самой дорогостоящей операцией является Clustered Index Scan (Просмотр, а также поиск ProductID 712 . Эта информация находится в секции Predicates (Предикаты). Кроме того, показаны предполагаемая стоимость и предполагаемое количество строк, а также размер строки. В то время, как количество строк оценивается на основе статистики, которую SQL Server хранит для этой таблицы, значения стоимости вычисляются на основе статистики и значений эталонной системы. Следовательно, значения стоимости не следует использовать для того, чтобы рассчитать, сколько времени запрос будет выполняться на компьютере. Эти цифры могут использоваться только для выявления более дешевой или более дорогостоящей операции.

4. Эту информацию об операторах можно увидеть также в окне Properties (Свойства) в SQL Server Management Studio. Чтобы открыть окно Properties (Свойства), щелкните правой кнопкой мыши на значке оператора и выберите из контекстного меню команду Properties (Свойства).
5. Планы запросов можно также сохранить. Чтобы сохранить план запроса, щелкните в панели плана правой кнопкой мыши и выберите из контекстного меню команду Save Execution Plan As (Сохранить план выполнения как). План сохраняется в формате XML с расширением .sqlplan . Его можно открыть через SQL Server Management Studio. выбрав из меню File (Файл) команды Open, File (Открыть, Файл).
6. То, что вы видели до сих пор - это предполагаемый план выполнения запроса, но можно просмотреть и действительный план выполнения. Действительный план выполнения аналогичен предполагаемому плану выполнения, но включает также действительные (не предполагаемые) значения количества строк, количества перемоток и т. д. Чтобы включить в запрос действительный план выполнения, нажмите (Ctrl+M) или выберите из меню Query (Запрос) команду Include Actual Execution Plan (Включить действительный план выполнения). Затем нажмите F5 и выполните запрос. Результаты запроса отображаются как обычно, но вы увидите также план выполнения, который показан на вкладке Execution Plan (План выполнения).

История стара как мир. Две таблицы:

• Cities – 100 уникальных городов.
• People – 10 млн. людей. У некоторых людей город может быть не указан.

Распределение людей по городам – равномерное.
Индексы на поля Cites.Id, Cites.Name, People .CityId – в наличии.

Нужно выбрать первых 100 записей People, отсортированных по Cites.

Засучив рукава, бодро пишем:

Select top 100 p.Name, c.Name as City from People p
order by c.Name

При этом мы получим что-то вроде:

За… 6 секунд. (MS SQL 2008 R2, i5 / 4Gb)

Но как же так! Откуда 6 секунд?! Мы ведь знаем, что в первых 100 записях будет исключительно Алматы! Ведь записей – 10 миллионов, и значит на город приходится по 100 тыс. Даже если это и не так, мы ведь можем выбрать первый город в списке, и проверить, наберется ли у него хотя бы 100 жителей.

Почему SQL сервер, обладая статистикой, не делает так:

Select * from People p
left join Cities c on c.Id=p.CityId
where p.CityId
in (select top 1 id from Cities order by Name)
order by c.

Данный запрос возвращает примерно 100 тыс. записей менее чем за секунду! Убедились, что есть искомые 100 записей и отдали их очень-очень быстро.

Однако MSSQL делает все по плану. А план у него, «чистый термояд» (с).

Вопрос к знатокам:
каким образом необходимо исправить SQL запрос или сделать какие-то действия над сервером, чтобы получить по первому запросу результат в 10 раз быстрее?

P.S.
CREATE TABLE . (


uniqueidentifier
ON
GO

CREATE TABLE . (
uniqueidentifier NOT NULL,
nvarchar(50) NOT NULL,
ON
GO

P.P.S
Откуда растут ноги:
Задача вполне реальная. Есть таблица с основной сущностью, от нее по принципу «звезда» отходит множество измерений. Пользователю нужно ее отобразить в гриде, предоставив сортировку по полям.
Начиная с некоторого размера основной таблицы сортировка сводится к тому, что выбирается окно с одинаковыми (крайними) значениями, (вроде «Алматы») но при этом система начинает жутко тормозить.
Хочется иметь ОДИН параметризированный запрос, который будет эффективно работать как с малым размером таблицы People так и с большим.

P.P.P.S
Интересно, что если бы City были бы NotNull и использовался InnerJoin то запрос выполняется мгновенно.
Интересно, что ДАЖЕ ЕСЛИ поле City было бы NotNull но использовался LeftJoin – то запрос тормозит.

В комментах идея: Сперва выбрать все InnerJoin а потом Union по Null значениям. Завтра проверю эту и остальные безумные идеи)

P.P.P.P.S Попробовал. Сработало!

WITH Help AS
select top 100 p.Name, c.Name as City from People p
INNER join Cities c on c.Id=p.CityId
order by c.Name ASC
UNION
select top 100 p.Name, NULL as City from People p
WHERE p.CityId IS NULL
SELECT TOP 100 * FROM help

Дает 150 миллисекунд при тех же условиях! Спасибо

Существует несколько способов получения плана выполнения, который использовать будет зависеть от ваших обстоятельств. Обычно вы можете использовать SQL Server Management Studio для получения плана, однако, если по какой-то причине вы не можете запустить свой запрос в SQL Server Management Studio, вам может оказаться полезным получить план через SQL Server Profiler или путем проверки кэш плана.

Метод 1 - Использование SQL Server Management Studio

SQL Server поставляется с несколькими опрятными функциями, которые позволяют легко выполнить план выполнения, просто убедитесь, что пункт меню «Включить фактический исполняемый план» (найденный в меню «Запрос») отмечен галочкой и запускает ваш запрос как обычно,

Если вы пытаетесь получить план выполнения для инструкций в хранимой процедуре, вы должны выполнить хранимую процедуру, например:

Exec p_Example 42

Когда ваш запрос завершен, вы увидите дополнительную вкладку «План выполнения», которая появится в панели результатов. Если вы запустили много утверждений, вы можете увидеть много планов, отображаемых на этой вкладке.

Отсюда вы можете проверить план выполнения в SQL Server Management Studio или щелкнуть правой кнопкой мыши по плану и выбрать «Сохранить план выполнения как...», чтобы сохранить план в файл в формате XML.

Способ 2 - Использование опций SHOWPLAN

Этот метод очень похож на метод 1 (на самом деле это то, что делает SQL Server Management Studio внутренне), однако я включил его для полноты или если у вас нет доступной SQL Server Management Studio.

Перед выполнением запроса запустите один следующих операторов. Оператор должен быть единственным оператором в пакете, т. Е. Вы не можете выполнять другой оператор одновременно:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS xml ON -- The is the recommended option to use

Это параметры подключения, поэтому вам нужно только запустить этот раз для каждого подключения. С этого момента все запущенные операторы будут сопровождаться дополнительным набором результатов , содержащим ваш план выполнения в желаемом формате, - просто запустите свой запрос, как обычно, чтобы увидеть план.

Как только вы закончите, вы можете отключить этот параметр со следующим утверждением:

SET < > OFF

Сравнение форматов плана выполнения

Если у вас нет сильных предпочтений, я рекомендую использовать параметр STATISTICS xml . Эта опция эквивалентна опции «Включить фактический план выполнения» в SQL Server Management Studio и предоставляет самую большую информацию в наиболее удобном формате.

• SHOWPLAN_TEXT - Displays a basic text based estimated execution plan, without executing the query
• SHOWPLAN_ALL - Displays a text based estimated execution plan with cost estimations, without executing the query
• SHOWPLAN_XML - Displays an xml based estimated execution plan with cost estimations, without executing the query. This is equivalent to the "Display Estimated Execution Plan..." option in SQL Server Management Studio.
• STATISTICS PROFILE - Executes the query and displays a text based actual execution plan.
• STATISTICS XML - Executes the query and displays an xml based actual execution plan. This is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.

Способ 3 - Использование SQL Server Profiler

Если вы не можете запустить свой запрос напрямую (или ваш запрос не выполняется медленно при его непосредственном запуске - помните, что мы хотим, чтобы план запроса выполнялся плохо), вы можете сделать план с использованием трассировки Profiler SQL Server. Идея состоит в том, чтобы запустить ваш запрос, пока трассировка, которая захватывает один из событий «Showplan», запущена.

Обратите внимание, что в зависимости от нагрузки вы можете использовать этот метод в рабочей среде, однако вы должны, очевидно, проявлять осторожность. Механизмы профилирования SQL Server предназначены для минимизации влияния на базу данных, но это не означает, что влияние производительности any не будет. У вас может также возникнуть проблема с фильтрацией и определением правильного плана в вашей трассе, если ваша база данных находится под большим использованием. Вы должны, очевидно, проверить с вашим администратором базы данных, чтобы убедиться, что они довольны тем, что вы делаете это в своей драгоценной базе данных!

1. Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace.
2. Under the "Events Selection" tab check "Show all events", check the "Performance" -> "Showplan XML" row and run the trace.
3. While the trace is running, do whatever it is you need to do to get the slow running query to run.
4. Wait for the query to complete and stop the trace.
5. To save the trace right click on the plan xml in SQL Server Profiler and select "Extract event data..." to save the plan to file in xml format.

План, который вы получаете, эквивалентен опции «Включить фактический план выполнения» в SQL Server Management Studio.

Способ 4. Проверка кэша запросов.

Если вы не можете выполнить свой запрос напрямую, и вы также не можете захватить трассировку профилировщика, вы можете получить оценочный план, проверив кеш-план SQL-запросов.

Мы проверяем кеш плана, запрашивая SQL Server DMVs . Ниже приведен базовый запрос, в котором будут перечислены все кэшированные планы запросов (как xml) вместе с их текстом SQL. В большинстве баз данных вам также необходимо будет добавить дополнительные условия фильтрации, чтобы отфильтровать результаты вплоть до планов, которые вас интересуют.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Выполните этот запрос и щелкните на плане XML, чтобы открыть план в новом окне - щелкните правой кнопкой мыши и выберите «Сохранить план выполнения как...», чтобы сохранить план в файл в формате XML.

Заметки:

Поскольку существует так много факторов (начиная от таблицы и схемы индекса до хранящихся данных и статистики таблицы), вы должны всегда пытаться получить план выполнения из интересующей вас базы данных (обычно тот, который испытывает проблему с производительностью).

Вы не можете зафиксировать план выполнения для зашифрованных хранимых процедур.

«фактические» и «оценочные» планы выполнения

План выполнения фактический - это тот, где SQL Server фактически выполняет запрос, тогда как план выполнения оцененный SQL Server выполняет то, что он делает без выполнения запрос. Хотя логически эквивалентный, фактический план выполнения намного полезнее, поскольку он содержит дополнительные данные и статистику о том, что на самом деле произошло при выполнении запроса. Это важно при диагностике проблем, когда оценки SQL-серверов отключены (например, когда статистика устарела).

Как интерпретировать план выполнения запроса?

Это тема, достойная достаточно для бесплатного в своем собственном праве.

• SQL Server 2008 - использование хеш-запросов и хэш-планов запроса
>

В дополнение к всеобъемлющему ответу, уже опубликованному иногда, полезно иметь возможность получить доступ к плану выполнения программно, чтобы извлечь информацию. Пример кода для этого ниже.

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Пример StartCapture Определение

CREATE PROCEDURE StartCapture @Spid INT, @TraceID INT OUTPUT AS DECLARE @maxfilesize BIGINT = 5 DECLARE @filepath NVARCHAR(200) = N"C:\trace_" + LEFT(NEWID(),36) EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL exec sp_trace_setevent @TraceID, 122, 1, 1 exec sp_trace_setevent @TraceID, 122, 22, 1 exec sp_trace_setevent @TraceID, 122, 34, 1 exec sp_trace_setevent @TraceID, 122, 51, 1 exec sp_trace_setevent @TraceID, 122, 12, 1 -- filter for spid EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid -- start the trace EXEC sp_trace_setstatus @TraceID, 1

Пример StopCapture Определение

CREATE PROCEDURE StopCapture @TraceID INT AS WITH XMLNAMESPACES ("http://schemas.microsoft.com/sqlserver/2004/07/showplan" as sql), CTE as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData, ObjectID, ObjectName, EventSequence, /*costs accumulate up the tree so the MAX should be the root*/ MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM fn_trace_getinfo(@TraceID) fn CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1) CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x CROSS APPLY (SELECT T.relop.value("@EstimatedTotalSubtreeCost", "float") AS EstimatedTotalSubtreeCost FROM xPlan.nodes("//sql:RelOp") T(relop)) ca WHERE property = 2 AND TextData IS NOT NULL AND ObjectName not in ("StopCapture", "fn_trace_getinfo") GROUP BY CAST(TextData AS VARCHAR(MAX)), ObjectID, ObjectName, EventSequence) SELECT ObjectName, SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM CTE GROUP BY ObjectID, ObjectName -- Stop the trace EXEC sp_trace_setstatus @TraceID, 0 -- Close and delete the trace EXEC sp_trace_setstatus @TraceID, 2 GO

Помимо методов, описанных в предыдущих ответах, вы также можете использовать бесплатный просмотрщик планов выполнения и инструмент оптимизации запросов План ApexSQL (с которым я недавно столкнулся).

Вы можете установить и интегрировать план ApexSQL в SQL Server Management Studio, поэтому планы выполнения можно просматривать непосредственно из SSMS.

Просмотр расчетных планов выполнения в Плане ApexSQL

1. Нажмите кнопку Новый запрос в SSMS и вставьте текст запроса в текстовое окно запроса. Щелкните правой кнопкой мыши и выберите «Отображать примерный план выполнения» в контекстном меню.

1. На диаграмме плана выполнения будет показана вкладка Планирование выполнения в разделе результатов. Затем щелкните правой кнопкой мыши план выполнения и в контекстном меню выберите вариант «Открыть в ApexSQL Plan».

1. Предполагаемый план выполнения будет открыт в Плане ApexSQL и может быть проанализирован для оптимизации запросов.

Просмотр фактических планов выполнения в Плане ApexSQL

Чтобы просмотреть фактический план выполнения запроса, продолжайте со второго шага, упомянутого ранее, но теперь, как только появится оценочный план, нажмите кнопку «Фактический» на главной панели ленты в Плане ApexSQL.

После нажатия кнопки «Фактический» будет показан фактический план выполнения с подробным предварительным просмотром параметров затрат вместе с другими данными плана выполнения.

Более подробную информацию о просмотре планов выполнения можно найти, следуя этой ссылке .

Мой любимый инструмент для получения и глубокого анализа планов выполнения запросов - SQL Sentry Plan Explorer . Это гораздо удобнее, удобнее и полно для детального анализа и визуализации планов выполнения, чем SSMS.

Вот примерный снимок экрана, чтобы вы поняли, какая функциональность предлагается инструментом:

Это только один из видов, доступных в инструменте. Обратите внимание на набор вкладок в нижней части окна приложения, что позволяет вам получать различные типы представления плана выполнения и полезную дополнительную информацию.

Кроме того, я не заметил никаких ограничений в его бесплатной версии, которая не позволяет использовать ее ежедневно или заставляет вас покупать версию Pro в конечном итоге. Итак, если вы предпочитаете придерживаться бесплатной версии, вам ничего не запрещается.

UPDATE: (Thanks to Martin Smith ) Plan Explorer now is free! See http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view for details.

Планы запросов можно получить в сеансе расширенных событий через событие query_post_execution_showplan . Вот пример сеанса XEvent:

/* Generated via "Query Detail Tracking" template. */ CREATE EVENT SESSION ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)), /* Remove any of the following events (or include additional events) as desired. */ ADD EVENT sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.sql_batch_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

После создания сеанса (в SSMS) перейдите в Обозреватель объектов и перейдите в раздел Управление | Расширенные события | Сессии. Щелкните правой кнопкой мыши сеанс «GetExecutionPlan» и запустите его. Щелкните его правой кнопкой мыши и выберите «Watch Live Data».

Затем откройте новое окно запроса и запустите один или несколько запросов. Вот для AdventureWorks:

USE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO

Через минуту или два вы увидите некоторые результаты на вкладке «GetExecutionPlan: Live Data». Выберите одно из событий query_post_execution_showplan в сетке, а затем щелкните вкладку «План запроса» под сеткой. Он должен выглядеть примерно так:

EDIT : The XEvent code and the screen shot were generated from SQL/SSMS 2012 w/ SP2. If you"re using SQL 2008/R2, you might be able to tweak the script to make it run. But that version doesn"t have a GUI, so you"d have to extract the showplan XML, save it as a *.sqlplan file and open it in SSMS. That"s cumbersome. XEvents didn"t exist in SQL 2005 or earlier. So, if you"re not on SQL 2012 or later, I"d strongly suggest one of the other answers posted here.

Начиная с SQL Server 2016+, для мониторинга производительности была представлена ​​функция Query Store. Он дает представление о выборе плана выполнения и производительности. Это не полная замена трассировки или расширенных событий, но поскольку она развивается от версии к версии, мы можем получить полностью функциональный магазин запросов в будущих выпусках SQL Server. Основной поток Query Store

1. SQL Server existing components interact with query store by utilising Query Store Manager.
2. Query Store Manager determines which Store should be used and then passes execution to that store (Plan or Runtime Stats or Query Wait Stats)
   • Plan Store - Persisting the execution plan information
   • Runtime Stats Store - Persisting the execution statistics information
   • Query Wait Stats Store - Persisting wait statistics information.
3. Plan, Runtime Stats and Wait store uses Query Store as an extension to SQL Server.

Enabling the Query Store : Query Store works at the database level on the server.

• Query Store is not active for new databases by default.
• You cannot enable the query store for the master or tempdb database.
• Available DMV

1. Collect Information in the Query Store : We collect all the available information from the three stores using Query Store DMV (Data Management Views).

   • Query Plan Store: Persisting the execution plan information and it is accountable for capturing all information that is related to query compilation.

     Runtime Stats Store: Persisting the execution statistics information and it is probably the most frequently updated store. These statistics represent query execution data.

     . У последнего также есть полезная бонусная функция выбора плана для конкретного оператора , а не всей партии. Вот как вы используете его для просмотра планов текущих запущенных операторов:

     SELECT p.query_plan FROM sys.dm_exec_requests AS r OUTER APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS p

     Текстовый столбец в результирующей таблице, однако, не очень удобен по сравнению с столбцом XML. Чтобы иметь возможность щелкнуть результат, который будет открыт на отдельной вкладке в виде диаграммы, без необходимости сохранять его содержимое в файл, вы можете использовать небольшой трюк (помните, что вы не можете просто использовать CAST (... AS XML)), хотя это будет работать только для одной строки:

     SELECT Tag = 1, Parent = NULL, = query_plan FROM sys.dm_exec_text_query_plan(-- set these variables or copy values -- from the results of the above query @plan_handle, @statement_start_offset, @statement_end_offset) FOR xml EXPLICIT