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 W...