如何获得查询的执行计划？（二）

最新推荐文章于 2025-05-14 22:27:35 发布

weixin_34318326

最新推荐文章于 2025-05-14 22:27:35 发布

阅读量106

点赞数

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/425699

版权

本文介绍了一种通过编程方式获取SQL执行计划的方法，利用自定义的StartCapture和StopCapture存储过程来启动和停止跟踪，从而收集执行计划信息，并提供了一个详细的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

英文原帖：

http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

除了以上回复的全面的答案，有时以编程方式访问执行计划会很有用。示例代码如下：

 
         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 
        
 
          
 
        
 
   
 
  

 
   本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1735404 
    ，如需转载请自行联系原作者