Also talk about SQL Server 2008 processing implicit data type conversions in execution plan enhancements

In SQL Server queries, inadvertently hidden data type conversions can cause great query performance problems, such as a simple condition that looks like there is no problem: WHERE c = N ' x ', if the data type of c is varchar, and the table contains a large amount of data, This query can lead to significant performance overhead because this operation causes column C's data type to be converted to nvarchar to match the constant value, which has been enhanced in SQL Server 2008 and later versions to somewhat reduce the performance overhead, referring to SQL Server 2008 Handling implicit data type conversions enhancements in the execution plan
In practice, however, it is found that this enhancement sometimes does not seem to play a role, or there will be a large performance problem.
Recently found time to do a test to find out a possible problem, first create a test table

USE tempdbGOCREATE TABLE _t(    c varchar(50));CREATE INDEX IX_c ON _t( c );GO-- 加入 10000 条数据INSERT _tSELECT (9999 + id) FROM(    SELECT TOP 10000 id = ROW_NUMBER() OVER( ORDER BY GETDATE() )    FROM sys.all_columns a, sys.all_columns)ID

Then look at the query plan through the execution plan

-- Rebuild索引，确保无索引碎片和统计信息准确ALTER INDEX IX_c ON _t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _t WHERE c = N‘10005b‘;GOSET SHOWPLAN_ALL OFF;

Note that the EstimateRows column, which has a value of 1, indicates that the evaluated data that meets the criteria is 1, and now everything looks fine.

And then we change the data to make a lot of the same value.

-- 将 5000 条数据值变成一样，重建索引之后重新测试UPDATE _t SET c = ‘15000‘ WHERE c >= ‘15000‘ALTER INDEX IX_c ON _t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _t WHERE c = N‘10005‘;GOSET SHOWPLAN_ALL OFF;

And then we find that the number of records is getting bigger.

Continue to increase the proportion of the same value

--continue to increase the proportion of the same value, re-test after rebuilding the index UPDATE _t SET c = ' 11000 ' WHERE c >= ' 11000 '  and C < ' 15000 'ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGOSELECT *  from _t WHERE c = N' 10005 ';GO SET showplan_all OFF;GO--continue to increase the proportion of the same value, re-test after rebuilding the index UPDATE _t SET c = ' 10100 ' WHERE c >= ' 10100 '  and C < ' 11000 'ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGOSELECT *  from _t WHERE c = N' 10005 ';GO SET showplan_all OFF;

Accordingly, the estimated number of rows is also increasing

If we use the correct data type, where C = ' 10005 ', you can always get the correct estimated number of rows.
I'm not sure what criteria SQL server uses to estimate the number of records in this case, from the execution plan, it evaluates a range of nvarchar values through Getrangethroughconvert, actually performing a range of seek, in experiments, The value of the query is a constant that can be accurately evaluated, and after this conversion, the constant is evaluated as a variable, so it is a general evaluation result value.
This problem does not look very good, but in practical applications, if the data volume of the table is large, and is not evenly distributed, the performance impact of this error is very large, such as clearly meet the conditions of the very few, can seek, but the results of the evaluation is very large, the execution plan changes scan, in the complex execution plan, This has a bigger impact.
It seems that 2008 (including R2) is not so worry, this problem has to be controlled, especially in the program. NET parameters are usually nvarchar types, which leads to a performance problem where n is more
Finally, in SQL Server 2014, there is no further discovery of this problem (not knowing how it is in 2012)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Also talk about SQL Server 2008 processing implicit data type conversions in execution plan enhancements