Plan Cache - Query Stats - Usecounts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Plan Cache - Query Stats - Usecounts -...

Plan Cache - Query Stats - Usecounts - Execution Count

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Friday, November 02, 2012 4:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 1,250, Visits: 2,220

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

Post #1380258

DenisSun

Posted Tuesday, November 06, 2012 1:00 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 8:06 AM
Points: 3, Visits: 39

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
----------------------------------------------

Post #1381696

« Prev Topic | Next Topic »

Permissions