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
MarcoM. Simioni (m.simioni@.gmail.com) writes:
> 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?
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment