0

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.

2 Answers 2

2

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

1
  • 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. Apr 26, 2017 at 12:59
0

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
11
  • Thanks. I need to run the first one..right? Apr 26, 2017 at 12:57
  • 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? Apr 26, 2017 at 13:02
  • 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. Apr 26, 2017 at 13:04
  • 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.) Apr 26, 2017 at 13:15
  • 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. Apr 26, 2017 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.