SQL Server misunderstanding: 8th-day misunderstanding about Online indexing

Misunderstanding #8: Online indexing does not lock related indexes

Error!

Online indexing is not as good as you think.

The online index operation locks resources temporarily at the start and end of the operation. This may cause serious blocking problems.

At the beginning of the Online Index operation, a shared table lock will be added to the sorted resources. This table lock will continue when new indexes are created and old indexes are scanned for versions.

But the problem is that the S lock will be in a lock queue with other locks on the table. This means that other locks that are incompatible with the S lock exist on the table or the lock queue in the table contains the S lock, such lock operations that are incompatible with S locks also need to wait. This also means various update operations will be blocked. Similarly, if the X lock or IX lock exists on the table, the S lock request will also be blocked.

After the above steps are completed, the S lock will be removed, but you can find that this has an impact on data updates. During this period, the execution plans of all pending update operations will be re-compiled.

Online indexing does not require any locks most of the time after the locks are completed. (Most of this refers to the entire online index sorting process)

After the Online Index operation is completed, a schema modification lock (SCH_M lock) must be added to the newly created index and the old index to complete the final operation. This lock can be imagined as a stronger table-level exclusive lock. During the existence of this lock, you are not allowed to perform any operations on the table, and the Execution Plan for the table cannot be re-compiled.

The blocking problem at the final stage of the Online Index operation is similar to the blocking problem caused by the S lock at the beginning of the Online Index operation-during the period when the SCH_M lock is sustained or is waiting to be granted, you cannot perform any operations on a table. If any read/write operation exists in the table, the SCH_M lock cannot be granted.

During the duration of the SCH_M lock in the final phase, the old index will be delayed by the DROP operation, and the allocation structure pointed to by the metadata will point to the new index (so the index id remains unchanged ), the table version has been updated. Congratulations! Now you have a brand new index.

As you can see, the beginning and end stages of Online Index operations may have huge blocking problems. Therefore, the technical online index operation should be called "online index operation most of the time", but this method is not popular in the market. If you want to learn more about Online Indexing Operations, read the White Paper: Online Indexing Operations in SQL Server 2005.

 

Note: Wang Yang has a very detailed article on Online indexing operations. If you are interested, read: How Online indexing works, next I will extract an image from his article to make the Online Index operation steps clearer.