Performance Tune Dynamics AX Query

@SQLTrooper 2015-01-27 18:30:35

I'm having one heck of a time finding the ideal indexes so I'm looking for such feedback. I'm out of ideas at this point, but it's a query that Dynamics AX runs and it's causing master planning to run real long. Thoughts?

These are the distinct counts of columns in the query (in parentheses)

SELECT COUNT(DISTINCT DATAAREAID) FROM REQTRANS (4)
SELECT COUNT(DISTINCT INVENTTRANSID) FROM REQTRANS (29845)
SELECT COUNT(DISTINCT REQPLANID) FROM REQTRANS (4)
SELECT COUNT(DISTINCT REFID) FROM REQTRANS (14287)
SELECT COUNT(DISTINCT REFTYPE) FROM REQTRANS (16)

This is the problematic portion of the query:

Plan

The execution plan is from my just stuffing in values that were of the correct data type. All I was getting in the trace was cursor activity, therefore no real parameters. However, I did figure out how to save the XML showplans and got some more valid parameters (in image). It certainly is more challenging when dealing with cursors (API_CURSOR, sp_cursorfetch, etc.) – I'm really trying to learn more about how to go about tuning these.

PS. The reason I was just stuffing in values is because we don't get actual ones from sys.dm_exec_sql_text – just @P1, @P2, etc. This is Dynamics AX and I can't really translate the X++ into tSQL ๐Ÿ˜‰

Parameters

queryanalysis file

This is the query – sorry it's so long ๐Ÿ˜‰ I also attached a text file of the DDL for the tables and indexes involved. This is base AX – but it would seem there have to be better tuning options. I just have not found one and I've tried quite a few things. Included columns aren't an option in AX 2009 – just very wide NC indexes (which I try to avoid).

SELECT A.ITEMGROUPID
,A.ITEMID
,A.ITEMNAME
,A.ITEMTYPE
,A.PURCHMODEL
,A.HEIGHT
,A.WIDTH
,A.SALESMODEL
,A.COSTGROUPID
,A.REQGROUPID
,A.EPCMANAGER
,A.PRIMARYVENDORID
,A.NETWEIGHT
,A.DEPTH
,A.UNITVOLUME
,A.BOMUNITID
,A.ITEMPRICETOLERANCEGROUPID
,A.DENSITY
,A.DIMENSION
,A.DIMENSION2_
,A.DIMENSION3_
,A.DIMENSION4_
,A.COSTMODEL
,A.USEALTITEMID
,A.ALTITEMID
,A.INTRACODE
,A.PRODFLUSHINGPRINCIP
,A.MINIMUMPALLETQUANTITY
,A.PBAITEMAUTOGENERATED
,A.WMSARRIVALHANDLINGTIME
,A.BOMMANUALRECEIPT
,A.STOPEXPLODE
,A.PHANTOM
,A.INTRAUNIT
,A.BOMLEVEL
,A.BATCHNUMGROUPID
,A.AUTOREPORTFINISHED
,A.ORIGCOUNTRYREGIONID
,A.STATISTICSFACTOR
,A.ALTCONFIGID
,A.STANDARDCONFIGID
,A.PRODPOOLID
,A.ABCTIEUP
,A.ABCREVENUE
,A.ABCVALUE
,A.ABCCONTRIBUTIONMARGIN
,A.COMMISSIONGROUPID
,A.CONFIGURABLE
,A.SALESPERCENTMARKUP
,A.SALESCONTRIBUTIONRATIO
,A.SALESPRICEMODELBASIC
,A.NAMEALIAS
,A.PRODGROUPID
,A.PROJCATEGORYID
,A.GROSSDEPTH
,A.GROSSWIDTH
,A.GROSSHEIGHT
,A.STANDARDPALLETQUANTITY
,A.QTYPERLAYER
,A.SORTCODE
,A.CONFIGSIMILAR
,A.SERIALNUMGROUPID
,A.DIMGROUPID
,A.MODELGROUPID
,A.ITEMBUYERGROUPID
,A.TAXPACKAGINGQTY
,A.WMSPALLETTYPEID
,A.ORIGSTATEID
,A.WMSPICKINGQTYTIME
,A.TARAWEIGHT
,A.PACKAGINGGROUPID
,A.SCRAPVAR
,A.SCRAPCONST
,A.STANDARDINVENTCOLORID
,A.STANDARDINVENTSIZEID
,A.ITEMDIMCOMBINATIONAUTOCREATE
,A.ITEMDIMCOSTPRICE
,A.ITEMIDCOMPANY
,A.ALTINVENTSIZEID
,A.ALTINVENTCOLORID
,A.FORECASTDMPINCLUDE
,A.PALLETTAGGING
,A.ITEMTAGGINGLEVEL
,A.BOMCALCGROUPID
,A.JSDECLAREDVALUEPERCENT
,A.JSDECLAREDVALUEPRICEMODEL
,A.JSNAFTARVC
,A.JSNAFTAPRODUCERFLAG
,A.JSNAFTAPREFERENCECRITERION
,A.NGPCODE_FR
,A.AXATJOVERDELIVERYPCT
,A.CATALOGPAGENUMBER
,A.INTRODATE
,A.ENDDATE
,A.RETURNABLE
,A.PRINTRCPTLABELS
,A.DROPSHIPONLY
,A.ITEMSTATUS
,A.PRODUCTCLASS
,A.ARSHAZARDOUS
,A.ITEMTYPE1
,A.ARSSALESCATEGORYIDBASE
,A.ARSSCHEDULEBCODE
,A.ARSRECEIVINGINSPECTREQUIRED
,A.ARSFLATRATETIME
,A.ARSFLATRATEGROUPID
,A.ARSEXCLUDESALESFORECASTHISTORY
,A.ARSITEMSTARTDATE
,A.ARSINCLUDEGOODSSCREEN
,A.ARSCANADIANCODE
,A.ARSUPCCODE
,A.ARSRAWMATERIAL
,A.ARSENABLEWEBUSE
,A.ARSABCPICKLINES
,A.ARSMULTIPLECOUNTIESOFORIG50027
,A.ARSATPSTATUS
,A.ARSUPCCODEOVERRIDE
,A.ARSWHEREUSEDCOUNT
,A.ARSDRWREVLETTER
,A.ARSSUPPLIERFORECAST
,A.ARSSERLABSAFETY
,A.ARSCECERT
,A.ARSULCERT
,A.ARSSACERT
,A.ARSSOUNDPOWER
,A.ARSBARSCODE
,A.ARSFORECAST
,A.ARSOEMUPC
,A.ARSPCS
,A.ARSITEMKIND
,A.ARSSERLABDESC1
,A.ARSSERLABDESC2
,A.ARSSOUNDPRESS
,A.ARSITEMBRANDID
,A.ARSSERVICESALESSTATUS
,A.ARSVALUESTREAM
,A.ARSPAINTCOATING
,A.ARSDRAWINGNUM
,A.ARSMODELAREA
,A.ARSMODELSERIES
,A.ARSSEQUENTIAL
,A.ARSATPOVDATE
,A.ARSATPOVERRIDE
,A.ARSBUSINESSUNITBRANDID
,A.ARSREASONFORNOEXPORT
,A.ARSEXPIREDATESERVICE
,A.ARSEXPIREDATELOC
,A.ARSEXPIREDATEID
,A.ARSDATESENSITIVETYPE
,A.ARSDATESENSITIVE
,A.ARSPARENTCODE
,A.ARSOPLABELED
,A.ARSIPLABELED
,A.ARSMSDS
,A.ARSFREIGHTCLASS
,A.ARSNAFTAENDDATE
,A.ARSNAFTASTARTDATE
,A.ARSECCN
,A.ARSSHELFLIFEMONTHS
,A.ARSPERSONALIZATIONPRODUCTID
,A.ARSPERSONALIZATIONCOLOR
,A.ARSPERSONALIZATIONCODE
,A.ARSNMFC
,A.ARSRESTRICTED
,A.ARSKITTYPE
,A.ARSSEASONCODE
,A.ARSSPCUSTPARENTITEM
,A.ARSSPECIALORDER
,A.ARSCUSTPRODUCT
,A.ARSPERSONALIZATIONNOTEID
,A.ARSGARMENTTYPEID
,A.ARSISSHIPSAVERITEM
,A.ARSEPAREGISTRATIONNUM
,A.ARSAEROSOLCLASS
,A.ARSMSDSFILE
,A.ARSNETCONTENT
,A.ARSNETCONTENTUOM
,A.ARSDONOTEXPORT
,A.ARSVALIDATEDDATE
,A.ARSEXCLUDEBACKORDERS
,A.ARSUNNUMBERPACKGR
,A.ARSFREIGHTTRUCK
,A.ARSFREIGHTOVERSIZE
,A.MODIFIEDDATETIME
,A.MODIFIEDBY
,A.MODIFIEDTRANSACTIONID
,A.CREATEDDATETIME
,A.CREATEDBY
,A.CREATEDTRANSACTIONID
,A.RECVERSION
,A.RECID
,A.ARSLONGERDESCRIPTION
,A.ARSSEARCHTEXT
FROM INVENTTABLE A
WHERE ((A.DATAAREAID = N'stn')
AND ((A.ITEMTYPE = 0)
OR (A.ITEMTYPE = 1)
)
)
AND EXISTS ( SELECT 'x'
FROM REQTRANS B
WHERE ((B.DATAAREAID = N'stn')
AND (((((B.REQPLANID = N'Forecast')
AND (B.REFTYPE = 21)
)
AND (B.REFID = N")
)
AND (B.INVENTTRANSID = N")
)
AND (A.ITEMID = B.ITEMID)
)
) )
ORDER BY A.DATAAREAID
,A.ITEMID
OPTION (FAST 1)link text

SQLkiwi 2015-01-28 03:46:04
Ok. Thanks. Could you also edit your question to include the full text of the source query please? It is cut off in the plan file. Also, how much freedom do you have here to change things? Just index definitions? Existing table and index DDL would also be useful (just please edit the question rather than adding an "answer"; helps keep everything in one place cheers)