What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?


You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it.

It might be overzealous index rebuilds.

Let’s demo why by starting a new query in the Stack Overflow database:

SELECT TOP 100 PostId, COUNT(*) AS Votes FROM dbo.Votes GROUP BY PostId ORDER BY COUNT(*) DESC;

After that query begins running, I’ll try to rebuild indexes in another window:

ALTER TABLE dbo.Votes REBUILD;

And after THAT begins running, I’ll start another reporting query in another