page contents

What is my data distribution by company(PARTITON and or DATAAREAID) for a specific Dynamics AX table?


 

If you have ever asked yourself the question “What is my data distribution by company(PARTITION an or DATAAREAID) for a specific Dynamics AX table?”, wonder no longer.  Attached is a TSQL Stored Procedure you can add to your Dynamics AX database (4.0, 2009, 2012, 2012 R2…) and then run it against any table in the Dynamics AX database that has includes the DATAAREAID column and it will should you the data distribution for all existing companies.  When you run the Stored Procedure against a specific table the output will look like the example below.

Example: Versions earlier than Dynamics AX 2012 R2

 

TABLE_NAME PARITION_KEY DATAAREAID TOTAL_ROWS ROW_COUNT DATAPAGES
INVENTSUM NA mfi 106503 106385 10626
INVENTSUM NA xxx 106503 118 12

 

Example: Version Dynamics AX 2012 R2 and later(?)

 

TABLE_NAME PARITION_KEY DATAAREAID TOTAL_ROWS ROW_COUNT DATAPAGES
INVENTSUM initial cee 4962 36 3
INVENTSUM initial ceed 4962 83 8
INVENTSUM initial ceu 4962 4524 427
INVENTSUM initial cfu 4962 40 4
INVENTSUM ext ext 4962 68 6
INVENTSUM cube dat 4962 211 20

 

The TSQL Stored Procedure is attached to this BLOG post, to build it just run the script against the Dynamics AX Database.

The syntax to run the Stored Procedure is as follows

EXEC sp_datadistrbution_by_parition_dataareaid_by_table ‘XXXXXXXXXX’

where XXXXXXX is the Dynamics AX Table name

I use this to determine whether Parameter Sniffing may be an issue or not.

 

DATA_DISTRIBUTION_BY_COMPANY_FOR_TABLE.SQL

Comments (4)
  1. Michael DeVoe says:

    Yes, at the last minute I decided most people do not know what dpages are so I changed it to percentage to make it easier to interoperate.  🙂

  2. M. Go says:

    Thanks for the script, Michael.  My results are a little different than yours.  In the last column, I have "Percentages" instead of Pages.

    TABLE_NAME PARITION_KEY DATAAREAID TOTAL_ROWS ROW_COUNT PERCENTAGE

    INVENTSUM initial 201 33722 1.0000000000 0.0000296542

    INVENTSUM initial 301 33722 17280.0000000000 0.5124251231

    INVENTSUM initial 303 33722 7873.0000000000 0.2334677659

    INVENTSUM initial 304 33722 1088.0000000000 0.0322638040

    INVENTSUM initial 305 33722 3423.0000000000 0.1015064350

    INVENTSUM initial 306 33722 4056.0000000000 0.1202775636

    INVENTSUM initial 307 33722 1.0000000000 0.0000296542

  3. Sorry, I meant "The Script should NOW be fixed".

  4. F. Peters says:

    Hi,

    seems very interesting. But the attached SQL Script is empty?

Comments are closed.

Skip to main content