Tuesday, May 05, 2009

Identify missing indexes in sql server 2005

SELECT DISTINCT DB_NAME(Database_ID) [Database]
,OBJECT_NAME(Object_ID) [Table]
,Equality_Columns
,Included_Columns
FROM sys.dm_db_missing_index_details mid
WHERE Database_ID = DB_ID()
ORDER BY 2

2 comments:

adeelcap15 said...

can you explain more.

Musab Umair said...

At times you create indexes for columns but they are never used because the SQL Server Query Optimizer does recognizes them as suitable indexes and instead it uses the Primary key to scan the index to fetch the records.
So when index or table scans are performed the SQL Server tracks them and stores those queries and table information in a table. And suggests you to create some indexes.
So you can view those suggestions using the given view.