Multiple indexes vs. forcing table scans

I am in the middle of optimizing the indexes in an SQL Server 2008 DB I'm working on. Right now I'm on the "central" table.

This table is joined on 7 columns. It also has a primary key (of course), plus a datestamp that I indexed in DESC order. Due to the nature of the joins, I believe 6 indexes is required (some of the joins are to tables of ultra low cardinality or won't be queried).

However, this table is heavily written to. Performance is going to be poor with 6 indexes (and 1 is a 2-column index). Queries could be run against most of the joined tables, so I don't see how I can drop any indexes.

Does anyone know of a best practice in determining how to balance the number of indexes?

Update:

Here are the columns of interest in the table:

MentionID       int             primary key
ParentID         int             indexed
ProfileID       int             indexed
SourceCategoryID tinyint         indexed with next column
SourceID         int
RoleplayerID     int             indexed
DateStamp       smalldatetime   indexed DESC
Subject       nvarchar(1000)
KeyMessage     nvarchar(1000)
SentimentID   tinyint         unindexed: low cardinality
ScopeID    开发者_运维技巧   tinyint         unindexed: low cardinality

Only one "best practice": try and measure, measure, measure and measure again.

There is no theoretical way to know how these additional indices will affect your performance - you need to try and see.

One approach that might help is described in great detail by Kimberly Tripp in hear blog post, Spring cleaning your indexes. It doesn't really give you much rules how to know how many indices to use - but it gives you methods and hints how to find out which indices might not be used at all - those can be tossed to speed up your processing.

On the flip side: SQL Server also has a DMV to tell you which indices it thinks would be beneficial and would help speed up your queries - inspect that DMV from time to time to see what the SQL Server query optimizer would like to have added; if that aligns with the indices you were about to create, you're probably on the right track!

• Are you using SQL's Missing Index DMVs?
• SQL Server - Find missing and unused indexes
• Identify Missing Indexes Using SQL Server DMVs