Sergiy (3/19/2013)
try to run
SELECTBatchID,
FieldA,
FieldB,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrder
FROM <myDatabase>.<mySchema>.<myTable>;
WHERE BatchID = @BatchID
and check the execution plan.
It goes for Index Seek, and then for Key Lookup.
That's where is your problem.
Key Lookup is extremely expensive operation and optimizer will avoid such a plan if there is a possibility of any more or less significant number of rows to lookup for.
During my my testing on Tally table optimizer switched from Key Lookup to Index Scan somewhere around 150 rows filtered by the WHERE clause.
Agreed - especially if your index doesn't include BatchID AND dateEntered. I'm frankly surprised it uses a BatchID index at all, since it need BOTH columns to determine the row_number value. I think you're seeing a clustered index scan (i.e. a table scan) and it's not trying to rely on ANY index you have.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?