Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
phrankbooth
Posting Yak Master
162 Posts |
 Posted - 2007-03-21 : 13:34:45
|
| Are there any rules of thumb to apply when entering the Index Fill Factor?I just want to make sure I'm enter a optimal value.Using MS SQL 2000.Thanks!--PhB |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-21 : 15:32:23
|
| As a rule of thumb, follow these guidelines: Low Update Tables (100-1 read to write ratio): 100% fillfactorHigh Update Tables (where writes exceed reads): 50%-70% fillfactorEverything In-Between: 80%-90% fillfactor.You may have to experiment to find the optimum fillfactor for your particular application. Don't assume that a low fillfactor is always better than a high fillfactor. While page splits will be reduced with a low fillfactor, it also increase the number of pages that have to be read by SQL Server during queries, which reduces performance. And not only is I/O overhead increased with a too low of fillfactor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page (including empty space) is moved to the buffer. So the lower the fillfactor, the more pages that have to be moved into SQL Serve's buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance.If you don't specify a fillfactor, the default fillfactor is 0, which means the same as a 100% fillfactor, (the leaf pages of the index are filled 100%, but there is some room left on intermediate index pages).Taken from here: http://www.sql-server-performance.com/sql_server_performance_audit7.asp |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-21 : 17:42:00
|
| "Everything In-Between: 80%-90% fillfactor."I would add that anything with an ascending PK Key (such as IDENTITY) should be 100% fill.Avoid using GUIDs - unless they are the SQL 2005 "sequential number" typeKristen |
|
|
|
|
|
|
|
|
Index Fill Factor % Rules of Thumb??