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.
Best Answer
In SQL Server 2014, new cardinality estimation logic was introduced.
From BOL :
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
So my advise would be
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 :
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.