improve/detect query cache resuse

In SQL Server 2008, how does one assure that a query plan is being resused from cache?

How can one detect if it's being reused from cache?
Who is Participating?
jogosConnect With a Mentor Commented:
DcpKing gave you a basic query that shows what plans are cached and how many times there used.  Reuse of a query plan is standard for same query.

Here link to the basics , including a list of things that prevent a plan to be reused

When you have the above query, it's worth to link it to other DMV's that give you more info on the usage

Like the execution statistics
DcpKingConnect With a Mentor Commented:
This should show you the query cache:

SELECT p.objtype, p.size_in_bytes, t.[text], p.usecounts
     FROM sys.dm_exec_cached_plans p
     OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) t
     WHERE objtype not IN ('Proc', 'View', 'UsrTab', 'Check', 'Prepared', 'Adhoc')
     ORDER BY usecounts DESC

Open in new window

Take out the object types you're not interested in.

(Compatibility level 90 databases and above)


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.