Deciding which indexes you should use and or remove is a very involved process and is not something that can be simply automated. There are plenty of ways to identify under used or missing indexes, but adding or removing indexes can have a wide variety of impacts and these impacts need to be reviewed.
In order to properly understand how indexes can affect query performance, I strongly recommend you read Grant Fritchey's SQL Server 2012 Query Performance Tuning book. This is a valuable reference book for many DBAs and, while it is written for SQL 2012, much of the information is applicable to other versions of SQL Server.
Listed below are some tools you can use to identify indexes you can either add or remove. Do not apply these recommendations without reviewing them first.
Microsoft provides the Database Engine Tuning Adviser to review your database and provide recommendations on actions that can improve your performance. There are many factors the Tuning Adviser can not evaluate, so make sure you consider the suggestions individually before applying them.
You can also review missing index information from two places within SQL Server. The first is within the query plans. If you view a query plan, the green text at the top will typically recommend an index that can help your query. This data is typically found in the second place, sys.dm_db_missing_index_details. SQL Server tracks index usage stats and compiles recommendations that can be reviewed for possible addition to improve your performance. The Tuning Adviser referenced above also makes use of this information.
Glenn Berry has a very helpful query as part of his DMV script that shows you indexes that aren't very helpful:
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]
, i.name AS [Index Name]
, i.index_id
, user_updates AS [Total Writes]
, user_seeks + user_scans + user_lookups AS [Total Reads]
, user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
Please note!
Query tuning and index management is a very deep and involved topic. It's not something you can simply turn on with a switch and allow an automated process to handle because of all the factors involved. Many companies have DBAs dedicated solely to the task of managing this, using the tools listed above. If you have no DBAs at your place of employment, I recommend either taking the time to train yourself in this OR seek a professional who has experience with this to aid you.
