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


 

Forums >

microsoft.public.sqlserver.programming

SQL Server Fill Factor question

ClinkeA

3/21/2007 9:45:00 PM

Assuming my database contains two tables. Each table with one clustered index
and no non-clustered indexes.

Given that these tables may share a data page within an extent. Is there any
benefit to using different fill factors for each of these tables when
creating the table indexes?

I understand this is possible with sql server but may not be maintained
after creation.

Would it not be best pratice to make sure that all indexes within a database
use the same fill factor, as to avoid unneeded severe external fragmentation?

If I've missed something please let me know.




9 Answers

Hugo Kornelis

3/21/2007 11:37:00 PM

0

On Wed, 21 Mar 2007 14:45:23 -0700, ClinkeA wrote:

>Assuming my database contains two tables. Each table with one clustered index
>and no non-clustered indexes.
>
>Given that these tables may share a data page within an extent. Is there any
>benefit to using different fill factors for each of these tables when
>creating the table indexes?
>
>I understand this is possible with sql server but may not be maintained
>after creation.
>
>Would it not be best pratice to make sure that all indexes within a database
>use the same fill factor, as to avoid unneeded severe external fragmentation?
>
>If I've missed something please let me know.

Hi ClinkeA,

The fill factor determines the amount of empty space in the pages. This
is useful when data is added in an order that doesn't match that of the
clustered index, or when clustered index keys are updated. The empty
space can be used to accomodate new or moved rows without having to
split the page. Of course, the empty space will eventually fill up, so
you'll want to reorganize the index periodically.

For a table that is not inserted into (or only in order of increasing
clustered key) and that has no changes to the clustered index key, using
the fill factor would be a waste - you end up storing less rows per
page, thus increasing the number of pages (and the I/O!) used for the
table, without any gain.

You can decide this for each table individually. If one of the tables is
heavily changed and the other barely, use a larger fill factor for the
former table and a smaller or none at all for the latter.

Bottom line for questions such as these is always: testing, on your own
hardware and with your own data!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Dan Guzman

3/22/2007 3:13:00 AM

0

> Given that these tables may share a data page within an extent. Is there
> any
> benefit to using different fill factors for each of these tables when
> creating the table indexes?

I believe Hugo provided a good explanation on the appropriate use of fill
factor but I want to make sure you are clear on this point.

Tables do not share pages . The first extent of a table is mixed, meaning
that the extent can be shared with other tables. However, each page belongs
to only one table.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ClinkeA" <ClinkeA@discussions.microsoft.com> wrote in message
news:4D404D59-D162-4E03-BE32-99DB8BCFD9A6@microsoft.com...
> Assuming my database contains two tables. Each table with one clustered
> index
> and no non-clustered indexes.
>
> Given that these tables may share a data page within an extent. Is there
> any
> benefit to using different fill factors for each of these tables when
> creating the table indexes?
>
> I understand this is possible with sql server but may not be maintained
> after creation.
>
> Would it not be best pratice to make sure that all indexes within a
> database
> use the same fill factor, as to avoid unneeded severe external
> fragmentation?
>
> If I've missed something please let me know.
>
>
>
>

Uri Dimant

3/22/2007 7:50:00 AM

0

Hugo
> The fill factor determines the amount of empty space in the pages.

Actually it is a little bit confused, the fill factor determines how full
the pages are.



"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:k4g3031vv2bs9u8bicvb69timeqnu68ac5@4ax.com...
> On Wed, 21 Mar 2007 14:45:23 -0700, ClinkeA wrote:
>
>>Assuming my database contains two tables. Each table with one clustered
>>index
>>and no non-clustered indexes.
>>
>>Given that these tables may share a data page within an extent. Is there
>>any
>>benefit to using different fill factors for each of these tables when
>>creating the table indexes?
>>
>>I understand this is possible with sql server but may not be maintained
>>after creation.
>>
>>Would it not be best pratice to make sure that all indexes within a
>>database
>>use the same fill factor, as to avoid unneeded severe external
>>fragmentation?
>>
>>If I've missed something please let me know.
>
> Hi ClinkeA,
>
> The fill factor determines the amount of empty space in the pages. This
> is useful when data is added in an order that doesn't match that of the
> clustered index, or when clustered index keys are updated. The empty
> space can be used to accomodate new or moved rows without having to
> split the page. Of course, the empty space will eventually fill up, so
> you'll want to reorganize the index periodically.
>
> For a table that is not inserted into (or only in order of increasing
> clustered key) and that has no changes to the clustered index key, using
> the fill factor would be a waste - you end up storing less rows per
> page, thus increasing the number of pages (and the I/O!) used for the
> table, without any gain.
>
> You can decide this for each table individually. If one of the tables is
> heavily changed and the other barely, use a larger fill factor for the
> former table and a smaller or none at all for the latter.
>
> Bottom line for questions such as these is always: testing, on your own
> hardware and with your own data!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...


ClinkeA

3/22/2007 9:21:00 AM

0

Thanks for your comments, I will be testing this out to see how changing fill
factor can affect things for myself.

My point however was if there is no way of maintaining the specified fill
factor after creation, why specify it in the first place? Especially one
that is low. On page 117 of Ian McLean's book 'Optimizing and Maintaining a
Database Administration Solution Using SQL 2005' from Microsoft Press he
clearly dispells the myth that the fill factor is maintained.

"Hugo Kornelis" wrote:

> On Wed, 21 Mar 2007 14:45:23 -0700, ClinkeA wrote:
>
> >Assuming my database contains two tables. Each table with one clustered index
> >and no non-clustered indexes.
> >
> >Given that these tables may share a data page within an extent. Is there any
> >benefit to using different fill factors for each of these tables when
> >creating the table indexes?
> >
> >I understand this is possible with sql server but may not be maintained
> >after creation.
> >
> >Would it not be best pratice to make sure that all indexes within a database
> >use the same fill factor, as to avoid unneeded severe external fragmentation?
> >
> >If I've missed something please let me know.
>
> Hi ClinkeA,
>
> The fill factor determines the amount of empty space in the pages. This
> is useful when data is added in an order that doesn't match that of the
> clustered index, or when clustered index keys are updated. The empty
> space can be used to accomodate new or moved rows without having to
> split the page. Of course, the empty space will eventually fill up, so
> you'll want to reorganize the index periodically.
>
> For a table that is not inserted into (or only in order of increasing
> clustered key) and that has no changes to the clustered index key, using
> the fill factor would be a waste - you end up storing less rows per
> page, thus increasing the number of pages (and the I/O!) used for the
> table, without any gain.
>
> You can decide this for each table individually. If one of the tables is
> heavily changed and the other barely, use a larger fill factor for the
> former table and a smaller or none at all for the latter.
>
> Bottom line for questions such as these is always: testing, on your own
> hardware and with your own data!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...
>

Someone Else

3/22/2007 9:57:00 AM

0

Of course it doesn't maintain the fill factor! If you never inserted
anything into the space you've left free what would be the point in
leaving it empty in the first place? The idea that SQL server could
persist a fill factor against an index defeats the very point of it.
Fill factor is maintained by performing a dbcc DBREINDEX, in which you
can either specify a new fill factor or if not specified it will use
the last provided.

In checking up on DBREINDEX I noticed that we now have to use ALTER
INDEX, this one had slipped me by, but the BOL description looks
pretty obvious so I'm sure you could do something similar with this
syntax.


On 22 Mar, 09:21, ClinkeA <Clin...@discussions.microsoft.com> wrote:
> Thanks for your comments, I will be testing this out to see how changing fill
> factor can affect things for myself.
>
> My point however was if there is no way of maintaining the specified fill
> factor after creation, why specify it in the first place? Especially one
> that is low. On page 117 of Ian McLean's book 'Optimizing and Maintaining a
> Database Administration Solution Using SQL 2005' from Microsoft Press he
> clearly dispells the myth that the fill factor is maintained.
>
>
>
> "Hugo Kornelis" wrote:
> > On Wed, 21 Mar 2007 14:45:23 -0700, ClinkeA wrote:
>
> > >Assuming my database contains two tables. Each table with one clustered index
> > >and no non-clustered indexes.
>
> > >Given that these tables may share a data page within an extent. Is there any
> > >benefit to using different fill factors for each of these tables when
> > >creating the table indexes?
>
> > >I understand this is possible with sql server but may not be maintained
> > >after creation.
>
> > >Would it not be best pratice to make sure that all indexes within a database
> > >use the same fill factor, as to avoid unneeded severe external fragmentation?
>
> > >If I've missed something please let me know.
>
> > Hi ClinkeA,
>
> > The fill factor determines the amount of empty space in the pages. This
> > is useful when data is added in an order that doesn't match that of the
> > clustered index, or when clustered index keys are updated. The empty
> > space can be used to accomodate new or moved rows without having to
> > split the page. Of course, the empty space will eventually fill up, so
> > you'll want to reorganize the index periodically.
>
> > For a table that is not inserted into (or only in order of increasing
> > clustered key) and that has no changes to the clustered index key, using
> > the fill factor would be a waste - you end up storing less rows per
> > page, thus increasing the number of pages (and the I/O!) used for the
> > table, without any gain.
>
> > You can decide this for each table individually. If one of the tables is
> > heavily changed and the other barely, use a larger fill factor for the
> > former table and a smaller or none at all for the latter.
>
> > Bottom line for questions such as these is always: testing, on your own
> > hardware and with your own data!
>
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo... Hide quoted text -
>
> - Show quoted text -

Roy Harvey

3/22/2007 12:29:00 PM

0

On Thu, 22 Mar 2007 09:50:11 +0200, "Uri Dimant" <urid@iscar.co.il>
wrote:

>> The fill factor determines the amount of empty space in the pages.
>
>Actually it is a little bit confused, the fill factor determines how full
>the pages are.

By specifying how full the pages will be, fill factor determines the
amount of empty space in the pages.

Roy Harvey
Beacon Falls, CT

Uri Dimant

3/22/2007 1:27:00 PM

0

Ok, english is not my native language, so my intention was
that 'fillfactor specifies how full each page should be' .50 means 50% full





"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:eit4031516aqp99rlk7r2of1eo26jugpi3@4ax.com...
> On Thu, 22 Mar 2007 09:50:11 +0200, "Uri Dimant" <urid@iscar.co.il>
> wrote:
>
>>> The fill factor determines the amount of empty space in the pages.
>>
>>Actually it is a little bit confused, the fill factor determines how full
>>the pages are.
>
> By specifying how full the pages will be, fill factor determines the
> amount of empty space in the pages.
>
> Roy Harvey
> Beacon Falls, CT


Hugo Kornelis

3/23/2007 11:32:00 PM

0

On Thu, 22 Mar 2007 09:50:11 +0200, Uri Dimant wrote:

>Hugo
>> The fill factor determines the amount of empty space in the pages.
>
>Actually it is a little bit confused, the fill factor determines how full
>the pages are.

Hi Uri,

You're right. Thanks for the correction.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Hugo Kornelis

3/23/2007 11:37:00 PM

0

On Thu, 22 Mar 2007 02:21:18 -0700, ClinkeA wrote:

>Thanks for your comments, I will be testing this out to see how changing fill
>factor can affect things for myself.
>
>My point however was if there is no way of maintaining the specified fill
>factor after creation, why specify it in the first place?

Hi ClinkeA,

Specifying it when creating a new table is pointless.

Specifying it when creating a new index for an existing table, or when
rebuilding an index, is useful. But only if data will be inserted in the
table that won't be added to the "begin" or the "end" or the index, or
when varying length data will be updated to get a longer length.

The reason that this is useful is that more pages will be allocated to
the index, wiith some empty space on each page. That way, the first
inserts and updates won't cause page splits. After many inserts and
updates, the page splits will start to happpen - until yoou rebuild the
index once more, to "repair" the fill factor.

Just think of it as buying a size 108 shirt for a kid that is actually
size 102 - it'll be baggy at first, but at least it'll be one or two
more month before the kid's grown too large to wear it.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...