0

When this query is executed, SQL Server chooses a wrong execution plan, why?

SELECT   top 10 AccountNumber , AVERAGE 
FROM     [M].[dbo].[Account] 
WHERE    [Code] = 9201
Go
SELECT   top 10 AccountNumber , AVERAGE 
FROM     [M].[dbo].[Account] with (index(IX_Account))
WHERE    [Code] = 9201

SQL Server chooses the clustered PK index for this query and elapsed time = 78254 ms, but if I force SQL Server to choose a non-clustered index then elapsed time is 2 ms, Stats Account table is updated.

Diego C Nascimento
  • 2,801
  • 1
  • 17
  • 23
user3189030
  • 1
  • 2
    Please post your table definition. – Raj Jan 13 '14 at 05:30
  • CREATE TABLE [dbo].[Account]( [AccountId] [bigint] IDENTITY(1,1) NOT NULL, [Code] [smallint] NOT NULL, [AccountNumber] [bigint] NOT NULL, [AVERAGE] [float] NULL, [TOTALDBAMNT] [float] NULL, [TOTALCRAMNT] [float] NULL, CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ( [AccountId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] – user3189030 Jan 13 '14 at 06:17
  • And you have a nonclustered index on the `CODE` column? – Raj Jan 13 '14 at 06:21
  • What if you add an order by? Seeing as you have TOP 10. – Allan S. Hansen Jan 13 '14 at 06:35
  • yes I have nonclustered index on the CODE columns but not Cover Average column and bookmark lookup happend – user3189030 Jan 13 '14 at 06:43
  • thanks, when add an order by, SQL Server chooses correct plan, why ? – user3189030 Jan 13 '14 at 07:03

1 Answers1

1

It's usually down to having bad statistics on the various indexes. Even with correct stats, an index can only hold so many samples and occasionally when there is a massive skew in the values then the optimiser can think that it won't find a sufficiently small number.

Also you can sometimes have a massive amount of [almost] empty blocks to read through with data values only at "the end". This can sometimes mean where you have a couple of otherwise close variations, one will require drastically more IO to burn through the holes. Likewise if you don't actually have 10 values for 9201 it will have to do an entire table scan if it choses the PK/CI rather than a more fitting index. This is more prevalent when you've done plenty of deletes.

Try updating the stats on the various indexes and things like that & see if it changes anything. 78 seconds is a lot of IO on a single table scan.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27