How to capture actual execution plan for specific spid

Sam 1,371 Reputation points
2023-03-02T08:54:52.8533333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,596 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-03-02T21:30:55.58+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Tasadduq Burney 8,356 Reputation points MVP
    2023-03-02T08:57:15.33+00:00

    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

    0 comments No comments

  3. Seeya Xi-MSFT 16,436 Reputation points
    2023-03-03T06:25:15.9966667+00:00

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