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?
>> >
>> >
>>
>