SQL Server INDEX OPTIMIZATION Series 3: Fill Factor

When creating an SQL Server Index, there is an option, that is, fillfactor ).

Few people may pay attention to it, but it is also important. You may also know this, but how to use it may be confusing. In addition, even if you understand the principles of the index, you may not be able to use it well. In this case, you must analyze the index Field update frequency and so on.

I remember reading a book knowing that there was such a thing, but they were all confused. I didn't know what to do, as if they were all set. As a matter of fact, when the data volume reaches hundreds of thousands or even millions, the effects of indexing are obvious.

Fill Factor definition: The data fill level of pages in the middle of the index. Its role: when the system creates or re-creates an index, a portion of the space is reserved on each index page. This allows the system to keep the index page from being split when adding index information. It aims to minimize the split of the index pages and fine-tune the performance.

(Figure)

As shown in Figure A, we know that data is stored by page. By definition, the fill factor refers to the data filled by page100 (page110, page120, page130) in Figure. By default, the fill factor is 0 (0 and 100% are the same), that is, completely filled. If 60% is set, only 60% of the data in page100 is full of space, and 40% of the remaining space is available.

The fill factor is only executed when the index is created. After the index is created, the fill factor is not maintained when data is added, deleted, or updated in the table. After the index is created, add data, such as "com", before "con" of page100, and the remaining space of page100 is less than 40%. Because the fill level is only 60%, there is a gap between "Barr" and "con", so "com" will be directly inserted between them, not as data movement and page split. If the page is filled with 100%, that is, the full page is filled with data, it will be regarded as "con" and the data behind it will move one digit backward, and then insert "com" before "con ", page100 moves about half of the rows (the second half) to the new page to free up space for the new row (this reorganization is called page splitting. Page splitting can reduce the performance and cause data storage fragments in the table.) In these moving processes, the speed decreases significantly, and fragments are generated on the other hand. After adding the data, you need to re-create the index to keep the fill factor at 60%.

Someone compares the fill factor to the following example:

To arrange the order of the 10 students in a class, we can give each student a number, for example:
A. From 1, 2, 3, 4, 5, 6, 7, 9, 10. In this case, the fill factor is 100.
At this time, if new students are coming, and their ranking is in the middle, we need to change the numbers of many students, such as the new students ranking 5th places, you need to add 1 to all the student numbers after the 4th, and then edit the new student number as the 5th.
B. We can give the students the numbers 1, 3, 5, 7, 9, 11, 13, 15, 17, and 19.
The order is also completed. we say that the fill factor is 50% at this time. If a new employee comes in the 5th bits, we only need to edit the number into 8. other students do not need to change.

It can be seen that when the fill factor is large, the number space used by the vertex is small, which consumes less resources. When the fill factor is small, the occupied resources increase, but the operation is convenient and fast.

When the fill factor is large, the re-indexing after the record is inserted or modified will be very large, and the disk I/O operations will increase, and the performance will inevitably decrease, but the occupied space is small. when the fill factor is small, the index file occupies a relatively large disk space and memory space. However, Io operations are required to re-index the system itself, which improves performance, but only occupies some storage space. depending on your own decision.

Generally, 100% is set only when data is not changed (for example, in a read-only table. In addition, it is useful to set the fill factor option to another value only when a new index is created based on existing data in the table and you can accurately predict which changes will be made to the data in the future. Therefore, it is not easy to set the fill factor.

 

 

One of the SQL Server INDEX OPTIMIZATION series: Working Principle & clustered index | non-clustered Index

SQL Server INDEX OPTIMIZATION Series II: index performance considerations

SQL Server INDEX OPTIMIZATION Series 3: Fill Factor