Query Plan Handle & Usage

SELECT

       cp.objtype AS ObjectType,

       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

       cp.usecounts AS ExecutionCount,

       st.TEXT AS QueryText,

       qp.query_plan AS QueryPlan,

       cp.plan_handle AS PlanHandle,

       execution_count,

       query_hash,

       query_plan_hash

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

join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle

WHERE OBJECT_NAME(st.objectid,st.dbid) = ''

ORDER BY cp.usecounts DESC

Static Pivot

select *

from

(

      select a.ObjectId, f.Name, a.Address

      from V6Addresses a

      join V6ObjectTypeFields f on a.V6ObjectTypeFieldId = f.Id

      join V6ObjectTypes t on f.V6ObjectTypeId = t.Id

      where t.Name = 'Servers'

) v

PIVOT

(

      MAX(Address)

      FOR v.Name in (ServersPrimary, ServersSecondary, ServersGateway, ServersManagement, ServersManagementGateway)

) as p