Every time you modify data in a table(with a insert, update, or delete), the affected indexes must also be updated. With an OLTP workload (which means that your data is volatile) you typically want to have fewer indexes than with a DSS/DW type of workload (where the data is relatively static). One guideline from Tom Davidson when he was on the SQLCAT team in 2006 was to try to have less than four indexes on tables that were frequently updated. Lindsey Allen (from SQLCAT) also discussed the cost of having too many relational indexes here.  I generally start to get concerned when I see more than about five or six relational indexes on a volatile table. Having too many relational indexes has a negative effect on insert, update, and delete performance. Ultimately, it is a judgment call, based on your workload, your hardware and I/O subsystem, and on your experience.

On the other hand, if you have too few effective indexes or you are “missing” important indexes that SQL Server could effectively use to satisfy its query workload, you will also see bad overall query performance. You will see table or index scans where a seek could have satisfied a query that only returned a few rows of data. This can cause increased memory, I/O, and CPU pressure, depending on the situation. It can also cause extra locking and blocking while these extra scans are occurring.

What you want to have is a happy balance between too many and two few relational indexes. You want to eliminate indexes that are not being used, and add indexes that SQL Server thinks it needs (using your own good judgment and knowledge of your workload). After all, my two miniature dachshunds always think they need more doggie treats, but I sometimes know better!

Here are a couple of queries that will help you reach this sweet spot for your relational indexes. I like to call these two the “dynamic duo”, since they are so useful for judging whether you need to adjust your overall index strategy at the database level.

-- Possible Bad NC Indexes (writes > reads)
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);

-- Look for indexes with high numbers of writes 
-- and zero or a very low numbers of reads
-- Consider your complete workload
-- Investigate further before dropping an index


-- Missing Indexes current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) 
AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, 
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC OPTION (RECOMPILE);

-- Look at last user seek time, number of user seeks 
-- to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!

After I look at that, I tend to try to focus on a single table in more detail before I make any changes. You should not just start blindly dropping or adding indexes based on these two queries alone. You should evaluate your existing indexes on that table and consider your complete workload, and apply some good human judgment before you make index changes.

The queries below will help focus your attention on a single table. In this case, I am looking at the ActivityEvent table.

-- Index Read/Write stats for a single table
SELECT OBJECT_NAME(s.[object_id]) AS [TableName], 
i.name AS [IndexName], i.index_id,
SUM(user_seeks) AS [User Seeks], SUM(user_scans) AS [User Scans], 
SUM(user_lookups)AS [User Lookups],
SUM(user_seeks + user_scans + user_lookups)AS [Total Reads], 
SUM(user_updates) AS [Total Writes]     
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
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 OBJECT_NAME(s.[object_id]) = N'ActivityEvent'
GROUP BY OBJECT_NAME(s.[object_id]), i.name, i.index_id
ORDER BY [Total Writes] DESC, [Total Reads] DESC OPTION (RECOMPILE);


-- Missing indexes for a single table
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) 
AS index_advantage, migs.last_user_seek, 
mid.statement AS 'Database.Schema.Table',
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, 
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
-- Specify one database, schema,table
WHERE [statement] = N'[NewsGator_SocialServices].[dbo].[ActivityEvent]' 
ORDER BY index_advantage DESC OPTION (RECOMPILE);


-- Look at existing indexes 
--(does not show included columns or filtered indexes)
EXEC sp_helpindex N'dbo.ActivityEvent';

-- See how big the table is
EXEC sp_spaceused N'dbo.ActivityEvent';

Remember, sp_helpindex does not show information about included columns or filtered indexes. One work-around is to script out the index creation DDL statements for each existing index to see what is really there. You can also use Kimberly Tripp’s (blog|twitter) excellent sp_helpindex2 procedure to pickup that information.