web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Need DBA help with com...
Microsoft Dynamics AX (Archived)

Need DBA help with compile question

(0) ShareShare
ReportReport
Posted on by Kevin Murray 1,005

Hello,

I'm having a major problem with the length of time it's

taking to compile product models in AX2009 since a particular

DBA left, his replacement is not very AX-specific.

As an example, one model has gone from 25 to 124 minutes, one

33 to 335 minutes, one 10 to 30 minutes. All models are having

the same increase. The DBA examined the indexes and feels the

problem is elsewhere, can anyone suggest some next steps to

find out what's going on?

Below is a sql extract of the PB* tables with the PB table,

IndexAvgFragmentationInPercent,the index, and the table's

total row count sorted descending.

Thanks

Kevin

Table                             AvgFragment   Index   Rows

_____________________________     ___________   _____   _______

            PBAREUSEBOMROUTE     30.3524732    01      8001673

            PBAREUSEBOMROUTE     10.0586559    02      8001673

       PBABOMROUTEOCCURRENCE     27.2232978    01      6031034

       PBABOMROUTEOCCURRENCE     25.8506304    02      6031034

                 PBATREENODE     29.6604741    01      0132589

                 PBATREENODE     32.3665893    02      0132589

                 PBATREENODE     29.2616226    03      0132589

                 PBATREENODE     18.0465116    04      0132589

         PBARULECODECOMPILED     09.9818512    01      0044871

               PBATREESIMPEL     15.9937888    01      0028777

                  PBATREEBOM     27.6226415    01      0025756

                  PBATREEBOM     21.3953488    02      0025756

            PBATREEINVENTDIM     16.2921348    01      0025756

                 PBATREECODE     22.0750552    01      0025547

                 PBATREECASE     19.2052980    01      0021584

            PBATABLEVARIABLE     28.8025890    01      0019404

            PBATABLEVARIABLE     12.7795527    02      0019404

            PBATABLEVARIABLE     23.4323432    03      0019404

            PBATABLEVARIABLE     31.8725100    04      0019404

                 PBARULELINE     10.8359133    01      0016987

                 PBARULELINE     31.5270936    02      0016987

                 PBARULELINE     09.6774194    03      0016987

              PBATREEINFOLOG     31.8518519    01      0006920

         PBARULEVARIABLELINE     11.2068966    01      0006711

                  PBAVERSION     92.5133690    01      0006199

                  PBAVERSION     95.7264957    02      0006199

                  PBAVERSION     11.5942029    03      0006199

                  PBAVERSION     10.5263158    04      0006199

               PBARULEACTION     38.6075949    01      0006055

               PBARULEACTION     18.5185185    02      0006055

               PBARULEACTION     18.9189189    18      0006055

               PBATREESWITCH     30.4347826    01      0004880

             PBARULEVARIABLE     82.0754717    01      0004247

             PBARULEVARIABLE     62.9213483    02      0004247

                     PBARULE     06.3291139    01      0003661

                     PBARULE     14.7058824    02      0003661

                     PBARULE     11.7647059    03      0003661

  PBARULEACTIONVALUECODEPARM     19.4029851    01      0003618

               PBARULECLAUSE     19.4805195    01      0003613

               PBARULECLAUSE     34.3750000    02      0003613

         PBARULEPBAID2CONSID     55.5555556    01      0003164

          PBARULEACTIONVALUE     50.5617978    01      0003030

          PBARULEACTIONVALUE     29.1666667    15      0003030

                PBATREETABLE     17.6470588    01      0002458

         PBARULELINECODEPARM     09.3023256    01      0002377

         PBARULELINECODEPARM     06.8965517    02      0002377

      PBARULEACTIONVALUECODE     65.5737705    01      0001935

      PBARULEACTIONVALUECODE     57.6923077    02      0001935

         PBATABLEVARIABLEVAL     33.3333333    01      0001469

         PBATABLEVARIABLEVAL     30.7692308    02      0001469

             PBARULELINECODE     68.1818182    01      0001247

             PBARULELINECODE     86.6666667    02      0001247

             PBARULELINECODE     78.9473684    03      0001247

              PBAVARIABLEVAL     22.2222222    01      0000687

              PBAVARIABLEVAL     25.0000000    02      0000687

              PBAVARIABLEVAL     14.2857143    03      0000687

               PBATABLEGROUP     10.0000000    01      0000684

               PBATABLEGROUP     16.6666667    02      0000684

               PBATABLEGROUP     12.5000000    03      0000684

               PBATABLEGROUP     12.5000000    04      0000684

          PBATABLEPRICESETUP     08.3333333    01      0000648

          PBATABLEPRICESETUP     12.5000000    02      0000648

                  PBATREEFOR     68.7500000    01      0000519

                 PBAVARIABLE     72.7272727    01      0000474

                 PBAVARIABLE     66.6666667    02      0000474

                PBATREEROUTE     33.3333333    01      0000379

                PBATREEROUTE     25.0000000    02      0000379

             PBATREEROUTEOPR     20.0000000    01      0000379

             PBATREEROUTEOPR     25.0000000    02      0000379

           PBARULELINESIMPLE     50.0000000    02      0000177

  PBATABLEVARIABLEDEFAULTVAL     66.6666667    01      0000131

                    PBATABLE     75.0000000    01      0000102

PBATABLEGENERATEITEMVARIABLES    50.0000000    01      0000086

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    Have you tired turning on SQL Server trace flags 1224, 4136, and 4199 in a replica environment to see if they help?

  • Kevin Murray Profile Picture
    Kevin Murray 1,005 on at

    I won't have a replica until late next week at the earliest, I'll try it then. Can I ask, would using the code profiler or sql trace in AX be of any use? X++ compiles seem relatively fine, PB compiles are definitely not. I guess the frag levels look ok? My DBA says the row counts are too low on most of these frag levels to justify rebuilding or reorganization. We don't put in a lot of fixes, the only event I can point to is one DBA left who had been here years (PB compiles were fine then) and other DBAs have come and gone since as the compile times have degraded over 6 months. It seems as if he was doing something the other's weren't. The new guys aren't super strong in AX.

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    I would argue that if the row counts are that small, that doing a reorg of the the index should already have been tried.  It can't hurt, it might help, and with smaller data sets it just takes a matter of seconds to rule it out.

    The easiest way, in my opinion, to find any SQL performance issue is to monitor the "top x" queries by cache plan while you are encountering the issue.  Generally this will tell you immediately which (hopefully) one or two queries are responsible for slowing down everything else.  For example, a single cache plan might be consuming 990 ms out of every 1000 ms of processor time, or tens or even hundreds of thousands of logical reads to accomplish what at a glance would seem to be a very simple statement.  The problem isn't always a matter of missing or poorly tuned indexes, though that is always the first thing you look at, for good reason.  It's low hanging fruit, and the rewards are high.

    This will also tell an experienced DBA whether the problem is SQL based at all.

  • Kevin Murray Profile Picture
    Kevin Murray 1,005 on at

    Just an update: The latest is that the Live environment is fine, but the Sandbox and Development instances are not. Something that takes 3 seconds in Live takes 1 minute 22 seconds in Development, and so on. Not sure how experienced he is with AX. Another DEV instance was delivered this week, same problem, so waiting on him. He ran a Profiler trace but has not mentioned anything useful from it, so I wrote back his latest instance wasn't useful and that he should compare Live with all the others, waiting to hear back. Hopefully it's not hardware 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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans