Plan Cache usecounts/execution_count

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  October 15, 2012 at 5:50 am

  #265632

  Hi all

  I'm using the below script to check my plan cache

  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

  I'm noticing that there are a few entries where the usecounts column is different from the execution_count column. Shouldn't these values be exactly the same?

  I've had a look at the following URL but can't seem to make any sense of it http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b7ddfb81-1554-40fd-ab88-ee1bb257cbd1/

  Thanks