有多种方法可以获得执行计划,使用哪一种取决于您的情况。通常,您可以使用SQL Server Management Studio获取计划,但如果由于某些原因无法在其中运行查询,则可以通过SQL Server Profiler或检查计划缓存来获取计划。
SQL Server带有一些很棒的功能,使捕获执行计划变得非常容易,只需确保“包括实际执行计划”菜单项(在“查询”菜单下找到)已被选中,并像平常一样运行查询即可。
如果您正在尝试获取存储过程中语句的执行计划,则应执行存储过程,如下所示:
exec p_Example 42
当您的查询完成时,您应该会在结果窗格中看到一个名为“Execution plan”的额外选项卡。如果运行了多个语句,则可能会在此选项卡中显示许多计划。
从这里,您可以在SQL Server Management Studio中查看执行计划,或右键单击计划并选择“另存为执行计划...”将计划以XML格式保存到文件中。
这种方法与方法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 中的“包括实际执行计划”选项。如果您无法直接运行查询(或者在直接运行查询时查询不慢 - 记住我们想要一个性能不佳的查询计划),则可以使用 SQL Server Profiler 跟踪来捕获计划。想法是在运行正在捕获“Showplan”事件的跟踪时运行查询。
请注意,根据负载,您可以在生产环境中使用此方法,但是您应该显然谨慎使用。 SQL Server 分析机制旨在最小化对数据库的影响,但这并不意味着不会有任何性能影响。如果数据库正在繁忙使用中,则您可能还会遇到过滤和识别正确计划的问题。您显然应该与 DBA 检查他们是否满意您在他们珍贵的数据库上执行此操作!
您获得的计划相当于 SQL Server Management Studio 中的“包括实际执行计划”选项。
如果您无法直接运行查询并且还无法捕获分析器跟踪,则仍然可以通过检查 SQL 查询计划缓存来获取估计计划。
我们通过查询 SQL Server DMV 来检查计划缓存。以下是一个基本查询,它将列出所有缓存的查询计划(作为 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 Servers估计错误的问题(例如当统计信息过期时)时,这是必不可少的。
这是一个值得在自己的(免费)书籍中讨论的话题。
SET STATISTICS XML ON
,在你不想要显示在计划输出中的区域周围加上 SET STATISTICS XML OFF|ON
。当查询包含一个你不想/不需要在执行计划中看到的迭代(WHILE)时,我发现这很有用(否则它会太重并且长,无法在 SQL SERVER 中显示)。 - Roimer除了已经发布的全面答案之外,有时候能够以编程方式访问执行计划并提取信息是很有用的。以下是示例代码。
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
在ApexSQL Plan中查看实际执行计划
要查看查询的实际执行计划,请从之前提到的第二步继续,但现在,在显示预估计划后,从ApexSQL Plan的主功能区单击“实际”按钮。
点击“实际”按钮后,将显示实际执行计划,其中包括成本参数的详细预览以及其他执行计划数据。
想要了解有关查看执行计划的更多信息,请点击此链接。
我最喜欢用的工具是SQL Sentry Plan Explorer,它比SSMS更加用户友好、方便和全面地分析和可视化执行计划。
以下是一个示例屏幕截图,让你了解该工具提供的功能:
这只是该工具中可用的视图之一。请注意应用程序窗口底部的一组标签,可让您获取不同类型的执行计划表示以及有用的附加信息。
此外,我还没有注意到免费版有任何限制,阻止您每天使用它或最终强制购买专业版。因此,如果您愿意坚持使用免费版,也没有任何禁止您这样做的限制。
从SQL Server 2016+开始,引入了Query Store功能来监控性能。它可以提供查询计划选择和性能的见解。
它不完全替代跟踪或扩展事件,但随着版本的不断升级,我们可能会在未来的SQL Server版本中获得一个完全功能的查询存储。
Query Store的主要流程:
启用查询存储: 查询存储在服务器的数据库级别上工作。
tempdb
数据库启用查询存储。
sys.database_query_store_options
(Transact-SQL)
收集查询存储中的信息: 我们使用查询存储的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)
查询等待统计存储: 持久化并捕获等待统计信息。
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
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 w / SP2生成的。如果您正在使用SQL 2008/R2,则可能调整脚本以使其运行。但是,该版本没有GUI,因此您必须提取showplan XML,将其保存为*.sqlplan文件并在SSMS中打开它。这很麻烦。 SQL 2005或更早版本中不存在XEvents。因此,如果您不是在SQL 2012或更高版本上,则强烈建议在此处发布的其他答案之一。
预估执行计划是由优化器生成的,而不需要运行SQL查询。
为了获取预估执行计划,在执行查询之前,您需要启用SHOWPLAN_ALL
设置。
SET SHOWPLAN_ALL ON
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管理工具中,你可以通过按下CTRL+L
快捷键来轻松获取任何SQL查询的预估计划。
当运行SQL查询时,优化器会生成实际SQL执行计划。如果数据库表的统计信息准确无误,则实际计划应该与预估计划没有显著区别。
要在SQL Server上获取实际执行计划,您需要启用STATISTICS IO、TIME、PROFILE
设置,如下所示:
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应用程序中,您可以通过按下CTRL + M
键快捷键轻松获取任何SQL查询的估计执行计划。
与SQL Server Management Studio(已解释)一样,使用Datagrip也是可能的,具体请参考此处。
- 右键单击SQL语句,选择Explain plan。
- 在输出窗格中,点击Plan。
- 默认情况下,您会看到查询的树形表示。若要查看查询计划,请单击显示可视化图标或按Ctrl+Shift+Alt+U。
除了之前说的一切,还有一件很重要的事情需要知道。
查询计划通常太复杂,无法用内置的XML列类型表示,该类型具有127个嵌套元素的限制。这就是为什么在早期的MS SQL版本中,sys.dm_exec_query_plan可能会返回NULL
甚至引发错误的原因之一,因此一般最好使用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