英文原帖：
http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
查询执行计划也可以从Extended Events会话通过query_post_execution_showplan事件获得。这是一个XEvent会话示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
/*
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）定位到Object Explorer并下拉到Management | Extended Events | Sessions。右击“GetExecutionPlan”会话并启动它。再次右击它并选择“Watch Live Data”。
接下来，打开一个新的查询窗口并运行一个或多个查询。这是一个对AdventureWorks库的示例：
|
1
2
3
4
5
6
7
8
9
10
|
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，你可能需要稍微调整下脚本让它运行。但那个版本没有图形界面，所以你只能抽取XML格式的执行计划，保存为一个*.sqlplan文件并在SSMS中打开它。那是麻烦的。XEvents不存在于SQL 2005或之前的版本。因此，如果你不是使用SQL 2012或之后版本，我强烈建议你使用这里答复其他方法。
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1735405 ，如需转载请自行联系原作者
