query optimization - SQL Server doesn't use existing index - Stack Overflow

admin管理员组

文章数量:1122832

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?

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?

• sql-server
• query-optimization

Share Improve this question Follow edited Nov 22, 2024 at 14:02 O. Jones 108k1717 gold badges128128 silver badges180180 bronze badges asked Nov 22, 2024 at 13:56 Alain BourgeoisAlain Bourgeois 8911 silver badge55 bronze badges 5

• The index is not unique, so maybe it parametrized the id and now trawling through the table. Try adding: OPTION(RECOMPILE) perhaps. If the values are unique, perhaps you can change select count(*) to select top 1 1. Can also be that it's busy gathering statistics so it hasn't started executing query yet – siggemannen Commented Nov 22, 2024 at 14:06
• 1 Is there a specific reason why a column named UniqueXxxx isn't indexed by a unique index? I guess there is given you need to count how many rows have this "unique" value but it seems odd. – Damien_The_Unbeliever Commented Nov 22, 2024 at 14:08
• How many rows are you expecting the "unique" value '054117860540129BbLRGcI' exactly? – Thom A Commented Nov 22, 2024 at 14:20
• 5 Cannot reproduce dbfiddle.uk/9UziH-sJ it uses the index. Please share the query plan via brentozar.com/pastetheplan. If yours uses the index then it may be a locking/blocking issue. – Charlieface Commented Nov 22, 2024 at 14:54
• Is the table under heavy concurrent usage, especially insert and update traffic? Try issuing the statement SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED right before your SELECT COUNT(*) query. This causes a so-called dirty read and so may not be super precise. But that may not matter. At any rate, it doesn't wait for other transactions as much, and so is usually faster. – O. Jones Commented Nov 22, 2024 at 17:59

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -3

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.

本文标签： query optimizationSQL Server doesn39t use existing indexStack Overflow