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