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?
>> >
>> >
>>
>
Showing posts with label differences. Show all posts
Showing posts with label differences. Show all posts
Monday, March 26, 2012
Fill factor 100% affects performance
Hi,
I've noticed something strange. I've got a table which contains a clustered
index. Now I've noticed a very big differences in the logical reads of a
complex join when I change the fill factor from 99 to 100. When the fill
factor is less than 100 then the number of logical reads is around 500. In
fact it imrpoves as I change it from 40 to 60 to 99% which seems normal as
there are fewer pages go through.
Now, once this changes to over 99 the logical reads jump to 16,000. Can
anyone explain why the dramatic difference? changing the fill factor seems t
o
mess up the whole query plan. And the output of DBCC SHOWCONTIG if it helps
.
- Pages Scanned........................: 124
- Extents Scanned.......................: 16
- Extent Switches.......................: 15
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [16:16]
- Logical Scan Fragmentation ..............: 8.06%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 52.3
- Avg. Page Density (full)................: 99.35%
Thanks,
PanosFirst off the 16,000 reads are not from the table you listed as it only has
124 pages in it. The other table in the join will also play a big factor in
the outcome of the # of reads. In any case unless the table is read only it
is almost never a good idea to use a 100% fill factor. Otherwise you are
likely to get lots of page splits. I am not sure why the move to 100% is
giving you the results you say. Maybe if we could look at the DDL including
indexes for both tables it would help.
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:3ED85CB0-41B5-4F4D-AF7A-0D5F780114F7@.microsoft.com...
> Hi,
> I've noticed something strange. I've got a table which contains a
> clustered
> index. Now I've noticed a very big differences in the logical reads of a
> complex join when I change the fill factor from 99 to 100. When the fill
> factor is less than 100 then the number of logical reads is around 500. In
> fact it imrpoves as I change it from 40 to 60 to 99% which seems normal as
> there are fewer pages go through.
> Now, once this changes to over 99 the logical reads jump to 16,000. Can
> anyone explain why the dramatic difference? changing the fill factor seems
> to
> mess up the whole query plan. And the output of DBCC SHOWCONTIG if it
> helps.
> - Pages Scanned........................: 124
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 15
> - Avg. Pages per Extent..................: 7.8
> - Scan Density [Best Count:Actual Count]......: 100.00% [16:16]
> - Logical Scan Fragmentation ..............: 8.06%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 52.3
> - Avg. Page Density (full)................: 99.35%
> Thanks,
> Panos|||There are definitely 16,000 logical reads for this table, at least this is
what it shows with statistics set on, unless there is a bug with the logical
reads output?
"Andrew J. Kelly" wrote:
> First off the 16,000 reads are not from the table you listed as it only ha
s
> 124 pages in it. The other table in the join will also play a big factor
in
> the outcome of the # of reads. In any case unless the table is read only
it
> is almost never a good idea to use a 100% fill factor. Otherwise you are
> likely to get lots of page splits. I am not sure why the move to 100% is
> giving you the results you say. Maybe if we could look at the DDL includin
g
> indexes for both tables it would help.
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:3ED85CB0-41B5-4F4D-AF7A-0D5F780114F7@.microsoft.com...
>
>|||What does the join look like? How about the query plan? Can you provide
more details so we don't have to guess?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:15565321-C6F5-49E1-B8C8-ED91559B8078@.microsoft.com...
> There are definitely 16,000 logical reads for this table, at least this is
> what it shows with statistics set on, unless there is a bug with the
> logical
> reads output?
> "Andrew J. Kelly" wrote:
>|||OK here it is but it looks horrible cause it comes from BusinessObjects!
Thank you. The table in question is REP_REVIEW_ALLOCATION_REF
exec sp_prepexec @.P1 output, NULL, N'SELECT DISTINCT
( Review.REVIEW_ID ),
( Review_Type.REVIEW_TYPE_DESCRIPTION ),
( Review.GID ),
( Review_Risk.TYPE_DESCRIPTION ),
( Review.ULTIMATE_NAME ),
AllocationRef.RB_USER_NAME,
( Review_Process_Status.STATUS_DATE_START ),
( Review.ULTIMATE_GID ),
( AllocationRef.WORKLIST ),
( AllocationRef.ALLOCATED_WORKLIST ),
( Process_Status.PROCESS_STATUS_DESCRIPTION ),
( Review.LEGAL_NAME ),
( Reviewer.FIRST_NAME ) + '' '' +( Reviewer.LAST_NAME ),
( Review_Process_Status.PROCESS_STATUS_ID ),
( Entity_Type.TYPE_DESCRIPTION ),
Review_Process_Status.STATUS_DATE_END,
Review_Process_Status.IS_SECONDARY_STATUS,
Process_Status.ORDER_ID,
( Actioner.FIRST_NAME ) + '' '' +( Actioner.LAST_NAME )
FROM
database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
database_rep.dbo.REVIEW Review,
database_rep.dbo.COMPANY_MEMBER Reviewer,
database_rep.dbo.COMPANY_MEMBER Actioner,
database_rep.dbo.PROCESS_STATUS Process_Status,
database_rep.dbo.ENTITY_TYPE Entity_Type,
database_rep.dbo.RISK_TYPE Review_Risk,
database_rep.dbo.REVIEW_TYPE Review_Type,
database_rep.dbo.REP_REVIEW_ALLOCATION_REF AllocationRef
WHERE
( Entity_Type.ENTITY_TYPE_ID=*Review.ENTITY_TYPE_ID )
AND (
Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID )
AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
AND ( Review.REVIEW_TYPE_ID=Review_Type.REVIEW_TYPE_ID )
AND ( Review_Risk.RISK_TYPE_ID=*Review.RISK_TYPE_ID )
AND ( AllocationRef.OCTOPUS_ID=Review.OCTOPUS_ID )
AND ( Review_Process_Status.MEMBER_ID=Reviewer.MEMBER_ID )
AND ( Review_Process_Status.COMPLETED_BY*=Actioner.MEMBER_ID )
AND (
( Process_Status.PROCESS_STATUS_DESCRIPTION ) != ''First Read''
AND ( (( ( AllocationRef.WORKLIST ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)''))
)
AND ( (( ( Review_Type.REVIEW_TYPE_DESCRIPTION ) ) IN (''Risk
Reassessment'')) )
AND ( (( ( AllocationRef.ALLOCATED_WORKLIST ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)''))
)
AND ( Review.REVIEW_ID ) IN (SELECT
( Review.REVIEW_ID )
FROM
database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
database_rep.dbo.REVIEW Review,
database_rep.dbo.PROCESS_STATUS Process_Status
WHERE
( Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID
)
AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
AND (
( (( ( Process_Status.PROCESS_STATUS_DESCRIPTION ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)'')) )
AND Review_Process_Status.IS_SECONDARY_STATUS = ''N''
AND Review_Process_Status.STATUS_DATE_END >= ''12/31/9999 00:0:0''
)
)
)
'
"Andrew J. Kelly" wrote:
> What does the join look like? How about the query plan? Can you provide
> more details so we don't have to guess?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:15565321-C6F5-49E1-B8C8-ED91559B8078@.microsoft.com...
>
>|||For some reason I thought it was only a 2 table join and didn't realize it
was such a messof a join<g>. With a WHERE clause like that it would be hard
pressed not to scan tables. FYI the old join syntax you are using can have
ambiguous results and is not supported moving forward. In any case I
suspect you are hitting some quirky threshold with the statistics between
the 99 and 100% fillfactors. You should choose a fill factor that is
suitable or at least a trade off to minimize pagesplits and keep the pages
as full as possible. Then worry about tuning the query. You can not tune a
query by adjusting the fill factor as it is just not practical. Again 100%
is almost never a good choice anyway so why spend time there in the first
place?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:8700DDA6-38CF-478B-A0FE-BCE71A724250@.microsoft.com...
> OK here it is but it looks horrible cause it comes from BusinessObjects!
> Thank you. The table in question is REP_REVIEW_ALLOCATION_REF
> exec sp_prepexec @.P1 output, NULL, N'SELECT DISTINCT
> ( Review.REVIEW_ID ),
> ( Review_Type.REVIEW_TYPE_DESCRIPTION ),
> ( Review.GID ),
> ( Review_Risk.TYPE_DESCRIPTION ),
> ( Review.ULTIMATE_NAME ),
> AllocationRef.RB_USER_NAME,
> ( Review_Process_Status.STATUS_DATE_START ),
> ( Review.ULTIMATE_GID ),
> ( AllocationRef.WORKLIST ),
> ( AllocationRef.ALLOCATED_WORKLIST ),
> ( Process_Status.PROCESS_STATUS_DESCRIPTION ),
> ( Review.LEGAL_NAME ),
> ( Reviewer.FIRST_NAME ) + '' '' +( Reviewer.LAST_NAME ),
> ( Review_Process_Status.PROCESS_STATUS_ID ),
> ( Entity_Type.TYPE_DESCRIPTION ),
> Review_Process_Status.STATUS_DATE_END,
> Review_Process_Status.IS_SECONDARY_STATUS,
> Process_Status.ORDER_ID,
> ( Actioner.FIRST_NAME ) + '' '' +( Actioner.LAST_NAME )
> FROM
> database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
> database_rep.dbo.REVIEW Review,
> database_rep.dbo.COMPANY_MEMBER Reviewer,
> database_rep.dbo.COMPANY_MEMBER Actioner,
> database_rep.dbo.PROCESS_STATUS Process_Status,
> database_rep.dbo.ENTITY_TYPE Entity_Type,
> database_rep.dbo.RISK_TYPE Review_Risk,
> database_rep.dbo.REVIEW_TYPE Review_Type,
> database_rep.dbo.REP_REVIEW_ALLOCATION_REF AllocationRef
> WHERE
> ( Entity_Type.ENTITY_TYPE_ID=*Review.ENTITY_TYPE_ID )
> AND (
> eview_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID )
> AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
> AND ( Review.REVIEW_TYPE_ID=Review_Type.REVIEW_TYPE_ID )
> AND ( Review_Risk.RISK_TYPE_ID=*Review.RISK_TYPE_ID )
> AND ( AllocationRef.OCTOPUS_ID=Review.OCTOPUS_ID )
> AND ( Review_Process_Status.MEMBER_ID=Reviewer.MEMBER_ID )
> AND ( Review_Process_Status.COMPLETED_BY*=Actioner.MEMBER_ID )
> AND (
> ( Process_Status.PROCESS_STATUS_DESCRIPTION ) != ''First Read''
> AND ( (( ( AllocationRef.WORKLIST ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)''))
> )
> AND ( (( ( Review_Type.REVIEW_TYPE_DESCRIPTION ) ) IN (''Risk
> Reassessment'')) )
> AND ( (( ( AllocationRef.ALLOCATED_WORKLIST ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)''))
> )
> AND ( Review.REVIEW_ID ) IN (SELECT
> ( Review.REVIEW_ID )
> FROM
> database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
> database_rep.dbo.REVIEW Review,
> database_rep.dbo.PROCESS_STATUS Process_Status
> WHERE
> (
> Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID
> )
> AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
> AND (
> ( (( ( Process_Status.PROCESS_STATUS_DESCRIPTION ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)'')) )
> AND Review_Process_Status.IS_SECONDARY_STATUS = ''N''
> AND Review_Process_Status.STATUS_DATE_END >= ''12/31/9999 00:0:0''
> )
> )
> )
> '
> "Andrew J. Kelly" wrote:
>|||The sql is coming from business objects and can't be changed, even though is
rubbish. The fill factor was changed to 100% automatically when I've switche
d
the index from nonclustered to clustered something that I didn't know. I did
some investigation and found out that the fill factor was 100% so
investigated more to see why performance gets affected little bit. Anyway, I
think I should just accept it and keep in mind that fill factor can affect
the performance of queries sometimes. Thank you for your help.
"Andrew J. Kelly" wrote:
> For some reason I thought it was only a 2 table join and didn't realize it
> was such a messof a join<g>. With a WHERE clause like that it would be ha
rd
> pressed not to scan tables. FYI the old join syntax you are using can hav
e
> ambiguous results and is not supported moving forward. In any case I
> suspect you are hitting some quirky threshold with the statistics between
> the 99 and 100% fillfactors. You should choose a fill factor that is
> suitable or at least a trade off to minimize pagesplits and keep the pages
> as full as possible. Then worry about tuning the query. You can not tune
a
> query by adjusting the fill factor as it is just not practical. Again 100
%
> is almost never a good choice anyway so why spend time there in the first
> place?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:8700DDA6-38CF-478B-A0FE-BCE71A724250@.microsoft.com...
>
>
I've noticed something strange. I've got a table which contains a clustered
index. Now I've noticed a very big differences in the logical reads of a
complex join when I change the fill factor from 99 to 100. When the fill
factor is less than 100 then the number of logical reads is around 500. In
fact it imrpoves as I change it from 40 to 60 to 99% which seems normal as
there are fewer pages go through.
Now, once this changes to over 99 the logical reads jump to 16,000. Can
anyone explain why the dramatic difference? changing the fill factor seems t
o
mess up the whole query plan. And the output of DBCC SHOWCONTIG if it helps
.
- Pages Scanned........................: 124
- Extents Scanned.......................: 16
- Extent Switches.......................: 15
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [16:16]
- Logical Scan Fragmentation ..............: 8.06%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 52.3
- Avg. Page Density (full)................: 99.35%
Thanks,
PanosFirst off the 16,000 reads are not from the table you listed as it only has
124 pages in it. The other table in the join will also play a big factor in
the outcome of the # of reads. In any case unless the table is read only it
is almost never a good idea to use a 100% fill factor. Otherwise you are
likely to get lots of page splits. I am not sure why the move to 100% is
giving you the results you say. Maybe if we could look at the DDL including
indexes for both tables it would help.
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:3ED85CB0-41B5-4F4D-AF7A-0D5F780114F7@.microsoft.com...
> Hi,
> I've noticed something strange. I've got a table which contains a
> clustered
> index. Now I've noticed a very big differences in the logical reads of a
> complex join when I change the fill factor from 99 to 100. When the fill
> factor is less than 100 then the number of logical reads is around 500. In
> fact it imrpoves as I change it from 40 to 60 to 99% which seems normal as
> there are fewer pages go through.
> Now, once this changes to over 99 the logical reads jump to 16,000. Can
> anyone explain why the dramatic difference? changing the fill factor seems
> to
> mess up the whole query plan. And the output of DBCC SHOWCONTIG if it
> helps.
> - Pages Scanned........................: 124
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 15
> - Avg. Pages per Extent..................: 7.8
> - Scan Density [Best Count:Actual Count]......: 100.00% [16:16]
> - Logical Scan Fragmentation ..............: 8.06%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 52.3
> - Avg. Page Density (full)................: 99.35%
> Thanks,
> Panos|||There are definitely 16,000 logical reads for this table, at least this is
what it shows with statistics set on, unless there is a bug with the logical
reads output?
"Andrew J. Kelly" wrote:
> First off the 16,000 reads are not from the table you listed as it only ha
s
> 124 pages in it. The other table in the join will also play a big factor
in
> the outcome of the # of reads. In any case unless the table is read only
it
> is almost never a good idea to use a 100% fill factor. Otherwise you are
> likely to get lots of page splits. I am not sure why the move to 100% is
> giving you the results you say. Maybe if we could look at the DDL includin
g
> indexes for both tables it would help.
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:3ED85CB0-41B5-4F4D-AF7A-0D5F780114F7@.microsoft.com...
>
>|||What does the join look like? How about the query plan? Can you provide
more details so we don't have to guess?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:15565321-C6F5-49E1-B8C8-ED91559B8078@.microsoft.com...
> There are definitely 16,000 logical reads for this table, at least this is
> what it shows with statistics set on, unless there is a bug with the
> logical
> reads output?
> "Andrew J. Kelly" wrote:
>|||OK here it is but it looks horrible cause it comes from BusinessObjects!
Thank you. The table in question is REP_REVIEW_ALLOCATION_REF
exec sp_prepexec @.P1 output, NULL, N'SELECT DISTINCT
( Review.REVIEW_ID ),
( Review_Type.REVIEW_TYPE_DESCRIPTION ),
( Review.GID ),
( Review_Risk.TYPE_DESCRIPTION ),
( Review.ULTIMATE_NAME ),
AllocationRef.RB_USER_NAME,
( Review_Process_Status.STATUS_DATE_START ),
( Review.ULTIMATE_GID ),
( AllocationRef.WORKLIST ),
( AllocationRef.ALLOCATED_WORKLIST ),
( Process_Status.PROCESS_STATUS_DESCRIPTION ),
( Review.LEGAL_NAME ),
( Reviewer.FIRST_NAME ) + '' '' +( Reviewer.LAST_NAME ),
( Review_Process_Status.PROCESS_STATUS_ID ),
( Entity_Type.TYPE_DESCRIPTION ),
Review_Process_Status.STATUS_DATE_END,
Review_Process_Status.IS_SECONDARY_STATUS,
Process_Status.ORDER_ID,
( Actioner.FIRST_NAME ) + '' '' +( Actioner.LAST_NAME )
FROM
database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
database_rep.dbo.REVIEW Review,
database_rep.dbo.COMPANY_MEMBER Reviewer,
database_rep.dbo.COMPANY_MEMBER Actioner,
database_rep.dbo.PROCESS_STATUS Process_Status,
database_rep.dbo.ENTITY_TYPE Entity_Type,
database_rep.dbo.RISK_TYPE Review_Risk,
database_rep.dbo.REVIEW_TYPE Review_Type,
database_rep.dbo.REP_REVIEW_ALLOCATION_REF AllocationRef
WHERE
( Entity_Type.ENTITY_TYPE_ID=*Review.ENTITY_TYPE_ID )
AND (
Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID )
AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
AND ( Review.REVIEW_TYPE_ID=Review_Type.REVIEW_TYPE_ID )
AND ( Review_Risk.RISK_TYPE_ID=*Review.RISK_TYPE_ID )
AND ( AllocationRef.OCTOPUS_ID=Review.OCTOPUS_ID )
AND ( Review_Process_Status.MEMBER_ID=Reviewer.MEMBER_ID )
AND ( Review_Process_Status.COMPLETED_BY*=Actioner.MEMBER_ID )
AND (
( Process_Status.PROCESS_STATUS_DESCRIPTION ) != ''First Read''
AND ( (( ( AllocationRef.WORKLIST ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)''))
)
AND ( (( ( Review_Type.REVIEW_TYPE_DESCRIPTION ) ) IN (''Risk
Reassessment'')) )
AND ( (( ( AllocationRef.ALLOCATED_WORKLIST ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)''))
)
AND ( Review.REVIEW_ID ) IN (SELECT
( Review.REVIEW_ID )
FROM
database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
database_rep.dbo.REVIEW Review,
database_rep.dbo.PROCESS_STATUS Process_Status
WHERE
( Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID
)
AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
AND (
( (( ( Process_Status.PROCESS_STATUS_DESCRIPTION ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)'')) )
AND Review_Process_Status.IS_SECONDARY_STATUS = ''N''
AND Review_Process_Status.STATUS_DATE_END >= ''12/31/9999 00:0:0''
)
)
)
'
"Andrew J. Kelly" wrote:
> What does the join look like? How about the query plan? Can you provide
> more details so we don't have to guess?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:15565321-C6F5-49E1-B8C8-ED91559B8078@.microsoft.com...
>
>|||For some reason I thought it was only a 2 table join and didn't realize it
was such a messof a join<g>. With a WHERE clause like that it would be hard
pressed not to scan tables. FYI the old join syntax you are using can have
ambiguous results and is not supported moving forward. In any case I
suspect you are hitting some quirky threshold with the statistics between
the 99 and 100% fillfactors. You should choose a fill factor that is
suitable or at least a trade off to minimize pagesplits and keep the pages
as full as possible. Then worry about tuning the query. You can not tune a
query by adjusting the fill factor as it is just not practical. Again 100%
is almost never a good choice anyway so why spend time there in the first
place?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:8700DDA6-38CF-478B-A0FE-BCE71A724250@.microsoft.com...
> OK here it is but it looks horrible cause it comes from BusinessObjects!
> Thank you. The table in question is REP_REVIEW_ALLOCATION_REF
> exec sp_prepexec @.P1 output, NULL, N'SELECT DISTINCT
> ( Review.REVIEW_ID ),
> ( Review_Type.REVIEW_TYPE_DESCRIPTION ),
> ( Review.GID ),
> ( Review_Risk.TYPE_DESCRIPTION ),
> ( Review.ULTIMATE_NAME ),
> AllocationRef.RB_USER_NAME,
> ( Review_Process_Status.STATUS_DATE_START ),
> ( Review.ULTIMATE_GID ),
> ( AllocationRef.WORKLIST ),
> ( AllocationRef.ALLOCATED_WORKLIST ),
> ( Process_Status.PROCESS_STATUS_DESCRIPTION ),
> ( Review.LEGAL_NAME ),
> ( Reviewer.FIRST_NAME ) + '' '' +( Reviewer.LAST_NAME ),
> ( Review_Process_Status.PROCESS_STATUS_ID ),
> ( Entity_Type.TYPE_DESCRIPTION ),
> Review_Process_Status.STATUS_DATE_END,
> Review_Process_Status.IS_SECONDARY_STATUS,
> Process_Status.ORDER_ID,
> ( Actioner.FIRST_NAME ) + '' '' +( Actioner.LAST_NAME )
> FROM
> database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
> database_rep.dbo.REVIEW Review,
> database_rep.dbo.COMPANY_MEMBER Reviewer,
> database_rep.dbo.COMPANY_MEMBER Actioner,
> database_rep.dbo.PROCESS_STATUS Process_Status,
> database_rep.dbo.ENTITY_TYPE Entity_Type,
> database_rep.dbo.RISK_TYPE Review_Risk,
> database_rep.dbo.REVIEW_TYPE Review_Type,
> database_rep.dbo.REP_REVIEW_ALLOCATION_REF AllocationRef
> WHERE
> ( Entity_Type.ENTITY_TYPE_ID=*Review.ENTITY_TYPE_ID )
> AND (
> eview_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID )
> AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
> AND ( Review.REVIEW_TYPE_ID=Review_Type.REVIEW_TYPE_ID )
> AND ( Review_Risk.RISK_TYPE_ID=*Review.RISK_TYPE_ID )
> AND ( AllocationRef.OCTOPUS_ID=Review.OCTOPUS_ID )
> AND ( Review_Process_Status.MEMBER_ID=Reviewer.MEMBER_ID )
> AND ( Review_Process_Status.COMPLETED_BY*=Actioner.MEMBER_ID )
> AND (
> ( Process_Status.PROCESS_STATUS_DESCRIPTION ) != ''First Read''
> AND ( (( ( AllocationRef.WORKLIST ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)''))
> )
> AND ( (( ( Review_Type.REVIEW_TYPE_DESCRIPTION ) ) IN (''Risk
> Reassessment'')) )
> AND ( (( ( AllocationRef.ALLOCATED_WORKLIST ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)''))
> )
> AND ( Review.REVIEW_ID ) IN (SELECT
> ( Review.REVIEW_ID )
> FROM
> database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
> database_rep.dbo.REVIEW Review,
> database_rep.dbo.PROCESS_STATUS Process_Status
> WHERE
> (
> Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID
> )
> AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
> AND (
> ( (( ( Process_Status.PROCESS_STATUS_DESCRIPTION ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)'')) )
> AND Review_Process_Status.IS_SECONDARY_STATUS = ''N''
> AND Review_Process_Status.STATUS_DATE_END >= ''12/31/9999 00:0:0''
> )
> )
> )
> '
> "Andrew J. Kelly" wrote:
>|||The sql is coming from business objects and can't be changed, even though is
rubbish. The fill factor was changed to 100% automatically when I've switche
d
the index from nonclustered to clustered something that I didn't know. I did
some investigation and found out that the fill factor was 100% so
investigated more to see why performance gets affected little bit. Anyway, I
think I should just accept it and keep in mind that fill factor can affect
the performance of queries sometimes. Thank you for your help.
"Andrew J. Kelly" wrote:
> For some reason I thought it was only a 2 table join and didn't realize it
> was such a messof a join<g>. With a WHERE clause like that it would be ha
rd
> pressed not to scan tables. FYI the old join syntax you are using can hav
e
> ambiguous results and is not supported moving forward. In any case I
> suspect you are hitting some quirky threshold with the statistics between
> the 99 and 100% fillfactors. You should choose a fill factor that is
> suitable or at least a trade off to minimize pagesplits and keep the pages
> as full as possible. Then worry about tuning the query. You can not tune
a
> query by adjusting the fill factor as it is just not practical. Again 100
%
> is almost never a good choice anyway so why spend time there in the first
> place?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:8700DDA6-38CF-478B-A0FE-BCE71A724250@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)