An easier way to determine which indexes may be needed:
•sys.dm_db_missing_index_details -
Returns detailed information about a missing index
•sys.dm_db_missing_index_group_stats - Returns summary information about missing index groups
•sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
•sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index.
This is a function and requires the index_handle to be passed.
==>Once the instance of SQL Server is restarted the data gets cleared out.
How often this query is being called
SELECT * FROM sys.dm_db_missing_index_group_stats
Information about the index_group_handle and the index_handle
SELECT * FROM sys.dm_db_missing_index_groups
SELECT
mig.*,
statement AS table_name,
column_id, column_name,
column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON
mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle,
mig.index_handle, column_id;
When you add or drop indexes on a table all stats for missing indexes get cleared for this table.
May not be an optimal approach for managing your indexes.
Deeper insight into unused indexes for SQL Server
Determine which indexes are being used and how they are being used:
sys.dm_db_index_usage_stats
Keeps track of each index that has been used and how it has been used.
EXAMPLE.
SELECT
A.NAME,
B.NAME,
C.KEY_ORDINAL, D.NAME
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN SYS.COLUMNS D
ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID WHERE A.TYPE <> 'S'
ORDER BY 1, 2, 3 EXAMPLE.
SELECT
PVT.TABLENAME,
PVT.INDEXNAME,
PVT.INDEX_ID,
[1] AS COL1,
[2] AS COL2,
[3] AS COL3,
[4] AS COL4, [5] AS COL5,
[6] AS COL6,
[7] AS COL7,
B.USER_SEEKS,
B.USER_SCANS, B.USER_LOOKUPS
FROM (SELECT
A.NAME AS TABLENAME,
A.OBJECT_ID,
B.NAME AS INDEXNAME, B.INDEX_ID, D.NAME AS COLUMNNAME, C.KEY_ORDINAL FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID INNER JOIN SYS.INDEX_COLUMNS C AND B.INDEX_ID = C.INDEX_ID INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID
WHERE A.TYPE <> 'S' ) P
PIVOT ( MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] )
) AS PVT
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B ON PVT.OBJECT_ID = B.OBJECT_ID AND PVT.INDEX_ID = B.INDEX_ID AND B.DATABASE_ID = DB_ID() UNION SELECT TABLENAME,
INDEXNAME,
INDEX_ID,
[1] AS COL1,
[2] AS COL2,
[3] AS COL3,
[4] AS COL4,
[5] AS COL5,
[6] AS COL6,
[7] AS COL7,
0, 0, 0
FROM ( SELECT
A.NAME AS TABLENAME,
A.OBJECT_ID, B.NAME AS INDEXNAME, B.INDEX_ID, D.NAME AS COLUMNNAME, C.KEY_ORDINAL FROM SYS.OBJECTS A INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID WHERE A.TYPE <> 'S') P PIVOT ( MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] )
) AS PVT
WHERE NOT EXISTS ( SELECT
OBJECT_ID,
INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS B
WHERE DATABASE_ID = DB_ID(DB_NAME())
AND PVT.OBJECT_ID = B.OBJECT_ID AND PVT.INDEX_ID = B.INDEX_ID) ORDER BY TABLENAME, INDEX_ID; How to get index usage information in SQL Server
http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/ Identify Missing Indexes
select d.* ,s.avg_total_user_cost ,s.avg_user_impact ,s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc go --- suggested index columns & usage declare @handle int select @handle = d.index_handle from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle select * from sys.dm_db_missing_index_columns(@handle) order by column_id |