Чтобы в Microsoft SQL Server Management Studio (SSMS) проанализировать длительные SQL-запросы:
1.Определите проблемный запрос.
Для поиска длительного запроса используйте диспетчер активности в SSMS. В нем перейдите в раздел «Процессы» и проанализируйте столбец «Время выполнения».
2.Соберите информацию о запросе.
Для этого в SSMS в меню Запрос выберите пункт Включить план выполнения. Отобразится графическое представление плана запроса.
Доступно два вида планов:
•Estimated Plan – предполагаемый план, строится на основе статистики;
•Actual Plan – актуальный план, строится на основе реального выполнения запроса с реальными данными.
Для анализа используйте Actual Plan.
3.Расшифруйте план выполнения.
Для этого определите операторы, которые занимают наибольшее время, попробуйте определить причину. В SSMS отображается подробная информацию о каждом операторе, например о количестве строк:
•Физическая операция (Physical Operation) – физическая операция для этой части плана выполнения, например объединение, поиск, сканирование;
•Логическая операция (Logical Operation) – логическая операция для этой части плана выполнения;
•Предполагаемая стоимость операций ввода-вывода (Estimated I/O Cost) – предполагаемая стоимость ввода/вывода. Оптимизатор запросов присваивает значения во время синтаксического анализа. Служит только в качестве сравнительного инструмента, помогающего определить, в чем заключаются затраты на эту операцию. Чем больше значение, тем более затратным является процесс;
•Предполагаемая стоимость ЦП (Estimated CPU Cost) – предполагаемая стоимость нагрузки на процессор. Оптимизатор запросов присваивает значения во время синтаксического анализа, служит только в качестве сравнительного инструмента, помогающего определить, в чем заключаются затраты на эту операцию. Чем больше значение, тем более затратным является процесс;
•Предполагаемая стоимость оператора (Estimated Operator Cost) – сумма предполагаемых затрат на ввод-вывод и процессор;
•Предполагаемая стоимость поддерева (Estimated Subtree Cost) – общая стоимость операции, а также других операций, которые предшествовали ей в запросе на данный момент;
•Фактическое число прочитанных строк (Actual Number of Rows) – фактическое количество строк, если запрос был выполнен;
•Расчетное число строк для всех выполнений (Estimated Number of Rows) – предполагаемое количество строк. Значение выводится на основе статистики, доступной оптимизатору запросов на момент составления плана выполнения;
•Предполагаемый размер строки (Estimated Row Size) – предполагаемый размер данных. Значение выводится на основе статистики, доступной оптимизатору запросов на момент составления плана выполнения;
•Отсортировано (Ordered) – логическое значение, которое указывает, упорядочены ли строки в операции;
•Идентификатор узла (NodeID) – порядковое значение, связанное с конкретной операцией в плане выполнения запроса.
4.Определите длительный запрос автоматически или вручную. Если запрос является длительным, то оптимизируйте его. Для этого вы можете изменить запрос, чтобы улучшить его производительность. Например, измените схему данных, индексы, статистику.
С помощью инструмента Database engine tuning adviser, встроенного в Microsoft SQL Server, можно проанализировать длительные запросы и сформировать рекомендации по созданию недостающих индексов или статистики. Подробнее см. в документации Microsoft статью Database engine tuning adviser.
ВАЖНО. Инструмент Database engine tuning adviser предложит создать отсутствующие индексы. Перед их созданием убедитесь, что индексы будут эффективными.
Запросы можно проанализировать вручную. Для этого:
1.Запустите SSMS и выполните запрос.
2.Из результата выполнения запроса откройте вкладку «Execution Plan» (План выполнения). Для этого в SSMS используйте сочетание клавиш CTRL + M или в меню Query выберите пункт Include Actual Execution Plan (Запрос – Включить фактический план выполнения).
3.Проанализируйте графическое представление плана выполнения. Обратите внимание на операторы, индексы, количество строк и предполагаемые затраты.
Проанализируйте план запроса и обратите внимание на операторы, такие как Clustered Index Scan, Nested Loop Join, Hash Match. Они показывают, как Microsoft SQL Server обрабатывает данные. Подробнее см. в документации Microsoft статью «Справочник по оператору логического и физического шоуплана». Clustered Index Scan (Сканирование кластеризованного индекса) – это оператор в плане выполнения запроса, который указывает на выполнение полного сканирования кластеризованного индекса. Кластеризованный индекс определяет порядок физического размещения данных в таблице на основе значений ключевого столбца. Другими словами, строки в таблице упорядочиваются так же, как и значения ключевого столбца в кластеризованном индексе. Nested Loop Join (Вложенное соединение циклов) – это один из операторов соединения в плане выполнения запроса. Он представляет собой метод объединения двух таблиц, использует вложенные циклы, где для каждой строки из внешней таблицы производится поиск соответствующих строк во внутренней таблице. Использование оператора Nested Loop Join эффективно, если: •внутренняя таблица небольшая или есть оптимальный индекс для условия соединения; •внешняя таблица отфильтрована, и ее размер существенно уменьшен; Использование оператора Nested Loop Join неэффективно, если: •внутренняя таблица очень большая, и нет оптимальных индексов; •используются условия соединения, которые не могут быть эффективно обработаны вложенными циклами. Hash Match – это оператор соединения в плане выполнения запроса, который использует хеширование для соединения двух наборов данных. Оператор применяется, когда Microsoft SQL Server определяет, что использование хешей для объединения данных будет более эффективным, чем вложенные циклы или другие методы соединения. Используйте оператор Hash Match, если: •внутренняя и внешняя таблицы большие и хеш-функция может быть эффективно распределена по данным; •внутренняя и внешняя таблицы отсортированы по ключевому столбцу. Не используйте оператор Hash Match: •хеш-функция плохо распределяется по данным. Это может привести к неравномерному распределению данных в хеш-таблице; •одна из таблиц слишком мала, и использование хеш-соединения не оправдано. |
Проверьте, используются ли индексы. Оператор Index Scan в плане запроса обычно говорит о том, что используемый индекс не очень эффективен либо он фрагментирован. Если поиск по индексам отсутствует, это может быть причиной медленного выполнения запроса. |
Оцените количество обрабатываемых строк. Если оно большое, это может указывать на неэффективность запроса или отсутствие необходимых индексов. |
Обратите внимание на предполагаемые затраты для каждого оператора. Они выражаются в процентах от общих затрат. Большие затраты могут указывать на места, где можно улучшить производительность. |
4.Протестируйте и проверьте изменения.
Для этого повторно выполните запрос и проанализируйте его производительность после внесения изменений. Проверьте, были ли эти изменения эффективными: посмотрите на новый план выполнения и сравните его со старым.
5.После успешного тестирования изменений убедитесь, что запрос продолжает работать эффективно в течение времени. Проводите регулярные проверки для убедительности.
Также при анализе запросов используйте раздел «Рекомендации по анализу ситуаций», а также ознакомьтесь с рекомендациями из статей на сайте Directum Club:
•Кэш планов и параметризация запросов. Часть 1. Анализ кэша планов
•5 рекомендаций для повышения производительности
| © Компания Directum, 2024 |
