How to capture actual execution plan for specific spid

Question

Hi All,

How to capture sql stmt(s) and actual execution plan of an already running spid? Lets say, we are not running any traces / extended events on the prod sql server and some user has come and says, hey, my search process is taking long time and here is my SPID#. I can run sp_whoisactive of DMV queries and check if it is blocking or having IO waittypes but if I want to capture Actual execution plan and blocked process report for that particular so that when we have that data , we can sent it to the team and tell this this what is happening at that time.

Can we do that for an already running spid or we should configure the XE/Trace and keep running on the server when the user tells at this time I am going to start my db testing?

We are on SQL Server 2017 EE,

Thanks,

Sam

Answer 1

You can use my beta_lockinfo for this purpose. Note that to see the actual execution plans so far for running queries, you need to enable TF7412.

Answer 2

It is not possible to capture the SQL statement(s) and the actual execution plan of an already running spid without running traces or extended events. To capture the actual execution plan and blocked process report for a particular spid, we need to set up traces or extended events beforehand.

However, we can use the DMV queries like sp_whoisactive to gather some information

Answer 3

Hi @Samantha r,

Unfortunately, it is not possible to capture the actual execution plan of an already running process (SPID) in SQL Server. Execution plans are generated at the start of the query execution and are not updated during the query execution.

However, you can capture the execution plan for a running query if you enable the appropriate settings before running the query. One way to do this is to use Extended Events to capture the information for the queries you are interested in.

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-ver16

https://www.sqlshack.com/using-sql-server-extended-events-to-monitor-query-performance/

As for capturing a Blocked Process Report (BPR), there are many methods. Please refer to this:

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking

Best regards,

Seeya

---

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".