fill factor problem

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 !A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegroups.com...
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 !

fill factor problem

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 !
A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegr oups.com...
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 !

Fill factor

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 state.
Perhaps more likely is that the reorganized index(es) benefitted from
fresh statistics.
Roy Harvey
Beacon Falls, CT
On 10 Mar 2006 18:20:47 -0800, "pizza" <jeffchongonly@.gmail.com>
wrote:

>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 !|||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 !|||>Shall I use DBCC Reindex for everytable, and then use sp_updatestats ?
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
On 12 Mar 2006 22:19:31 -0800, "pizza" <jeffchongonly@.gmail.com>
wrote:

>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 !|||Take a look at this example (and the note at the bottom):
http://milambda.blogspot.com/2005/0...in-current.html
ML
http://milambda.blogspot.com/