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 alsoDBCC 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 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.