Create Missing Indexes in AOT

(0) Share

Report

Posted on by A.Prasanna

8,223

Hi everyone ,

I've found some missing indexes when I checked query plan from SQL and Suggestion from SQL server for recreate missing query as follows

USE [AXDB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[INVENTTRANS] ([TRANSTYPE],[DATAAREAID])
INCLUDE ([TRANSREFID],[RECID])
GO

so I have question that How do i create this on AOT and which field i have to add to Index [TRANSTYPE],[DATAAREAID] or [TRANSREFID],[RECID] or both sets?.

any help will be highly appreciated.

thanks & Regards,

Amith Prasanna

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

RE: Create Missing Indexes in AOT

Hi,

As far I know we don't have included clause(includeColumn property on indexes) in AX 2009. So, the option is to create a index with four fields TransType, dataAreaId, TransRefId and RecId on table InventTrans. But as its being standard table, I recommend to test adding this index on test environment.

Refer following link, though its in AX2012 still its applicable:

(How to: Create an Index [AX 2012])

msdn.microsoft.com/.../aa607289.aspx

Community discussion thread suggesting some alternatives:

(Index with include columns in AX 2009)

https://community.dynamics.com/ax/f/33/t/200658

(How to create Index with include clause in Dyanamics AX 2009)

https://community.dynamics.com/ax/f/33/t/94027

Was this reply helpful? Yes No
Suggested answer

Srini Ramidi 2 on at

Like (0)

Report

RE: Create Missing Indexes in AOT

Hello,

Is it not creating the missing Indexes if you sync the table from AOT? I never tested in AX 2009 but i have dropped the index using SQL and re created by synchronizing the table in AX 2012.

Best,

Srini

Was this reply helpful? Yes No
Martin Dráb 236,285 Most Valuable Professional on at

Like (0)

Report

RE: Create Missing Indexes in AOT

Firstly, do you really want to create such an index? Selectivity of TransType is very low, because it's an enum and therefore it can have only a few distinct values. You should always analyze if the query benefiting from the new index is worth optimizing and whether the cost of maintaining the index can be justified.

Was this reply helpful? Yes No
A.Prasanna 8,223 on at

Like (0)

Report

RE: Create Missing Indexes in AOT

Hi Martin ,

Thank you for the information.we have one report it's taking long time to complete because of excessive amount of data. and when I've checked long running query and Query plan it's showed me suggestion to create that index.How ever even we created that Indexes it didn't improve report execution time . any suggestions will be highly appreciated.

Please note this is not standard AX and it has modifications on VAR layer.

Thanks & Regards,

Amith Prasanna

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Create Missing Indexes in AOT

From our experience, you could really bloat your database if you start adding indexes that have a low hit rate. This is especially true for InventTrans and similar tables.

If you still want to add the index, then you could just add it in the AOT with TransType, TransRefId fields, and set the Included column property on the latter field to Yes. The dataAreaId and Partition fields are automatically added by AX. RecId - I am not sure about this, since AX typically creates this as a separate index on the table, so normally in execution plans it would be just another join with an index seek.

Try it without the RecId being manually added, and if the newly compiled plan is not performing well enough, include that too.

As for the report execution time, just collect a client/server trace in AX, and an SQL Query Profiler trace for your database and see what takes the longest time. One you know the root cause, update the topic so we could come up with recommendations.

In AX 2012 R3 and D365fF&O the TransType field is deprecated along with a bunch of others in the InventTrans table, it has a DEL_ prefix. Maybe your problem will go away if you just upgrade to the latest version, since you should do it anyway because of the application hotfixes and improvements (especially in the performance area).

Was this reply helpful? Yes No
A.Prasanna 8,223 on at

Like (0)

Report

RE: Create Missing Indexes in AOT

Hi Vilmos,

thanks for your input.

apart from that i found below thread

community.dynamics.com/.../200658.

According to that we can create include columns in AX2009 but for me it didn't work.

when we first scan for the fragmentation there were 700+ indexes with fragmentation more than 30% some are even 95% .so we rebuilt all those indexes and reorganized leftover.but even that there was no noticeable improvement in performance.

and other thing is that we found that there are 300 + indexes are missing as per the sql

any clue or any method to Identify the indexes which are the indexes are mandatory , and what are the indexes we can avoid ?.

thanks & Regards,

Amith Prasanna

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Create Missing Indexes in AOT

You need to identify missing indexes based on how frequently is the query plan being called, you would want to cover the ones which have a high hit ratio. Also lower use indexes with a long execution time, but within a critical business process or area could also be considered.

Adding indexes though can bloat your DB size, and slow down insert processes/SQL query plan compilation times, so it is not a good idea to have too many of the suggested indexes created. There are some SQL forums where you could read up more about this.

Was this reply helpful? Yes No