Hi !

I have a  problem with my current database.

All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.

I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.

There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?

thanks !

I suspect that the cause of the significant improvment in performance
is not the difference between a fill factor of 90 and one of 0.  That
doesn't seem to be enough of a change for a large improvement.

Large changes in performance almost always result from changes in
execution plans.  That isn't that likely from a fill factor change
directly.

Fill factor is only used when an index is created, or re-organized. If
the table has undergone major updates since either of those last
happened the original fill factor probably does not describe the
current state of the table.  So unless the change was from a fresh
index at 90 it is uncertain what the real state of the table and
indexes was before.

One possible reason for the change in performance is that reorganizing
the index(es) brought the table back down to a reasonable number of
pages from its formerly confused state.

Perhaps more likely is that the reorganized index(es) benefitted from
fresh statistics.

Roy Harvey
Beacon Falls, CT

Show quoteHide quote

Hi Roy Harvey,

Thanks for the reply,
How should i refresh the statistics ?
Shall I use DBCC Reindex for everytable, and then use sp_updatestats ?

Thanks !

That should work fine.  You may find that there are a few key tables
that are updated and joined to often that could benefit from periodic
refresh of the indexes.

Roy Harvey
Beacon Falls, CT

Show quoteHide quote

Take a look at this example (and the note at the bottom):
http://milambda.blogspot.com/2005/07/defragment-all-indexes-in-current.html


ML

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

Problems with stored procedure
Determin start and end date of a month
BCP into image columns
I cannot explain the difference in execution plans.
INNER JOIN key words