Начиная с версии SQL Server 2019, инструмент Redgate Monitor приобрел новые функциональные возможности, позволяющие администраторам баз данных и разработчикам легко диагностировать и устранять проблемы производительности. Теперь пользователи могут наблюдать не только оценку производительности запроса («оценочный план»), но и настоящий ход выполнения («реальный план»). Рассмотрим подробнее, как правильно пользоваться этими возможностями и какие преимущества они предоставляют.

Эта статья представляет собой подробный перевод оригинальной публикации “Troubleshooting SQL Server Queries using Actual Execution Plans”, адаптированной специалистом «Автомакон» специально для русскоязычной аудитории. Материал посвящен методикам диагностики и устранения проблем с производительностью запросов SQL Server путем изучения реальных планов выполнения.

Redgate Monitor показывает реальный план и статистику длительности для медленных запросов за любой заданный период. Это означает, что вы не только узнаете, как SQL Server выполнил ваш плохо работающий запрос, но также увидите точные данные о времени его работы. Любые значительные расхождения между предполагаемыми и реальными значениями количества строк будут выявлены, а Redgate Monitor выделит любые предупреждения и предложит рекомендации. Неэффективные запросы могут значительно замедлить работу вашего приложения, что может быть очень неприятно как для вас, так и для ваших пользователей, особенно если эти проблемы можно было бы избежать. Независимо от того, расследуете ли вы проблему производительности или активно настраиваете производительность, часто вам необходимо найти причину проблемы запроса, чтобы иметь возможность ее исправить, а планы запросов помогают с ее выявлением.

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

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

Когда в ходе расследования вы обнаружите «проблемный» запрос, который потребляет много ресурсов и вызывает замедление работы, вам потребуется выяснить, почему это происходит. Однако, просто взглянув на текст запроса, может быть неочевидно, почему этот запрос ведет себя таким образом или почему он внезапно начал работать хуже, особенно для больших запросов. Нам также нужно знать, что SQL Server сделал для выполнения этого запроса. Именно это нам и показывает план выполнения. Когда SQL Server получает новый запрос, Оптимизатор запросов SQL Server создает план выполнения, включающий все этапы и операции, которые должны быть сделаны для полного выполнения запроса и возврата необходимых данных.

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

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

Просмотр планов выполнения в Redgate Monitor

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

На странице «Обзор» в Redgate Monitor вы можете переместить окно фокусировки на нужный период исследования, возможно, на тот момент, когда ранее в течение дня была зафиксирована проблема с производительностью, и посмотреть на основные запросы, которые выполнялись в этот период. Если вы щелкнете на любом запросе в списке, вы увидите детали запроса, которые будут включать текст запроса и его план выполнения наряду с другой информацией. 

SQL Server Redgate Monitor покажет вам предполагаемый план выполнения для любого из основных запросов, который он извлекает из кэша планов. Он также может показать реальный план выполнения для любого наблюдаемого экземпляра, работающего под управлением SQL Server 2019 и выше, если вы активировали новую конфигурацию уровня базы данных LAST_QUERY_PLAN_STATS.

В чем дополнительная ценность просмотра реального плана? Давайте разберемся, просмотрев предполагаемый план для запроса в Redgate Monitor, а затем реальный план.

Просмотр предполагаемого плана

Вот предполагаемый план выполнения одного из основных запросов, который был выполнен в базе данных под названием PizzaParadise:

Разбивка каждого оператора в плане этого запроса и его предполагаемая стоимость — сильно помогает, поскольку она указывает, как ресурсы используются на каждом этапе запроса, и, следовательно, позволяет понять, какая именно часть вызывает проблему. Над каждым оператором мы видим две важные части информации: его предполагаемую процентную стоимость и предполагаемое количество строк, которое он вернет. Основываясь на том, сколько строк оптимизатор считает, что каждый оператор в плане должен обработать, он оценивает стоимость этого оператора (предполагаемые затраты на обработку ЦПУ и ввода-вывода) относительно общей стоимости плана. Таким образом, оценки стоимости указывают на те операторы, которые, по мнению оптимизатора, будут использовать больше всего ресурсов.

Если мы увеличим определенный участок плана, мы сможем лучше рассмотреть, какие таблицы были доступны и как обрабатывались данные. В данном случае сканирование индекса извлечет более 1,6 миллиона строк из таблицы заказов, а затем выполнятся агрегация, вычисления, сортировка и упорядочивание перед тем, как вернуть единственный результат строки с рейтингом продаж клиента. Среди этих «тяжелых» операций с данными оптимизатор предполагает, что оператор сортировки займет более 93% ресурсов, выделенных этому запросу:

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

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

Именно поэтому всегда полезно проверять оценки стоимости в плане оптимизатора, просматривая статистику выполнения в реальном плане после его выполнения.

Дополнительная ценность просмотра реального плана

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

Начиная с версии 2019 (CTP 2.4 и выше), SQL Server хранит последнюю версию реального плана для каждого запроса в отдельной области памяти. Вы можете получить последний реальный план для запроса из представления sys.dm_exec_query_plan_stats. Тем не менее, у вас по-прежнему нет возможности ретроспективного анализа прошлого, чтобы увидеть, что могло пойти не так в запусках, например, со вчерашнего дня, когда сообщалось о проблеме с производительностью сервера. К счастью, Redgate Monitor захватывает реальный план через определенные промежутки времени, чтобы впоследствии представить его. Это позволяет вам рассматривать временной интервал из прошлого, чтобы просмотреть собранные за это время реальные планы.

Это значит, что если вы отслеживаете экземпляры, работающие под управлением SQL Server 2019 или новее, Redgate Monitor может предоставить две копии одного и того же плана выполнения для запроса: «предполагаемый план», который мы видели раньше, и другую копию (реальный план) со статистикой выполнения, введенной механизмом выполнения после того, как план использовался для выполнения запроса. Вот реальный план выполнения для того же запроса в базе данных PizzaParadise, который мы видели ранее:

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

В данном случае говорится, что оптимизатор недооценил количество строк, которые оператор агрегирования потока должен был вернуть (предупреждение 2), и, следовательно, также количество строк, которые оператор сортировки должен был обработать (предупреждение 3). Это привело к недостаточному выделению памяти для операции сортировки, которая затем была сброшена на диск в tempdb (предупреждение 1).

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

Существует несколько распространенных причин таких «недооценок». Например, типичная ситуация при выполнении хранимых процедур заключается в том, что оптимизатор использует значение параметра, полученное путем динамического определения («sniffed»), которое возвращает очень мало строк, и поэтому выбирает план, оптимизированный для него, тогда как большинство других значений возвращают тысячи строк или наоборот, и, следовательно, большинство выполнений запроса будут использовать неоптимальный план. Это может привести к «нестабильной» производительности, когда запрос работает ужасно в один день, но нормально на следующий, например, после перекомпиляции плана.

История запросов Redgate Monitor: сравнение предполагаемых и фактических строк за время

Для каждого запроса на вкладке «Основные запросы», учитывая, что запрос имеет непростой план, Redgate Monitor анализирует реальный план, чтобы найти оператор, предположительно являющийся самым дорогим внутри плана, определяет величину разницы между предполагаемыми и фактическими строками и строит график этой разницы за указанный промежуток времени. Представленные вместе с предполагаемыми планами они позволяют вам одновременно видеть изменения предполагаемых планов и изменение производительности реальных планов в зависимости от изменений самого плана. Затем вы можете навести указатель мыши на отдельные планы, чтобы увидеть эту разницу и дорогую операцию, или нажать на точку, чтобы перейти к конкретному плану.

Рекомендации по запросам на основе предупреждений во время выполнения

Ранее мы рассмотрели, как SQL Server использует реальную информацию во время выполнения запросов для выдачи специальных предупреждений о возможных проблемах, таких как нехватка или перерасход памяти, необходимость выгрузки данных в tempdb либо ожидание операторами освобождения необходимого объема памяти. Redgate Monitor собирает эти предупреждения и предоставляет их через функцию рекомендаций, которую можно найти в разделе «Детали запроса» в разделе «Основные запросы», где вы можете увидеть рекомендации, извлеченные из последнего плана в определенном временном окне.

Как включить сбор реальных планов в Redgate Monitor

Предоставление реальных планов — функция доступна, начиная с версии SQL Server 2019 (CTP 2.4 и выше). Чтобы включить эту функцию на уровне базы данных, вам нужно включить новую конфигурацию уровня базы данных LAST_QUERY_PLAN_STATS, либо, если хотите, вы можете включить её на уровне сервера с помощью флага трассировки 2451. Для тех, кто беспокоится о дополнительной нагрузке, которую это может создать на их сервер, Microsoft заявляет в своей документации, что эта функциональность работает под инфраструктурой профилирования легких статистик выполнения запросов. Эта функциональность позволяет SQL Server хранить последний реальный план для каждой ручки плана в памяти, который заменяется новыми запусками. Здесь вступает в дело Redgate Monitor, который захватывает реальный план в любой заданный момент времени, чтобы сделать его доступным для последующего просмотра.

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