Sql-server – Query wont compile/run

execution-planperformancequery-performancesql serversql server 2014

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         ( 
                                            t4.dataareaid = dat.id 
                                 AND        dat.isvirtual = 0)) T4 
WHERE      (( 
                                 t1.partition=@P1) 
           AND        ((( 
                                                       t1.speccompany=@P2) 
                                 AND        ( 
                                                       t1.spectableid=@P3)) 
                      AND        ( 
                                            t1.specrecid=@P4))) 
AND        (( 
                                 t2.partition=@P5) 
           AND        ((( 
                                                       t1.refcompany=t2.dataareaid) 
                                 AND        ( 
                                                       t1.reftableid=@P6)) 
                      AND        ( 
                                            t1.refrecid=t2.recid))) 
AND        (( 
                                 t3.partition=@P7) 
           AND        ( 
                                 t2.refrecid=t3.recid 
                      AND        ( 
                                            t2.dataareaid = t3.dataareaid) 
                      AND        ( 
                                            t2.partition = t3.partition))) 
AND        (( 
                                 t4.partition=@P8) 
           AND        ( 
                                 t3.accountnum=t4.accountnum 
                      AND        ( 
                                            t3.dataareaid = t4.dataareaid) 
                      AND        ( 
                                            t3.partition = t4.partition)))
  • If I use the execution plan from the test server it will execute in less than a second.
  • I have tried OPTION (RECOMPILE) and also DBCC FREEPROCCACHE
  • There is no blocking

Diff table "sys.configurations" between PROD and TEST:

PROD    TEST    description
    30  0   Blocked process reporting threshold
    1   0   Enable or disable Database Mail XPs
    1   0   Sets the FILESTREAM access level
    1   2   maximum degree of parallelism
    230000  22528   Maximum size of server memory (MB)
    1   0   Dedicated Admin Connections are allowed from remote clients

Environment Details

PROD: Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

TraceFlag   Status  Global  Session
1117    1   1   0
1118    1   1   0
1224    1   1   0
2371    1   1   0
2505    1   1   0
3226    1   1   0
4199    1   1   0

TEST: Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

TraceFlag   Status  Global  Session
1117    1   1   0
1224    1   1   0
2371    1   1   0
2505    1   1   0
3226    1   1   0
4199    1   1   0

Compatibility level: 120 on both.

Data Details

PROD

SELECT COUNT(*) FROM SPECTRANS -- 4601
SELECT COUNT(*) FROM CUSTTRANSOPEN -- 14162
SELECT COUNT(*) FROM CUSTTRANS -- 137127
SELECT COUNT(*) FROM CUSTTABLE -- 35617
SELECT COUNT(*) FROM VIRTUALDATAAREALIST -- 3
SELECT COUNT(*) FROM DATAAREA -- 5

Estimated execution plan: http://pastebucket.com/326386
Statistics – http://pastebucket.com/326459

TEST

 SELECT COUNT(*) FROM SPECTRANS -- 10753
 SELECT COUNT(*) FROM CUSTTRANSOPEN -- 7150
 SELECT COUNT(*) FROM CUSTTRANS -- 77342
 SELECT COUNT(*) FROM CUSTTABLE -- 36297
 SELECT COUNT(*) FROM VIRTUALDATAAREALIST -- 3
 SELECT COUNT(*) FROM DATAAREA -- 5 

Actual execution plan: http://pastebucket.com/326387
Statistics – http://pastebucket.com/326458

Best Answer

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.