Sql-server – Primary Key Index on a non-filtered column has a very high total lock wait time

sp-blitzindexsql server

I'm seeing the

Aggressive Indexes: Total lock wait time > 5 minutes (row + page) with
short average waits

warning via BlitzIndex on one of our tables, and I cannot quite understand what could be happening. The index in question is related to a primary key on our table:

ALTER TABLE [Authentication].[Tokens] 
ADD CONSTRAINT [PK_Authentication.Tokens] 
PRIMARY KEY CLUSTERED ( [TokenID] )

And for completeness, here's the gist of the table structure:

CREATE TABLE [Authentication].[Tokens](
    [TokenID] [int] IDENTITY(1,1) NOT NULL,
... other columns here)

BlitzIndex reports the following stats: Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup) Writes:1,399,277, 314 rows; 1.1MB

I'm somewhat confused. We don't query/filter off of this column (like SELECT ... WHERE TokenID = 1). My only guess is maybe the table is being read quite often, and lots of queries are slamming against each other using the default SQL locking strategy?

I'm open to any advice or feedback here, for a table that seems simple and benign.

Best Answer

Notice that you're talking about a PRIMARY KEY CLUSTERED (which means it's the table itself) so you don't need a query like WHERE TokenID = 1 to hit that index. From your scenario most of the reads are from lookups (do you understand what is a lookup read method?) what means that most of the queries are something like SELECT TokenID, Column2, Column3 WHERE Column2 = 1 and you have an index for Column2 that doesn't include TokenID, Column2 and Column3.

Here's how I would interpret that output you got:

Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup)

  • 2,259,000 seek: It's a considerable value, but a seek is usually the best method of accessing an index and there's little room for improvement here.

  • 934,476 scan: It's usually the worst access method, but this value compared to the others might not have the biggest impact to be the first fixed.

  • 6,873,373 lookup: By far the biggest number and deserves some attention as this could be caused by a few queries repeatedly hitting nonclustered indexes that could be improved to do seeks instead.

Writes: 1,399,277, 314 rows;

  • These can cause blocks on the reads or get blocked because of them, so if I can make those reads finish their job faster (like tuning the lookups to become seeks by improving either the indexes or the queries) the time a read would have to wait for the write to finish (or vice versa) would be reduced.

With that information I would search among the queries hitting the table [Authentication].[Tokens] what are doing the lookup read. Once they are improved I would run the sp_BlitzIndex again to measure the results of the tuning.

Further reading:

Brent Ozar explains the sp_BlitzIndex Aggressive Indexes and How to Fix sp_BlitzIndex Aggressive Indexes Warnings.

Pinal Dave has an article on SQL SERVER – Removing Key Lookup

Related Question