I've been running dbo.sp_BlitzIndex and have 4 somewhat similar indexes on the main table in my database. Each one has a high number of waits and escalation attempts. I'm not sure where these are coming from. I don't have missing indexes. I don't see searches on this table taking a long time so not sure if these are coming from selects or inserts. Need some pointers on where to look next.

CORE.tblCase.idx_tblCase_bClosed_nUserID_PrimaryAgent (2):
Row lock waits: 1; total duration: 0 seconds; avg duration: 0 seconds;
Page lock waits: 85; total duration: 92 minutes; avg duration: 1 minutes;
Lock escalation attempts: 100,965;
Actual escalations: 1. NC indexes on table: 1

CORE.tblCase.idx_tblCase_bClosed_nActionID_Last_nWorkflowID (27):
Row lock waits: 3; total duration: 26 seconds; avg duration: 8 seconds;
Page lock waits: 15; total duration: 10 minutes; avg duration: 42 seconds;
Lock escalation attempts: 31,908;
Actual Escalations: 0. NC indexes on table: 1

CORE.tblCase.idx_tblCase_bClosed_nWorkflowID_nActionID_Last (28):
Page lock waits: 112; total duration: 85 minutes; avg duration: 46 seconds;
Lock escalation attempts: 31,748;
Actual Escalations: 0. NC indexes on table: 1

CORE.tblCase.idx_tblCase_bClosed_nCaseID_INC (35):
Row lock waits: 2; total duration: 13 minutes; avg duration: 6 minutes;
Page lock waits: 307; total duration: 399 minutes; avg duration: 1 minutes;
Lock escalation attempts: 43,090;
Actual Escalations: 0. NC indexes on table: 1


1 Answer 1


Side note first - hmm, there's something a little suspicious in those results. They all say the same table, but at the same time, they say "NC indexes on table: 1". That means we think there's only 1 index on the table - but you're showing 4 here. These could be in different databases perhaps, or maybe there's a bug in the sp_BlitzIndex code.

That aside, you have a couple of different questions in here.

How do I know if I have a blocking problem? Look at wait stats, which tracks what your SQL Server has been waiting on. My personal favorite tool for that is sp_Blitz @CheckServerInfo = 1, @OutputType = 'markdown' because you can post the results here on Stack in your question. (Disclaimer: I work for the company that started those scripts.) One of the sections will show your most significant waits - focus on those first. If your primary wait types include LCK*, then you probably have a blocking issue.

How do I find queries that are taking locks? This one can be kinda tough without monitoring software. I'd start with sp_BlitzCache @SortOrder = 'duration' That'll show you the longest-running queries, which can sometimes point out queries involved in blocking. You can't really filter by table, though, at least not quickly.

How do I design the right indexes to support those queries? That's kind of a big question. Generally, you want as many indexes as you need to support your workloads, but not so many that delete/update/insert (DUI) operations slow down due to the wide number of locks they have to get. If your table really does only have one index, then DUI queries are probably doing table scans in order to find the rows they want to update. It's time to look at how you're accessing those tables when you do updates.

For example, say you have the classic white pages of the phone book - organized by last name, first name. If you try to update all users whose first name is 'Brent', you're going to have to scan the entire phone book, and you can end up with some nasty locking situations. You'd want to create a separate index on first name so that you can quickly find the rows you need to update, and then get out of the table.

  • ran sp_Blitz and found wait stats as Priority 240: Priority 240: Wait Stats:1 - OLEDB - 555.3 hours of waits, 31.1 minutes average wait time per hour, 0.0% signal wait, 4824250258 waiting tasks, 0.4 ms average wait time.; We have a good amount of linked server queries that run during batch processing in the early morning pulling data; When I run Glen Berrys 2014 Diag script, the wait stats query shows LCK_M_S at #11. I'll be running BlitzCache today to see what I can find.
    – MikeC
    Apr 7, 2017 at 12:32
  • OK, great, there you go: your #11 task in your to-do list is probably not the first one you wanna focus on. ;-)
    – Brent Ozar
    Apr 7, 2017 at 14:57
  • For others reading, I have found a script from Jonathan Kehayias script to try and see what queries are using these indexes. Seeing what that returns
    – MikeC
    Apr 7, 2017 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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