CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-execution-plan
user3189030
user3189030

Reputation: 1

SQL Server choice wrong execution plan

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.

Upvotes: 0

Views: 527

Answers (1)

LoztInSpace
LoztInSpace

Reputation: 5697

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.

Upvotes: 1

Related Questions