SQL queries to find missing indexes and unused
indexes :
since last 2 days I was doing R&D on this topic.
Now I found the important queries to find MISSING INDEXES, UNUSED INDEXES.
QUERY 1: to find missing indexes in database-
SELECT
TOP 25 dm_mid.database_id
AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)
Avg_Estimated_Impact, dm_migs.last_user_seek
AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id)
AS [TableName],
'CREATE INDEX
[IX_'
+ OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id)
+ '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),',
','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns
IS NOT
NULL AND dm_mid.inequality_columns
IS NOT
NULL THEN
'_' ELSE
'' END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),',
','_'),
'[',''),']','')
+ ']'
+ ' ON '
+ dm_mid.statement
+ ' ('
+ ISNULL
(dm_mid.equality_columns,'')
+ CASE
WHEN dm_mid.equality_columns
IS NOT
NULL AND dm_mid.inequality_columns
IS NOT
NULL THEN
',' ELSE
'' END
+ ISNULL
(dm_mid.inequality_columns,
'')
+ ')'
+ ISNULL
(' INCLUDE ('
+ dm_mid.included_columns
+ ')',
'')
AS Create_Statement
FROM sys.dm_db_missing_index_groups
dm_mig INNER JOIN
sys.dm_db_missing_index_group_stats
dm_migs ON dm_migs.group_handle
= dm_mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details
dm_mid ON dm_mig.index_handle
= dm_mid.index_handle
WHERE dm_mid.database_ID
= DB_ID()
ORDER BY
Avg_Estimated_Impact DESC
GO
Now, Read carefully Avg_Estimated_Impact column and put your missing indexes to
tune up your database performance.
QUERY 2: to find unused indexes in specific table-
SELECT
DISTINCT OBJECT_NAME(sis.OBJECT_ID)
TableName, si.name
AS IndexName, sc.Name
AS ColumnName,
sic.Index_ID,
sis.user_seeks,
sis.user_scans,
sis.user_lookups,
sis.user_updates
FROM
sys.dm_db_index_usage_stats
sis
INNER
JOIN sys.indexes
si ON sis.OBJECT_ID
= si.OBJECT_ID
AND sis.Index_ID
= si.Index_ID
INNER
JOIN sys.index_columns
sic ON sis.OBJECT_ID
= sic.OBJECT_ID
AND sic.Index_ID
= si.Index_ID
INNER
JOIN sys.columns
sc ON sis.OBJECT_ID
= sc.OBJECT_ID
AND sic.Column_ID
= sc.Column_ID
WHERE
sis.Database_ID =
DB_ID('DATABSE
NAME') AND
sis.OBJECT_ID
= OBJECT_ID('TABLE
NAME');
GO
Above query will return your unused indexes of a table. Read user_seeks column
if is 0 ,means it is not using by your single query.