SQL Server Fill Factor question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ClinkeA - 21 Mar 2007 22:45 GMT

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.

Reply to this Message

Hugo Kornelis - 22 Mar 2007 00:36 GMT

>Assuming my database contains two tables. Each table with one clustered index
>and no non-clustered indexes.
[quoted text clipped - 10 lines]
>
>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!

Signature

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

Reply to this Message

Uri Dimant - 22 Mar 2007 08:50 GMT

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.

>>Assuming my database contains two tables. Each table with one clustered
>>index
[quoted text clipped - 36 lines]
> Bottom line for questions such as these is always: testing, on your own
> hardware and with your own data!

Reply to this Message

Roy Harvey - 22 Mar 2007 13:29 GMT

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

Reply to this Message

Uri Dimant - 22 Mar 2007 14:27 GMT

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

>>> The fill factor determines the amount of empty space in the pages.
>>
[quoted text clipped - 6 lines]
> Roy Harvey
> Beacon Falls, CT

Reply to this Message

Hugo Kornelis - 24 Mar 2007 00:31 GMT

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

Signature

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

Reply to this Message

ClinkeA - 22 Mar 2007 10:21 GMT

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.

> >Assuming my database contains two tables. Each table with one clustered index
> >and no non-clustered indexes.
[quoted text clipped - 32 lines]
> Bottom line for questions such as these is always: testing, on your own
> hardware and with your own data!

Reply to this Message

Someone Else - 22 Mar 2007 10:56 GMT

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.

> Thanks for your comments, I will be testing this out to see how changing fill
> factor can affect things for myself.
[quoted text clipped - 47 lines]
>
> - Show quoted text -

Reply to this Message

Hugo Kornelis - 24 Mar 2007 00:36 GMT

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

Signature

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

Reply to this Message

Ajay Choudhary - 04 Jul 2008 12:54 GMT

i am agree with the above details but i am getting little bit confused..

can u please tell me ...if i assigned fill factor 70% then what abt 30%

My confusion

1. 30% will be used for insertion and updation process only. is it never holds the data. if new data stored in 30% area than when page is going to split.

2. is it true each table can contain only one data page ?

if yes ..than what abt data page size ?

Reply to this Message

Tibor Karaszi - 04 Jul 2008 13:28 GMT

Fillfactor is only applied when the index is built (or re-built). After that, the pages will fill up
as they are destination pages for new rows. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for a good
backgrounder.

Page size is 8 kByte. You can definitely have more than one page per table and index. Check out the
section about "Physical database architecture" in Books Online for details.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>i am agree with the above details but i am getting little bit confused..
>
[quoted text clipped - 8 lines]
>
> if yes ..than what abt data page size ?

Reply to this Message

Dan Guzman - 04 Jul 2008 14:06 GMT

> can u please tell me ...if i assigned fill factor 70% then what abt 30%
>
[quoted text clipped - 3 lines]
> holds the data. if new data stored in 30% area than when page is going to
> split.

The FILLFACTOR applies only when the index is created. The 30% free space
can be used for new/expanded data and will avoid page splits until the page
becomes full. Note that the free space will increase storage requirements
and can decrease read performance so it should be specified judiciously.
See the Books Online for details.

> 2. is it true each table can contain only one data page ?

A table can contain zero or more pages and is limited only by available
storage. Table storage is allocated in units of 8 page (64K) extents.
The first page of a table is created in a mixed extent (shared with other
tables) and subsequent pages are allocated in dedicated extents (not
shared). This allocated technique reduces space requirements of small
tables while efficiently handling larger tables as well.

> if yes ..than what abt data page size ?

The page size is 8K (8192 bytes) in SQL Server.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>i am agree with the above details but i am getting little bit confused..
>
[quoted text clipped - 9 lines]
>
> if yes ..than what abt data page size ?

Reply to this Message

Dan Guzman - 22 Mar 2007 04:12 GMT

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

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Assuming my database contains two tables. Each table with one clustered
> index
[quoted text clipped - 14 lines]
>
> If I've missed something please let me know.

Reply to this Message

Ajay Choudhary - 04 Jul 2008 12:57 GMT

Reply to this Message