June 25, 2015 at 2:31 pm
Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where.
Thanks!
June 26, 2015 at 5:46 am
Nope. Nor should you want to. Gathering the extra bits of data that separate an actual plan from an estimated plan can be costly. You can quickly pull the plan out of cache after you've had a long running plan. You'll only be missing some stuff related to the statistics. If you really need it, go and get that, but a pretty healthy percentage of the time, you don't need it.
"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
June 26, 2015 at 5:53 pm
In most of the cases this will return you plan from the cache for the currently running statement:
select
s.session_id,
r.plan_handle,
substring(h.text, (r.statement_start_offset/2)+1 , case when r.statement_end_offset IN(0,-1) then 2147483647 else ((r.statement_end_offset - r.statement_start_offset)/2) + 1 end) As sql_text,
CAST(qp.query_plan AS XML) AS query_plan
from sys.dm_exec_sessions as s
LEFT JOIN sys.dm_exec_requests as r on s.session_id = r.session_id and s.last_request_start_time = r.start_time
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) h
OUTER APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) qp
where s.session_id = <your session id>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply