Set the clustering index and fill factor to reduce deadlocks (reprinted)

My database is prone to deadlocks. I later found that it was caused by clustered indexes. My index fill factor was 90. Then I removed the clustered index and solved the problem, but I don't understand why clustered indexes cause deadlocks ??

If your fill factor is set incorrectly, the clustered index indicates the storage order of the records in the table. Therefore, each data change, all of these may cause the table data to be readjusted according to the clustered index order and the fill factor to be set to 90. This is intended to reduce the index space usage, make the free space on the data page very small. When a new row is added to a full index page, SQL Server moves about half of the rows to the new page to free up space for the new row. This reorganization is called page splitting. Page splitting will reduce the performance and cause data storage fragmentation in the table. Due to such a fill factor setting, the free space on the data page is very small, so page splitting often occurs, which leads to low data processing performance, of course, it is easy to lock the table;

For instructions on fill factor, refer to SQL online help:Fill FactorWhen creating a clustered index, the data in the table is stored in the database data page in the order of the values in the index column. When inserting new data rows in a table or changing the values in the index column, you may have to re-organize the data storage in the table to free up space for new rows and maintain orderly data storage. This is also applicable to non-clustered indexes. When adding or changing data, SQL Server may have to re-organize data storage on non-clustered index pages. When a new row is added to a full index page, SQL Server moves about half of the rows to the new page to free up space for the new row. This reorganization is called page splitting. Page splitting reduces performance and fragments data storage in the table. For more information, see table and index architecture. When creating an index, you can specify a fill factor to set aside additional gaps and retain a certain percentage of space on each leaf page of the index, it can expand the data storage capacity of tables in the future and reduce the possibility of page splitting. The fill factor value is a percentage value from 0 to 100, which specifies the proportion of data pages to be filled after the index is created. When the value is 100, the page will be filled up, and the storage space reserved is the minimum. This setting is used only when data is not changed (for example, in a read-only table. The smaller the value, the larger the free space on the data page. This reduces the need for splitting data pages during index growth, but requires more storage space. This setting is more appropriate when data in the table is changed. The fill factor option is provided to fine tune the performance. However, the default fill factor for the server range specified by the sp_configure system stored procedure is the best choice in most cases. This means that even for an application that targets many insert and update operations, the number of database reads generally exceeds 5 to 10 times the number of database writes. Therefore, specifying a fill factor different from the default setting reduces the read performance of the database, and the decrease is inversely proportional to the value of the fill factor setting. For example, when the fill factor value is 50%, the read performance of the database will be twice as low. 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. 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. If you try to keep extra space on the data page, it will be back to the intention of using the fill factor, because with the data input, SQL Server must split pages on each page, to keep the percentage of free space specified by the fill factor. Therefore, if the data in the table is greatly changed and new data is added, the free space of the data page can be filled. In this case, you can re-create an index and re-specify the fill factor to re-distribute data.

　Fill Factor OptionUse the fill factor option to specify the extent to which Microsoft & reg; SQL Server & #8482; fills each page when a new index is created using existing data. Since SQL server must take time to split these pages during filling, the fill factor percentage affects system performance. The fill factor percentage is only used when an index is created. These pages cannot be maintained at any specific full level. The default value of fill factor is 0. Its valid value ranges from 0 to 100. The value of fill factor is 0, which does not indicate that the page is filled with 0%. Similar to the case where fill factor is set to 100, when fill factor is set to 0, SQL Server creates a clustered index on all pages that contain data, create a non-clustered index using all the leaf pages of the data. Different from setting fill factor to 100, SQL Server reserves space at the top level of the index tree. There is little reason to change the default value of fill factor because it can be overwritten by the create Index Command. A small fill factor value will cause SQL Server to create a new index on an incomplete page. For example, setting the fill factor value to 10 is suitable for creating an index on a table that will eventually retain less data. The smaller the fill factor value, the more storage space occupied by each index. However, the smaller the fill factor value, the more storage space occupied by each index can be inserted without page splitting. If the fill factor value is set to 100, SQL Server creates clustered and non-clustered indexes with 100% fullness. Setting fill factor to 100 is only suitable for read-only tables because data is never added to such tables. Fill factor is an advanced option. If you want to change this setting using the sp_configure system stored procedure, you must change the fill factor only when show advanced options is set to 1. This option takes effect after the server is stopped and restarted.

General fill factor setting policy: the more frequent the data changes, the smaller the fill factor, the less the data changes, the larger the fill factor, for tables with no data changes, the fill factor is set to 100