May 10, 2023 at 2:21 am
I have a couple of very long running jobs which are scheduled by SQL Agent.
These jobs are doing archiving task & runs to several hours. I would want to view the execution plan for the jobs to see what can be improve. May I know how can I view the actual execution plans for the jobs?
May 10, 2023 at 10:23 am
May 10, 2023 at 12:38 pm
Let's be clear up front, there is no such thing as an "estimated" or "actual" plan. There is an execution plan. And then, there's an execution plan with the addition of runtime metrics. So in order to see what we call the actual plan, metrics must be gathered.
You only get those two ways, capturing them after execution, or, using the lightweight metrics described in the link in the preceding post. With the lightweight metrics turned on, in addition to doing stuff like looking at the plan as it executes, you can also reference the DMV sys.dm_exec_query_plan_stats to see the last plan with runtime metrics included. That's only available after execution though, and it's only the "most recent" (there are lots of rules about just how much is captured and how, so there's some variability at play here).
Otherwise, frequently, but not always, what we call the "estimated" plan is a great resource for troubleshooting. And when you consider that two of the sources for "estimated" plans are the Query Store and the cache, well, those are the "actual" plans used to execute the query, just without runtime metrics. Don't shy from using these plans just because of the stigma that got attached to the name back in 2006/2007 when some idiot wrote a book (Hi, my name is Grant, and I'm the idiot) over-emphasizing the differences between actual & estimated plans.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply