Missing or poorly formed indexes (Microsoft)

Missing or poorly formed indexes can also cause excessive memory pressure or cache flushes. In some cases, SQL Server 2005 optimizer identifies potentially useful indexes to benefit a specific query.
The computed benefit of the index can be seen in the column avg_user_impact (percentage improvement with suggested index). It should be noted that this benefit applies to the individual query only where the maintenance cost is borne by inserts, updates, and delete operations.

-- Potentially Useful 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 and 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

How to use them?

run the following query many times

USE AdventureWorks
SELECT AddressLine1,StateProvinceID from Person.Address
Where AddressLine1 like '1970 %'

And then,

Execute the script of Potentially Useful Indexes; You will have the report as:

index_handle: 4
database_id:6
object_id: 53575229
equality_columns:NULL
inequality_columns:[AddressLine1]
included_columns:[StateProvinceID]
statement:[AdventureWorks].[Person].[Address]
avg_total_user_cost:0.247314785185185
avg_user_impact: 84.66
last_user_seek:2007-11-01 12:28:02.170
unique_compiles: 4

Run the script of the suggested index columns and usage

Based on the recommendation, an index is created as follows:

USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_Address_Province] ON [Person].[Address]
(
[AddressLine1] ASC
)
INCLUDE ( [StateProvinceID])

Running the Query Execution Plan, you will find the index seek is used.