Get information about missing indexes in SQL Server 2005 and onwards databases

Indexes are core of optimization and performance of DBMS. Keen attention is required while planning indexes. Fortunately SQL Server contains a suggestion mechanism for creating indexes where required. While optimizing the submitted queries, Query optimizer notes down the columns where indexes are missing and their creation may boost the performance. First let us view the script to retrieve the missing indexes information in SQL Server 2005 databases

SELECT DB_NAME(database_id) AS [DATABASE],
equality_columns, inequality_columns,
included_columns, STATEMENT
FROM sys.dm_db_missing_index_details
ORDER BY 1

Result of above script will be generated in five columns.

• [Database] is name of database where missing index is detected
• [equality_columns] is list of columns that are used in equality comparison and index is required on these.
• [inequality_columns] is list of columns that are used for inequality comparison and index is required on these.
• [included_columns] is list of columns that are used in query for other than comparison (e.g. Select List). And covering index on these is suggested.
• [Statement] is name of table along with column where index is missing
  

It is important to mention that this information is geather since the time when SQL Server Service starts. Existing information about missing indexes will be vanished with service stop/restart. So DBA should backup missing indexes information time to time for later analysis.
Also note that all this information is based on queries submitted to SQL Server query engine either by user or application. So there may be other objects where indexes are required but these are not suggested because of not being used in scripts.
For deep and detailed monitoring/analysis i would suggest Database Engine Tuning Advisor.