Plan Re-Use Calculation...please help.

Last Post 04 Oct 2011 10:37 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

04 Oct 2011 06:05 AM

The old traditional way using perf counters to get plan re-use as a percentage was: Plan re-use=
(Batch requests - SQL compilations) / Batch requests.  My question is as follows:

1. Is the above method still valid; and does this still prove useful

2. What the equivalent way to get the PlanReUse (server wide) using DMVs?

New Member
New Member

04 Oct 2011 10:37 AM
Well that doesn't take into consideration recompiles only compiles. These days recompiles aren't as prevelant as they used to be but can still be a factor. That being said the ratio of requests to compiles is a decent quick reference as to how effeceient the calls are in terms of plan reuse. But if you want to see which plans get reused and which don't you can look at the usecounts column of the query below. That will sort out which queries are either only called once or they were not properly parameterized so they can reuse the previous plans.
-- All Plans
SELECT b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts], b.[usecounts]
, a.[dbid], a.[objectid], b.[size_in_bytes], a.[text]
FROM sys.dm_exec_cached_plans as b
CROSS APPLY sys.dm_exec_sql_text(b.[plan_handle]) AS a
ORDER BY [usecounts] DESC

Another way is to look at the execution count of this DMV for the Query stats.

SELECT t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
, qs.[execution_count] AS [Counts]
, qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time]
, qs.[total_physical_reads] AS [Total Physical Reads], (qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
, qs.[total_logical_writes] AS [Total Logical Writes], (qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
, qs.[total_logical_reads] AS [Total Logical Reads], (qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
, qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] / qs.[execution_count]) AS [Avg CLR Time]
, qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time] / qs.[execution_count]) AS [Avg Elapsed Time]
, qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Total Worker Time] DESC
-- ORDER BY [Total Physical Reads] DESC
-- ORDER BY [Total Logical Writes] DESC
-- ORDER BY [Total Logical Reads] DESC
-- ORDER BY [Total CLR Time] DESC
-- ORDER BY [Total Elapsed Time] DESC

However keep in mind that neither of these will show any calls in which the plans never get cached in the first place.
You are not authorized to post a reply.

Acceptable Use Policy