MSSQL Rebuild Index (on-line rebuild, control Max processor count, MAXDOP)

First, under what circumstances need to rebuild index 1, too many fragments (reference value: >20%)

How index fragmentation occurs, move to the fragmentation and fill factor in the T-SQL query Advanced-sql Server index

2, fill through the Low (reference value: <75%)

Possible causes for the population to pass Low: ①. There is a partition table ③. The clustered index is not a digital ④. Less than the number of millions of data

Ii. reconstruction of the index

ALTER INDEX <  of Index >  on < Table or View name>with=on=4)

Online : on-line execution reduces locks during rebuild (extended execution time)

MAXDOP: Manually configure the maximum number of processors used to run index statements

value	Description
0	Specifies that the server determines the number of CPUs used based on the current system workload. This is the default value, or the recommended setting.
1	Cancel the generation of the parallel plan. The operation will be executed serially.
2-64	Limit the number of processors to the specified value. depending on the current workload, fewer processors may be used. if the specified value is greater than the number of available CPUs, the actual number of available CPUs will be used.

third, extended notes:

Workaround for fragmentation (referenced from: Song and fill factor in SQL Server index)

Basically all of the solutions are based on the reconstruction and collation of the index, but in a different way

1. Delete the index and rebuild

This is not a good way. The index is not available during the drop index. Causes blocking to occur. In the case of dropping a clustered index, it causes the corresponding nonclustered index to be rebuilt two times (when the deletion is rebuilt, and then rebuilt at build time). Although this method is not good, it is most effective for index collation.

2. rebuilding an index using the DROP_EXISTING statement

To avoid rebuilding two indexes, use the DROP_EXISTING statement to rebuild the index, because the statement is atomic and does not cause nonclustered indexes to be rebuilt two times, but the same way it can cause blocking

3. use the ALTER INDEX rebuild statement to rebuild the index as shown in the previous article

Using this statement also rebuilds the index, but it does not need to unload and rebuild the index by dynamically rebuilding the index. is superior to the first two methods, but will still cause blocking. You can reduce the lock by using the online keyword, but it will cause the rebuild time to be extended.

4. using the Alter INDEX REORGANIZE

This method does not rebuild the index, nor does it generate a new page, it simply organizes and skips when a locked page is encountered, so it does not cause blocking. But at the same time, the finishing effect will be worse than the first three kinds.

MSSQL Rebuild Index (on-line rebuild, control Max processor count, MAXDOP)