The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created.A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table.A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.
If you create a clustered index that has a fillfactor of 100%, every time a record is inserted, deleted or even modified, page splits can occur because there is likely no room on the existing index data page to write the change. Page splits increase IO and can dramatically degrade SQL Server performance. Specifying the fillfactor percentage When creating indexes in SQL Server, by default, if you do not specify an index fillfactor, the fillfactor will be 0 (effectively the same as 100%). You can specify an indexes fillfactor percentage in a number of different ways. Some of these methods include: The Create Index statement, DBCC DBReindex and database maintenance plans. CREATE CLUSTERED INDEX [ci_Product_ProductId] ON [dbo].[Product]([ProductId]) WITH FILLFACTOR=80 ON [PRIMARY]
Rules of Thumb
So what should your fillfactors be set to? That is a difficult question to answer. I believe that performance should be tightly monitored in a production environment and indexes should be fine tuned as performance shows that changes are necessary. The following numbers are recommended as a general starting point:
If your database is read only (low number of updates), use a fillfactor of 100%.
If your database is write intensive (writes greatly exceed reads), use a fill factor somewhere between 50% and 70%.
If your database is both read and write intensive, start with a fillfactor of 80% to 90%. As you can see, these settings depend on the use of the table.
If a specific table is only used for lookups, then its clustered index can have a fillfactor of 100% even in an update intensive environment. Furthermore, for indexes that consume fewer than 100 pages, the fillfactor settings are likely to show negligible results.
Index Maintenance
Finally, index fillfactors are not automatically maintained by SQL Server. This means that when you create the index, the fillfactor setting is honored, but as data changes in the database, the pages will eventually fill up. I recommend that you have a maintenance plan that reindexes on a scheduled basis (nightly or weekly for example, depending on your needs).
