September 2, 2015 at 2:03 pm
One monitoring tool indicates that one of our functions experienced large waits due to LCK_M_IS wait type in production last night. Besides function name, time when it occurred and wait type it does not provide any further information.
I checked it in test environment, it takes between 1 - 4 sec to run. Execution plan looks good, it uses non-clustered index seek and clustered index seek for two tables involved in inner join. One of these tables is large - it has close to 10 bln records and its size is 150 GB and it is partitioned. Another table is 140 mln recs and 13 GB in size, not partitioned. The query is not enclosed in explicit transaction.
But what I think is that execution plan does not matter because indicated wait type -- LCK_M_IS -- intend for shared locks - can hardly be related to execution plan performance. Here is something different. What else should I check for this? What other things I can tune? Should I run server-side trace for lock escalation or it is useless? Does it make a sense to SET LOCK_ESCALATION = AUTO for partitioned table? Will it prevent excessive lock escalation?
Thanks
September 2, 2015 at 3:01 pm
You haven't got lock escalation. If you had, it'd be a LCK_M_S at the table level, not the intent lock.
The only thing that would be blocking an intent S lock would be an exclusive lock at the page or table level. Identify what's blocking the query, and fix that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2015 at 3:25 pm
Yes, thanks Gail, I just found that it was blocked by another insert.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply