FREE SPACE and Fill Factor - confusion

[katbear]

Hi,

I realize I am confused about this. I have a table, and ran a DBCC Showcontig on it.

Is see that there is about 836 bytes free per page (7346 bytes are used), and the avg. page density is 89.67%. Avg row size is 400 bytes, so that means we can fit about 18 rows per page (7346/400).

If I were to update all the rows on this page by adding an additional 100 bytes per row, that would mean that all the additional data cannot be contained on the existing page, correct? Some fragmentation would result, by the addition of extra page(s), yes?

But how does FILL FACTOR fit into all of this? I thought fill factor only applies to indexes... so if I were to increase the fill factor for the indexes on this table, how does this affect FREE SPACE on the page?

There is no clusted index on this table.

Thanks

 

[ptheriault]

The higher your Fill factor is the less free space you will have on a page. For example If my fill factor for an index is 80 then 20% of the page will be free for new data. Finding the correct fill factor is trial and error. For tables with a lot of inserts you want a fill factor around 80-85 for tables that don't have a lot of changes set the fill factor to above 90 or 0 which is the same as 100%. The goal here is to reduce page splits which can cause performance degridation.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

To the best of my knowledge the fillfactor of the table will only be effected by the fillfactor of the clustered index (and then only some times).

Changing the fill factor of non-clustered indexes will do nothing to the fill factor of the table.

If anyone has any followup it would be most helpful.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

Sorry I forgot to answer this question.

>>Some fragmentation would result, by the addition of extra page(s), yes?

Yes. When you have page spilts you get fragmentation. This is why you should run ALTER INDEX commands to clean the indexes on some kind of maintance schedule. I run mine weekly.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

To add to what Denny said.
For ALTER INDEX to work you must have a clustered index.

BOL doesn't say anything about having to have a clustered index to specify a fill factor for non-clustered indexes. That's good point Denny. I'm not sure if you need it.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Q. So, the fill factor *only* applies to the clustered index?

Q. Are data and indexes stored on the same 8K page? (yes I know clustered index = data rows, but what about non-clustered indexes?)

I know that free space in page is determined by the fill factor, but I guess I never really thought about it too deeply, because the question is, the fill factor of what??

The clustered index only?

So the conclusion would be, no clustered index = no ability to control the page fill factor, yes?

 

[mrdenny]

Q. So, the fill factor *only* applies to the clustered index?

No, each index can have it's own fill factor.

Q. Are data and indexes stored on the same 8K page? (yes I know clustered index = data rows, but what about non-clustered indexes?)

No they would be stored in different pages as tables and indexes are different objects and can be in different file groups (and should be on high end systems).

From what I understand of the Clustered Index and what I've seen (which isn't much at this deep level) without the clustered index the table will default to the database default setting. If you do have a clustered index the table uses the same fill factor of the clustered index.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

From what I understand of the Clustered Index and what I've seen (which isn't much at this deep level) without the clustered index the table will default to the database default setting. If you do have a clustered index the table uses the same fill factor of the clustered index.

Yes, thanks this is the explanation I was looking for.