SQL Server Index Usage with an Order By

I have a table named Workflow. There are 38M rows in the table. There is a PK on the following columns:

ID: Identity Int
ReadTime: dateTime

If I perform the following query, the PK is not used. The query plan shows an index scan being performed on one of the nonclustered indexes plus a sort. It takes a very long time with 38M rows.

Select TOP 100 ID From Workflow
Where ID &开发者_Go百科gt; 1000
Order By ID

However, if I perform this query, a nonclustered index (on LastModifiedTime) is used. The query plan shows an index seek being performed. The query is very fast.

Select TOP 100 * From Workflow
Where LastModifiedTime > '6/12/2010'
Order By LastModifiedTime

So, my question is this. Why isn't the PK used in the first query, but the nonclustered index in the second query is used?

Without being able to fish around in your database, there are a few things that come to my mind.

• Are you certain that the PK is (id, ReadTime) as opposed to (ReadTime, id)?
• What execution plan does SELECT MAX(id) FROM WorkFlow yield?
• What about if you create an index on (id, ReadTime) and then retry the test, or your query?

Since Id is an identity column, having ReadTime participate in the index is superfluous. The clustered key already points to the leaf data. I recommended you modify your indexes

CREATE TABLE Workflow
(
  Id       int IDENTITY,
  ReadTime datetime,
  -- ... other columns, 
  CONSTRAINT PK_WorkFlow
  PRIMARY KEY CLUSTERED
  (
    Id
  ) 

)

CREATE INDEX idx_LastModifiedTime
ON WorkFlow
(
  LastModifiedTime
)

Also, check that statistics are up to date.

Finally, If there are 38 million rows in this table, then the optimizer may conclude that specifying criteria > 1000 on a unique column is non selective, because > 99.997% of the Ids are > 1000 (if your identity seed started at 1). In order for an index to considered helpful, the optimizer must conclude that < 5% of the records would be selected. You can use an index hint to force the issue (as already stated by Dan Andrews). What is the structure of the non-clustered index that was scanned?