В Microsoft SQL Server как я могу получить план выполнения запроса для запроса / хранимой процедуры?
Существует несколько методов получения плана выполнения, какой из них использовать, зависит от ваших обстоятельств. Обычно для получения плана можно использовать SQL Server Management Studio, однако если по каким-то причинам вы не можете запустить запрос в SQL Server Management Studio, то вам может оказаться полезной возможность получить план с помощью SQL Server Profiler или путем просмотра кэша планов.
exec p_Example 42
После завершения запроса в панели результатов должна появиться дополнительная закладка "План выполнения". Если вы выполнили много запросов, то вы можете увидеть много планов, отображаемых на этой вкладке. ![Скриншот плана выполнения][3] Отсюда вы можете просмотреть план выполнения в SQL Server Management Studio или щелкнуть правой кнопкой мыши на плане и выбрать "Save Execution Plan As ...", чтобы сохранить план в файл в формате XML.
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
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.
В дополнение к уже опубликованному исчерпывающему ответу иногда полезно иметь возможность программного доступа к плану выполнения для извлечения информации. Пример кода для этого приведен ниже.
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 сервер менеджмент студию
На планов запросов нажмите нажмите Ctrl + л или следующие кнопки.
На план текущий запрос, (только в SSMS 2016) используйте следующие кнопки перед выполнением запроса.
Помимо методов, описанных в предыдущих ответах, вы также можете использовать бесплатное средство просмотра планов выполнения и инструмент оптимизации запросов план ApexSQL (который я недавно столкнулся).
Вы можете установить и интегрировать план ApexSQL в среде SQL Server в студии, так что планы выполнения могут быть просмотрены с SSMS напрямую.
Просмотр предполагаемых планов выполнения плана ApexSQL
Просмотр фактических планов выполнения плана ApexSQL
Чтобы просмотреть фактический план выполнения запроса, и далее со 2-го шага упоминалось ранее, но теперь, после того, как предполагаемый план отображается, нажмите кнопку “фактическое” из основной ленты в план ApexSQL.
После того как кнопка “фактическое” нажата, фактический план выполнения будет отображаться с подробным просмотром параметров расходов наряду с другой план выполнения данных.
Более подробная информация о просмотре планов выполнения можно ознакомиться, перейдя по этой ссылке.
Мой любимый инструмент для получения и глубокого анализа планов выполнения запросов - SQL Sentry Plan Explorer. Он гораздо более дружественный, удобный и комплексный для детального анализа и визуализации планов выполнения, чем SSMS.
Вот примерный снимок экрана, чтобы вы имели представление о том, какие функциональные возможности предлагает инструмент:
Это только один из видов, доступных в инструменте. Обратите внимание на набор вкладок в нижней части окна приложения, которые позволяют получить различные виды представления плана выполнения, а также полезную дополнительную информацию.
Кроме того, я не заметил каких-либо ограничений в бесплатной версии, которые мешают использовать ее на ежедневной основе или заставляют вас в конечном итоге приобрести Pro-версию. Так что, если вы предпочитаете пользоваться бесплатной версией, ничто не запрещает вам это сделать.
UPDATE: (Спасибо Мартину Смиту) Plan Explorer теперь бесплатный! Подробности смотрите на http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view.
Планы запросов можно получить от сеанса расширенных событий с помощью 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) перейдите в Обозревателе объектов и вникать в Управление | расширенные события | сессии. Щелкните правой кнопкой мыши на "GetExecutionPlan" на сессии и запустить его. Щелкните правой кнопкой мыши еще раз и выберите "и смотреть живые данные и".
Затем откройте новое окно запроса и выполните один или несколько запросов. Здесь's один для 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: вкладка данные" по. Выберите одно из событий query_post_execution_showplan в сетке, а затем нажмите на "план" как запрос; вкладку таблица. Это должно выглядеть примерно так:
Редактировать: код xevent и снимок экрана были созданы из SQL/среда SSMS 2012 Ш/ с SP2. Если вы'повторного использования SQL 2008/R2, то может быть в состоянии настроить скрипт, чтобы запустить его. Но эта версия не't имеют графический интерфейс, так что вы'd должны извлечь инструкции showplan XML-файле, сохранить его как *.sqlplan файл и открыть его в SSMS. Что'ы громоздким. События xevent я'т существуют в SQL 2005 или раньше. Так что, если вы'повторно не на SQL 2012 или позже, я'd и настоятельно рекомендовать один из других ответов здесь выложил.
Начиная с SQL сервером 2016+, характеристика магазина запрос был введен для мониторинга производительности. Она дает представление о выборе плана запроса и производительности. Это не полная замена трассировки или расширенных событий, но, как он эволюционирует от версии к версии, мы могли бы получить полностью функциональный магазин запрос в будущих выпусках из SQL сервера. Первичный поток запросов
sys файла.database_query_store_options
(Язык Transact-SQL)в
Запрос План Магазина: Сохранение исполнение информационного плана и несет ответственность за захват все сведения, относящиеся к составлению запроса.
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)в
Запрос Статистика Магазине: Сохранение и захват подождать статистические данные.
sys файла.query_store_wait_stats
(язык Transact-SQL)в
Примечание: запрос ждать магазин статистика доступна только в SQL сервере 2017+
Здесь's одна важная вещь, чтобы знать, помимо всего сказанного выше.
Планы запросов часто слишком сложны, чтобы быть представлены с помощью встроенного в тип XML-столбца, который имеет ограничение 127 уровней вложенных элементов. Это одна из причин, почему Сыс.dm_exec_query_plan может возвращать null или даже выдавать сообщение об ошибке в ранее МС в SQL версии, так как правило, это'ы безопаснее использовать sys файла.dm_exec_text_query_plan вместо этого. Последний также имеет полезную особенность бонус на выбор план по конкретному заявлению, а не вся партия. Здесь's, как вы использовать его для просмотра планов на настоящее время с заявлениями:
в
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-столбцом. Чтобы быть в состоянии нажать на результат, чтобы быть открыт в отдельной вкладке, как в схеме, без того, чтобы сохранить ее содержимое в файл, вы можете использовать маленькую хитрость (помните, вы не можете просто использовать литой(... в 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 сервера студии (уже объяснил), это также возможно с Datagrip как пояснил здесь.
- Нажмите правой кнопкой мыши на операторе SQL, и выберите объяснять план.
- В области вывода, нажмите кнопку план.
- По умолчанию, вы видите изображение дерева запроса. Чтобы увидеть план запроса, щелкните по значку показать визуализацию, или пресс сочетание клавиш Ctrl+Шифт+Альт+Щ
Вы также можете сделать это через PowerShell с помощью набора статистики в формате XML, чтобы получить фактический план. Я'вэ написано так, что она сливается из нескольких планов в единый план;
########## 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-сервера.
Расчетная плана выполнения оптимизатор без выполнения SQL-запроса.
Для того, чтобы сделать предполагаемый план выполнения, вам нужно включить `присвоено установки до выполнения запроса.
НАБОР 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 |
После выполнения запроса мы заинтересованы в получении предполагаемый план выполнения, необходимо отключить `присвоено как, в противном случае, в ходе нынешней сессии базе будет создан только предполагаемый план выполнения, а не при выполнении предоставленной SQL-запросов.
SET SHOWPLAN_ALL OFF
В приложении для управления сервером SQL Studio, вы можете легко получить предполагаемый план выполнения любого SQL-запроса, нажав сочетание клавиш Ctrl+л`.
Фактический план выполнения SQL генерируется оптимизатор при выполнении SQL-запроса. Если статистика таблицы базы данных является точным, фактический план не должен существенно отличаться от предполагаемого одного.
Чтобы получить действительный план выполнения в SQL Server, вы должны включить статистику `ИО, время, профиль, Настройки, как показано на следующую команду SQL:
SET STATISTICS IO, TIME, PROFILE ON
Теперь, при выполнении предыдущего запроса, то SQL сервер будет генерировать следующий план выполнения:
| 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 Studio, вы можете легко получить предполагаемый план выполнения любого SQL-запроса, нажав сочетание клавиш Ctrl+м`.
для получения более подробной информации о получении план выполнения при использовании SQL-сервера, проверьте настоящей статьи.