在Microsoft SQL Server如何获得查询/存储过程的查询执行计划?
当前回答
在SQL Server Management Studio:
“Ctrl + M”将生成实际执行计划
“Ctrl + L”将生成估计执行计划
“Shift + Alt + S”为客户端统计
“Ctrl + Alt + P”用于SQL Server Profiler中的跟踪查询。
其他回答
除了前面所说的,还有一件重要的事情需要知道。
Query plans are often too complex to be represented by the built-in XML column type which has a limitation of 127 levels of nested elements. That is one of the reasons why sys.dm_exec_query_plan may return NULL or even throw an error in earlier MS SQL versions, so generally it's safer to use sys.dm_exec_text_query_plan instead. The latter also has a useful bonus feature of selecting a plan for a particular statement rather than the whole batch. Here's how you use it to view plans for currently running statements:
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
预估执行计划
估计的执行计划是由Optimizer在不运行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 |
在运行我们想要获得估计执行计划的查询之后,您需要禁用SHOWPLAN_ALL,否则,当前数据库会话将只生成估计的执行计划,而不是执行提供的SQL查询。
SET SHOWPLAN_ALL OFF
SQL Server Management Studio估计计划
在SQL Server Management Studio应用程序中,您可以通过按CTRL+L快捷键轻松获得任何SQL查询的估计执行计划。
实际执行计划
实际的SQL执行计划由Optimizer在运行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应用程序中,您可以通过按CTRL+M快捷键轻松获得任何SQL查询的估计执行计划。
在SQL Server Management Studio:
“Ctrl + M”将生成实际执行计划
“Ctrl + L”将生成估计执行计划
“Shift + Alt + S”为客户端统计
“Ctrl + Alt + P”用于SQL Server Profiler中的跟踪查询。
查询计划可以通过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”会话并启动它。再次右键选择“观看实时数据”。
接下来,打开一个新的查询窗口并运行一个或多个查询。以下是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事件之一,然后单击网格下面的“Query Plan”选项卡。它应该看起来像这样:
编辑:XEvent代码和屏幕截图是从SQL/SSMS 2012 w/ SP2生成的。如果您使用的是SQL 2008/R2,则可以调整脚本以使其运行。但那个版本没有GUI,所以你需要提取showplan XML,保存为*。并在SSMS中打开。这是麻烦的。XEvents在SQL 2005或更早的版本中并不存在。所以,如果你没有使用SQL 2012或更高版本,我强烈建议你使用这里发布的其他答案之一。
解释执行计划可能会非常详细,并占用大量阅读时间,但总的来说,如果你在查询之前使用'explain',它应该会给你很多信息,包括哪些部分首先执行等等。 如果你想了解更多关于这方面的细节,我写了一篇关于这方面的小博客,它也会给你指明正确的裁判。 https://medium.com/swlh/jetbrains-datagrip-explain-plan-ac406772c470
推荐文章
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- .toArray(new MyClass[0]) or .toArray(new MyClass[myList.size()])?
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 是什么导致JNI调用变慢?
- 如何将SQL Azure数据库复制到本地开发服务器?
- 检查属性是否有属性
- SQL Server 2008不能用新创建的用户登录
- 如何快速清除JavaScript对象?
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- SQL Server中User和Login的区别