如何在SQL Server中获取查询执行计划？

24 浏览
0 Comments

如何在SQL Server中获取查询执行计划？

在 Microsoft SQL Server 中，我如何获取查询 / 存储过程的查询执行计划？

admin 更改状态以发布 2023年5月23日
0

2 答案

0 Comments

除了已发布的综合答案外，有时候能够以编程方式访问执行计划并提取信息是很有用的。以下是示例代码。

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

0
0 Comments

有许多获取执行计划的方法，取决于您的情况而定。通常可以使用SQL Server Management Studio获取计划，但是如果由于某些原因无法在SQL Server Management Studio中运行查询，则可能会发现通过SQL Server Profiler或检查计划缓存来获取计划非常有用。

方法1-使用SQL Server Management Studio

SQL Server带有一些非常方便的功能，使得捕获执行计划非常容易，只需确保“查询”菜单下的“包括实际执行计划”菜单项已选择，然后像平常一样运行查询即可。

Include Action Execution Plan menu item

如果您尝试获取存储过程中语句的执行计划，则应执行存储过程，如下所示：

exec p_Example 42

当查询完成后，您应该会在结果窗格中看到一个名为“执行计划”的额外选项卡。如果运行了许多语句，则可能会在此选项卡中显示许多计划。

Screenshot of an Execution Plan

从这里，您可以在SQL Server Management Studio中检查执行计划，或右键单击计划并选择“保存执行计划为...”将计划保存到XML格式的文件中。

方法2-使用SHOWPLAN选项

此方法与方法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 <

执行计划格式的比较

除非您有明显的偏好，否则我建议使用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中的“包括实际执行计划”选项。

方法3-使用SQL Server Profiler

如果无法直接运行查询（或者当您直接执行查询时查询不运行缓慢-请记住，我们希望获取执行效率低下的查询计划），则可以使用SQL Server Profiler跟踪捕获计划 。想法是在捕获一个“Showplan”事件的跟踪运行时运行查询。

请注意，根据负载情况，您可以在生产环境中使用此方法，但是您应该显然要小心。 SQL Server的专业机制旨在最大限度地减少对数据库的影响，但这并不意味着不会有任何性能影响。 如果您的数据库正在被大量使用，您可能还会遇到过滤和识别正确查询计划的问题。 您显然应该与您的DBA核实他们是否满意您在他们宝贵的数据库上进行此操作！

  1. 打开SQL Server探查器并创建一个新跟踪，连接到您希望记录跟踪的目标数据库。
  2. 在“事件选择”选项卡下，选中“显示所有事件”，选中“性能”->“Showplan XML”行并运行跟踪。
  3. 在跟踪运行时，执行你需要运行的缓慢查询所需的任何操作。
  4. 等待查询完成并停止跟踪。
  5. 要保存跟踪，请右键单击SQL Server探查器中的计划XML，并选择“提取事件数据...”以将计划保存为XML格式的文件。

您获得的计划等效于SQL Server Management Studio中的“包括实际执行计划”选项。

方法4-检查查询高速缓存

如果您无法直接运行查询而且也无法捕获探查器跟踪，那么您仍然可以通过检查SQL查询计划高速缓存来获取估计计划。

我们通过查询SQL Server DMVs来检查计划高速缓存。 以下是一个基本查询，它将列出所有缓存的查询计划（作为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格式的文件。

注：

由于涉及的因素如此之多（从表和索引架构到存储的数据和表统计信息），因此您应该始终尝试从您感兴趣的数据库中（通常是遇到性能问题的数据库）获得执行计划。

您无法捕获加密存储过程的执行计划。

“实际” vs“估计”执行计划

实际执行计划是SQL Server实际运行查询的执行计划，而估计执行计划是SQL Server在不执行查询的情况下计算出该怎么做。虽然在逻辑上是等效的，但实际执行计划更有用，因为它包含有关执行查询时实际发生的详细信息和统计信息。在诊断SQL Server估计错误（例如，当统计信息过时时）的问题时，这是必不可少的。

我该如何解释查询执行计划？

这个主题值得写一本（免费）书。

另请参阅:

0