Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
Chapter 6. Configuring Microsoft SQL Ser... > Setting the Index Fill
Setting the Index Fill
The default index fill determines how much space SQL Server should reserve when it creates a new index using existing data. Setting the fill factor involves a tradeoff—if you set the fill factor too high, SQL Server will slow down when you add data to a table. However, if you set the fill factor too low, read performance can be affected by an amount inversely proportional to the fill factor. For example, a fill factor of 25 percent can degrade read performance by a factor of 4 (or 4 times normal), but the setting makes it possible to perform large updates faster initially. Ideally, you should balance the need to make updates quickly with the need to have good read performance, and then select a fill factor that makes sense for your situation.
Best Practices
The fill factor is used only when an index is created; it is not maintained afterward. This allows you to add, delete, or update data in a table without worrying about maintaining a specific fill factor.
Therefore, the empty space in the data pages can fill up if you make extensive additions or modifications to the data. To redistribute the data, re-create the index and specify a fill factor when you do so. Indexes are discussed more completely in Chapter 10, “Manipulating Schemas, Tables, Indexes, and Views.”
