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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply