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:

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

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

And while all 3 of those are trying to run, I’ll run sp_BlitzWho and see what’s happening:

Let’s zoom in on the sp_BlitzWho results and talk through ’em:

sp_BlitzWho’s output is arranged from the first running query to the latest started ones.

The first select is off and running, actively building its query results.

The second query is trying to rebuild the Votes index. However, to do that, it needs a schema modification lock, as indicated by the LCK_M_SCH_M in the wait_info column. It’s being blocked by the first select, and it can’t even begin its work until the first select releases its schema stability locks.

The third query, the select, simply needs a shared lock on the table. It isn’t trying to do anything fancy, just read it – but because the index rebuild is technically modifying the table’s schema, the select can’t even start. That third query will pile up LCK_M_IS waits the whole time query #1 does its work, and then query #2 does its work, cumulatively.

If your SQL Server is mostly bored during the daytime, and you’re doing full-blown index rebuilds every night, this can make LCK% waits inch up to the top of your wait stats metrics.

To solve it:

  • If you’re on Enterprise Edition, consider switching to online index rebuilds with WAIT_AT_LOW_PRIORITY on
  • If you’re on Standard Edition, consider rebuilding indexes at a lower-load time, or only on the weekends
  • And keep your wait time ratio in perspective: your server might just be bored, with low wait times overall, so the LCK% issues look like they’re high priority when they’re really not
  • Track your wait times by hour of day and day of week, and focus on the wait stats during hours when users care about query performance
Previous Post
Updated First Responder Kit and Consultant Toolkit for April 2022
Next Post
[Video] Office Hours: Last San Diego Balcony Edition

2 Comments. Leave new

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.

Menu