Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Dynamics AX 2012 price...
Finance | Project Operations, Human Resources, ...
Answered

Dynamics AX 2012 pricedisctable clustered index

(0) ShareShare
ReportReport
Posted on by patrickjolliffe 20 
I'm a DBA supporting Dynamics AX.  In looking at a performance issue on our system I noticed that the clustered index on PRICEDISCTABLE is the fairly wide
index I_315PRICEDISCIDX rather then the primary key I_315RECID.  I've contacted application team and they say this is not changed on their side.
Could anyone check and confirm this is standard configuration?    


I_315ACCOUNTIDX    nonclustered located on PRIMARY    PARTITION, DATAAREAID, RELATION, ACCOUNTCODE, ACCOUNTRELATION, CURRENCY, ITEMCODE, ITEMRELATION, UNITID, QUANTITYAMOUNTFROM, FROMDATE, AGREEMENTHEADEREXT_RU
I_315AGREEMENTHEADEREXT_RUIDX    nonclustered located on PRIMARY    PARTITION, DATAAREAID, AGREEMENTHEADEREXT_RU
I_315DBA    nonclustered located on PRIMARY    PARTITION, DATAAREAID, ACCOUNTCODE, ACCOUNTRELATION
I_315GENERICCURRENCY    nonclustered located on PRIMARY    PARTITION, DATAAREAID, GENERICCURRENCY, RELATION, ITEMCODE, ITEMRELATION, ACCOUNTCODE, ACCOUNTRELATION, INVENTDIMID, UNITID
I_315ITEMDIMIDX    nonclustered located on PRIMARY    PARTITION, DATAAREAID, ITEMRELATION, ITEMCODE, INVENTDIMID, ACCOUNTCODE
I_315ITEMIDX    nonclustered located on PRIMARY    PARTITION, DATAAREAID, RELATION, ITEMCODE, ITEMRELATION, CURRENCY, ACCOUNTCODE, ACCOUNTRELATION, UNITID, QUANTITYAMOUNTFROM, FROMDATE
I_315PRICEDISCIDX    clustered, unique located on PRIMARY    PARTITION, DATAAREAID, RELATION, ITEMCODE, ITEMRELATION, ACCOUNTCODE, ACCOUNTRELATION, CURRENCY, INVENTDIMID, UNITID, QUANTITYAMOUNTFROM, RECID, FROMDATE, AGREEMENTHEADEREXT_RU, KRFSALESORDERCATEGORY
I_315RECID    nonclustered, unique, primary key located on PRIMARY    RECID
  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Dynamics AX 2012 pricedisctable clustered index

    No huge concerns, just aware that the way SQL Server indexing works is that all the clustered index fields will also need to be included in all non-clustered indexes.   But that's fine, if it's standard configuration I'll leave it unless we can prove that this is causing problems, thanks for checking.

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: Dynamics AX 2012 pricedisctable clustered index

    Hi,

    Yes, index I_315PRICEDISCIDX is marked as clustered in the standard. I think, there is no point to use RecId index as clustered as the most read load comes from the search function and I believe all fields from I_315PRICEDISCIDX are used as search criteria. But I would be interested to hear your concerns.

  • patrickjolliffe Profile Picture
    patrickjolliffe 20 on at
    RE: Dynamics AX 2012 pricedisctable clustered index

    Thanks all, OK looks like we've got some customisations, but what I'm trying to ascertain is whether it's standard for the clustered index to be I_315PRICEDISCIDX rather than the primary key I_315RECID.  I don't have access to application side unfortunately, I'm (just) a DBA :)

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: Dynamics AX 2012 pricedisctable clustered index

    Hi,

    Agree with Kevin. KRFSALESORDERCATEGORY looks like custom fields and others are part of OOB index.

  • Suggested answer
    zhifeng Profile Picture
    zhifeng on at
    RE: Dynamics AX 2012 pricedisctable clustered index

    In my standard R3 CU13, i don't have KRFSALESORDERCATEGORY, this looks like a customization. BUt to confirm please got to your AOT to check layer.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    RE: Dynamics AX 2012 pricedisctable clustered index

    Hi Patrickjolliffe,

    You shared some SQL statements, but can you probably check the index node on the table in the AOT? It looks like you shared information about all indexes you have on this table. That is more convenient to check what is setup and if it is the standard or not. If the index is the same as of the SYP layer for all layers where this table is used, then it is the standard.

    Can you tell what type of performance issue you have and how potentially this index is related?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans