How many indexes can I add to the table before performance degrades?

Question

I have a table with 10 columns that currently has about 1,000,000 rows. We will be adding about 1,000,000 new rows each year. How many indexes can I add to the table before performance degrades?

Answer

You should only create an index if you know that one or more queries that you run against a table need it. You can find this out manually by analyzing each query, or by running the Index Wizard against or Database Engine Tuning Advisor a Profiler trace of actual queries hitting the table.

How many indexes you can have depends on many factors. One of the most common factors used to help determine whether you have too many indexes on a table is whether or not data modifications (INSERTS, UPDATES, or DELETES) to a table begin to slow down as more indexes are added to a table. For every data modification you have, each index in your table needs to be updated. This requires overhead. While some overhead is acceptable, too much overhead can slow down data modifications, significantly hurting your application's performance.

Another factor is how wide the indexes are. A table with more (narrower) indexes may be less of a problem than a table with fewer (wider) indexes.

If the table is modified very often (say more than once a second), then having lots of indexes could cause a performance problem when modifying the database. But if your table is modified less often, say less than once every 10 seconds, then having more indexes most likely won't be a problem.

As you can see, it is difficult to give you an absolute answer. I think the key is to add as many indexes as your query needs demand, and watch to see if data modifications begin to slow down. If they do, then you will have to reconsider how many indexes your table has.