Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What is Fill Factor in SQL servers? Expand / Collapse
Author
Message
Posted Tuesday, May 3, 2011 2:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
What is fill factor ? Can anyone explain it with one example?
Post #1102684
Posted Tuesday, May 3, 2011 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 5,439, Visits: 7,608
Fill factor only makes sense if you understand how SQL Server stores records. It stores in an object called a page, which when you're done with header information is roughly 8000 bytes. If you have rows that are 50 bytes each, you can put 160 of these rows on a page. That would be 100% fill.

By dictating fill factor, you're leaving yourself a little space for rows to be inserted into the middle of these pages, since they must be ordered by the clustered index. So if you fill this theoretical page with all of your entries 1-160, there's no room for any new records in between. Now, if you have a new record with (as a bad example but good for this discussion) a record numbered 100.5, it needs to go between records 100 and 101. This causes what's known as a page split, which moves a portion of the records on this page to a new one. This is an intensive operation, causes fragmentation, and all sorts of annoyances.

However, if I used FILL FACTOR to put it to 90%, that puts (160 rows * .9) only 144 rows on this page. A second page is prebuilt in the proper order for 145 - 160. When I want to insert 100.5 into this page, it merely needs to shift things around a little.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1102692
Posted Sunday, May 15, 2011 6:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 35,554, Visits: 32,151
jitendra.padhiyar (5/3/2011)
What is fill factor ? Can anyone explain it with one example?


Craig's explanation is a good one. For more detail, I recommend looking up "page splits" in Books Online (the help system that comes with SQL Server).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1109053
Posted Monday, May 16, 2011 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:21 PM
Points: 31,282, Visits: 15,741
http://msdn.microsoft.com/en-us/library/aa933139%28SQL.80%29.aspx






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1109476
Posted Monday, May 16, 2011 10:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
Got It. Thanks a lot Guys...!!
Post #1109591
Posted Tuesday, May 17, 2011 6:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 35,554, Visits: 32,151
You bet. Thanks for the feedback.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1110673
Posted Monday, May 30, 2011 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 2,301, Visits: 1,432
The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Note
Fill-factor values 0 and 100 are the same in all respects.


You can use the CREATE INDEX or ALTER INDEX statements to set the fill-factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes.
Page Splits
A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can be rebuilt by using a new or existing fill-factor value to redistribute the data. For more information, see Reorganizing and Rebuilding Indexes.

Although a low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

Adding Data to the End of the Table

A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.

Post #1117012
Posted Monday, May 30, 2011 8:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
SUPER SQL STAR (5/30/2011)
The fill-factor option is provided for fine-tuning index data storage and performance...


If you are going to quote from Books Online, say so and provide the reference. http://msdn.microsoft.com/en-us/library/ms177459.aspx Don't imply that it's your own work.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1117015
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse