[SOLVED] SQL Server doesn't use existing index

SQL Server doesn't use existing index

Using SQL Server 2017. I have a table with an indexed column [UniqueIdentifierId], making a select to count records take hours although table is indexed.

CREATE TABLE [ZetEvent2UniqueIdentifier]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EventId] [int] NOT NULL,
    [UniqueIdentifierId] [varchar](100) NULL,
    [Destinations] [varchar](120) NULL,
    [Localization] [varchar](50) NULL,
    [EDPType] [tinyint] NULL,
    [Export] [bit] NULL,
    [FirstArrival] [bit] NULL,

    CONSTRAINT [PK_ZetEvent2UniqueIdentifier] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ZetEvent2UniqueIdentifier_UniqueIdentifierId] 
ON [ZetEvent2UniqueIdentifier] ([UniqueIdentifierId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
             ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

If I do

SELECT COUNT(*) 
FROM ZetEvent2UniqueIdentifier zeu 
WHERE UniqueIdentifierId = '054117860540129BbLRGcI'

it takes hours. Explain shows that the index is used. Why is it so slow? A bug?

Solution

Problem was that row was locked in another pending transaction for days