[From SQL Server Performance
In this fast moving world, data is the heart and soul of any enterprise. As the data is growing very rapidly day by day, the biggest challenge which enterprises face today is to store the data in such a way that it can be retrieved quickly whenever required. The most common thought which comes in the mind of database administrators who basically works on performance improvement is to add indexes for tables to improve the data retrieval. However adding too many indexes on a table can sometimes reduce the performance of the table considerably. So it is very important for the database administrator to know whether the indexes created on the tables are used effectively or not. If there are indexes created on a table and they are not used, then they should be dropped, as having unwanted index will slow down Insert, Update or Delete operations on the underlying tables.
The article continues at