SQL-Ex blog

Оператор с максимальной стоимостью

Каждый план выполнения в SQL Server содержит то, что оптимизатор определяет как оценочную стоимость каждого оператора. Все эти оценочные стоимости операторов складываются, что составляет оценочную стоимость всего плана выполнения.

Вы получаете одни и те же данные о плане выполнения, независимо от того, захватываете ли вы так называемый предварительный план (Estimated Plan), или получаете план выполнения с метриками времени выполнения, который называется фактическим планом (Actual Plan). Оценочные значения для плана выполнения, или для плана плюс метрики времени выполнения, остаются теми же самыми. Единственным исключением является событие перекомпиляции, которое может стать причиной изменения этих значений. В противном случае они будут одинаковыми.

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

Давайте рассмотрим оператор, чтобы увидеть точно, какое число я имею в виду. Для начала вот полный план выполнения:



Давайте сосредоточимся на одном операторе, фактически, самом дорогом операторе в плане, Key Lookup (поиск ключа). Здесь я выделил оценочную стоимость этого оператора:



Выделенное значение показывает, что стоимость одного этого оператора оценивается в 60% от полной стоимости запроса. Давайте посмотрим на тот же оператор в плане выполнения с метриками времени исполнения:



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

Для выделения оператора в наибольшей стоимостью только с помощью SSMS вам потребуется просмотреть весь план. Сторонние инструменты зачастую выделяют в списке оператор с максимальной стоимостью. Вы могли бы также запросить представление XML плана, чтобы найти оператор с максимальной стоимостью по NodeID, а затем найти этот NodeID в SSMS.

OK. Я нашел оператор с максимальной стоимостью. Что теперь?

Это сложная часть. Вы нашли оператор с максимальной стоимостью. Это оператор Key Lookup в нашем примере плана. Теперь нужно понять, что этот оператор делает, и действительно ли то, что он делает, должно столько стоить.

Иногда это просто. Вы имеете запрос SELECT * без предложения WHERE. Оператором с наибольшей стоимостью в таком плане является оператор Scan для выборки всех данных. Это имеет смысл.

Однако, как насчет оператора Key Lookup в нашем примере?

Начать с того, знаете ли вы, что делает оператор Key Lookup? В двух словах, был использован некластеризованный индекс для получения некоторого числа строк. Однако этот некластеризованный индекс не был покрывающим; это означает, что он не содержал всех столбцов, требуемых запросу, либо в ключе, либо в столбцах INCLUDE. Поэтому мы должны пройти туда, где хранятся данные, т.е. в кластеризованный индекс, и найти необходимые столбцы.

ОК. Итак, теперь мы знаем, что он делает. И мы должны определить в контексте этого плана, действительно ли это самая болезненная точка? Как мы это определим?

Как я уже говорил, это трудная часть. Мы должны начать смотреть подробные детали этого оператора. Мы знаем, что он делает, и мы можем определить, в частности, почему, рассматривая свойства и “Output List” (список вывода):



Мы запрашиваем столбец City из кластеризованного индекса. Да, это необходимо. Теперь, действительно ли это столько стоит? Ну, нам требуется снова взглянуть на детали в свойствах. Где это возможно, мы хотим получить план выполнения с метриками времени исполнения. Почему? Потому что они добавят фактические значения для некоторых оценок. Фактически, мы можем увидеть это в приведенном выше операторе, где показывается, что возвращается только 6% от предварительной оценки числа строк, а именно, 16 вместо 265. Это ключевой момент, связанный с данным оператором. Давайте посмотрим детали в свойствах, не увидим ли мы еще что-нибудь:



Я выделил три фрагмента данных. Фактическое число строк, фактическое число выполнений и оценочное число выполнений. Можно увидеть, что оценочное число выполнений составляло 265.054. Фактическое - 16. Уже одно это говорит о том, что оценочная стоимость этого оператора может быть неточной, по крайней мере, для выполнения данного запроса.

Нам нужен контекст, в котором выполняется оператор. Это означает понимание его связи с другими операторами плана.

Контекст оператора

Для полного понимания поведения данного оператора мы должны посмотреть на его связи с другими операторами в плане. В данном примере это оператор, который непосредственно предшествует нашему, тот, который находится выше и слева от него, является оператором соединения вложенными циклами (Nested Loops Join). Оператор Nested Loops Join выполняет перемещение по набору значений, переданных ему предыдущим оператором (в нашем случае это другой оператор Nested Loops Join), а затем ищет совпадающие значения в операциях ниже (у нас это оператор с наивысшей стоимостью - Key Lookup).

Опять таки мы должны посмотреть подробности в свойствах. Наша цель проста - понять, почему наш оператор Key Lookup думал, что он возвращает 265 строк, хотя возвращается только 16. Видно, что Nested Loops Join думал, что он получает 265.054 строк. Поскольку Nested Loops не является источником данных, мы должны переместиться дальше по плану.

Следующий оператор - это другой оператор Nested Loops. Он соединяет данные из двух таблиц, используя на обеих таблицах (StateProvince и Address) Index Seek:



Обратите внимание на Index Seek для таблицы Address. Здесь находится источник наших проблем. Оптимизатор думал, что он получает 265 строк, но фактически получил 16. Отсюда наше расхождение по всех остальных оценках.

Отлично, но где проблема?

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

Просмотр остальной части плана обнаруживает нечто, что может оказаться более проблемным:



Мы сканируем 19614 строк в таблице BusinessEntityAddress и 19820 строк в таблице Customer. Заметим также, что после соединений возвращается всего 3 строки. Однако мы заплатили за сканирование 20000 строк дважды плюс стоимость построения хэш-таблиц для поддержки операторов Hash Match. Таким образом, устранение наиболее дорогостоящего оператора Key Lookup, возможно, даст нам небольшое повышение производительности. Но фактически эти сканирования являются настоящими болевыми точками в сочетании с соединениями Hash Match, необходимыми для работы с большими наборами данных, которые мы здесь имеем.

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

Заключение

Всякий, кто говорит, что чтение плана легкое дело, вероятно, пытается что-то продать. На самом деле читать план непросто. Но вы имеете мой начальный список для старта. Затем постарайтесь понять оператор, который вы нашли при поиске оператора с самой высокой стоимостью. Наконец, поймите контекст этого оператора в плане. С этого вы начнете процесс понимания планов выполнения.