How CE Change Should Impact SQL Queries By Examples Part 2: Filtered Predicates

Part1, Part3, Part4, Part5, Part6, Part7, Part8 , Part9

This article series describe some example cases that benefit from the enhancements implemented in the CE in recent releases.

The test database environment set up as below:

• SQL Instance: An Azure SQL Managed Instance

• Test Database: Two Microsoft SQL Server sample databases for SQL Server 2012

  • AdventureWorks2012

  • AdventureWorksDW2012

• SQL Query execution environment: SQL Server Management Studio v18.9.1

Case 1: New CE understand that filtered predicates on the same table are often correlated

In absence of existing multi-column statistics, the legacy SQL Server Query Optimizer views the distribution of data contained across different columns as uncorrelated with one another. This assumption of independence often does not reflect the reality of a typical SQL Server database schema, where implied correlations do actually exist. Starting with CE 120, SQL Server understands there might be a correlation between the two columns on the same table. The CE makes a more accurate estimation of how many rows will be returned by the query, and the query optimizer generates a more optimal plan.

Below is to verify the theory:

First, set database compatibility level 130 and CE model 7.0:

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 130 --SQL Server 2016
GO
  
GO
USE [AdventureWorks2012]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = On;  -- CardinalityEstimationModelVersion 70
GO

Enable "Include Actual Execution Plan" , then run the test query:

USE [AdventureWorks2012]
GO
SELECT [AddressID], [AddressLine1], [AddressLine2] FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND [City] = N'Burbank' AND  [PostalCode] = N'91502'
GO

Verify the cardinality estimate model version in the query plan tree by looking at the root (left-most) operator's properties. the CardinalityEstimationModelVersionattribute value 70 means it's the legacy CE functionality generated the plan

The following screenshot shows the graphical actual plan and the associated index scan details in the property window. The estimated number of rows for the Index Scan was 1 and the actual number of rows was 194. The Query Optimizer assumed that the combination of filter predicates would result in a much higher selectivity. This assumption implied that there were fewer rows in the query result than what was actually the case.

Enable new CE and run the same query again:

USE [AdventureWorks2012]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;  -- CardinalityEstimationModelVersion 130
GO
SELECT [AddressID], [AddressLine1], [AddressLine2] FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND [City] = N'Burbank' AND  [PostalCode] = N'91502'
GO

Verify the cardinality estimate model version in the query plan tree by looking at the root (left-most) operator's properties. the CardinalityEstimationModelVersionattribute value 130 means it's the new (SQL Server 2016) CE functionality generated the plan

The estimated number of rows moves from 1 to 13.4692. The gap between estimated and actual rows is somewhat reduced. For this simple query, it's hard to observe apparent performance difference, but for larger data sets and higher complexity queries, this reduction may result in a modified query plan shape.