Select top - performance issues

Hi,

I am running into issues with "select top" in SQL Server 2005.

I have a table without around 3.5 million records in and am doing a select top i.e.

Select top 10 *
from Products

And it takes around 17 seconds to come back, which is crazy. I have looked at the execution plan and its because its doing a clustered index scan. Why does it not use a non-clustered index in this situation?

If I change it to the following:-

select top 10 *
from Products
order by Ref

explicitly ordering by a non-clustered index then it returns instantaneously.

The table currently has a clustered index on the primary key ProductID (which is an identity column). Is it because its the primary key? I could make another unique field primary clustered, but it isn't a sequential integer, its a 13 character varchar.

Any thoughts?

Andrew

Try updating the statistics on this table and check the query I/O stats...

MohammedU.
Moderator
SQL-Server-Performance.com

Hi,
You could even try adding non-existent value filter
something like:
Select top 10 * from Products
WHERE ProductID>0

Is there any text or binary column in the table?