November 2, 2012 at 4:32 am
Hello All
First of all, I'm using the script at the bottom to investigate my Plan Cache
I've noticed that there are quite a few results where the s.execution_count is more that the cp.usecounts.
I can understand how these values would be different for auto-parameterized statements as the everytime the auto-parameterized plan is used with a different value in the where clause, a non-parametarized shell is created and the use_counts of that shell increases everytime the exact same query is issued.
What I am trying desperately to understand is how there can be a difference between the s.execution_count and cp.usecounts with stored procedures (objtype = proc)
Any help with this?
SELECT top 20
SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset ) / 2 ) + 1)
AS statement_text,
text,
objtype,
cacheobjtype,
usecounts,
last_execution_time,
total_worker_time,
total_worker_time / execution_count AS [Avg CPU Time],
execution_count ,
qp.query_plan
FROM sys.dm_exec_query_stats AS s
inner join sys.dm_exec_cached_plans cp
on s.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp
order by last_execution_time desc
Thanks
November 6, 2012 at 1:00 pm
I checked one of our produciton database. For top 100 proc based on execution_counts, I found usecount >=execution_count. Did not find one case not so. Wondering can you show your output ?
A few of my output;
----------------------------------------------
execution_count : 13990403
usecounts : 13990435
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 718123082
Avg CPU Time : 51
----------------------------------------------
execution_count : 9808702
usecounts : 9808738
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 426855463
Avg CPU Time : 43
----------------------------------------------
execution_count : 8613635
usecounts : 12572425
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : **********
Avg CPU Time : 1174
----------------------------------------------
execution_count : 7806431
usecounts : 7806468
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 230842176
Avg CPU Time : 29
----------------------------------------------
execution_count : 5819691
usecounts : 5819731
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 125503148
Avg CPU Time : 21
----------------------------------------------
execution_count : 5819688
usecounts : 5819728
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 190051878
Avg CPU Time : 32
----------------------------------------------
execution_count : 5796245
usecounts : 5815962
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 168716651
Avg CPU Time : 29
----------------------------------------------
execution_count : 5517525
usecounts : 5517525
objtype : Proc
cacheobjtype : Compiled Plan
last_execution_time : Nov 6 2012 2:49PM
total_worker_time : 3540085537
Avg CPU Time : 641
----------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply