Data World

MSSQL Server: Find Missing Indexes

Posted by Pramod Singla on March 4, 2017


Problem Statement: Script to find the missing indexes using 3 different ways.
Solution: 1) Find missing indexes using DMVs

--Find top ten missing indexes using following DMV 
SELECT TOP 10 ( user_seeks ) * avg_total_user_cost * ( avg_user_impact * 0.01 ) 
              AS 
              [index_advantage],--%advantage in the query 
              migs.last_user_seek, 
              --Date and time of last seek caused by user queries that the recommended index in the group could have been used for.
              mid.[statement] 
              AS [Database.Schema.Table], 
              'Create index idx_' 
              + Parsename( mid.[statement], 1) + '_' 
              + Cast(row_number() OVER(ORDER BY (user_seeks) * 
              avg_total_user_cost * ( 
              avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) 
              + ' on ' + Parsename( mid.[statement], 1) + '(' ++ 
              equality_columns + ')' + CASE WHEN mid.included_columns IS NOT 
              NULL THEN 
              ' include ('+ mid.included_columns+')' ELSE '' END 
              create_index, 
              'drop index idx_' 
              + Parsename( mid.[statement], 1) + '_' 
              + Cast(row_number() OVER(ORDER BY (user_seeks) * 
              avg_total_user_cost * ( 
              avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) 
              + ' on ' + Parsename( mid.[statement], 1) 
              drop_index, 
              mid.equality_columns, 
              mid.inequality_columns, 
              mid.included_columns, 
              migs.unique_compiles, 
              migs.user_seeks, 
              migs.avg_total_user_cost, 
              --  Average cost of the user queries that could be reduced by the index in the group. 
              migs.avg_user_impact 
--Average percentage benefit that user queries could experience, 
FROM   sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) 
       INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) 
               ON migs.group_handle = mig.index_group_handle 
       INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) 
               ON mig.index_handle = mid.index_handle 
WHERE  mid.database_id = Db_id() -- Remove this to see for entire instance 
ORDER  BY index_advantage DESC 
OPTION (RECOMPILE);

2) Find missing indexes on FK

SELECT fk.NAME    AS CONSTRAINT_NAME,
s.NAME     AS SCHEMA_NAME,
o.NAME     AS TABLE_NAME,
fkc_c.NAME AS CONSTRAINT_COLUMN_NAME
FROM   sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS fkc_c
ON fkc.parent_object_id = fkc_c.object_id
AND fkc.parent_column_id = fkc_c.column_id
LEFT JOIN sys.index_columns ic
JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
ON fkc.parent_object_id = ic.object_id
AND fkc.parent_column_id = ic.column_id
JOIN sys.objects AS o
ON o.object_id = fk.parent_object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE  c.NAME IS NULL

3) Find missing indexes using stored query plans.

SELECT MissingIndexNode.value('(MissingIndexGroup/@Impact)[1]', 'float')     AS 
       impact, 
       Object_name(sub.objectid, sub.dbid)                                   AS 
       calling_object_name, 
       MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Database)[1]', 
       'VARCHAR(128)') 
       + '.' 
       + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Schema)[1]', 
       'VARCHAR(128)') 
       + '.' 
       + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Table)[1]', 
       'VARCHAR(128)')                                                       AS 
       table_name, 
       Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
              FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''EQUALITY'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
       equality_columns, 
Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
       FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''INEQUALITY'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
       inequality_columns, 
Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
       FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''INCLUDE'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
       include_columns, 
sub.usecounts                                                         AS 
       qp_usecounts, 
sub.refcounts                                                         AS 
       qp_refcounts, 
qs.execution_count                                                    AS 
       qs_execution_count, 
qs.last_execution_time                                                AS 
       qs_last_exec_time, 
qs.total_logical_reads                                                AS 
       qs_total_logical_reads, 
qs.total_elapsed_time                                                 AS 
       qs_total_elapsed_time, 
qs.total_physical_reads                                               AS 
       qs_total_physical_reads, 
qs.total_worker_time                                                  AS 
       qs_total_worker_time, 
StmtPlanStub.value('(StmtSimple/@StatementText)[1]', 'varchar(8000)') AS 
statement_text 
FROM   (SELECT ROW_NUMBER() 
                 OVER ( 
                   PARTITION BY qs.plan_handle 
                   ORDER BY qs.statement_start_offset ) AS StatementID, 
               qs.* 
        FROM   sys.dm_exec_query_stats qs) AS qs 
       JOIN (SELECT x.query('../../..')                          AS StmtPlanStub 
                    , 
                    x.query('.') 
                    AS MissingIndexNode, 
                    x.value('(../../../@StatementId)[1]', 'int') AS StatementID, 
                    cp.*, 
                    qp.* 
             FROM   sys.dm_exec_cached_plans AS cp 
                    CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp 
                    CROSS APPLY qp.query_plan.nodes ( 
'/ShowPlanXML/BatchSequence/                                         Batch/Statements/StmtSimple/                                         QueryPlan/MissingIndexes/                                         MissingIndexGroup'
) mi ( x )) AS sub 
  ON qs.plan_handle = sub.plan_handle 
     AND qs.StatementID = sub.StatementID
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s