question

DonThe avatar image
DonThe asked

Optimize Indexes for busy website

I am not experienced in the details of setting up an Index and have gone with Sql Server 2005 defaults until now.

The question is how to best optimize the main table for my website. 150,000 reads each day. 10-20 updates and deletes each day.

The primary key has a clustered index. Fill Factor = 0. Pad Index = No.

4 other nonunique, nonclustered indexes on join fields, foreign key and columns which are used to sort.

All have Fill Factor = 0. Pad Index = No.

The 4 other indexes are always fragmented every time I check. Between 25% -75%.

I rebuild the indexes once a month.

  1. Should I be setting the fill factor of the 4 other indexes differently?
  2. What other advice can you give me to optimize this table or indexes in general?

Thanks!

indexesfillfactor
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

4 Answers

·
MladenPrajdic avatar image
MladenPrajdic answered

Fill Factor 0 means the same as 100 so this might be bad if you don't have ver increasing PK. rule of thumb is around 90 but that should be tested.

you should monitor queries that get executed and check their execution plans. then optimize accordingly.

if you instal SSMS 2008 you get the cool little show missing indexes feature

if you can't install SSMS 2008 there's always this way of finding missing and unused indexes

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bart Czernicki avatar image
Bart Czernicki answered

@Mladen,

Fillfactor of 0 and 100 is NOT the same. They are very similar, except that a fillfactor setting of 0 leaves some space for the higher level nodes of an index. A setting of a 100 tells all levels (leaf and higher) to be maxed out at capacity.

@DonThe

Lowering the fillfactor should help you unfragment the indexes a little (as the table is used for update/delete) operations. You have so few updates that and many more reads, that you should really optimize for read rather than worry about the 10-20 updates.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

We set FillFactor to 100% on any index with strictly increasing keys - e.g. index on Identity column.

For random keys we generally set FillFactor to 80%

I rebuild the indexes once a month.

Why wait that long? Are you happy that performance is sub-optimal for 30 days out of 31?

We re-optimise all indexes every night (except on very busy servers where we re-optimise a proportion of indexes each night - e.g. [simplistically] indexes A-F during Monday night, indexes G-N on Tuesday night, and so on.)

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

donthe 1 avatar image
donthe 1 answered

The server is busy 24 hours. i already have the job running at the slowest hour of the week. I am using Index Rebuild. I would like to know more about

Index rebuild operations in SQL 2005 can use "online" methods,

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.