在Microsoft SQL Server中,我怎样才能获得一个查询/存储过程的查询执行计划?
获得执行计划的方法有很多,使用哪种方法取决于你的情况。 通常情况下,你可以使用SQL Server Management Studio来获取计划,但是如果由于某些原因,你不能在SQL Server Management Studio中运行你的查询,那么你可能会发现通过SQL Server Profiler或通过检查计划缓存来获取计划是有帮助的。
exec p_Example 42
当你的查询完成后,你应该看到在结果窗格中出现一个额外的标签,标题为"执行计划"。 如果你运行了许多语句,那么你可能会看到许多计划显示在这个标签中。 执行计划的截图][3] 。 从这里你可以在SQL Server Management Studio中检查执行计划,或者在计划上点击右键,选择"将执行计划保存为..."将计划保存为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
我最喜欢的获取和深入分析查询执行计划的工具是SQL Sentry Plan Explorer。与SSMS相比,它在执行计划的细节分析和可视化方面更加友好、方便和全面。
下面是一个屏幕截图,让你了解该工具所提供的功能。
[][2]
这只是该工具中的一个视图。注意到应用程序窗口底部的一组标签,它可以让你获得不同类型的执行计划表示和有用的额外信息。
此外,我没有注意到它的免费版有任何限制,妨碍了日常使用或迫使你最终购买专业版。因此,如果你喜欢坚持使用免费版,没有什么可以禁止你这样做。
更新:(感谢Martin Smith)Plan Explorer现在是免费的了详见http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view。