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.

• Debajit Chandra: That's a great tool but it returned with error. I got in touch with Brent's team and probably it happened because some of the tables were accessing at the time when I ran.

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

• Debajit Chandra: Thanks. I need to run the first one..right?
• Debajit Chandra: I ran the first query but it returned with only 0 rows affected. Do I need to run all the above queries one after another in order?
• SqlWorldWide: Please run the whole thing. It is saving in a temp table. You need to select form the temp table. Also looking at your plan cache. If you server just rebooted you will not see anything.
• Debajit Chandra: I have tried but results are coming with blank info. I have three databases but next time when I choose different database it tells me temptable is already exists (There is already an object named '#MissingIndexInfo' in the database.)
• SqlWorldWide: drop it or connect and reconnect. This is looking at you plan cache so a single run will work for all database. I will modify my script to check for that temp table at the top. Thx for letting me know.