Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics with fullscan and rebuilt the indexes of all involved tables without success, it's not that many rows in any of the tables. I'm not able to change the query because it's the AOS (AX 2012) creating it. What should i do to be able to use the query as is?

Query

```
DECLARE @P1 AS BIGINT = 5637144576
DECLARE @P2 AS NVARCHAR(4) = N'1003'
DECLARE @P3 AS INT = 212
DECLARE @P4 AS BIGINT = 5638885273
DECLARE @P5 AS BIGINT = 5637144576
DECLARE @P6 AS INT = 865
DECLARE @P7 AS BIGINT = 5637144576
DECLARE @P8 AS BIGINT = 5637144576

SELECT t1.balance01,
t1.recid,
t2.amountcur,
t2.dataareaid,
t2.recid,
t3.recid,
t3.voucher,
t3.accountnum,
t3.approved,
t3.closed,
t3.dataareaid,
t4.party,
t4.dataareaid,
t4.recid
FROM spectrans T1
CROSS JOIN custtransopen T2
CROSS JOIN custtrans T3
CROSS JOIN
(
SELECT virt.id AS dataareaid ,
t4.accountnum,
t4.party,
t4.partition,
t4.recid
FROM custtable T4
INNER JOIN virtualdataarealist VIRT
ON t4.dataareaid = virt.virtualdataarea
UNION ALL
SELECT t4.dataareaid ,
t4.accountnum,
t4.party,
t4.partition,
t4.recid
FROM custtable T4
INNER JOIN dataarea DAT
ON (

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's.

As stated in this blog post on msdn


You need to apply SP1 but you must also enable trace flag 4199 in
order to activate the fix.


SQL Server 2014 Service Pack 1 made various fixes on new Cardinality
Estimator (new CE). The release notes also documents the fixes.

You already have TF 4199 active so the improvements should become active automatically.

I would strongly advise you to update and use the new Cardinality Estimator but with all the more recent fixes instead of disabling the new CE alltogheter.

If you still have issues with this single query you can add a plan guide that says OPTION (QUERYTRACEON 9481) for this query alone.

StackExchange Database Administrators Q#136768, answer score: 5