Get an Actual Execution Plan of a Stored Procedure using temporary tables

Question

I need a way to obtain an actual execution plan of a complicated stored procedure, which executes as a part of a step of a scheduled SQL Server Agent job, and which uses lots of temporary tables.

Because of its use of temporary tables, I could not simply open its script and get its estimated execution plan. And because it runs in a production server and takes hours to execute that stored procedure so I cannot manually run it for for getting its actual execution plan.

The stored procedure runs on a SQL Server 2008 R2. How can I get its actual execution plan while it runs as a scheduled SQL Server Agent job? Perhaps by inserting some statements before and after the core of this stored procedure and save the actual execution plan in XML format ? Or other better approaches? Please advise.

Answer 1

The stored procedure runs on a SQL Server 2008 R2.

What a pitty that you have such old, unsupported version. Since SQL Server 2016 we have the feature "Query Store"

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16

Answer 2

You could use Lee Tudor's sp_sqltrace which I host on my web site. sp_sqltrace accepts an SQL batch and then sets up a trace filtered for the current spid. There are several options, including capturing the actual execution plan.

If you take this route, there are several things you need to consider. First of all, by default the trace self-destructs after five minutes. You need to use the parameter @trace_timeout to extend the time.

Next, keep in mind that capturing actual execution plans is expensive, and even if the trace is filtered for you spid, the trace causes all statements executed on the server to start generate actual execution plans. The effect of this depends on the workload. An analytic workload with few, but long-running queries will not suffer a lot. But if you have processes that run many short statements in rapid succession, you may cause severe performance issues on the server.

Answer 3

Hi @Norman K. F. Chow

How about this query:

SELECT  [ProcedureName] = OBJECT_NAME([ps].[object_id], [ps].[database_id]) 
       ,[ProcedureExecutes] = [ps].[execution_count] 
       ,[VersionOfPlan] = [qs].[plan_generation_num]
       ,[ExecutionsOfCurrentPlan] = [qs].[execution_count] 
       ,[Query Plan XML] = [qp].[query_plan]  

FROM [sys].[dm_exec_procedure_stats] AS [ps]
JOIN [sys].[dm_exec_query_stats] AS [qs] ON [ps].[plan_handle] = [qs].[plan_handle]
CROSS APPLY [sys].[dm_exec_query_plan]([qs].[plan_handle]) AS [qp]

WHERE [ps].[database_id] = DB_ID() AND  OBJECT_NAME([ps].[object_id], [ps].[database_id])  = 'PROC_Name'

Referring from this similar thread: How can I display the execution plan for a stored procedure?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".