SQL Server 2005 get a list of missing indexes and identify useful indexes

It is evident that well-designed indexes will help a lot in attaining performance for a poorly performing queries, this can also reduce disk I/O operations by consuming lesser resources on the server. Maily indexes are helpful during SELECT queries and must be carefully adopt when there are much UPDATE or DELETE statements are involved within your queries.

Table scan is not a good method of seeking rows as the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive, but in case you have a high percentage of rows to be returned this is better. Searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.

SQL Server 2005 has got missing indexes feature that uses dynamic management objects and query showplan to provide information about missing indexes that could enhance query performance. Books online highlights the list of DMVs used here:

After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.

Dynamic management object	Information returned
sys.dm_db_missing_index_group_stats	Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups	Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.
sys.dm_db_missing_index_details	Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns	Returns information about the database table columns that are missing an index.

You can use the information returned by these dynamic management objects with tools or scripts that use the information to generate CREATE INDEX DDL statements that will implement the missing indexes.

Using couple of scripts you can identify useful indexes and also recommendation for a better change.

Using this script to find missing indexes:

  select id.*
  , gs.avg_total_user_cost
, gs.avg_user_impact
, gs.last_user_seek
,gs.unique_compiles
from sys.dm_db_missing_index_group_stats gs
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details id
where gs.group_handle = g.index_group_handle
and id.index_handle = g.index_handle
order by s.avg_user_impact desc
go

To get 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