Query Plan Error

Question

I have an interesting situation. I use SQL2014 Enterprise version with SP1. The following query results an error message :

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

The query runs fine without the insert statement. Also the query runs if I interchange where clause, say the exists statement with [step] = 1 after the exists statement with [step] = 3. Actually the SQL is generated dynamically, so interchanging the order in where clause is not a solution for me. This was working fine in SQL 2008 and SQL 2012.

Any idea/solutions for this issue is recommended and I appreciate for you time and effort.

insert into tblPatientConditions (
    cmrn_id
    ,metricmnemonic
    ,metricname
    ,status
    ,adddate
    )
select cmrn_id
    ,'PBLEHTN'
    ,'PossibleHTN'
    ,1
    ,Getdate()
from tblPatientInfo pers
where exists (
        select *
        from tblPatientDiagnosisCriteria
        where cmrn_id = pers.cmrn_id
            and metricname = 'PossibleHTN'
            and category = 'CLAIMS'
            and [step] = 1
            and testname = 'HTN'
        )
    and exists (
        select *
        from tblPatientDiagnosisCriteria
        where cmrn_id = pers.cmrn_id
            and metricname = 'PossibleHTN'
            and category = 'PROBLIST'
            and [step] = 2
            and testname = 'HTN'
        )
    and exists (
        select *
        from tblPatientDiagnosisCriteria
        where cmrn_id = pers.cmrn_id
            and metricname = 'PossibleHTN'
            and category = 'CLAIMS'
            and [step] = 3
            and testname = 'HTN'
        )
    and (
        exists (
            select *
            from tblPatientDiagnosisCriteria
            where cmrn_id = pers.cmrn_id
                and metricname = 'PossibleHTN'
                and category = 'DEFINITION'
                and [step] = 4
                and testname = 'SBPRECENT'
            )
        or exists (
            select *
            from tblPatientDiagnosisCriteria
            where cmrn_id = pers.cmrn_id
                and metricname = 'PossibleHTN'
                and category = 'DEFINITION'
                and [step] = 5
                and testname = 'DBPRECENT'
            )
        )
    and (
        exists (
            select *
            from tblPatientDiagnosisCriteria
            where cmrn_id = pers.cmrn_id
                and metricname = 'PossibleHTN'
                and category = 'DEFINITION'
                and [step] = 6
                and testname = 'SBPRECENT'
            )
        or exists (
            select *
            from tblPatientDiagnosisCriteria
            where cmrn_id = pers.cmrn_id
                and metricname = 'PossibleHTN'
                and category = 'DEFINITION'
                and [step] = 7
                and testname = 'DBPSECOND'
            )
        )

The SQL is generated from a set of rules. There are 14 more dynamic SQL statements generated for identifying certain medical conditions and all those queries work fine. Just this one failed and only for inserting to the table. I also tried SELECT INTO #table FROM and that failed.

Answer 1

In SQL Server 2014, new cardinality estimation logic was introduced.

From BOL :

The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

Recently, we upgrade from SQL server 2012 to SQL server 2014 and got hit by the new Cardinality estimator short coming - queries were timing out, cpu pegging close to 100%.

After much troubleshooting, updating stats, rebuilding indexes, doing query plan analysis, we figured out that changing compatibility level to sql 2012 works well.

Paul White explains - Cardinality Estimation for Multiple Predicates

Selectivity computation in SQL Server 2014 behaves the same as previous versions (and trace flag 4137 works as before) if the database compatibility level is set lower than 120, or if trace flag 9481 is active.

So my advise would be

• For small number of queries revealing the problem, use QUERYTRACEON(9481) hint.
• If you dont want to gamble, then just have the trace flag TF9481 as a start-up parameter, so it gets persisted during server restarts.

Note: Enabling TF 9481, you dont need to set the compatibility level of the database to a lower level.

From KB2801413 :

9481 :Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.

As a side note, along with proper testing - you also want to look into TF4199 (Think of it as a master key to turn on every fix for the query optimizer). TF4199 behavior changes with sql server 2016.. TF4199 helped in my environment at lot and is by default ON for all new installations.