6

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

  • 1
    @DanielBjörk since this is an AX query, the actual query will be executed using a cursorprepare and fetching from said cursor. Can you capture the actual statement using profiler and get the execution plans for that? Also add it to the question because the fact that a cursor is used can change the situation massively – Tom V Apr 27 '16 at 21:12
5

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.

2

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the following rearrangement of the query (it is functionally exactly the same but uses joins and removes all the redundant comparisons) to see what the planner comes up with in both production and test?

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 ( 
      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)
      WHERE t4.partition = @P1
      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)
      WHERE t4.partition = @P1
     ) t4 
JOIN custtrans     t3 ON (t3.partition = t4.partition AND t3.dataareaid = t4.dataareaid AND t3.accountnum = t4.accountnum)
JOIN custtransopen t2 ON (t2.partition = t4.partition AND t2.dataareaid = t4.dataareaid AND t2.refrecid = t3.recid)
JOIN spectrans     t1 ON (t1.partition = t4.partition AND t1.refcompany = t4.dataareaid AND t1.refrecid = t2.recid)

WHERE --t1.partition=@P1 AND -- redundant (already set in t1 join t2)
      t1.speccompany=@P2 AND 
      t1.spectableid=@P3 AND 
      t1.specrecid=@P4 AND
      t1.reftableid=@P6 AND
      -- AND t2.partition=@P5 -- already defined in the subselect
      -- AND t3.partition=@P7 -- redundant (already set in t2 join t3)
      -- AND t4.partition=@P8 -- redundant (already set in t3 join t4)
  • that works, but I can't use it because can't change the query. It's AX that generates the query. – Daniel Björk Apr 28 '16 at 7:21
2

We solved the issue.

The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.