May 19, 2010 at 10:06 am
I don't have a lot of experience writing queries using DMVs and DMFs, but what I'm wondering is there a way to find execution plan history for a stored procedure?
What I'd like to do is be able to compare execution plans on occasion to determine if they have changed.
I tried the following and was able to retrieve plans for procs but I wasn't sure if SQL Server kept old cache plans around.
SELECT plan_handle, query_plan, objtype,object_name(objectid) ProcName
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE objtype ='Proc' and object_name(objectid) is not null
May 19, 2010 at 10:50 am
No, when a plan is flushed from cache, it's gone forever. The only thing you can do is try to capture the plans regularly (based on how often they flush out of cache) and then store them on the side.
"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
May 19, 2010 at 11:10 am
There's a profiler event that gets fired when SQL removes a plan from cache.
Checking....
Performance: Performance Statistics
Indicates that a compiled plan has been cached for the first time, recompiled, or evicted from the plan cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2010 at 2:02 pm
Thanks guys! That will be very helpful for me moving forward.
February 26, 2025 at 6:55 pm
Find below/attached script to get the execution plan of stored procedure and execution count.
--get plan handle using below query , passing procedure detail
select DEST.text,DEPS.plan_handle, DEPS.* from sys.dm_exec_procedure_stats as DEPS cross apply sys.dm_exec_sql_text(DEPS.sql_handle) dest
where text like '%sp_OAG%'
select DEST.text, DEPS.* from sys.dm_exec_procedure_stats as DEPS cross apply sys.dm_exec_sql_text(DEPS.sql_handle) dest
select DEST.text, DEPS.* from sys.dm_exec_procedure_stats as DEPS cross apply sys.dm_exec_sql_text(DEPS.sql_handle) dest order by execution_count desc
select DEST.text, DEPS.* from sys.dm_exec_procedure_stats as DEPS cross apply sys.dm_exec_sql_text(DEPS.sql_handle) dest where database_id=8 order by execution_count desc
select DEST.text, DEPS.* from sys.dm_exec_procedure_stats as DEPS cross apply sys.dm_exec_sql_text(DEPS.sql_handle) dest where database_id=8 and text like '%sp_Name%'order by execution_count desc
select * from sys.dm_exec_procedure_stats where object_name(object_id,database_id) = 'sp_Name'
--get execution plan using below query passing plan handle captured above
select * from sys.dm_exec_query_plan(0x0500FF7F06D96E3940212819010000000000000000000000)
SELECT TOP(25) p.name AS [SP Name], qs.last_logical_writes AS [LastLogicalWrites], qs.last_logical_reads AS [LastLogicalReads], qs.last_physical_reads AS [LastPhysicalReads],
qs.execution_count, (qs.last_elapsed_time/1000) as Last_RunTime_in_MS, qs.cached_time, qs.last_execution_time, qp.query_plan
,total_worker_time/1000000 WorkerTime
, total_elapsed_time/1000000 as TotalTime
, execution_count as executionCount
,total_elapsed_time/1000000/execution_count AS AvgExecutiontime
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID() and p.name like '%delete%'
order by last_elapsed_time desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply