number of indexes

Question

number of indexes

Matt-1034261

Default port

Points: 1453
More actions
March 29, 2009 at 5:09 am

#207915

Hi
A website that I run has a forum on it and the table that holds the posts has 10 indexes on it including the clustered index.
Is this an acceptable amount or should I try to reduce this number?
Matt

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

There is no correct answer to this without knowing what queries are being used against the table. 10 indexes may be the correct number.

It really depends on things like how many updates Vs reads are used and even then you need to understand whether the benefits of an index outweigh the costs of not having it.

To quantify this, you need monitor performance with and without the indexes using a representative set of queries (not necessarilly an easy job if you do it properly)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Matt (3/29/2009)
Is this an acceptable amount or should I try to reduce this number?

Maybe.

Are they all used? Check the index usage stats DMV for that info, but note that it only stores data since the last start of SQL, so you need to keep track of it for at least a couple of weeks before you can determine if an index really is used or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt-1034261 Default port Points: 1453 More actions · Answer 3

GilaMonster (3/29/2009)
Check the index usage stats DMV for that info,

Thanks Gail, excuse my ignorance but how do I do that?:blush:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Matt (3/29/2009)
GilaMonster (3/29/2009)
Check the index usage stats DMV for that info,
Thanks Gail, excuse my ignorance but how do I do that?:blush:

SELECT * from sys.dm_db_index_usage_stats

Check out Books Online for the details of that DMV, what it stores and what the columns mean.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 5

As always, it is acceptable if it serves you good.

I think MS Best practices states a number of 5 or 7.

Here are some nice guidelines on common stuff:

http://technet.microsoft.com/nl-be/library/cc966401(en-us).aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 6

Ten seems high, but it depends on usage, as mentioned. You would want to look at the stats, see if they are really used. Also check the missing indexes view to see if there are some that are needed. Shuffling indexes around might make sense.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

Matt (3/29/2009)
Hi
A website that I run has a forum on it and the table that holds the posts has 10 indexes on it including the clustered index.
Matt

As ALZDBA and others suggested yes, if you have 5 or more indexes on a single table you should consider monitoring the table and track the usage of indexes.

What are the frequent operations that you perform against this table?

Online Forum- I have not worked on this kind of application before but, Steve and Gail would be much help on this. Would you have many inserts performed? If so, you should also consider fragmentation on these tables?

I want to ask you something: What made you to ask this question?, I meant have you experienced any slow down on your server?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

It is impossible to assign a fixed number or even a fixed range as right or best, because the best number of indexes is entirely dependent of design and usage.

One question that you can ask is: What is the ratio of reads to writes for this table? Or alternatively: "What is the ratio of Selects to Inserts on this table?" The higher this ratio is, the more you could theoretically justify additional indexes.

This is because indexes are technically a performance feature that tries to improve the performance of SELECTs at the expense of INSERTS. So if you have a table that only has SELECTS and no INSERTS (say a zip code/city/county/state table that is only updated once a year) then you could justify a TON of indexes on it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt-1034261 Default port Points: 1453 More actions · Answer 9

Krishna (3/29/2009)
I want to ask you something: What made you to ask this question?, I meant have you experienced any slow down on your server?

No, the forum part of the site runs very well, I was reading a different forum and people seemed aghast that one guy had 7 indexes on one of his tables so I was just wondering if there was any kind of acceptable limit

RBarryYoung (3/29/2009)
One question that you can ask is: What is the ratio of reads to writes for this table? Or alternatively: "What is the ratio of Selects to Inserts on this table?" The higher this ratio is, the more you could theoretically justify additional indexes.

There are around a thousand inserts a day but many more thousands of selects however at the moment the inserts are working fine with no prolonged delay.

But I will check to see if all the indexes are being used, no sense in having them if they are not, even if performance isn't being affected

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

If your Inserts are fine, then you are probably OK with that many indexes. You might still use some of the prior advice to determine whether you are using all of them though.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

maechismo_8514 SSCoach Points: 17510 More actions · Answer 11

and also fragmentation on these tables and accordingly defrag them or rebuild them

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 12

As Barry mentioned, the inserts/updates will affect performance. If the SELECTs are not being blocked by INSERT activity (which has to update each index) then you are probably OK.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 13

Out of curiosity, is this your main post table or something else?

And which forum software do you use?

Matt-1034261 Default port Points: 1453 More actions · Answer 14

Steve Jones - Editor (3/29/2009)
Out of curiosity, is this your main post table or something else?
And which forum software do you use?

Yes this is the main post table and the forum software is my own brew 🙂