SELECT TOP 10
p.*,
q.*,
qs.*,
cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
JOIN sys.dm_exec_query_stats qs
ON qs.plan_handle = cp.plan_handle
WHERE
cp.cacheobjtype = 'Compiled Plan' AND
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION (MAXDOP 1)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @IndexName nvarchar(100) = '[I_CDRRating_Status_SubscriberId_BillingPopulationId]';

WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT OBJECT_NAME(qp.objectid, qp.dbid) as obj,
    st.text,
    cp.usecounts,
    cp.objtype,
    qp.query_plan.value('(//RelOp[IndexScan/Object/@Index = sql:variable("@IndexName")]/@PhysicalOp)[1]', 'varchar(50)') as usage,
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where --cp.objtype = 'Proc' and
      qp.query_plan.exist('//RelOp[
                                  (@PhysicalOp = "Index Seek" or @PhysicalOp = "Index Scan") and
                                  IndexScan/Object/@Index = sql:variable("@IndexName")
                                  ]') = 1
ORDER BY cp.usecounts DESC
OPTION (MAXDOP 1);