CODEWITHSUNDEEP
sql-serverquery-optimization
Alain Bourgeois
Alain Bourgeois

Reputation: 89

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?

Upvotes: 1

Views: 64

Answers (2)

Alain Bourgeois
Alain Bourgeois

Reputation: 89

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

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

We have insufficient information to determine why this is happening. There are a number of situations that might be behind it. The following are some aspects to look into.

  • How big is the table? Mere size should not be enough to produce hours-long execution times, but it might be a contributing factor.
  • What hardware is involved? Again, given modern technology, unlikely on it’s own, but could also be a contributing factor.
  • Volume of usage. Again likely not a sole cause, but if hundreds or thousands of operations are simultaneously hitting your system, this could impact things.
  • Contention. If other operations are opening and holding locks on the table (such as updates, deletes, perhaps reindexing operations), this would block the select from executing. Note that some locks might block this, others might not.
  • Fringe cases. There might be some (relatively) non-standard situation present that’s behind this situation

My guess is it’s a combination of some of the above. Without hands-out review and observation, I would not know how to determine what your actual situation is.

Upvotes: -2

Related Questions