SQL Server Adjustment Factor

SQL Server table indexes have the sparse feature. The larger the table is and the more frequently accessed it is, the more appropriate adjustment factor is needed.

The fill factor option of SQL Server table indexes is the most common method for tuning indexes. The index fill factor is a percentage used to tell SQL Server how much index data can be filled in each leaf index page, and how much space should be reserved as the growth space. If the columns of the base table are modified, the columns are added, or data is added to the table, expansion occurs. As index fragmentation increases over time, or index performance is poor, this is a sign that you may need to adjust the index fill factor of the most commonly used table. The problem is how to make adjustments.

First, you must avoid this type of situation. manually set an instance-level fill factor, that is, the fill factor will affect all tables in the given SQL server instance. The reason is simple: Each SQL Server table has its own operation features and needs. The fill factor is set to 100% by default. By default, it can be adjusted over time, just like adjusting database performance.

The second thing to remember: it is possible that many instances do not need to adjust the fill factor. You don't have to worry about indexes with only a few pages (using the DBCC showcontig command). These indexes will be cached anyway. First, check the maximum number of pages and low scanning density of the index (you can see it using the DBCC showcontig command ). A index with only 12 pages and 50% density produces much less problems than a index with 7000 pages and the same density.

Third, you need to determine how big the adjustment is and how it should be adjusted, because no magic number can satisfy all the needs. If the fill factor is set too high, it will waste a lot of space and be split into a single page. The fill factor setting is too low. Increasing the database size will lead to a large number of input/output operations, thus affecting performance.

Randal recommends that you use 70% as the default value for about a week at the beginning, and then adjust the value up or down based on the change results. In this regard, the SQL server performance counter (SYS. dm_ OS _performance_counters) page splits/sec is a great help. The higher the counter value, the more you need to optimize the SQL Server table index.

It is also suggested that the setting of the fill factor depends on the read/write rate of the problematic table. Tables with low updates (frequently read) use the high fill factor, and vice versa. Therefore, the fill factor value should not be lower than 60%-70%, unless you have some unusual use solutions, generally the write order of magnitude is more than the read.

Another suggestion is: If you only add data to the end of the table, the fill factor cannot be set to 0 or 100%. In my experience, tables operated by append usually benefit from 90% of the fill factor. If there are not many update operations, you only need to leave enough space for index growth.

The Tara kizer of sqlteam.com also talked about how changes to the fill factor can reduce the performance of the SELECT statement, and any actions to change the fill factor must have regular database performance audits.