I have a problem with the following query executing from the application (Microsoft Dynamics AX):
DECLARE @p1 INT;
SET @p1 = NULL;
DECLARE @p2 INT;
SET @p2 = 0;
DECLARE @p5 INT;
SET @p5 = 2 + 4096;
DECLARE @p6 INT;
SET @p6 = 8193;
DECLARE @p7 INT;
SET @p7 = 0;
EXEC sp_cursorprepexec
@p1 OUTPUT,
@p2 OUTPUT,
N'@P1 nvarchar(5),@P2 int,@P3 nvarchar(5),@P4 int,@P5 nvarchar(5),@P6 nvarchar(5),@P7 datetime,@P8 nvarchar(21),@P9 numeric(28, 12)',
N'SELECT A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ FROM INVENTTRANS A,INVENTTRANS B,INVENTTABLE C WHERE ((A.DATAAREAID=@P1) AND (A.TRANSTYPE=@P2)) AND ((B.DATAAREAID=@P3) AND (((B.INVENTTRANSID=A.INVENTTRANSIDFATHER) AND (B.TRANSREFID=A.TRANSREFID)) AND (B.TRANSTYPE=@P4))) AND ((C.DATAAREAID=@P5) AND (C.ITEMID=B.ITEMID))
AND EXISTS (SELECT ''x'' FROM INVENTSETTLEMENT D WHERE ((D.DATAAREAID=@P6) AND ((((D.TRANSDATE=@P7) AND (D.VOUCHER=@P8)) AND (D.TRANSRECID=A.RECID)) AND (D.COSTAMOUNTADJUSTMENT<>@P9))))
GROUP BY A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ ORDER BY B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_',
@p5 OUTPUT,
@p6 OUTPUT,
@p7 OUTPUT,
N'dat',
8,
N'dat',
2,
N'dat',
N'dat',
'2017-05-17 00:00:00.000',
N'IM17008141934',
'0.000000000000';
EXEC sp_cursorfetch
@p2,
2,
1,
1;
The estimated plan for this query is here
The query keeps running for almost 3 hours. I have also captured metrics from WhoIsActive sp for this queries session:
wait_info (2ms)PAGEIOLATCH_SH:ecc_wrk:4(*)
CPU 88,765
CPU_delta 47
tempdb_allocations 0
tempdb_current 0
tempdb_allocations_delta 0
tempdb_current_delta 0
blocking_session_id NULL
blocked_session_count 16
reads 26,610,593
reads_delta 20,903
writes 0
writes_delta 0
physical_reads 1,212,764
physical_reads_delta 418
used_memory used_memory_delta 276
status 0
open_tran_count 1
host_name ***
database_name ***
program_name Microsoft Dynamics AX
As I can see it reads huge amount of data and executes for enormous amount of time.
I also captured the query and executed it in management studio where it took 1 second to complete. The actual execution plan is here:
I cannot understand what the problem might me and where to dig. Any help on what to do next will be deeply appreciated.
Best Answer
First of all, the code you posted is not the code executed by the application. There is no
Fetch Query
operator so you aren't retrieving any rows.As you noted in chat you constructed the code yourself but it's hard to guess what exactly will be the code executed by AX, that depends on some settings and calculations done by the AX kernel.
First of all, your cursor is likely to be
FAST_FORWARD
and the number of rows fetched could either be everything at once, or a number of records at a time.The number of rows fetched at once is defined by the Maximum Buffer Size parameter/the row size. See Microsoft Dynamics AX 2012 Server Configuration
The cursor definition will look something like this
or this
That being said, most of the time when I get a poor performing query in AX the query behaves the same with or without a cursor, and it's rare that I need the actual code including the cursors to be able to reproduce the problem in Management Studio, however if you really need the cursor definition the easiest is if you can reproduce it in a test environment.
Once you can reproduce the issue you need to locate the offending query in code.
If you can reproduce the issue in a test environment (because AX is fairly prone to parameter sniffing) you can use the built-in SQL Statement trace log to find the call stack for the query.
You can also look into the Trace Parser tool to capture traces with the actual parameter values and the code trace up to the query. That can help a lot too.
Once you know where the query is executed you could put a breakpoint just before the query and when you hit it fire up SQL Server Profiler to capture the exact code being executed while you step through the select in your AX debugger.
Once you have the problematic code (if you really need it including cursors) the regular tuning principles are valid.
Your first attempts should be indexing because your options to change the query are a bit limited in AX. Then again the estimated plan you posted only has an estimated cost of 0.04 and uses index seeks I'm not sure this is your problem.
AX is very prone to parameter sniffing because of all the
select *
resulting in a lot of key lookups.You could try to use some keywords to influence the plan, namely
forceliterals
to disable parameterization and force a new plan to be compiled for each execution or you could set some row goals usingfirstfast
and that's about it.See the Select statement syntax documentation to see which options you have, but don't overdo it with the keywords (as I suggested in my answer to your other question). My first guess is you are running into parameter sniffing.