Free Space vs. Fill Factor in Rebuild Index Task

RobertKaucher

I was put under the impression that Free Space was the oposite of Fill Factor. MEaning if Iwanted to have an index rebuild with 80% fill factor I would fill out the field "Change free space per page percentage to" to be set to 20. Yet the T-SQL command this option generates set FILLFACTOR to 80. So I am a bit confused. Any help?

USE [MyCustomers]
GO
ALTER INDEX [PK__Fr_Customers__7C8480AE] ON [dbo].[Fr_Customers] REBUILD WITH ( FILLFACTOR = 80, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )
GO
USE [MyCustomers]
GO
ALTER INDEX [PK__UK_Customers__7E6CC920] ON [dbo].[UK_Customers] REBUILD WITH ( FILLFACTOR = 80, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )

RobertKaucher

Just to be clear... When I set "free space" in the GUI to 80 the SQL code sets FILLFACTOR to 80. This is not what I would expect....

brad-

RobertKaucher wrote:

Just to be clear... When I set "free space" in the GUI to 80 the SQL code sets FILLFACTOR to 80. This is not what I would expect....

The wording is aweful, but it seems the words are interchangeable.

http://www.ucertify.com/article/what-is-fillfactor.html

FILLFACTOR specifies a percentage that indicates how much free space will be in the leaf level of each index page. When a clustered index is created on a table, SQL Server 2005 does not stuff data wherever it finds space, but it physically rearranges data in order. SQL Server 2005 cannot rearrange data without page split if it does not find free space at an index page. To help SQL Server 2005 accomplish this, there is a need to leave a little space at the leaf level of each page on a clustered index. This free space is called FILLFACTOR.

http://msdn.microsoft.com/en-us/library/aa933139(SQL.80).aspx

It has to do with page splitting when the index begins to fill.

When a new row is added to a full index page, SQL Server moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. Page splitting can impair performance and fragment the storage of the data in a table. For more information, see Table and Index Architecture.

When creating an index, you can specify a fill factor to leave extra gaps (READ FREE SPACE, MY ASSUMPTION) and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table's data and reduce the potential for page splits.

RobertKaucher

The way I have had this explained to me is that free space is the opposite of fill factor. Which makes sense. A fillfactor of 90 would be the same as 10% free space, a fillfactor of 80 would be the same as 20% freespace. This explanation was given by some very competent DBAs and corresponds to MS's documntation and makes sense based on the MSDN link you gave. However, the script that SQL server generates seems to contradict this.

The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space.

"A lower value leaves more empty space would" mean that a lower fillfactor should correspond to a higher value in free space, right? A fillfactor of 20 should give me 80% free space. et that is not what is suggested by the script.

GeoChed

A Microsoft SQL 2k8 R2 Management Studio gotcha with Clustered Index Fill Factors that affects SQL Performance.

A Fill Factor is the the amount of fill each 8k page in an Index gets , opposite to Free Space. High Fill Factor means more Index fragmentation is possible. Low = low Fragmentation. High Fill Factor means faster reads and slower write, but the big issue is performance with Clestered indexs usually used as the Primary Key the exssive page split cause High Disk I/O. These Indexes are also used by non Clustered indexes and can spread your data across a large file, if you choose a low Fill Factor. Below is a real life example of how Microsoft has confused wording on the issue as it changed from version 2k to 2k5/2k8.



A Problem:
Let’s say a Vendor or Customized Index job sets all your server’s DB table pages Clustered index Fill Factor to a low figure of 50%. The DB then grows to a large size. You now have a potential time bomb Clustered Index Split I/O disk performance degradation issue, no matter how good your SAN I/O is.
User scenarios:
1: You don’t notice it and take no action.
2: You do notice it and commence with a Maintenance Plan Rebuild Index Task option to bring it back to a Default 100% Fill Factor. You think it’s OK, then try to shrink the DB, which doesn’t work.
3: Same as point 2 you notice it, you run the Default reindex job and think all is well, but you don’t worry about shrinking the DB, as it may take a little time.

The above three scenarios produce the same result, a potential time bomb in the Clustered Indexes due to the fact that the Default Reindex job wording is deceptive, the system default being 100% Fill Factor.
Below is a screen shot of the old sql 2000 Rebuild index job and it does not even use the Default term, but says the original amount of Free Space.



Solution 1:
Microsoft has to either train all SQL DBA upfront to diagnose the above Fill Factor and to be aware of what this wording means. As Default may be easily misunderstood as the SQL System Fill Factor default.
Solution 2:
The user chooses to go down an incorrect path of diagnosis, and things go downhill, unless you quickly address issue with Microsoft SQL Support.
Solution 3:
Fix the wording Bug under the Maintenance Plan Rebuild Index Task to make clear the difference between;
1. A system wide Default Fill Factor,
2. Previous Fill Factor customization
3. New Customization
See below mock-up of what it would look like.

Rgs
George Chedra