[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Fill Factor?!?

Derekman

3/22/2007 2:34:00 PM

After much research I am still stumped as to exactly how decreasing the fill
factor for an OLTP database would reduce page splits. If I were to cut the
fill factor from 100% to 50% this would allow half the original space that I
had before, thereby doubling the page splits.

Any help would be greatly appreciated.
Thank you
8 Answers

TheSQLGuru

3/22/2007 2:50:00 PM

0

Derekman, you have a common misconception about fill factor. Fillfactor
specifies how much EMPTY space will be placed on each 8K index page when the
index is built/maintained. If a page is 100% full, clearly there will be
ZERO rows worth of space available in it and thus the FIRST row that needs
to be inserted into that page will cause a page split. This means 2 pages
will be created in the index tree, each with roughly 50% of the original
page's data in them. Then the new row will be put on the appropriate page.

If you have a 50% Fillfactor when you build the index, each page (at
creation time) will only be filled up 50% full, leaving ~4K of space for new
rows to be placed into the index page before it has to split. Obviously if
each page is initially only 50% full, there will be TWICE as many 8K pages
(actually some number more) required for all of the index entries. Thus
using the index will likely take a bit more effort, but it will hopefully be
laid down on disk contiguously (leading to well-performing sequential I/O
where applicable) and it will not FRAGMENT by having lots of page splits for
quite some time. This assumes the data will be inserted across the range of
the index values (such as LastName). If the index is on an Identity PK,
then splits will only occur at the tail of the index and a large fill factor
(perhaps even 100%) would be more appropriate.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Derekman" <Derekman@discussions.microsoft.com> wrote in message
news:8E853D20-CC40-45B6-BD97-62B98647B26A@microsoft.com...
> After much research I am still stumped as to exactly how decreasing the
> fill
> factor for an OLTP database would reduce page splits. If I were to cut
> the
> fill factor from 100% to 50% this would allow half the original space that
> I
> had before, thereby doubling the page splits.
>
> Any help would be greatly appreciated.
> Thank you


Russell Fields

3/22/2007 2:54:00 PM

0

Actually, a page split happens when a page is updated and now has too much
data in it. At that point the page needs to be split into two. One page
remains in the current location, but the other page will have to be
allocated somewhere potentially far away. This increases fragmentation.
(Using too low a fill factor does not cause fragmentation (pages physically
disassociated) but it does waste space.)

Reserving some free space reduces the likelihood of a split since it gives
the page a buffer for an update or insert to the page. The actual good
setting is a factor of how large the rows are in a table, how variable they
are in size, and the extent to which new data is being inserted.

For example, assuming a row size of 200 bytes, a fill factor of 90% would
allow 4 rows to be inserted into a page without causing a split.

RLF
"Derekman" <Derekman@discussions.microsoft.com> wrote in message
news:8E853D20-CC40-45B6-BD97-62B98647B26A@microsoft.com...
> After much research I am still stumped as to exactly how decreasing the
> fill
> factor for an OLTP database would reduce page splits. If I were to cut
> the
> fill factor from 100% to 50% this would allow half the original space that
> I
> had before, thereby doubling the page splits.
>
> Any help would be greatly appreciated.
> Thank you


Derekman

3/22/2007 5:24:00 PM

0

I believe that this has answered my question. The fill factor is at creation
time and provides the buffer for the indexes during the insert update delete.
By changing the fill factor after creation than the best results will not be
recognized. Let me know if I an on track.

"Kevin G. Boles" wrote:

> Derekman, you have a common misconception about fill factor. Fillfactor
> specifies how much EMPTY space will be placed on each 8K index page when the
> index is built/maintained. If a page is 100% full, clearly there will be
> ZERO rows worth of space available in it and thus the FIRST row that needs
> to be inserted into that page will cause a page split. This means 2 pages
> will be created in the index tree, each with roughly 50% of the original
> page's data in them. Then the new row will be put on the appropriate page.
>
> If you have a 50% Fillfactor when you build the index, each page (at
> creation time) will only be filled up 50% full, leaving ~4K of space for new
> rows to be placed into the index page before it has to split. Obviously if
> each page is initially only 50% full, there will be TWICE as many 8K pages
> (actually some number more) required for all of the index entries. Thus
> using the index will likely take a bit more effort, but it will hopefully be
> laid down on disk contiguously (leading to well-performing sequential I/O
> where applicable) and it will not FRAGMENT by having lots of page splits for
> quite some time. This assumes the data will be inserted across the range of
> the index values (such as LastName). If the index is on an Identity PK,
> then splits will only occur at the tail of the index and a large fill factor
> (perhaps even 100%) would be more appropriate.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Derekman" <Derekman@discussions.microsoft.com> wrote in message
> news:8E853D20-CC40-45B6-BD97-62B98647B26A@microsoft.com...
> > After much research I am still stumped as to exactly how decreasing the
> > fill
> > factor for an OLTP database would reduce page splits. If I were to cut
> > the
> > fill factor from 100% to 50% this would allow half the original space that
> > I
> > had before, thereby doubling the page splits.
> >
> > Any help would be greatly appreciated.
> > Thank you
>
>
>

Russell Fields

3/22/2007 5:43:00 PM

0

Derekman,

Yes. Although you still have options to apply it sooner.

SQL 2005
ALTER INDEX... REBUILD WITH FILLFACTOR = fillfactorvalue

SQL 2000
DBCC DBREINDEX ('TableName', 'IndexName',fillfactor);

Check the Books Online for details.

RLF
"Derekman" <Derekman@discussions.microsoft.com> wrote in message
news:ADE22E1C-5D8F-4545-BA47-2D6E7C8D0CD2@microsoft.com...
>I believe that this has answered my question. The fill factor is at
>creation
> time and provides the buffer for the indexes during the insert update
> delete.
> By changing the fill factor after creation than the best results will not
> be
> recognized. Let me know if I an on track.
>
> "Kevin G. Boles" wrote:
>
>> Derekman, you have a common misconception about fill factor. Fillfactor
>> specifies how much EMPTY space will be placed on each 8K index page when
>> the
>> index is built/maintained. If a page is 100% full, clearly there will be
>> ZERO rows worth of space available in it and thus the FIRST row that
>> needs
>> to be inserted into that page will cause a page split. This means 2
>> pages
>> will be created in the index tree, each with roughly 50% of the original
>> page's data in them. Then the new row will be put on the appropriate
>> page.
>>
>> If you have a 50% Fillfactor when you build the index, each page (at
>> creation time) will only be filled up 50% full, leaving ~4K of space for
>> new
>> rows to be placed into the index page before it has to split. Obviously
>> if
>> each page is initially only 50% full, there will be TWICE as many 8K
>> pages
>> (actually some number more) required for all of the index entries. Thus
>> using the index will likely take a bit more effort, but it will hopefully
>> be
>> laid down on disk contiguously (leading to well-performing sequential I/O
>> where applicable) and it will not FRAGMENT by having lots of page splits
>> for
>> quite some time. This assumes the data will be inserted across the range
>> of
>> the index values (such as LastName). If the index is on an Identity PK,
>> then splits will only occur at the tail of the index and a large fill
>> factor
>> (perhaps even 100%) would be more appropriate.
>>
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>> "Derekman" <Derekman@discussions.microsoft.com> wrote in message
>> news:8E853D20-CC40-45B6-BD97-62B98647B26A@microsoft.com...
>> > After much research I am still stumped as to exactly how decreasing the
>> > fill
>> > factor for an OLTP database would reduce page splits. If I were to cut
>> > the
>> > fill factor from 100% to 50% this would allow half the original space
>> > that
>> > I
>> > had before, thereby doubling the page splits.
>> >
>> > Any help would be greatly appreciated.
>> > Thank you
>>
>>
>>


SKD

7/19/2011 5:05:00 PM

0

On Jul 19, 11:12 pm, Eunometic <eunome...@yahoo.com.au> wrote:
> On Jul 19, 7:15 pm, PJ 0d0novan HisVeryOwnSelf <z200...@post.com>
> wrote:
>
> > Education is worse than we thought: "..black students aged 17 do not
> > read with any greater facility than whites who are four years younger
> > and still in junior high. ... Exactly the same glaring gaps appear in
> > NAEP's tests of basic mathematics skills..." "..Given the black
> > education disaster, racial preferences in college admissions will
> > become a permanent feature, because given the status quo, blacks as a
> > group will never make it into top colleges based upon academic
> > merit...."
>
> >http://bit.ly...
>
> Since the perfection of IQ tests in the 1950s there is now 70 years of
> IQ data which shows racial variation with IQ.
>
> There are many types of IQ tests, including culturally neutral ones
> such as the Ravens Progessive Matrices and Reaction Time tests
> (patterns of light).  All show the same thing.
>
> Roughly only 15% of blacks have an IQ above 100 whereas 50% of Whites
> do.  Few whites have an IQ below 85 but 50% of Blacks do.
>
> Despite endless claims that the tests are somehow flawed, biased or
> that the researchers are biased they are not.
> They have never been discredited.
>
> The proof is that after decades of lavish special programs (for
> hispanics and blacks), of bullying and blaming of teachers that the
> achievment gap has NOT been closed.
>
> Dozens of special research programs make claims of major
> breakthroughs.  Programs usually involve developping pride or positive
> sense of achievement in the stundents etc.  They all fall flat
> eventually.
>
> It will never be closed unless whites are labotamised or there are
> more 'mullatos' (PC term biracial)
>
> Furthermore 3rd and 4th generation hispanics show no improvement.  Its
> not immigraion related.
>
> 35 years of affirmitve action quotas, no improvement of note.
>
> IQ tests basically predict abillity to learn, they correlate with
> success in business, trade and profession.  They correlated with the
> success of your marriage, your chance of having an accident and of
> commiting a crime.
>
> Whites of the same IQ as blacks are half as likely to commit a crime,
> (impulse control is another issue).
>
> The real tragedy is that increasing immigrant Hispanic population has
> access to quotas.  While Whites could have subsidised blacks
> indefinetly on affirmitive action they can't handle the influx of
> somalis, ethiopians, mexicans, hispanics and even latin americans.
>
> Australia MUST stay away from this.

What difference does IQ make? For survival of the species you need
very little, rest is all for mental wanking or in PC terms
infotainment. Chinese discovered gunpowder and antibiotics thousands
of year back. Indian knew of higher maths and advanced philosophical
concepts even longer. All this bullcrap about IQ. Real test come when
one is sick and a choice has to be made for the doctor. I'd rather
take my chances with an African, Arab, Indian, or Chinese trained
without a doubt.

John Rennie

7/19/2011 8:43:00 PM

0

On 19/07/2011 18:05, SKD wrote:
> On Jul 19, 11:12 pm, Eunometic<eunome...@yahoo.com.au> wrote:
>> On Jul 19, 7:15 pm, PJ 0d0novan HisVeryOwnSelf<z200...@post.com>
>> wrote:
>>
>>> Education is worse than we thought: "..black students aged 17 do not
>>> read with any greater facility than whites who are four years younger
>>> and still in junior high. ... Exactly the same glaring gaps appear in
>>> NAEP's tests of basic mathematics skills..." "..Given the black
>>> education disaster, racial preferences in college admissions will
>>> become a permanent feature, because given the status quo, blacks as a
>>> group will never make it into top colleges based upon academic
>>> merit...."
>>
>>> http://bit.ly...
>>
>> Since the perfection of IQ tests in the 1950s there is now 70 years of
>> IQ data which shows racial variation with IQ.
>>
>> There are many types of IQ tests, including culturally neutral ones
>> such as the Ravens Progessive Matrices and Reaction Time tests
>> (patterns of light). All show the same thing.
>>
>> Roughly only 15% of blacks have an IQ above 100 whereas 50% of Whites
>> do. Few whites have an IQ below 85 but 50% of Blacks do.
>>
>> Despite endless claims that the tests are somehow flawed, biased or
>> that the researchers are biased they are not.
>> They have never been discredited.
>>
>> The proof is that after decades of lavish special programs (for
>> hispanics and blacks), of bullying and blaming of teachers that the
>> achievment gap has NOT been closed.
>>
>> Dozens of special research programs make claims of major
>> breakthroughs. Programs usually involve developping pride or positive
>> sense of achievement in the stundents etc. They all fall flat
>> eventually.
>>
>> It will never be closed unless whites are labotamised or there are
>> more 'mullatos' (PC term biracial)
>>
>> Furthermore 3rd and 4th generation hispanics show no improvement. Its
>> not immigraion related.
>>
>> 35 years of affirmitve action quotas, no improvement of note.
>>
>> IQ tests basically predict abillity to learn, they correlate with
>> success in business, trade and profession. They correlated with the
>> success of your marriage, your chance of having an accident and of
>> commiting a crime.
>>
>> Whites of the same IQ as blacks are half as likely to commit a crime,
>> (impulse control is another issue).
>>
>> The real tragedy is that increasing immigrant Hispanic population has
>> access to quotas. While Whites could have subsidised blacks
>> indefinetly on affirmitive action they can't handle the influx of
>> somalis, ethiopians, mexicans, hispanics and even latin americans.
>>
>> Australia MUST stay away from this.
>
> What difference does IQ make? For survival of the species you need
> very little, rest is all for mental wanking or in PC terms
> infotainment. Chinese discovered gunpowder and antibiotics thousands
> of year back. Indian knew of higher maths and advanced philosophical
> concepts even longer. All this bullcrap about IQ. Real test come when
> one is sick and a choice has to be made for the doctor. I'd rather
> take my chances with an African, Arab, Indian, or Chinese trained
> without a doubt.


You'll be telling us that the Indians invented the zero next. Oh yes,
they did didn't they?

Runge 128

7/20/2011 5:28:00 AM

0

Well you didn't , that's for sure.


"John Rennie" a ?crit dans le message de groupe de discussion :
BeOdnfL9mvHacbjTnZ2dnUVZ8nudnZ2d@giganews.com...

On 19/07/2011 18:05, SKD wrote:
> On Jul 19, 11:12 pm, Eunometic<eunome...@yahoo.com.au> wrote:
>> On Jul 19, 7:15 pm, PJ 0d0novan HisVeryOwnSelf<z200...@post.com>
>> wrote:
>>
>>> Education is worse than we thought: "..black students aged 17 do not
>>> read with any greater facility than whites who are four years younger
>>> and still in junior high. ... Exactly the same glaring gaps appear in
>>> NAEP's tests of basic mathematics skills..." "..Given the black
>>> education disaster, racial preferences in college admissions will
>>> become a permanent feature, because given the status quo, blacks as a
>>> group will never make it into top colleges based upon academic
>>> merit...."
>>
>>> http://bit.ly...
>>
>> Since the perfection of IQ tests in the 1950s there is now 70 years of
>> IQ data which shows racial variation with IQ.
>>
>> There are many types of IQ tests, including culturally neutral ones
>> such as the Ravens Progessive Matrices and Reaction Time tests
>> (patterns of light). All show the same thing.
>>
>> Roughly only 15% of blacks have an IQ above 100 whereas 50% of Whites
>> do. Few whites have an IQ below 85 but 50% of Blacks do.
>>
>> Despite endless claims that the tests are somehow flawed, biased or
>> that the researchers are biased they are not.
>> They have never been discredited.
>>
>> The proof is that after decades of lavish special programs (for
>> hispanics and blacks), of bullying and blaming of teachers that the
>> achievment gap has NOT been closed.
>>
>> Dozens of special research programs make claims of major
>> breakthroughs. Programs usually involve developping pride or positive
>> sense of achievement in the stundents etc. They all fall flat
>> eventually.
>>
>> It will never be closed unless whites are labotamised or there are
>> more 'mullatos' (PC term biracial)
>>
>> Furthermore 3rd and 4th generation hispanics show no improvement. Its
>> not immigraion related.
>>
>> 35 years of affirmitve action quotas, no improvement of note.
>>
>> IQ tests basically predict abillity to learn, they correlate with
>> success in business, trade and profession. They correlated with the
>> success of your marriage, your chance of having an accident and of
>> commiting a crime.
>>
>> Whites of the same IQ as blacks are half as likely to commit a crime,
>> (impulse control is another issue).
>>
>> The real tragedy is that increasing immigrant Hispanic population has
>> access to quotas. While Whites could have subsidised blacks
>> indefinetly on affirmitive action they can't handle the influx of
>> somalis, ethiopians, mexicans, hispanics and even latin americans.
>>
>> Australia MUST stay away from this.
>
> What difference does IQ make? For survival of the species you need
> very little, rest is all for mental wanking or in PC terms
> infotainment. Chinese discovered gunpowder and antibiotics thousands
> of year back. Indian knew of higher maths and advanced philosophical
> concepts even longer. All this bullcrap about IQ. Real test come when
> one is sick and a choice has to be made for the doctor. I'd rather
> take my chances with an African, Arab, Indian, or Chinese trained
> without a doubt.


You'll be telling us that the Indians invented the zero next. Oh yes,
they did didn't they?

Bob LeChevalier

7/22/2011 12:13:00 PM

0

eunometic@yahoo.com.au wrote:
>On Jul 20, 11:27?pm, Bob LeChevalier <loj...@lojban.org> wrote:
>> Eunometic <eunome...@yahoo.com.au> wrote:
>> >Since the perfection of IQ tests in the 1950s
>>
>> There never has nor ever will be "perfection of IQ tests"
>
>IQ tests are well developed.

They are developed.

Whether they are well-developed is another question.

>An IQ test correlates very well with latter educational or training achievement.

Even if so, so what?

At best, it means that we know how to train certain kinds of people to
do certain kinds of things.

>It basically predicts ones abillity to reason and learn.

No it doesn't.

>It also correlates postively with income,

in some societies and some professions. But it also may be
irrelevant.

Einstein didn't make nearly as much money as the typical sports star
or movie star. Neither J. K Rowling nor the Queen of England are
necessarily of superior intelligence, but both are multi-billionaires.

And whoever you are, I am sure your income as compared to theirs
doesn't correlate with your IQ compared to theirs. Or maybe it does -
racist slime are completely lacking in intelligence.

At best one can say that certain people will pay more for certain
skills "highly correlated with" IQ score, while others will pay more
for skills that have no such correlation.


>negatively with the chance of having an car accident

So does age up to the point where physical infirmity interferes.
Infants driving cars have a 100% chance of having an accident. A
recent study found that gradnparents drive more safely with kids than
the parents do.

And yet if intelligence is innate, then a baby, a parent and a
grandparent have the same intelligence, and therefore should have the
same chance of an accident, if indeed there is "correlation".

>and positively with success in marriage.

I rather suspect that to be false, given that highly intelligent
people are frequently unstable, and sometimes have problems getting
along with those of significantly lesser intelligence.

But irrelevant.

>Mathematically we say the 'correlation coefficient' is high; about
>0.72 or so.

..72 isn't that high, even if all of those supposed correlations were
correctly stated.


>IQ tests will pick up a poor student in a poor school and indentify
>him as capable of accepting superior education. It will even pick up
>a high IQ child with a learning difficulty such as ADD.

It might or might not. Some learning disabilities lead to erroneous
(or even nonsensical) IQ scores, and some people with extremely high
IQs do not significantly benefit from education.

>They can also pick up a slower child and allow him to be given
>education at a slower level.

Or they might lead to someone being assumed to be incapable of
learning at a faster level, when indeed they can.

>IQ tests were widely used by the military prior to tertiary education
>because the accurately predicted the abillity of the testee to be say
>an electronics technician or pilot/navigator.
>
>IQ tests have been corrleated with herreditiy.
>
>Bottom line, IQ tests work.

Not really.

>> [remaining subhuman racists slime tripe deleted]
>
>Unfortunatly facts

You know no facts.

>do have an independant existance outside of your
>self righteous moronic politically correct agenda.
>
>You are simply too much of a fuckwit to use your reason.

And yet it is close to certainty that my IQ is higher than yours (and
probably my income, driving record and marital stability as well). So
much for your "theory".

lojbab
---
Bob LeChevalier - artificial linguist; genealogist
lojbab@lojban.org Lojban language www.lojban.org