Also talk about SQL Server 2008 enhancements to processing implicit data type conversions in a run plan

In SQL Server queries, inadvertently hidden data type conversions can cause significant query performance problems. For example, there is a simple condition that does not seem to matter: where C = N ' x '. Suppose that the data type of c is varchar. And the table contains a lot of data, and this query can lead to significant performance overhead. Because this operation causes the data type of column C to be converted to nvarchar to match the constant value, this is enhanced in SQL Server 2008 and later in the version number, which reduces the performance overhead somewhat, and references SQL Server 2008 Handling implicit data type conversions in run-plan enhancements
It is only found in practical applications. Such enhancements sometimes do not seem to work, or there is a very big performance problem.
Find time to do a test in the near future. Find out a possible problem, first create a measurement frequently

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 run 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 looks fine.

Then we change it. Turn large amounts of data into 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. Try again 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, rebuild the index after another test 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;

The corresponding. The estimated number of rows is also added

Assuming we use the correct data type, where C = ' 10005 ', we can always get the correct estimated number of rows.
I'm not sure what criteria SQL Server is based on to estimate the number of records in this case. From the run plan, it evaluates a range of nvarchar values through Getrangethroughconvert, actually running a range of seek, in which the value of the query is a constant. Can be accurately evaluated, after this conversion, the constant is evaluated as a variable. So it is a general evaluation result value.

This problem does not look very big, but it is in practical use. Suppose the table has a very large amount of data. And not evenly distributed, the performance impact of such a false estimate is very large, for example, obviously satisfying the condition is very small, can seek, but the results of the evaluation is very large. The running plan changed scan. In a complex operational plan, this has a greater impact.
It seems that 2008 (including R2) is not so hassle-free, such problems have to be controlled, especially in the program. NET is usually the nvarchar type, which leads to a performance problem where n is more
The last wordy is. In SQL Server 2014, no more problems were found (not knowing how in 2012)

Also talk about SQL Server 2008 enhancements to processing implicit data type conversions in a run plan