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

Thrinatha reddy 20 Reputation points
2023-03-09T13:35:35.14+00:00

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.warning

plan

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,179 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,441 Reputation points
    2023-03-09T14:36:40.7233333+00:00

    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

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 27,306 Reputation points
    2023-03-09T14:15:49.5366667+00:00

    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