Dynamic Management Views and functions are useful when exploring plan reuse and the following objects are most helpful:
sys.dm_exec_query_plan
sys.dm_exec_sql_text
sys.dm_exec_plan_attributes
sys.dm_exec_cached_plan_dependent_object
The following sql query is useful to exploring the current plan cache contents in buffer pool:
SELECT DB_NAME( st.dbid) AS DatabaseName,
st.dbid AS Database_ID,
cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.text AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY
sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.[dbid] = DB_ID()
ORDER BY UseCounts DESC;