Sql-server – Aggressive Under-Indexing and no data for missing index

blockingindexsp-blitzindexsql server

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)

Best Answer

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!

Related Solutions

Sql-server – How to investigate “aggressive indexes”

We've got a ton of learning resources on that Aggressive Indexes page (I just updated it with more, coincidentally) and I can't even begin to do justice to it overall. Having said that, here goes.

Imagine that you've only got a clustered index on a table. Inserts will be pretty doggone fast. However, when you try to update a row in that table, and you don't specify the clustering key in your where clause, then you'll end up with a lot of table locks (aggression). In that case, the fix is to add indexes for the fields you frequently query on.

On the other hand, imagine that you've got dozens of indexes. Whenever you want to insert or delete rows, you're going to need locks all over the place to get your work done. The high number of indexes will slow you down because nobody can get their insert or delete done quickly - and again, you'll see aggressive index warnings. In that case, the fix is to prune down the number of indexes to a more managable number.

Generally, for transactional tables (as opposed to overnight data warehouse loads), I tell people to aim for:

5 or less nonclustered indexes per table,
5 or less fields per index

That rule of 5 and 5 can be violated - the less write activity you have, and the faster your hardware is, and the better you tune your queries, the more you may be able to get away with more indexes. On the flip side, if you have crappy hardware and crappy queries, you might need to drop those numbers lower.

The 5 and 5 rule stems from the fact that I've gotta start people somewhere, and I have 5 fingers on one hand, and 5 fingers on the other - so the rule is easy to communicate.

Sql-server – Lots of short term blockings on SQL Server 2008-R2 database

This:

wait_type  wait_time_ms_per_sec
PAGELATCH_EX    733024.4
PAGELATCH_SH    328846.4
CPU_USED    7131.4
WRITELOG    6484.4

Shows that you have a latch contention problem, and only a latch contention problem.

Since

@ below was run 15 mins after we did node failover

The CPU_USED row should be accurate. And so your workload is only managing to use 7sec of CPU time per second. This is like only running on 7 cores. In the mean time you have 1000sec of page latch time, meaning that you have, on average 1000 sessions waiting to latch a page, and only 7 performing useful work.

You are also running very old software on a very large server. The throughput on this server is pretty high, suggesting that the operation it supports is important. Also consider your username. So you should consider, strongly, engaging Microsoft Support or a partner with expertise in this sort of thing.

That said, the next step is to identify the source of your latch waits. Generally these are either on a hot page of a table in your database, or a system page in TempDb. The sys.dm_exec_requests.wait_resource should tell you which.

If tempdb, then Recommendations to reduce allocation contention in SQL Server tempdb.

If a table in your database, then post the DDL (including indexes) for the table and a the details of the queries that are blocking.

There is a whitepaper on diagnosing and resolving latch contention on SQL 2008 here: Diagnosing and Resolving Latch Contention on SQL Server