September 5, 2012 at 8:29 am
Hi All
I've created the below script (by taking pieces from other similar scripts) to check my plan cache and query_stats and I think I might have bitten off a bit too much
1. Is the script put together correctly?
2. The script returns an execution_count and a use_count, am I right in that the execution_count is the amount of times the query was executed and the use_count is the amount of times the plan was used? Should these values be the same?
Any help would be great, thanks
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 [Avg CPU Time] desc
Thanks
September 5, 2012 at 10:38 am
Correct to what end? What are you trying to figure?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 6, 2012 at 12:07 am
opc.three (9/5/2012)
Correct to what end? What are you trying to figure?
Current expensive queries
And
2.The script returns an execution_count and a use_count, am I right in that the execution_count is the amount of times the query was executed and the use_count is the amount of times the plan was used? Should these values be the same?
Thanks
September 6, 2012 at 10:34 am
SQLSACT (9/6/2012)
opc.three (9/5/2012)
Correct to what end? What are you trying to figure?Current expensive queries
You're looking at the right views. The web has tons of examples of 'most expensive by cpu' and other metrics.
2.The script returns an execution_count and a use_count, am I right in that the execution_count is the amount of times the query was executed and the use_count is the amount of times the plan was used? Should these values be the same?
No. A plan can be looked up in cache but that lookup does not always materialize into an execution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply