I have a table with something like 4.000.000 rows, but only once per
day about 200 rows are inserted by an application, running as a
scheduled job.
So few writes per day are performed, but many reads are executed.

We have on this table 1 clustered index and 5 non-clustered indexes:
this is because the queryes applied to this table are very different,
and we have to use very different indexes.

Could the number of indexes affect performance, even if this is mainly
a read-only table?

Is there a 'maximum' number of indexes suggested for such large tables,
or i can build as many indexes as i want, nearly one for each 'where'
clause applied to the table?

Thnx i.a. for the answer

Marco

If you're referring to insert performance, then given 200 rows per day this
is not an issue. Even if all 200 rows are inserted in a batch this might only
slightly affect overall read performance for the duration of the batch.

What is the selectivenes of your clustered index column(s)? What kind of
data is stored in these columns?


Look up "covering indexes" in Books Online. If the majority of your business
cases revolve around reading data from this table (rather than inserting and
updating it) then you are basically free to plan as many covering indexes as
you need, keep in mind, though, that I personally have not seen your data. As
Books Online suggests you hould still plan indexes (and queries) very
carefully.


ML

---
http://milambda.blogspot.com/

M. Simioni (m.simi***@gmail.com) writes:
The maximum number of tables is 250 if memory serves.

Can the number of index on a read-only table affect performance negatively?
Yes, from two different angles: 1) the optimizer gets more choices, and it
can take longer time to build the query plan. 2) the optimizer can pick an
index which it shouldn't. None of these issues should be given too much
weight. Most of the time the compilation time for a query is worth the
wait. And it's only occasionally the optimizer picks the wrong index. (But
I ran into that yesterday, when a customer got a problem with running a
stored procedure, and all that had happened was that I had added an index
to a table.)

So, if you feel that your table could benefit from more indexes, just do
adding them.

Faster execution of stored procedure
No Of Months - Function
IIF Statement
DECLARE CURSOR intermittently failing
How do I pass a second parameter to user-defined aggregate (SQL 20