Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core

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.

References


Learn about the terminology  that Microsoft uses to describe software updates.