SQL Server – Does LCK_M_SCH_S Cause Tempdb Read/Write Latency?

lockingsql servertempdb

I noticed a big uptick in tempdb read/write. This was during the same period of time that someone ran Alter Index and I noticed that the biggest wait at that time was LCK_M_SCH_S.

I'm trying to track down what caused the uptick in read/write latency. All four tempdb files had the uptick, so I'm thinking it was either the schema lock from LCK_M_SCH_S or maybe a network connectivity issue.

It has since cleared and gone back to normal so I'm thinking more towards the lock issue. The wait type was not I/O according to the SQL monitor we use, but I can't find anything online that makes the connection between LCK_M_SCH_S and tempdb. Thoughts?

Best Answer

The LCK_M_SCH_S was most likely related to the index/table from your ALTER INDEX and not to tempdb.

About the spike you see on the tempdb during the ALTER INDEX operation it might be caused by the SORT_IN_TEMPDB Option For Indexes:

When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.

An index rebuild can also cause increased tempdb usage if the ONLINE = ON option is used, as modifications during the rebuild are maintained in a special version store in tempdb. See Temporary Disk Space for Online Index Operations for the details:

Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. This avoids the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb.

Related Question