Missing Index finding queries

Question

I just need a reliable Script by which I can get all the missing index report.

I found many but they don't want to work.

Answer 1

Check out Brent Ozar's stored procedure called sp_BlitzIndex. You will find a lot of usefull applications of it.

Answer 2

Try this one. I do not have notes about source. Most likely from Johnathon's blog.

WITH XMLNAMESPACES  
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 

SELECT 
    query_plan, executioncount, cpu, logicalreads, total_logical_writes, physicalreads, duration, total_rows,
       n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text, 
       n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact, 
       DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id, 
       OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID, 
       n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + 
           n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')  
       AS statement, 
       (   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', ' 
           FROM n.nodes('//ColumnGroup') AS t(cg) 
           CROSS APPLY cg.nodes('Column') AS r(c) 
           WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY' 
           FOR  XML PATH('') 
       ) AS equality_columns, 
        (  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', ' 
           FROM n.nodes('//ColumnGroup') AS t(cg) 
           CROSS APPLY cg.nodes('Column') AS r(c) 
           WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY' 
           FOR  XML PATH('') 
       ) AS inequality_columns, 
       (   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', ' 
           FROM n.nodes('//ColumnGroup') AS t(cg) 
           CROSS APPLY cg.nodes('Column') AS r(c) 
           WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE' 
           FOR  XML PATH('') 
       ) AS include_columns 
INTO #MissingIndexInfo 
FROM  
( 
   SELECT query_plan , executioncount , cpu, logicalreads, total_logical_writes, physicalreads, duration, total_rows
   FROM (    
           SELECT plan_handle , max(execution_count) as executioncount, AVG(total_worker_time) as cpu, AVG(total_logical_reads) as logicalreads,
                AVG(total_logical_writes) as total_logical_writes, AVG(total_physical_reads) as physicalreads, AVG(total_elapsed_time) as duration, AVG(total_rows) as total_rows
           FROM sys.dm_exec_query_stats WITH(NOLOCK)  group by plan_handle 
         ) AS qs 
       OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp     
   WHERE tp.query_plan.exist('//MissingIndex')=1 
) AS tab (query_plan, executioncount, cpu, logicalreads, total_logical_writes, physicalreads, duration, total_rows) 
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n) 
WHERE n.exist('QueryPlan/MissingIndexes') = 1 

-- Trim trailing comma from lists 
UPDATE #MissingIndexInfo 
SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1), 
   inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1), 
   include_columns = LEFT(include_columns,LEN(include_columns)-1) 

SELECT * 
FROM  #MissingIndexInfo 


select COUNT (*), AVG(impact) as avgimpact, DB_NAME(database_id) as dbname, statement, equality_columns, inequality_columns, include_columns 
from #MissingIndexInfo group by 
 DB_NAME(database_id), statement, equality_columns, inequality_columns, include_columns
 order by 3 desc, 4,5,6

select COUNT (*), AVG(impact) as avgimpact, 
 avg(executioncount) as executioncount , avg(cpu) as cpu, avg(logicalreads) as logicalreads, avg(total_logical_writes) as writes, avg(physicalreads) as physicalreads, 
 avg(duration) as duration, avg(total_rows) as total_rows,
DB_NAME(database_id) as dbname, statement, equality_columns, inequality_columns, include_columns 
from #MissingIndexInfo group by 
 DB_NAME(database_id), statement, equality_columns, inequality_columns, include_columns
 order by 3 desc, 4,5,6