Slow Query Execution, SQL Server 2000

Hi All,

I have a table that currently contains approx. 8 million records.

I'm running a SELECT query against this table that in some
circumstances is either very quick (ie results returned in Query
Analyzer almost instantaneously), or very slow (ie 30 to 40 seconds to
return results), and I'm trying to work out how I improve performance.

Essentially the query I'm running is nothing more complex than:

SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id

[tier] is a smallint column with a non-clustered, non-unique index on
it. [member_id] is a numeric column with a clustered, unique index on
it.

When I supply a [tier] value of 1, it returns results instantaneously.
I have no idea if this is meaningful, but the tier = 1 records were
loaded first into the table, and comprise approximately 5 million
records.

When I supply a [tier] value of 2, the results take 30 to 40 seconds.
tier =2 records were loaded second, and comprise approximately 3
million records.

I've tried running an execution plan, and while I'm no expert, it
appears to me that the index on tier isn't being used, even if I use:

tier = CAST(2 as SMALLINT)

I'm wondering if anyone can give me ANY advice on how to get any
better performance out of this SELECT statement?

Also, out of curiosity, can a disk defragment have a positive impact
on SELECT query performance?

Any help very much appreciated!

Much warmth,

Murray