In SQL Server, when a query lacks an index for efficient execution, or if implicit conversion occurs due to a type mismatch, the execution plan will display a noticeable warning. However, there is a query performance issue that isn't easily noticeable, known as Residual I/O or Residual Predicate. This means the query has to read many more rows than necessary. More critically, it often reads from disk due to the large amount of data involved and then applies a filter to eliminate most of the rows that were read. To fix this Residual I/O issue, add the predicate filtering column to the index in use, but first, you need to identify it. For example, the query listed below has a residual predicate issue, but in SQL Server Management Studio (SSMS), the execution plan does not present any warnings in the graph or XML, as shown below:
But if you open the same plan with Plan Explorer, it will display a clear warning in the operator, as illustrated below:
