SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES  
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT TOP 100--ecp.plan_handle,
        DENSE_RANK() OVER ( ORDER BY ecp.plan_handle ) AS ArbitraryPlanNumber ,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')
        * ISNULL(n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float'), 0)
        * ecp.usecounts AS Improvement ,
        query_plan AS CompleteQueryPlan ,
        n.value('(@StatementId)[1]', 'float') AS StatementID ,
        n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost ,
        n.query('./QueryPlan/MissingIndexes') MissingIndex ,
        n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IndexImpact ,
        ecp.usecounts
FROM    sys.dm_exec_cached_plans AS ecp
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
        AS qn ( n )
WHERE   n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IS NOT NULL AND
        ecp.usecounts > 100 AND
        eqp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
         /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes') = 1 
ORDER BY Improvement DESC