Fillfactor

we have 2 servers with similar tables and columns and indexes with fillfactor
0. My replication is very slow during daytime and comes to normal at night
times.when i check the reads and writes both are more that 90 % high but when
i check the page splits it shows nothing.what do i need to do to fasten my
replication.
Do i need to add fillfactor?
i didn't see any page splits though
Thanks
Pardhi
Message posted via http://www.droptable.com
If you have no page splits, then I'd start monitoring for blocking issues
during synchronization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul i didn't see any blocking either
Paul Ibison wrote:
>If you have no page splits, then I'd start monitoring for blocking issues
>during synchronization.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
Message posted via http://www.droptable.com
|||Then perhaps it's just one of the the usual processor/memory/disk access
issues. You could use profiler to monitor these counters and see if this is
the issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

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 !sql

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/

Filegrowth by percent or by size. (And how much).

Yesterday we encountered a problem.
Setting a small testmachine (slow disks) filegrowth at 10 % and
a databasesize of 1.2 Gbyte.
Problem : A simple insert took over 1 minute.
Causing a timeout.
Took some time, but the growing of the database took some time.
Because of the timeout, the extend was not added after the action,
so the next action causes the same problem.
Our production machines are quite a bit faster, so the problem has
not been noticed (yet) on a production machine.
Our development machines are faster as wel, but a glitch once
probably wouldn't be noticed as significant.
So what timing is to be expected by extending the database ?
(Is 64 Mbyte a good size for extending)
Any thoughts about this ?
ben brugmanHi Ben,
In our production environment, we give a good initial size
to the data and log files and set the increment value by
200 MB and not by size. We have found that this is the
best way to handle databases that grow by 100 MB in a week
and more than 2 GB in size... In this way, we reduce
frequent growth of dbs.
regards,
bharath
mcdba
>--Original Message--
>Yesterday we encountered a problem.
>Setting a small testmachine (slow disks) filegrowth at 10
% and
>a databasesize of 1.2 Gbyte.
>Problem : A simple insert took over 1 minute.
>Causing a timeout.
>Took some time, but the growing of the database took some
time.
>Because of the timeout, the extend was not added after
the action,
>so the next action causes the same problem.
>Our production machines are quite a bit faster, so the
problem has
>not been noticed (yet) on a production machine.
>Our development machines are faster as wel, but a glitch
once
>probably wouldn't be noticed as significant.
>So what timing is to be expected by extending the
database ?
>(Is 64 Mbyte a good size for extending)
>Any thoughts about this ?
>ben brugman
>
>.
>|||Can you give an indication of the 'size' of your machine
and the time it takes to extend by 200 MB ?
ben
"bharath" <barathsing@.hotmail.com> wrote in message
news:a20401c3b7d3$6dc70d90$a601280a@.phx.gbl...
> Hi Ben,
> In our production environment, we give a good initial size
> to the data and log files and set the increment value by
> 200 MB and not by size. We have found that this is the
> best way to handle databases that grow by 100 MB in a week
> and more than 2 GB in size... In this way, we reduce
> frequent growth of dbs.
> regards,
> bharath
> mcdba
>
> >--Original Message--
> >Yesterday we encountered a problem.
> >Setting a small testmachine (slow disks) filegrowth at 10
> % and
> >a databasesize of 1.2 Gbyte.
> >
> >Problem : A simple insert took over 1 minute.
> >Causing a timeout.
> >
> >Took some time, but the growing of the database took some
> time.
> >Because of the timeout, the extend was not added after
> the action,
> >so the next action causes the same problem.
> >
> >Our production machines are quite a bit faster, so the
> problem has
> >not been noticed (yet) on a production machine.
> >Our development machines are faster as wel, but a glitch
> once
> >probably wouldn't be noticed as significant.
> >
> >So what timing is to be expected by extending the
> database ?
> >(Is 64 Mbyte a good size for extending)
> >
> >Any thoughts about this ?
> >
> >ben brugman
> >
> >
> >.
> >