Format Alignment: factor

Showing posts with label factor. Show all posts

Monday, March 26, 2012

fill factors

How come most of the indexes created in MS SQL 2000 the
fill factor is 90 even though the default setting is 0?
Thanks,
LeighAre you running maintenance jobs generated by the maintenance plan =wizard? - it sets fillfactors to 90 by default.
Mike John
"Leigh Wilson" <anonymous@.discussions.microsoft.com> wrote in message =news:069901c3bbea$69d2cd30$a401280a@.phx.gbl...
> How come most of the indexes created in MS SQL 2000 the > fill factor is 90 even though the default setting is 0?
> > Thanks,
> Leigh|||Leigh,
From where did you get the 90% value? Sysindexes?
Perhaps the ones who created the indexes used some GUI tool (like EM
perhaps) which has a default value of 90%? Or someone re-configured the
default value with sp_configure?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leigh Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:069901c3bbea$69d2cd30$a401280a@.phx.gbl...
> How come most of the indexes created in MS SQL 2000 the
> fill factor is 90 even though the default setting is 0?
> Thanks,
> Leigh

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 settings

In 2005 Database settings....default fill factor 0

When this is 0 and and index rebuild is performed does that mean there is no space for the indexes to grew and issues a page split immediately.

Would it be better to adjust the fill factor to 5

Thanks

It all depends on how your database is used.
This Books Online 2005 topic discusses the options: Fill Factor

fill factor question

Would it make sense that on a table with an Identity column, a fill factor
of 100 would be OK sense data will never be inserted into the "middle" of
the table?
SQL2K SP3
TIA, ChrisR
Thatis only one aspect that you have to consider. Anytime you update any
variable columns to a size larger than what the value was when inserted it
may cause a page split.
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:OejxSx29EHA.2032@.tk2msftngp13.phx.gbl...
> Would it make sense that on a table with an Identity column, a fill factor
> of 100 would be OK sense data will never be inserted into the "middle" of
> the table?
> --
> SQL2K SP3
> TIA, ChrisR
>

fill factor question

Would it make sense that on a table with an Identity column, a fill factor
of 100 would be OK sense data will never be inserted into the "middle" of
the table?
SQL2K SP3
TIA, ChrisRThatis only one aspect that you have to consider. Anytime you update any
variable columns to a size larger than what the value was when inserted it
may cause a page split.
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:OejxSx29EHA.2032@.tk2msftngp13.phx.gbl...
> Would it make sense that on a table with an Identity column, a fill factor
> of 100 would be OK sense data will never be inserted into the "middle" of
> the table?
> --
> SQL2K SP3
> TIA, ChrisR
>

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.What column(s) is the clustered index on?
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please bear
>> with me. Say Ive got a huge table that gets lots of Inserts. But to do
>> these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> What column(s) is the clustered index on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please
>> bear with me. Say Ive got a huge table that gets lots of Inserts. But to
>> do these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>>
>|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> What column(s) is the clustered index on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please
>> bear with me. Say Ive got a huge table that gets lots of Inserts. But to
>> do these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>>
>

fill factor question

Would it make sense that on a table with an Identity column, a fill factor
of 100 would be OK sense data will never be inserted into the "middle" of
the table?
--
SQL2K SP3
TIA, ChrisRThatis only one aspect that you have to consider. Anytime you update any
variable columns to a size larger than what the value was when inserted it
may cause a page split.
--
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:OejxSx29EHA.2032@.tk2msftngp13.phx.gbl...
> Would it make sense that on a table with an Identity column, a fill factor
> of 100 would be OK sense data will never be inserted into the "middle" of
> the table?
> --
> SQL2K SP3
> TIA, ChrisR
>

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 in dbcc dbreindex

Hi
I am on sqlserver 2000 and my question is on dbcc dbreindex.
If I say dbcc dbreindex('TABLE_NAME')
what is the fillfactor used on index rebuilds.
Documents does not say anywhere about it.
Tks
MangeshHi
The fill factor that was used when the index was originally created. 90% by
default.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
> Hi
> I am on sqlserver 2000 and my question is on dbcc dbreindex.
> If I say dbcc dbreindex('TABLE_NAME')
> what is the fillfactor used on index rebuilds.
> Documents does not say anywhere about it.
> Tks
> Mangesh
>|||I believe that default is 100%, unless you change it with sp_configure...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> The fill factor that was used when the index was originally created. 90% by default.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>> Hi
>> I am on sqlserver 2000 and my question is on dbcc dbreindex.
>> If I say dbcc dbreindex('TABLE_NAME')
>> what is the fillfactor used on index rebuilds.
>> Documents does not say anywhere about it.
>> Tks
>> Mangesh
>|||I have just tried this on SP3 and the default is 80
"Tibor Karaszi" wrote:
> I believe that default is 100%, unless you change it with sp_configure...?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > The fill factor that was used when the index was originally created. 90% by default.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> > news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
> >> Hi
> >> I am on sqlserver 2000 and my question is on dbcc dbreindex.
> >> If I say dbcc dbreindex('TABLE_NAME')
> >> what is the fillfactor used on index rebuilds.
> >>
> >> Documents does not say anywhere about it.
> >>
> >> Tks
> >> Mangesh
> >>
> >
> >
>
>|||Hi Marc
What exactly did you try?
I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
shown as completely full pages with DBCC SHOWCONTIG.
This seems to support what Tibor said.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...
>I have just tried this on SP3 and the default is 80
> "Tibor Karaszi" wrote:
>> I believe that default is 100%, unless you change it with
>> sp_configure...?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Hi
>> >
>> > The fill factor that was used when the index was originally created.
>> > 90% by default.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > IM: mike@.epprecht.net
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote
>> > in message
>> > news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>> >> Hi
>> >> I am on sqlserver 2000 and my question is on dbcc dbreindex.
>> >> If I say dbcc dbreindex('TABLE_NAME')
>> >> what is the fillfactor used on index rebuilds.
>> >>
>> >> Documents does not say anywhere about it.
>> >>
>> >> Tks
>> >> Mangesh
>> >>
>> >
>> >
>>|||I agree with Kalen and Tibor. The default should be 0 which is 100% full.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ucTTcPFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Hi Marc
> What exactly did you try?
> I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
> shown as completely full pages with DBCC SHOWCONTIG.
> This seems to support what Tibor said.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...
>>I have just tried this on SP3 and the default is 80
>> "Tibor Karaszi" wrote:
>> I believe that default is 100%, unless you change it with
>> sp_configure...?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Hi
>> >
>> > The fill factor that was used when the index was originally created.
>> > 90% by default.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > IM: mike@.epprecht.net
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote
>> > in message
>> > news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>> >> Hi
>> >> I am on sqlserver 2000 and my question is on dbcc dbreindex.
>> >> If I say dbcc dbreindex('TABLE_NAME')
>> >> what is the fillfactor used on index rebuilds.
>> >>
>> >> Documents does not say anywhere about it.
>> >>
>> >> Tks
>> >> Mangesh
>> >>
>> >
>> >
>>
>

Fill factor in dbcc dbreindex

Fill factor for ever increasing clustered index

Alan
Fillfactoe specifies how full each page should be. 70 means 70%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> If I defined the clustered index on identity column, what will be the
> differences between fill factor 100% and 70%?
>
Most people forget about this scenario when choosing to go with 100% fill
factors.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on
> their pages.
> A very common example is "notes" type columns which are very often
> widened, causing page splits even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space
> otherwise your updates might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>
|||Yes, so this is most of the answer to the OP. I believe he was wondering on
an increasing value if there was any reason to go with 70 vs 100. The answer
has to do with what else are you going to do with the table besides INSERTs.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ovperrb5uhvd1@.corp.supernews.com...
> Most people forget about this scenario when choosing to go with 100% fill
> factors.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||1) it is my understanding that new pages will take the fill factor.
2) Deletes will leave holes in their respective pages. Those holes will be
'filled' when you reindex/defrag.
3) That is my understanding as well.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Yes, if for the case of INSERT only, when the last page is full, any
> further
> INSERT will be put into the new page. So there is no difference between
> 10%,
> 20% or 70% or even 100%?
> If there is DELETE, so there will be some holes in the page? Because the
> column is ever increasing. The holes will never be filled unless
> re-indexing? If re-indexing, then this is my original question, does the
> fill factor affect it?
> In my understanding is when the page is splitted, the new page will be
> filled by determined by fill factor.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
> on
> answer
> INSERTs.
> fill
> on
> space
> the
>
|||No a fill factor is ONLY adhered to during a create or rebuilding of an
index. It would be pointless to always keep a page 70% full for example as
you are inserting new rows. If a page splits approximately half of the rows
stay on the existing page and half on the new page.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13p1m1aqmcpbr15@.corp.supernews.com...
> 1) it is my understanding that new pages will take the fill factor.
> 2) Deletes will leave holes in their respective pages. Those holes will
> be 'filled' when you reindex/defrag.
> 3) That is my understanding as well.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>
|||DUH!! You know, having a 2 1/2 year old really does occassionally affect
the adult brain. :-))
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an
> index. It would be pointless to always keep a page 70% full for example as
> you are inserting new rows. If a page splits approximately half of the
> rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13p1m1aqmcpbr15@.corp.supernews.com...
>
|||Well that isn't really a split then

. That's business as usual.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
> ... unless the page splits at the "end" (the last row according to the
> index). Then now pages are moves, instead just a new page is linked into
> the linked list, at the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||I believe it is recorded as a split event. There are also other special
cases where the split will not always be 50/50. I wrote a series of articles
for SQL Magazine about this.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then

. That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>

Fill factor for ever increasing clustered index

If I defined the clustered index on identity column, what will be the
differences between fill factor 100% and 70%?Alan
Fillfactoe specifies how full each page should be. 70 means 70%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> If I defined the clustered index on identity column, what will be the
> differences between fill factor 100% and 70%?
>|||In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
ever increasing so there will be no page splits in such an index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
> Alan
> Fillfactoe specifies how full each page should be. 70 means 70%
> full, 100 means 100% full. The only special case is 0, which means the leaf
> level is full, but there is room for one or two rows per page in the upper
> levels of the index tree.
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>|||Except where rows are updated with wider values so they no longer fit on
their pages.
A very common example is "notes" type columns which are very often widened,
causing page splits even on ID based CIXs.
For these scenarios, you might still want to leave some fillfactor space
otherwise your updates might be slowed down badly
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
> In addition, I recommend 0 (same as 100) or 100 for an index on an
> identity column. The value is ever increasing so there will be no page
> splits in such an index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||> Except where rows are updated with wider values so they no longer fit on their pages.
Good point, Greg. One should consider what other columns are in the index (for a cl index, of course
all column) and whether any of those column risk being "widened" by updates.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on their pages.
> A very common example is "notes" type columns which are very often widened, causing page splits
> even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space otherwise your updates
> might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
>> ever increasing so there will be no page splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the leaf
>> level is full, but there is room for one or two rows per page in the upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||Yes, so this is most of the answer to the OP. I believe he was wondering on
an increasing value if there was any reason to go with 70 vs 100. The answer
has to do with what else are you going to do with the table besides INSERTs.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ovperrb5uhvd1@.corp.supernews.com...
> Most people forget about this scenario when choosing to go with 100% fill
> factors.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Except where rows are updated with wider values so they no longer fit on
>> their pages.
>> A very common example is "notes" type columns which are very often
>> widened, causing page splits even on ID based CIXs.
>> For these scenarios, you might still want to leave some fillfactor space
>> otherwise your updates might be slowed down badly
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> Benchmark your query performance
>> http://www.SQLBenchmarkPro.com
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> identity column. The value is ever increasing so there will be no page
>> splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>>
>|||Most people forget about this scenario when choosing to go with 100% fill
factors.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on
> their pages.
> A very common example is "notes" type columns which are very often
> widened, causing page splits even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space
> otherwise your updates might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> identity column. The value is ever increasing so there will be no page
>> splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||Yes, if for the case of INSERT only, when the last page is full, any further
INSERT will be put into the new page. So there is no difference between 10%,
20% or 70% or even 100%?
If there is DELETE, so there will be some holes in the page? Because the
column is ever increasing. The holes will never be filled unless
re-indexing? If re-indexing, then this is my original question, does the
fill factor affect it?
In my understanding is when the page is splitted, the new page will be
filled by determined by fill factor.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Yes, so this is most of the answer to the OP. I believe he was wondering
on
> an increasing value if there was any reason to go with 70 vs 100. The
answer
> has to do with what else are you going to do with the table besides
INSERTs.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ovperrb5uhvd1@.corp.supernews.com...
> > Most people forget about this scenario when choosing to go with 100%
fill
> > factors.
> >
> >> Except where rows are updated with wider values so they no longer fit
on
> >> their pages.
> >>
> >> A very common example is "notes" type columns which are very often
> >> widened, causing page splits even on ID based CIXs.
> >>
> >> For these scenarios, you might still want to leave some fillfactor
space
> >> otherwise your updates might be slowed down badly
> >>
> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
> >> identity column. The value is ever increasing so there will be no page
> >> splits in such an index.
> >>
> >> Alan
> >> Fillfactoe specifies how full each page should be. 70 means 70%
> >> full, 100 means 100% full. The only special case is 0, which means
the
> >> leaf
> >> level is full, but there is room for one or two rows per page in the
> >> upper
> >> levels of the index tree.|||1) it is my understanding that new pages will take the fill factor.
2) Deletes will leave holes in their respective pages. Those holes will be
'filled' when you reindex/defrag.
3) That is my understanding as well.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Yes, if for the case of INSERT only, when the last page is full, any
> further
> INSERT will be put into the new page. So there is no difference between
> 10%,
> 20% or 70% or even 100%?
> If there is DELETE, so there will be some holes in the page? Because the
> column is ever increasing. The holes will never be filled unless
> re-indexing? If re-indexing, then this is my original question, does the
> fill factor affect it?
> In my understanding is when the page is splitted, the new page will be
> filled by determined by fill factor.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
> on
>> an increasing value if there was any reason to go with 70 vs 100. The
> answer
>> has to do with what else are you going to do with the table besides
> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>|||No a fill factor is ONLY adhered to during a create or rebuilding of an
index. It would be pointless to always keep a page 70% full for example as
you are inserting new rows. If a page splits approximately half of the rows
stay on the existing page and half on the new page.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13p1m1aqmcpbr15@.corp.supernews.com...
> 1) it is my understanding that new pages will take the fill factor.
> 2) Deletes will leave holes in their respective pages. Those holes will
> be 'filled' when you reindex/defrag.
> 3) That is my understanding as well.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>|||DUH!! You know, having a 2 1/2 year old really does occassionally affect
the adult brain. :-))
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an
> index. It would be pointless to always keep a page 70% full for example as
> you are inserting new rows. If a page splits approximately half of the
> rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will
>> be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||> If a page splits approximately half of the rows stay on the existing page and half on the new
> page.
... unless the page splits at the "end" (the last row according to the index). Then now pages are
moves, instead just a new page is linked into the linked list, at the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an index. It would be
> pointless to always keep a page 70% full for example as you are inserting new rows. If a page
> splits approximately half of the rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will be 'filled' when you
>> reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any further
>> INSERT will be put into the new page. So there is no difference between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||Well that isn't really a split then:). That's business as usual.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing page
>> and half on the new page.
> ... unless the page splits at the "end" (the last row according to the
> index). Then now pages are moves, instead just a new page is linked into
> the linked list, at the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an
>> index. It would be pointless to always keep a page 70% full for example
>> as you are inserting new rows. If a page splits approximately half of
>> the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will
>> be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because
>> the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does
>> the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which
>> >> means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||I believe it is recorded as a split event. There are also other special
cases where the split will not always be 50/50. I wrote a series of articles
for SQL Magazine about this.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then:). That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing
>> page and half on the new page.
>> ... unless the page splits at the "end" (the last row according to the
>> index). Then now pages are moves, instead just a new page is linked into
>> the linked list, at the end.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an
>> index. It would be pointless to always keep a page 70% full for example
>> as you are inserting new rows. If a page splits approximately half of
>> the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes
>> will be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference
>> between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because
>> the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does
>> the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with
>> > 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on
>> >> an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which
>> >> means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>>
>|||> Well that isn't really a split then:). That's business as usual.
Tues, but it is sometimes referred to as a " page split" (even though it really isn't such). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then:). That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing page and half on the new
>> page.
>> ... unless the page splits at the "end" (the last row according to the index). Then now pages are
>> moves, instead just a new page is linked into the linked list, at the end.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an index. It would be
>> pointless to always keep a page 70% full for example as you are inserting new rows. If a page
>> splits approximately half of the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will be 'filled' when you
>> reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any further
>> INSERT will be put into the new page. So there is no difference between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>>
>>
>>
>|||If the page is full and a new record is insert, no matter at the beginning,
middle or end, then page split is the result. Then if the fill factor is for
example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
has effect?|||Fillfactor only applies when building an index. It has no affect on page
splits.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> If the page is full and a new record is insert, no matter at the
> beginning,
> middle or end, then page split is the result. Then if the fill factor is
> for
> example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
> has effect?
>|||Thanks.
So if I rebuild the index, do 'all' pages will be filled up determined by
the fill factor?
eg. 70%.
All pages will be filled up 70 %?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23LBNtnLXIHA.5448@.TK2MSFTNGP04.phx.gbl...
> Fillfactor only applies when building an index. It has no affect on page
> splits.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> > If the page is full and a new record is insert, no matter at the
> > beginning,
> > middle or end, then page split is the result. Then if the fill factor is
> > for
> > example, 70%, then are the two pages still 50/50 ? Or the 705 fill
factor
> > has effect?
> >
> >
>|||As far as possible, all leaf level pages will be filled 70%. The upper
levels of indexes will be filled almost full, unless you ALSO specify the
PAD_INDEX option.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:%234AgUbjYIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Thanks.
> So if I rebuild the index, do 'all' pages will be filled up determined by
> the fill factor?
> eg. 70%.
> All pages will be filled up 70 %?
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23LBNtnLXIHA.5448@.TK2MSFTNGP04.phx.gbl...
>> Fillfactor only applies when building an index. It has no affect on page
>> splits.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> > If the page is full and a new record is insert, no matter at the
>> > beginning,
>> > middle or end, then page split is the result. Then if the fill factor
>> > is
>> > for
>> > example, 70%, then are the two pages still 50/50 ? Or the 705 fill
> factor
>> > has effect?
>> >
>> >
>>
>

Monday, March 26, 2012

fill factors

fill factor value

Fill Factor settings

fill factor question

fill factor question

fill factor question

fill factor question

fill factor question

fill factor question

fill factor problem

fill factor problem

fill factor problem

Fill factor in dbcc dbreindex

Fill factor in dbcc dbreindex

Fill factor in dbcc dbreindex

Fill factor for ever increasing clustered index

Fill factor for ever increasing clustered index

Format Alignment

Blog Archive

About Me