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?

  • how high was your read and write latency? – Bob Klimes Jun 11 at 13:28

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.

  • Thanks Ronaldo. That seems to be the most reasonable explanation. I certainly can't think of why it would only affect TempDB otherwise. I'll leave this open for a day or so just in case, but that does make sense. Thanks again! – user80151 Jun 10 at 21:04
  • Glad to help, @user80151. And your wait has already benefited both of us: Josh Darnell has contributed improving my answer by adding relevant info. – Ronaldo Jun 11 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.