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:
|
1 2 3 4 5 |
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:
|
1 2 |
ALTER TABLE dbo.Votes REBUILD; |
And after THAT begins running, I’ll start another reporting query in another window:
|
1 2 3 4 5 |
SELECT TOP 100 PostId, COUNT(*) AS Votes FROM dbo.Votes GROUP BY PostId ORDER BY COUNT(*) DESC; |
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
5 Comments. Leave new
For those readers who aren’t aware of this resource and want to read further:
https://www.sqlskills.com/help/waits/
https://www.sqlskills.com/help/waits/lck_m_sch_m/
https://www.sqlskills.com/help/waits/lck_m_is/
This is very helpful! I’ve been a DBA for about 2 years now and I still cannot believe how many different wait types can happen, along with the various locks as well. I use waitopedia for new waits I encounter, but I like how this article explains these types of locks. As a noob, I understand locks just a bit better now.
I get these locks just on updating statistics. Sigh.
I’ve got one of these (LCK_M_SCH_M) on my Alter Index Rebuild and my Alter Index Rebuild process has been stuck on ‘suspended’ for hours. Should I abort (kill) my Alter Index Rebuild task? I did not run it in OFFLINE mode by the way – hindsight on that very painful.
For personal help with production outages, click Consulting at the top of the site.