Aggressive Under-Indexing and no data for missing index

Question

I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).

I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.

I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:

EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';

I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.

I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.

Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query" and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.

Any help is highly appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Answer 1

Free Unit

Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.

No Missing Indexes?

Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

First, Wait

Use sp_BlitzFirst @SinceStartup = 1;, and check out your wait stats.

If LCK_ waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait column isn't showing values over 1-2 seconds for the LCK_ waits, the situation might not be so dire. I'd probably switch my focus elsewhere.

Modifiers

Rather than using sp_BlitzCache to look at Reads or Duration, use it to look at these:

EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';

You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.

The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.

Since you're running sp_BlitzCache, pay attention to the line in the Warnings rollup (second output);

Common Problems

Long locks can come from places that end users may not notice, like index maintenance.

If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).

Look for code patterns that use BEGIN TRAN, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.

Look in sp_BlitzIndex for foreign keys with cascading actions. These can cause a dramatic amount of locking.

Hope this helps!