What should be the Best Value for Fill Factor in SQL Server
Lot of SQL Server resources ask what is the best value for fill factor in SQL Server and should we change its value from default to some other value? On high level, generally we should not change fill factor value from its default value but you should analyze the history and based on its fragmentation frequency you can take the call. Here, I will show you how to check fill factor in SQL Server and also its best value that you should keep in your database.
A 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, If we specify a fill factor value of 95 means that 5% of each leaf-level page will be left empty, providing space for future 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.
Fill factor values 0 and 100 are the same in all respects. The fill-factor option is provided for fine-tuning index data storage and performance. Setting inappropriate fillfactor value will increase the page split occurrence that is not good from SQL Server performance point of view. A correctly chosen value can reduce potential page splits by providing enough space for index expansion.
Although, there is no defined value that we can say is perfect value for fill factor. You can set it to somewhere around 80% and monitor fragmentation over time. Then, you can tweak its value up or down depending on how fragmented the indexes get.
There is a myth that Database engine considers fill factor value during every INSERT or UPDATE transaction. But it’s not true. Database engine does not consider fill factor value during normal DML (insert / update / delete) operations. This value comes into picture only during index creation or rebuild operation. So whenever you insert or load data in your database data pages will be filled 100% irrespective of your fill-factor value.
How to specify a fill factor in an index by using GUI in SSMS
- Connect to SQL Server Instance using SSMS.
- Expand Database folder followed by the database that contains the table on which you want to specify an index’s fill factor.
- Expand the Tables folder.
- Click the plus sign to expand the table on which you want to specify an index fill-factor.
- Click the plus sign to expand the Indexes
- Right-click the index with the fill factor that you want to specify and select Properties.
- Under Select a page, select Options.
- In the Fill factor row, enter the fill factor that you want.
- Click OK.
You can check fill factor value in below screenshot as well. Fill factor value is showing zero because of its default value. Change it for this particular index if you want.
How to specify a fill factor in an index using Transact-SQL
We can determine fill factor either during Index REBUILD operation or during Index creation. Below is the T-SQL codes that we can use to specify fill factor for an index.
To specify a fill factor in an existing index
Run below T-SQL command to determine fill factor for an existing index during Rebuild operation.
-- Rebuilds the IX_Salary_EMPID index with a fill-factor of 95 on the dbo.Salary table. USE Techyaz GO ALTER INDEX IX_Salary_EMPID ON dbo.Salary REBUILD WITH (FILLFACTOR = 95); GO
To specify a fill-factor during create index
Run below command to specify a fill-factor during index creation.
--Drops and re-creates the IX_Salary_EMPID index on the dbo.Salary table with a fill-factor of 95. USE Techyaz; GO CREATE INDEX IX_Salary_EMPID ON dbo.Salary (EMPID) WITH (DROP_EXISTING = ON, FILLFACTOR = 95); GO
- Understanding SQL Server Indexes
- What is difference between Rebuild and Reorganize Indexes?
- Table Row Count is showing incorrect in sys.partitions
- How to get Total Row Count of all tables of a database
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018