Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

AX 2012 View Not Using Index

Posted on by 485

I have a view that pulls batch properties for on-hand inventory.  The view joins InventSum to InventDim on InventDimId and joins InventBatch on ItemId = InventSum.ItemId and InventBatchId = InventDim.InventBatchId.

If the relation on ItemId is removed, the query performance is good, but could potentially return inaccurate data on batches that are not unique to an item Id.

With the ItemId relation included, the view takes 2-3 minutes to open, which is horrible.

SQL performance analyzer recommends this index be created:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[INVENTSUM] ([PHYSICALINVENT])
INCLUDE ([ITEMID],[INVENTDIMID],[DATAAREAID],[PARTITION],[RECID])

AX already has an index on ItemId and InventDimId, but it is a clustered alternate key index, which I why I assume SQL is not using it.

If I try to create the index, AX complains that the new one overlaps the existing index.  Also, AX will not allow both ItemId and InventDimId be included.

Any suggestions on how to modify the view so the existing index will be utilized?

or.. suggestions on how to create an index that SQL will recognized and use?

Thanks,

- Brad

  • Brad Docimo Profile Picture
    Brad Docimo 485 on at
    RE: AX 2012 View Not Using Index

    Just skipped over PhysicalInvent when I read the output.  Attention to detail..   : )  That did it.  Thanks for the help.

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: AX 2012 View Not Using Index

    Hi Brad D,

    Looks like that suggested index is the result of the range in view by PHYSICALINVENT field. Are you selecting all records from view in SQL management or the view is a cross-company view? Usually, if you select data from AX there are additional filters by Partition, and DataAreaId are added and it improves query plans as all indexes created from AX have the first two columns Partition and  DataAreaId (if you don't' specify them explicitly in index).

    How many records you have in InventSum, InventDim and in InventBatch tables?

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: AX 2012 View Not Using Index

    Actually the suggested index has field PhysicalInvent as a normal column, and ItemId, InventDimId, DataAreaId and Partition as included columns.

    An index that has ItemId and InventDimId is very different so it's not really working with the queries that cause this suggestion to be shown. SQL would definetely use the existing index if it would make sense.

    What kind of queries are you making?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans