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.

;
WITH XMLNAMESPACES (DEFAULT http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: