Case 8: New CE assume larger number of rows from implicit data type conversions
Mismatched data type usage within join or filter predicates can affect cardinality estimates and the associated plan selection. To avoid cardinality estimate issues caused by data type mismatches, use identical data types in search and join conditions.
Below is a demo showing how badly the query plans(performance) are impacted by the inaccurate CE results:
USE [dba_admin]
GO
--Create a table in a new schema with some sample data
CREATE SCHEMA [test]
GO
CREATE TABLE test.ImplicitTypeConvert (id varchar(25) not null, NAME VARCHAR(25))
DECLARE @I INT,@M INT
SET @I =1
SET @M =100000
WHILE @I<=@M
BEGIN
INSERT INTO test.ImplicitTypeConvert VALUES(@I,'SampleText'+ convert(varchar,@I))
SET @I=@i+1
END --duration 00:05:57
-- Create a primary key on the id column
ALTER TABLE test.ImplicitTypeConvert ADD CONSTRAINT PK_QueryIOStats PRIMARY KEY CLUSTERED (id)
-- Configure database to match PointClickCare's Product environment
alter database dba_admin set compatibility_level = 130; -- SQL Server 2016
go
alter database scoped configuration set legacy_cardinality_estimation = on; -- CardinalityEstimationModelVersion 70
go
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- Enable "Include Actual Execution Plan" then trun query below
SELECT * FROM test.ImplicitTypeConvert WHERE id = 1 -- Mismatched data type causing implicit data type conversion
SELECT * FROM test.ImplicitTypeConvert WHERE id = '1' -- No data type conversion
-- Test to run the same query with new CE
alter database scoped configuration set legacy_cardinality_estimation = Off; -- CardinalityEstimationModelVersion 130
SELECT * FROM test.ImplicitTypeConvert WHERE id = 1 -- Mismatched data type causing implicit data type conversion
SELECT * FROM test.ImplicitTypeConvert WHERE id = '1' -- No data type conversion
Comments