Primary Key Index on a non-filtered column has a very high total lock wait time
2
votes
1
answer
202
views
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.
Asked by Nicholas Head
(123 rep)
Jun 10, 2020, 11:18 PM
Last activity: Jun 11, 2020, 06:12 PM
Last activity: Jun 11, 2020, 06:12 PM