fill factor value

I do have table like this

(

uniqueid int

,scandate datetime

,courseno varchar(10)

)

this table has two indexes:

primary key clustered index on uniqueid, scandate

non-clustered index on courseno asc, uniqueid asc, courseno asc

this table can have 10 million records. there are no updates to this table. only data is inserted and selected from this table.

when i created these indexes the default fill factor value is 0. should i change the fill factor.

Thanks

sandeep, see BOL http://msdn2.microsoft.com/en-us/library/ms177459.aspx

Quick answer is probably OK, but read the article to determine if something in your environment (disk space limitation, etc) might want you to change it...

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 confusion

Turn away pure key zealots

I have a clustered index that starts with an INT IDENTITY(1,1) column and therefore you can only add data to the end of the cluster. What I'm confused about is the relationship between this and the fill factor. In a normal fill factor scenario you'd be worried about inserts causing page splits but if you can only append to this cluster does this mean I should set the factor to 100% even if I'm expecting a large number of inserts? Basically I don't understand what happens when you run out of space on a page on a B-tree if it's based upon an ever increasing number.

For an ever-increasing index the fill factor of 100 should indeed not increase the page splits.

It might cause hot spots having a clustered index on the ever-increasing key but after how many inserts this will become a problem really depends on your hardware (might even be several thousands a second).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

You are correct. 100% fill factor will not cause page splits on an index that is an identity column. But why would you create a clustered index over an identity column. Yes, I understand that loading data is very fast. Data retrieval and table joins would be costly.You should use natural keys with an identity column if a primary key is required.

|||

Thanks for the answers, re-assuring that I hadn't lost the plot.

re: TheBrick

I'm not telling the whole story that's why, just concentrating on the fill factor & identity issue. That's also why I said to look-away now

FYI the key is something like <Identity> + <natural key> where the idenitity is used as the join for all sorts of relationships. I won't go into the in's and out's but don't fret it needs to be there for this specific case. Yes it breaks rules, but sometimes you have to. Also there are other (obviously nonclustered) indexes that represent the more natural relationships. All this muddies the waters when asking about 100% fill factors hence I didn't include it

fill factor

i have changed a fill factor of a table which wont be updated any more.
i gave all the index+primary key ==>> fill factor=100%
and it seems like i have lost rows! is this possible?
the data is :
===================================
rows size index size
before 4,375,000 4GB 1.65GB
after 4,331,000 2.9GB 1.1GB
thnaks in advance
peleg
Hi
I assume this is from sp_spaceused which may not be accurate. Did you run
DBCC UPDATEUSAGE first?
John
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:EA0F7254-EFF4-464B-B3B1-72641A07DE4D@.microsoft.com...
>i have changed a fill factor of a table which wont be updated any more.
> i gave all the index+primary key ==>> fill factor=100%
> and it seems like i have lost rows! is this possible?
> the data is :
> ===================================
> rows size index size
> before 4,375,000 4GB 1.65GB
> after 4,331,000 2.9GB 1.1GB
> thnaks in advance
> peleg
|||nope
i looked at it before and after using the
"view->takpad" in the enterprise manager
can this be the problem?
"John Bell" wrote:

> Hi
> I assume this is from sp_spaceused which may not be accurate. Did you run
> DBCC UPDATEUSAGE first?
> John
> "pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
> news:EA0F7254-EFF4-464B-B3B1-72641A07DE4D@.microsoft.com...
>
>
|||sorry my mistake i have compared 2 tables with similar names by mistake
everything ok
peleg
"John Bell" wrote:

> Hi
> I assume this is from sp_spaceused which may not be accurate. Did you run
> DBCC UPDATEUSAGE first?
> John
> "pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
> news:EA0F7254-EFF4-464B-B3B1-72641A07DE4D@.microsoft.com...
>
>

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/

fill factor

i have changed a fill factor of a table which wont be updated any more.
i gave all the index+primary key ==>> fill factor=100%
and it seems like i have lost rows! is this possible?
the data is :
=================================== rows size index size
before 4,375,000 4GB 1.65GB
after 4,331,000 2.9GB 1.1GB
thnaks in advance
pelegHi
I assume this is from sp_spaceused which may not be accurate. Did you run
DBCC UPDATEUSAGE first?
John
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:EA0F7254-EFF4-464B-B3B1-72641A07DE4D@.microsoft.com...
>i have changed a fill factor of a table which wont be updated any more.
> i gave all the index+primary key ==>> fill factor=100%
> and it seems like i have lost rows! is this possible?
> the data is :
> ===================================> rows size index size
> before 4,375,000 4GB 1.65GB
> after 4,331,000 2.9GB 1.1GB
> thnaks in advance
> peleg|||nope
i looked at it before and after using the
"view->takpad" in the enterprise manager
can this be the problem?
"John Bell" wrote:
> Hi
> I assume this is from sp_spaceused which may not be accurate. Did you run
> DBCC UPDATEUSAGE first?
> John
> "pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
> news:EA0F7254-EFF4-464B-B3B1-72641A07DE4D@.microsoft.com...
> >i have changed a fill factor of a table which wont be updated any more.
> > i gave all the index+primary key ==>> fill factor=100%
> > and it seems like i have lost rows! is this possible?
> > the data is :
> > ===================================> > rows size index size
> > before 4,375,000 4GB 1.65GB
> > after 4,331,000 2.9GB 1.1GB
> >
> > thnaks in advance
> > peleg
>
>|||sorry my mistake i have compared 2 tables with similar names by mistake
everything ok
peleg
"John Bell" wrote:
> Hi
> I assume this is from sp_spaceused which may not be accurate. Did you run
> DBCC UPDATEUSAGE first?
> John
> "pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
> news:EA0F7254-EFF4-464B-B3B1-72641A07DE4D@.microsoft.com...
> >i have changed a fill factor of a table which wont be updated any more.
> > i gave all the index+primary key ==>> fill factor=100%
> > and it seems like i have lost rows! is this possible?
> > the data is :
> > ===================================> > rows size index size
> > before 4,375,000 4GB 1.65GB
> > after 4,331,000 2.9GB 1.1GB
> >
> > thnaks in advance
> > peleg
>
>