SQL_SERVER 2017- warning operation may cause residual I/O.

Question

SELECT REQUEST_ID FROM BUREAU_REFERENCE_TRACKING WHERE TRACKING_ID=@P0

I have index on both columns and the optimizer using the index but it showing warning like operation may cause residual I/O.

Answer 1

What is the datatype of TRACKINGID? The parameter "@P0" is an integer, but TRACKINGID is something else (varchar maybe?). This is forcing SQL to convert TRACKINGID. That causes the query to scan your entire index (87+ million rows) instead of going directly to the desired rows. Change the datatype of "@P0" to match the datatype of TRACKINGID.

Tom

Answer 2

I have index on both columns and the optimizer using the index

Not really, database engine performance an index scan, because it's a little faster then a full table scan.

If it would be really used, then it would be an index seek