optimize this query not sure what is the problem

GANGA_dba 2014-06-02 18:42:12

I am attaching the one of the execution plan which is having more IO,please do suggest where i can look for optimization link text

@SQLTrooper 2014-06-02 18:50:38
The first thing I would personally do is get rid of those Key Lookups by adding included columns to existing indexes. What version of AX is that? In know in 2009 there was no support for included columns, but in 2012 there is. If you are on 2009 you would have to add the columns to the end of the indexes, sometimes making them a little wide, but that's the only way to cover the query needs unfortunately.

Edit: I'm assuming you know you have to edit AX indexes via the AOT inside the AX client, but just in case.

John M Couch 2014-06-02 18:54:06
There is a link posted below to work around included columns on 2009. Had to do this myself. We have a large Dynamics implementation in our European office.
@SQLTrooper 2014-06-02 19:01:01
Yeah, that's one way to do it. If push comes to shove and I really need to have an index with included columns (which is fairly rare), I just add another job step to my custom index job in SQL Server – if exists, do nothing; if not, recreate it.

In his case, he only has a couple columns to add in each case. I personally would just add them to the existing indexes.

John M Couch 2014-06-02 18:51:37
First thing I see is that this is Dynamics AX. What version is it? If its 2012, then there are 5 Key lookups occurring that could probably be resolved with some included column indexes. If its not 2012, i.e. 2009, then there is a workaround that will allow you to use included columns found here:

http://daxdilip.blogspot.com/2011/05/tip-how-to-avoid-overriding-of-sql.html

GANGA_dba 2014-06-02 19:07:11
yes i do have 2012 version, do you suggest to add covering indexes to INVENTTABLE ?
John M Couch 2014-06-02 19:15:33
I would add the columns to the indexes to cover what you need to. You can do it via included columns or append them tot he column list.
@SQLTrooper 2014-06-02 19:18:07
One gotcha is the INVENTTABLE index is a Property (CreateRecIdIndex=Yes) and unless something has changed, you can't edit it. He will likely have to create a new index.

Just something I thought was worth mentioning – I saw RECID in the name there.