May 3, 2011 at 2:01 pm
What is fill factor ? Can anyone explain it with one example?
May 3, 2011 at 2:09 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 15, 2011 at 6:15 pm
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
Change is inevitable... Change for the better is not.
May 16, 2011 at 9:24 am
May 16, 2011 at 10:38 am
Got It. Thanks a lot Guys...!!
May 17, 2011 at 6:33 pm
You bet. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2011 at 7:57 am
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.
May 30, 2011 at 8:02 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply