March 19, 2005 at 11:00 pm
Hi friends,
I'm seeking advice about indexing. I notice our database has many indexes. It's not unusual for a table to have 7 indexes, and I know of one table that has 11. Is this excessive?
I read somewhere that if too many indexes are available, there is a higher chance that the query processor will choose the wrong one when executing a query. Are there any general rules of thumb regarding indexing? Also, are there any rules of thumb that apply to queries, as far as what operations tend to be expensive and which tend not to be?
If anyone can recommend a good resource for learning more about these things, I would be grateful.
March 21, 2005 at 1:33 am
Whether or not you have too many indexes, depends on your system and the queries against the db. There is no general answer to this. But I'd like to think that 7 or 11 indexes on a table is pretty much indeed. The main point here, I think, is no if SQL Server might choose a "wrong" index, but rather that indexes have to be maintained for each single INSERT, UPDATE or DELETE operation. This can slow down performance quite a bit. See if you find something useful here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311826
http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/itwforsql.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply