July 26, 2013 at 12:50 am
Hi
I am looking in to sys.dm_exec_procedure_stats, one of the SP cached on 06/20 and the same plan is used for all the subsequent execution till date(July 26). This I confirmed, by looking at cached_time and last_execution_time of sys.dm_exec_procedure_stats.
But when I look at the sys.dm_exec_query_stats for the same plan_handle the creation_time shows different date as 07/21. – I thought it would be same as cached_time of sys.dm_exec_procedure_stats
Any idea why it differs for the same plan_handle ?
July 26, 2013 at 4:19 pm
Without looking or testing, I would assume that the value you see in exec_procedure_stats is when the procedure originally was compiled, but in query stats you see the recompilation times for the individual statements.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 29, 2013 at 5:04 am
Ok, in this case why the cached plan is not updated since the statements were recompiled?
When I compare the current cached plan with previous plan (10 days old) there is no change. Do you mean when the statement level recompilation occurs the plan in not cached similar to WITH RECOMPILE.
July 29, 2013 at 5:11 am
Just because recompilation occurs, does not mean that the optimizer will make a different decision, but it may very well arrive at the same plan.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 1:21 am
if the optimizer generate the new plan during the statement level recompilation, will it remove the old cached plan which is created for the SP?
July 30, 2013 at 1:35 am
No. Say that a procedure consists of four statements, A, B, C and D. Only statement C refers to the table X. Because statistics on X are updated, the plan for C is invalidated. The plans for A, B and D are not.
But even if these statements would be recompiled as well, the date for the outer structure, the procedure would not change. That is just a shell that holds the inner parts.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 5:02 am
Thank you, It is clear now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply