Click here to Skip to main content
15,878,231 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to find the missing and unused Indexes of a Table in a DataBase via SQL QUERY in sql server 2005?
Posted

1 solution

The data about missing indexes is stored in the following DMV’s which all exclude info about spatial indexes:
SQL
sys.dm_db_missing_index_groups

This DMV returns only 2 columns with information about which indexes are in which group.
SQL
sys.dm_db_missing_index_group_stats

This DMV returns information about each missing indexes group. It returns info like the estimated average impact or how many seeks, scans and compilations/recompilations would benefit from adding the missing indexes.
SQL
sys.dm_db_missing_index_details

This DMV returns detailed information about each missing index like table name that is missing an index and CSV’s of columns that the index would be beneficial on.
SQL
sys.dm_db_missing_index_columns

This a Dynamic Management Function (DMF) that accepts an index_handle parameter. It returns columns that should be in the suggested index identified with the index_handle that can be obtained from sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups. It does not include spatial indexes. The column named column_usage returns info on how this column would benefit for a particular index. EQUALITY and INEQUALITY mean that the column would be used in a where clause predicate. INCLUDE means that the column should be an included column on an existing non-clustered index.

OR
Use the follwing script[^]:
SQL
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO


--Amy
 
Share this answer
 
v2
Comments
Maciej Los 1-Aug-14 3:15am    
Good job, Amit ;)
_Amy 1-Aug-14 3:19am    
Thank you Maciej. :)
But to be true, your smiley is confusing me.
Maciej Los 1-Aug-14 6:14am    
You're welcome.
Why? I often use it, even in real.
;)
Cheers,
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900