Slow query performance using UPPER, LOWER or RTRIM in SQL Server 2017 and 2019

Hi Guys,
Are you ready for another interesting post?

Today we will inspect and solve a problem that afflict SQL SERVER 2017 and SQL Server 2019 that cause Slow query performance.
Is this also an opportunity to talk about the latest SQL server CU: the CU4

 

The Upper , Lower and Rtrim...

So where is the problem?
Imagine to execute a Query that has a filter predicate on a column (e.g. in our example the field "nemail2").
Suppose also using the UPPER, LOWER, or RTRIM functions.
In this scenario you may have an inaccurate cardinality estimates and so SQL Server may uses an inefficient execution plan.

We already talked about cardinality estimates here: Inside the SQL Server Query Optimizer - part 3 Cardinality estimation 

Let's go with our usual example.

Take a database with compatibity level 140 or 150.
Now filter on a column of nvarchar data type for example:

SELECT id FROM Person WHERE nemail LIKE (N'%@libero.it') 

SELECT id FROM Person WHERE nemail LIKE UPPER(N'%@libero.it')

 

As you can see if you use a UPPER (and also a LOWER and RTRIM) function the estimated number of rows if not accurate.

Estimated rows infacts are 576 with 14 rows extracted while if you don't use any function estimated rows are 19.

As said before this could cause slow query performances.
But..

How To Solve this problem?

The only method I found is to force the "legacy cardinality estimation" using an hint as below:

       
SELECT id FROM Persone WHERE nemail LIKE UPPER(N'%@libero.it')
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
 

Any other method doensn’t work:

SELECT id FROM Person WHERE nemail LIKE UPPER(N'%@libero.it')
OPTION(USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES')) 

SELECT id FROM Person WHERE nemail LIKE UPPER(N'%@libero.it') 
OPTION(QUERYTRACEON 4199)
 

This is infact a bug but it is already fixed with the latest CU (cumulative update) for SQL Server 2017 and SQL Server 2019.

So now we will install the latest CU for SQL Server 2019: the CU4

Install the latest SQL Server CU

Install a CU is an easy operation.
Download it through the official Microsoft site.

Execute it with "Run As Administrator"..

The SQL Server engine service will be restarted and the upgrade is done.

Now run the test again

Now, if you execute the Query below:

       
SELECT id FROM Persone WHERE nemail LIKE UPPER(N'%@libero.it')
 

The number of estimated rows are once again wrong:

BUT


If you now specificy the following option..

       
SELECT id FROM Persone WHERE nemail LIKE UPPER(N'%@libero.it')
OPTION(USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))      
 

or

       

SELECT id FROM Persone WHERE nemail LIKE UPPER(N'%@libero.it')
OPTION(QUERYTRACEON 4199)      
 

You will obtain a more accurate estimated number of rows (19,242) also using the UPPER, LOWER or RTRIM functions.

That's all for today!
I hope you enjoy this post.

See you soon.
Luca


Luca Biondi @ SQLServerPerformance blog 2020!  
 

Previous post: How to upgrade the SQL Server Management studio. SSMS 18.5 news. Moreover.. Notebook and the SQL assessment API