英文原帖:
http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
除了以上回复的全面的答案,有时以编程方式访问执行计划会很有用。示例代码如下:
1
2
3
4
|
DECLARE
@TraceID
INT
EXEC
StartCapture @@SPID, @TraceID
OUTPUT
EXEC
sp_help
'sys.objects'
/*<
-- Call your stored proc of interest here.*/
EXEC
StopCapture @TraceID
|
示例StartCapture定义
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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定义
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
29
30
31
32
33
34
|
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
|
本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1735404
,如需转载请自行联系原作者