Как получить план выполнения запроса?
В Microsoft SQL Server, как я могу получить план выполнения запроса для хранимой процедуры?
13 ответов
Существует несколько способов получения плана выполнения, который будет зависеть от ваших обстоятельств. Обычно вы можете использовать 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 <<option>> OFF
Сравнение форматов плана выполнения
Если у вас нет сильных предпочтений, я рекомендую использовать STATISTICS XML вариант. Этот параметр эквивалентен параметру "Включить фактический план выполнения" в SQL Server Management Studio и предоставляет большую часть информации в наиболее удобном формате.
SHOWPLAN_TEXT- Отображает базовый текстовый примерный план выполнения без выполнения запроса.SHOWPLAN_ALL- Отображает основанный на тексте оценочный план выполнения с оценками затрат без выполнения запроса.SHOWPLAN_XML- Отображает оценочный план выполнения на основе XML с оценками затрат без выполнения запроса. Это эквивалентно параметру "Показать примерный план выполнения..." в SQL Server Management Studio.STATISTICS PROFILE- Выполняет запрос и отображает текстовый план фактического выполнения.STATISTICS XML- Выполняет запрос и отображает фактический план выполнения на основе XML. Это эквивалентно параметру "Включить фактический план выполнения" в SQL Server Management Studio.
Метод 3 - Использование SQL Server Profiler
Если вы не можете запустить свой запрос напрямую (или ваш запрос не выполняется медленно, когда вы выполняете его напрямую - помните, что мы хотим, чтобы план запроса выполнялся плохо), тогда вы можете захватить план с помощью трассировки SQL Server Profiler. Идея состоит в том, чтобы выполнить ваш запрос во время трассировки, которая захватывает одно из событий "Showplan".
Обратите внимание, что в зависимости от нагрузки вы можете использовать этот метод в производственной среде, однако вам следует соблюдать осторожность. Механизмы профилирования SQL Server предназначены для минимизации воздействия на базу данных, но это не означает, что не будет никакого влияния на производительность. У вас также могут возникнуть проблемы с фильтрацией и определением правильного плана в вашей трассировке, если ваша база данных интенсивно используется. Вы должны, очевидно, проконсультироваться со своим администратором базы данных, чтобы узнать, довольны ли они тем, что вы делаете это на своей драгоценной базе данных!
- Откройте SQL Server Profiler и создайте новую трассировку, соединяющуюся с нужной базой данных, для которой вы хотите записать трассировку.
- На вкладке "Выбор событий" установите флажок "Показать все события", проверьте строку "Производительность" -> "Showplan XML" и запустите трассировку.
- Пока трассировка выполняется, делайте все, что вам нужно, чтобы запустить медленный запрос.
- Дождитесь завершения запроса и остановите трассировку.
- Чтобы сохранить трассировку, щелкните правой кнопкой мыши план xml в SQL Server Profiler и выберите "Извлечь данные события...", чтобы сохранить план в файл в формате XML.
Полученный план эквивалентен параметру "Включить фактический план выполнения" в SQL Server Management Studio.
Метод 4 - Проверка кеша запросов
Если вы не можете выполнить свой запрос напрямую и также не можете получить трассировку профилировщика, вы все равно можете получить примерный план, проверив кэш плана запросов SQL.
Мы проверяем кэш плана, запрашивая DMV-файлы SQL Server. Ниже приведен базовый запрос, в котором будут перечислены все кэшированные планы запросов (в формате 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-серверов отключены (например, когда статистика устарела).
Как мне интерпретировать план выполнения запроса?
Это тема, достойная отдельной (бесплатной) книги.
Смотрите также:
В дополнение к уже опубликованному исчерпывающему ответу иногда полезно иметь программный доступ к плану выполнения для извлечения информации. Пример кода для этого ниже.
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
Предполагая, что вы используете Microsoft SQL Server Management Studio
- Для оценочного плана запроса вы можете нажать Ctrl + L или следующую кнопку.
- Для Actual Query Plan вы можете нажать Ctrl + M или следующую кнопку перед выполнением запроса.
- Для Live Query Plan(только в SSMS 2016) используйте следующую кнопку перед выполнением запроса.
Помимо методов, описанных в предыдущих ответах, вы также можете использовать бесплатную программу просмотра плана выполнения и инструмент оптимизации запросов ApexSQL Plan (с которым я недавно столкнулся).
Вы можете установить и интегрировать ApexSQL Plan в SQL Server Management Studio, чтобы планы выполнения можно было просматривать непосредственно из SSMS.
Просмотр примерных планов выполнения в ApexSQL Plan
- Нажмите кнопку " Новый запрос" в SSMS и вставьте текст запроса в текстовое окно запроса. Щелкните правой кнопкой мыши и выберите "Показать примерный план выполнения" в контекстном меню.
- Диаграммы плана выполнения будут показаны на вкладке План выполнения в разделе результатов. Затем щелкните правой кнопкой мыши план выполнения и в контекстном меню выберите опцию "Открыть в ApexSQL Plan".
- Предполагаемый план выполнения будет открыт в ApexSQL Plan и может быть проанализирован для оптимизации запросов.
Просмотр фактических планов выполнения в ApexSQL Plan
Чтобы просмотреть Фактический план выполнения запроса, продолжите со 2-го шага, упомянутого ранее, но теперь, когда отображается Предполагаемый план, нажмите кнопку "Факт" на главной ленточной панели в Плане ApexSQL.
После нажатия кнопки "Фактически" будет показан Фактический план выполнения с подробным предварительным просмотром параметров стоимости вместе с другими данными плана выполнения.
Дополнительную информацию о просмотре планов выполнения можно найти по этой ссылке.
Мой любимый инструмент для получения и глубокого анализа планов выполнения запросов - SQL Sentry Plan Explorer. Это гораздо более удобный для пользователя, удобный и всеобъемлющий для детального анализа и визуализации планов выполнения, чем SSMS.
Вот пример снимка экрана, чтобы вы могли понять, какие функции предлагает инструмент:
Это только одно из представлений, доступных в инструменте. Обратите внимание на набор вкладок в нижней части окна приложения, который позволяет вам получать различные типы представления плана выполнения, а также полезную дополнительную информацию.
Кроме того, я не заметил каких-либо ограничений бесплатной версии, которые не позволяют использовать его ежедневно или вынуждают покупать версию Pro в конце концов. Так что, если вы предпочитаете придерживаться бесплатной версии, ничто не запрещает вам делать это.
ОБНОВЛЕНИЕ: (Спасибо Мартину Смиту) Plan Plan теперь бесплатно! См. http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view для получения подробной информации.
Начиная с SQL Server 2016+, была добавлена функция хранилища запросов для мониторинга производительности. Это дает представление о выборе плана запроса и производительности. Это не полная замена трассировки или расширенных событий, но по мере развития от версии к версии мы могли бы получить полнофункциональное хранилище запросов в будущих выпусках SQL Server. Основной поток Query Store
- Существующие компоненты SQL Server взаимодействуют с хранилищем запросов с помощью диспетчера хранилища запросов.
- Диспетчер хранилища запросов определяет, какое хранилище следует использовать, а затем передает выполнение этому хранилищу (статистика плана или времени выполнения или статистика ожидания запроса)
- Хранилище планов - сохранение информации о плане выполнения
- Runtime Stats Store - Сохранение информации статистики выполнения
- Query Wait Stats Store - Сохраняющаяся информация статистики ожидания.
- Хранилище Plan, Runtime Stats и Wait использует хранилище запросов в качестве расширения SQL Server.
Включение хранилища запросов: хранилище запросов работает на уровне базы данных на сервере.
- По умолчанию хранилище запросов неактивно для новых баз данных.
- Вы не можете включить хранилище запросов для мастера или
tempdbбаза данных. - Доступен DMV
sys.database_query_store_options(Transact-SQL)
Сбор информации в хранилище запросов. Мы собираем всю доступную информацию из трех хранилищ, используя Query Store DMV (представления управления данными).
Хранилище плана запросов. Сохраняет информацию о плане выполнения и отвечает за сбор всей информации, связанной с компиляцией запроса.
sys.query_store_query(Transact-SQL)sys.query_store_plan(Transact-SQL)sys.query_store_query_text(Transact-SQL)Хранилище статистики времени выполнения: Сохраняет информацию статистики выполнения, и, вероятно, это наиболее часто обновляемое хранилище. Эта статистика представляет данные выполнения запроса.
sys.query_store_runtime_stats(Transact-SQL)Query Wait Stats Store: сохранение и сбор статистических данных ожидания.
sys.query_store_wait_stats(Transact-SQL)
ПРИМЕЧАНИЕ. Хранилище статистики ожидания запросов доступно только в SQL Server 2017+
Планы запросов можно получить из сеанса расширенных событий через query_post_execution_showplan событие. Вот пример сеанса XEvent:
/*
Generated via "Query Detail Tracking" template.
*/
CREATE EVENT SESSION [GetExecutionPlan] 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 ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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 ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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 ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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 ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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 ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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 ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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) перейдите в Обозреватель объектов и углубитесь в Management | Расширенные события | Сессии. Щелкните правой кнопкой мыши сеанс "GetExecutionPlan" и запустите его. Щелкните правой кнопкой мыши еще раз и выберите "Просмотр данных в реальном времени".
Затем откройте новое окно запроса и выполните один или несколько запросов. Вот один для 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 в сетке, а затем перейдите на вкладку "План запроса" под сеткой. Это должно выглядеть примерно так:
РЕДАКТИРОВАТЬ: XEvent код и снимок экрана были сгенерированы из SQL/SSMS 2012 с SP2. Если вы используете SQL 2008/R2, вы можете настроить скрипт для его запуска. Но эта версия не имеет графического интерфейса, поэтому вам нужно будет извлечь XML showplan, сохранить его как файл *.sqlplan и открыть его в SSMS. Это громоздко. XEvents не существовало в SQL 2005 или более ранних версиях. Поэтому, если вы не используете SQL 2012 или более позднюю версию, я настоятельно рекомендую один из других ответов, опубликованных здесь.
Как я объяснил в этой статье, при использовании SQL Server вы можете получить два типа плана выполнения.
Предполагаемый план выполнения
Предполагаемый план выполнения создается оптимизатором без выполнения запроса SQL.
Чтобы получить примерный план выполнения, вам необходимо включить SHOWPLAN_ALL настройка перед выполнением запроса.
ВКЛЮЧИТЬ SHOWPLAN_ALL
Теперь при выполнении следующего 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 |
После выполнения запроса, который нас интересует, чтобы получить предполагаемый план выполнения, необходимо отключить SHOWPLAN_ALL поскольку в противном случае текущий сеанс базы данных будет генерировать только предполагаемый план выполнения, а не выполнять предоставленные запросы SQL.
SET SHOWPLAN_ALL OFF
Предполагаемый план SQL Server Management Studio
В приложении SQL Server Management Studio вы можете легко получить предполагаемый план выполнения для любого SQL-запроса, нажав CTRL+L сочетание клавиш.
Фактический план выполнения
Фактический план выполнения SQL создается оптимизатором при выполнении запроса SQL. Если статистика таблицы базы данных точна, фактический план не должен существенно отличаться от предполагаемого.
Чтобы получить фактический план выполнения на SQL Server, вам необходимо включить STATISTICS IO, TIME, PROFILE настройки, как показано следующей командой 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.
После выполнения запроса, который нас интересует, чтобы получить фактический план выполнения, вам необходимо отключить STATISTICS IO, TIME, PROFILE ON такие настройки:
SET STATISTICS IO, TIME, PROFILE OFF
Фактический план SQL Server Management Studio
В приложении SQL Server Management Studio вы можете легко получить предполагаемый план выполнения для любого SQL-запроса, нажав CTRL+M сочетание клавиш.
Дополнительные сведения о получении плана выполнения при использовании SQL Server см. В этой статье.
Вот одна важная вещь, которую нужно знать в дополнение ко всему сказанному ранее.
Планы запросов часто слишком сложны, чтобы быть представленными встроенным типом столбца XML, который имеет ограничение в 127 уровней вложенных элементов. Это одна из причин, по которой sys.dm_exec_query_plan может вернуться NULL или даже выдать ошибку в более ранних версиях MS SQL, поэтому обычно безопаснее использовать sys.dm_exec_text_query_plan. Последний также имеет полезную бонусную функцию выбора плана для конкретного оператора, а не для всей партии. Вот как вы можете использовать его для просмотра планов текущих операторов:
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, [ShowPlanXML!1!!XMLTEXT] = 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
Как и в SQL Server Management Studio (уже объяснено), это также возможно с помощью Datagrip, как описано здесь.
- Щелкните правой кнопкой мыши оператор SQL и выберите "Объяснить план".
- На панели "Вывод" нажмите "План".
- По умолчанию вы видите древовидное представление запроса. Чтобы увидеть план запроса, щелкните значок Показать визуализацию или нажмите Ctrl+Shift+Alt+U
Вы также можете сделать это через PowerShell, используя SET STATISTICS XML ON, чтобы получить фактический план. Я написал так, что он объединяет планы с несколькими утверждениями в один план;
########## BEGIN : SCRIPT VARIABLES #####################
[string]$server = '.\MySQLServer'
[string]$database = 'MyDatabase'
[string]$sqlCommand = 'EXEC sp_ExampleSproc'
[string]$XMLOutputFileName = 'sp_ExampleSproc'
[string]$XMLOutputPath = 'C:\SQLDumps\ActualPlans\'
########## END : SCRIPT VARIABLES #####################
#Set up connection
$connectionString = "Persist Security Info=False;Integrated Security=true;Connection Timeout=0;Initial Catalog=$database;Server=$server"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
#Set up commands
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$command.CommandTimeout = 0
$commandXMLActPlanOn = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML ON",$connection)
$commandXMLActPlanOff = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML OFF",$connection)
$connection.Open()
#Enable session XML plan
$result = $commandXMLActPlanOn.ExecuteNonQuery()
#Execute SP and return resultsets into a dataset
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
#Set up output file name and path
[string]$fileNameDateStamp = get-date -f yyyyMMdd_HHmmss
[string]$XMLOutputFilePath = "$XMLOutputPath$XMLOutputFileName`_$fileNameDateStamp.sqlplan"
#Pull XML plans out of dataset and merge into one multi-statement plan
[int]$cntr = 1
ForEach($table in $dataset.Tables)
{
if($table.Columns[0].ColumnName -eq "Microsoft SQL Server 2005 XML Showplan")
{
[string]$fullXMLPlan = $Table.rows[0]."Microsoft SQL Server 2005 XML Showplan"
if($cntr -eq 1)
{
[regex]$rx = "\<ShowPlanXML xmlns\=.{1,}\<Statements\>"
[string]$startXMLPlan = $rx.Match($fullXMLPlan).Value
[regex]$rx = "\<\/Statements\>.{1,}\<\/ShowPlanXML\>"
[string]$endXMLPlan = $rx.Match($fullXMLPlan).Value
$startXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
}
[regex]$rx = "\<StmtSimple.{1,}\<\/StmtSimple\>"
[string]$bodyXMLPlan = $rx.Match($fullXMLPlan).Value
$bodyXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
$cntr += 1
}
}
$endXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
#Disable session XML plan
$result = $commandXMLActPlanOff.ExecuteNonQuery()
$connection.Close()
Объяснение плана выполнения может быть очень подробным и занимает довольно много времени для чтения, но в целом, если вы используете "объяснение" перед запросом, он должен предоставить вам много информации, в том числе, какие части были выполнены первыми и так далее. Если вы хотите прочитать об этом немного подробнее, я составил небольшой блог об этом, который также указывает вам на правильные ссылки.https://medium.com/swlh/jetbrains-datagrip-explain-plan-ac406772c470
В SQL Server Management Studio:
«Ctrl + M» сгенерирует фактический план выполнения.
«Ctrl + L» сгенерирует предполагаемый план выполнения.
«Shift + Alt + S» для клиентской статистики
«Ctrl + Alt + P» для отслеживания запроса в SQL Server Profiler.