What is the ideal Fill Factor?
Fill factor is an attribute of an index that defines the amount of free space on each page of the index.
In an insert-intensive environment, the index pages will eventually split to accommodate additional entries. To avoid or reduce the frequency of page splits, the index should be rebuilt using an appropriate fill factor.
So what is the ideal Fill factor?
The article "Tips for Rebuilding Indexes" has this suggestion:
It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:
* Low Update Tables (100-1 read to write ratio): 100% fill factor
* High Update Tables (where writes exceed reads): 50%-70% fill factor
* Everything In-Between: 80%-90% fill factor.
You may have to experiment to find the optimum fill factor for your particular application.
Performance Monitor counters (Performance object/Counter) to watch for determining the ideal Fill factor are:
In an insert-intensive environment, the index pages will eventually split to accommodate additional entries. To avoid or reduce the frequency of page splits, the index should be rebuilt using an appropriate fill factor.
So what is the ideal Fill factor?
The article "Tips for Rebuilding Indexes" has this suggestion:
It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:
* Low Update Tables (100-1 read to write ratio): 100% fill factor
* High Update Tables (where writes exceed reads): 50%-70% fill factor
* Everything In-Between: 80%-90% fill factor.
You may have to experiment to find the optimum fill factor for your particular application.
Performance Monitor counters (Performance object/Counter) to watch for determining the ideal Fill factor are:
- PhysicalDisk - % Disk Read Time
- PhysicalDisk - % Disk Write Time
- SQL Server:Access Methods - Pages Splits/Sec
Comments
Post a Comment