Summary
Some query execution plans in Microsoft SQL Server 2016 include a pattern of evaluating a filter on a table or index scan/range operation. Some parts of the filter predicate may match an index key and may therefore be used to run an index seek or range scan. The remaining parts of the predicate are known as residual and must be evaluated for each row output by the scan or range operation. This corresponds to a filter operator. However, to improve performance, SQL Server can push such a filter down to the table access operator itself.Although this approach improves performance overall, under some conditions (for example, for an inaccurate cardinality estimation that's related to parameter sensitivity), the scan-below filter may process a greater number of rows than expected. This behavior may be hidden during query performance troubleshooting when you're using an actual execution plan, because the number of rows that's returned corresponds to the number of rows after the residual predicate is applied and not to the actual number of rows that are scanned from a table or index.
Update information
To improve diagnostics for the scenario that's described in the "Symptoms" section, SQL Server 2016 Service Pack 1 (SP1) introduces a new showplan XML attribute, Estimated Rows Read. This attribute provides the estimated count of rows will be read by the operator before the residual predicate is applied. This update is a complement to KB 3107397.This functionality is included in Service Pack 1 for SQL Server 2016.
Each new build for SQL Server 2016 contains all the hotfixes and all the security fixes that were included with the previous build. We recommend that you install the latest build for SQL Server 2016.
References
Learn about the terminology that Microsoft uses to describe software updates.
