Following basic rules can be applied:
Low Update Tables (100-1 read to write ratio): 100% fillfactor
High Update Tables (where writes exceed reads): 50%-70% fillfactor
Everything In-Between: 80%-90% fillfactor.
It is very important to do some experiment with your particular application to find the optimum fillfactor. While page splits will be reduced with a low fillfactor, it also increases the number of pages that have to be read by SQL Server during queries, which reduces performance. 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 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).
Understanding SQL Server Architecture really helps in tuning the SQL Server queries.
I would recommend using DBCC commands, profiler and DMVs for finding out the queries bottlenecks.
Last edited: 21-Sep-10 04:02 PM
Last edited: 21-Sep-10 04:03 PM