BOL: Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Good for seeing what objects have a cached plan. We don't want to see too many single use (usecount = 1) prepared or adhoc queries in here, but remember that something that is completely reusable that has only been used once so far, will have a usecount = 1! Use with sys.dm_exec_plan_attributes to get additional info such as the SET OPTIONS the plan was created with.

Tony Rogerson has a very good blog post demonstrating how adhoc queries grow the proc cache whilst parameterised queries don't.

SELECT TOP 50
   CASE WHEN st.dbid = 32767 THEN 'resourceDb' ELSE db_name(st.dbid) END AS database_name,
   CASE WHEN st.dbid IS NULL THEN NULL ELSE object_schema_name(st.objectid, st.dbid) END AS object_schema_name,
   CASE WHEN st.dbid IS NULL THEN NULL ELSE object_name(st.objectid, st.dbid) END AS object_name,
   cp.cacheobjtype,
   cp.objtype,
   st.text,
   --qp.query_plan,
   cp.refcounts,
   cp.usecounts,
   cp.size_in_bytes,
   pa_set.value AS plan_set_options,
   pa_iec.value AS inuse_execution_contexts,
   pa_fec.value AS free_execution_contexts,
   CASE WHEN ISNULL(pa_db.value, 0) = 0 THEN NULL ELSE db_name(CAST(pa_db.value AS INT)) END AS plan_db_execute
FROM 
   sys.dm_exec_cached_plans AS cp
      OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
      --OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
      OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa_set
      OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa_db
      OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa_iec
      OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa_fec
WHERE
   pa_set.attribute = 'set_options' 
   AND pa_db.attribute = 'dbid_execute' 
   AND pa_iec.attribute = 'inuse_exec_context' 
   AND pa_fec.attribute = 'free_exec_context' 
   --AND qp.query_plan.exist(N'declare namespace sql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//sql:RelOp[@Parallel = 1])') = 1

ORDER BY
   cp.usecounts DESC, st.text

SELECT
   cp.cacheobjtype,
   cp.objtype,
   CASE WHEN st.dbid = 32767 THEN 'resourceDb' ELSE db_name(st.dbid) END as database_name,
   SUM(CASE WHEN cp.usecounts <= 1 THEN 1 ELSE 0 END) as single_use_count,
   SUM(CASE WHEN cp.usecounts > 1 THEN 1 ELSE 0 END) as multi_use_count,
   AVG(CASE WHEN cp.usecounts > 1 THEN cp.usecounts ELSE NULL END) as multi_use_avg_use_count,
   (SUM(CASE WHEN cp.usecounts <= 1 THEN cp.size_in_bytes ELSE 0 END) / (1024 * 1024)) as single_use_size_in_Mbytes,
   (SUM(CASE WHEN cp.usecounts > 1 THEN cp.size_in_bytes ELSE 0 END) / (1024 * 1024)) as multi_use_size_in_Mbytes,
   (SUM(cp.size_in_bytes) / (1024 * 1024)) as total_size_in_Mbytes
FROM 
   sys.dm_exec_cached_plans as cp
      OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
GROUP BY
   cp.cacheobjtype,
   cp.objtype,
   CASE WHEN st.dbid = 32767 THEN 'resourceDb' ELSE db_name(st.dbid) END
ORDER BY
   cp.cacheobjtype,
   cp.objtype,
   CASE WHEN st.dbid = 32767 THEN 'resourceDb' ELSE db_name(st.dbid) END