March 2, 2023 at 8:55 am
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
March 2, 2023 at 2:49 pm
Nope. If it's already running, you can't retroactively start collecting the data necessary for a plan with runtime statistics.
However, on SQL Server 2019 or greater and Azure SQL Database, you can get the last actual plan from a DMV as outlined here.
Most of the time, the plan in cache, the plan captured in Query Store, is good enough to evaluate performance issues. What are called actual plans, just the plan plus runtime metrics, really are just the addition of those runtime metrics. Those are handy, especially to compare estimated row counts versus actual row counts. However, that's their primary purpose. The plan itself is still what you're going to use to understand the choices made by the optimizer and places where you can potentially get improvements. This doesn't demand, in all cases, a plan plus runtime metrics, aka, actual plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2023 at 3:00 am
You can also use sp_whoisactive.
Check this thread:
=======================================================================
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply