Type conversion in expression may affect "CardinalityEstimate" in query plan choice

John C 1 Reputation point
2021-11-26T14:14:37.787+00:00

Hello Team

We had restore database from SQL 2008 to SQL 2014

All code is same on both servers but still one of SP is executing slow and execution plan showing yellow warning on SELECT with below warning

Please help with it. I tried a lot and read google search but not resolved .

Please note - code is same on SQL 2008 and 2014 still why its warning not showing on 2008 ?

Type conversion in expression (CONVERT(varchar(30),) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression may affect "CardinalityEstimate" in query plan choice,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,619 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-11-26T22:30:53.377+00:00

    Warning is not showing on SQL 2008, because it was not implemented in that release. It's that simple.

    A big change in SQL 2014 was the introduction of the Cardinality Estimator. In many cases, this is for the better, but there are also situations where it can cause plan regressions, and this is what you may be seeing.

    A first step is to enable trace flag 4199, to get access to bug fixes in the optimizer. It may be an issue in the CE that was fixed since the original release of SQL 2014. (And I take for granted that you are on SQL 2014 SP3 CU4.)

  2. Seeya Xi-MSFT 16,436 Reputation points
    2021-11-29T03:21:51.417+00:00

    Hi @johnrp,

    Welcome to Microsoft Q&A!
    Please see this MS Doc about Cardinality Estimation.

    Subsequent updates started with SQL Server 2014 (12.x), meaning compatibility levels 120 and above. The CE updates for levels 120 and above incorporate updated assumptions and algorithms that work well on modern data warehousing and on OLTP workloads.

    Please also see this article about Implicit conversion. This may be helpful to you.
    https://www.sqlshack.com/implicit-conversion-in-sql-server/

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments