Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как мы можем получить план выполнения запроса при использовании системы реляционных баз данных SQL Server.
План выполнения позволяет понять операции, выполняемые компонентом database engine при выполнении определенного SQL-запроса.
План выполнения SQL
Шаги, связанные с выполнением данного SQL-запроса, можно визуализировать на следующей диаграмме.
Первым шагом является анализ SQL-запроса в AST (Абстрактное синтаксическое дерево) . Во время синтаксического анализа база данных проверяет синтаксис SQL-запроса и ресурсы базы данных, на которые имеются ссылки (например, таблицы, столбцы). Анализатор может предварительно оптимизировать запрос, исключив части, которые не являются избыточными.
Полученное дерево запросов отправляется Оптимизатору, который создает пошаговый список операций, необходимых для получения желаемого набора результатов.
Полученный план выполнения отправляется Исполнителю, который запускает его и отправляет полученный набор результатов обратно клиенту базы данных.
Предполагаемый план выполнения SQL
Предполагаемый план выполнения генерируется оптимизатором без выполнения SQL-запроса.
Чтобы получить расчетный план выполнения, вам необходимо включить параметр SHOWPLAN_ALL перед выполнением запроса.
SET SHOWPLAN_ALL ON
Теперь при выполнении следующего SQL-запроса:
SELECT p.id
FROM post p
WHERE EXISTS (
SELECT 1
FROM post_comment pc
WHERE
pc.post_id = p.id AND
pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
SQL Server создаст следующий предполагаемый план выполнения:
| NodeId | Parent | LogicalOp | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions | |--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|--------------------| | 1 | 0 | NULL | 10 | NULL | NULL | NULL | 0.03374284 | NULL | | 2 | 1 | Top | 10 | 0 | 3.00E-06 | 15 | 0.03374284 | 1 | | 4 | 2 | Distinct Sort | 30 | 0.01126126 | 0.000504114 | 146 | 0.03373984 | 1 | | 5 | 4 | Inner Join | 46.698 | 0 | 0.00017974 | 146 | 0.02197446 | 1 | | 6 | 5 | Clustered Index Scan | 43 | 0.004606482 | 0.0007543 | 31 | 0.005360782 | 1 | | 7 | 5 | Clustered Index Seek | 1 | 0.003125 | 0.0001581 | 146 | 0.0161733 | 43 |
Для краткости, не все столбцы были включены в приведенный выше расчетный план выполнения. Тем не менее, вы можете получить представление о том, что содержит план и как он может помочь вам понять, что делает компонент database engine за кулисами при выполнении данного SQL-запроса.
После выполнения запроса, который нас интересует для получения предполагаемого плана выполнения, вам необходимо отключить SHOWPLAN_ALL , так как в противном случае текущий сеанс базы данных будет генерировать только предполагаемый план выполнения вместо выполнения предоставленных SQL-запросов.
SET SHOWPLAN_ALL OFF
Расчетный план среды SQL Server Management Studio
В приложении SQL Server Management Studio вы можете легко получить расчетный план выполнения для любого SQL-запроса, нажав сочетание клавиш CTRL+L .
Фактический план выполнения SQL
Фактический план выполнения SQL генерируется оптимизатором при выполнении SQL-запроса. Если статистика таблицы базы данных точна, фактический план не должен существенно отличаться от расчетного.
УСТАНОВИТЕ СТАТИСТИКУ ВВОДА-ВЫВОДА, ВРЕМЯ, ПРОФИЛЬ НА
Чтобы получить фактический план выполнения на SQL Server, вам необходимо включить ВВОД-вывод СТАТИСТИКИ, ВРЕМЯ, ПРОФИЛЬ параметры, как показано в следующей команде SQL:
SET STATISTICS IO, TIME, PROFILE ON
Теперь при выполнении предыдущего запроса SQL Server создаст следующий план выполнения:
| Rows | Executes | NodeId | Parent | LogicalOp | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | |------|----------|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------| | 10 | 1 | 1 | 0 | NULL | 10 | NULL | NULL | NULL | 0.03338978 | | 10 | 1 | 2 | 1 | Top | 1.00E+01 | 0 | 3.00E-06 | 15 | 0.03338978 | | 30 | 1 | 4 | 2 | Distinct Sort | 30 | 0.01126126 | 0.000478783 | 146 | 0.03338679 | | 41 | 1 | 5 | 4 | Inner Join | 44.362 | 0 | 0.00017138 | 146 | 0.02164674 | | 41 | 1 | 6 | 5 | Clustered Index Scan | 41 | 0.004606482 | 0.0007521 | 31 | 0.005358581 | | 41 | 41 | 7 | 5 | Clustered Index Seek | 1 | 0.003125 | 0.0001581 | 146 | 0.0158571 | SQL Server parse and compile time: CPU time = 8 ms, elapsed time = 8 ms. (10 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'post'. Scan count 0, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'post_comment'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (6 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
После выполнения запроса, который нас интересует, чтобы получить фактический план выполнения, вам необходимо отключить ВВОД-ВЫВОД СТАТИСТИКИ, ВРЕМЯ, ПРОФИЛЬ В настройках, подобных этому:
SET STATISTICS IO, TIME, PROFILE OFF
Фактический план среды SQL Server Management Studio
В приложении SQL Server Management Studio вы можете легко получить расчетный план выполнения для любого SQL-запроса, нажав сочетание клавиш CTRL+M .
Вывод
Если вам нужно оптимизировать выполнение данного SQL-запроса, вам необходимо проверить план выполнения, чтобы определить, что заставляет запрос выполняться медленно. Без визуализации плана выполнения запроса вы не сможете определить, что делает база данных под капотом при выполнении определенной инструкции SQL.