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) / Fresh install of AX201...
Microsoft Dynamics AX (Archived)

Fresh install of AX2012 VM, non-stop CPU usage > 85% (SQL Server = 50%)

(0) ShareShare
ReportReport
Posted on by TrevorG

I've just done a fresh install of the AX2012R3CU9 VM and am seeing extremely excessive non-stop CPU usage (85% to 99%), with Sql Server being almost constantly 50%.  This has been running for hours like this.

Are there any known issues that might cause this?

Is there a document that might describe some recommended approaches for debugging this issue?  Can I take some sort of an approach of shutting down AX components/services/jobs until I see the excessive CPU drop?

One somewhat relevant post I came across is this:

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

...where it says: "The AX database has an sp_axwho stored procedure which could reveal that who is executing that specific query.  If the SP is missing then you probably should have bigger concerns about your database integrity."

Well, that SP is missing from my [MicrosoftDynamicsAX] database, but it seems incredibly difficult to believe that the fresh out of the box VM (which seems to be mostly functional, I can navigate around screens and see data, etc without encountering any errors) is "broken".

I've been googling this all day and it is very disheartening, if anyone has any advice I would be very grateful.  And if this is a bit more complicated, if someone would be available for a remote screen share debugging session, at least to just take a quick look, I'd be MORE than happy to paypal payment for that, I absolutely must get this problem solved so I can move on with learning, and fast.

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,970 Most Valuable Professional on at

    How many processor cores have you assigned to your VM? What kind of CPU it is?

  • TrevorG Profile Picture
    TrevorG on at

    I've assigned 4 processors and 12 GB RAM.  It's a bit laggy, you can definitely feel that something is happening in the background, but it is still relatively responsive, I don't think it is lacking resources.

    I ran a trace into a table and then ran the following query against that table, nothing terrible is jumping out at me, but then I'm not really sure what I should be looking for.

    (I'm fairly technically competent if you're able to give me any hints of what I should be looking at....)

    use MicrosoftDynamicsAX

    SELECT

    [CPU]

    ,[Duration] --microseconds

    ,[Duration]/1000.0 as DurationInMS

    ,[Duration]/1000000.0 as DurationInSec

    ,[Duration]/1000000/60.000 as DurationInMin

    , datediff(Second,StartTime,EndTime) as seconds

    ,[Reads]

    ,[Writes]      

    ,[ClientProcessID]

    ,[EventClass]

    ,[TextData]

    ,[ApplicationName]

    ,[NTUserName] ,[LoginName]

    ,[SPID]

    ,[StartTime] ,[EndTime] ,[BinaryData]

     FROM [MicrosoftDynamicsAX].[dbo].[__TWG_TRACE]

     where TextData is not null

     order by duration desc

  • Martin Dráb Profile Picture
    Martin Dráb 237,970 Most Valuable Professional on at

    First of all, look at known optimization techniques for AX VMs (such as Disable Retail services in Dynamics AX 2012 R3 Virtual Machine and Optimizing the Dynamics AX 2012 Demo VM for Windows 8 and Windows Server 2012).

  • TrevorG Profile Picture
    TrevorG on at

    Thanks Martin,

    Both of those I've already done, I had been working from this guide:  

    guyterry.wordpress.com/.../pimp-your-ax-demo-vm

    By the way I missed one of your previous questions, the host is an i& 920 with 24GB ram with Windows 10 Pro (also a fresh install) running on a 500GB Samsung 850 EVO SSD.

  • TrevorG Profile Picture
    TrevorG on at

    i7 920

  • Martin Dráb Profile Picture
    Martin Dráb 237,970 Most Valuable Professional on at

    Regarding SQL Server, my first place to look at would be Activity Monitor.

  • TrevorG Profile Picture
    TrevorG on at

    Thanks so much for your help, especially considering it is like doing surgery wearing mitts (with a clueless assistant on top of it).

    I'm guessing "Recent Expensive Queries" would be the first place to look?

    http://i.imgur.com/42LlFHW.png

    424755 ms (7 minutes!) certainly looks like it could be the problem.  

    Does the SQL give you any hint what is going on here?

    select T.UserKey, T.NAME, T.Alias, T.Domain, T.SecurityID, MAX(T.GeneralLedgerRoleType) GeneralLedgerRoleType, T.CompanyKey, T.IsEnabled

    from (

    select ui.RECID UserKey, ui.NAME, ui.NETWORKALIAS Alias, ui.NETWORKDOMAIN Domain, SID SecurityID,

    CASE st.AOTNAME

    WHEN 'SysSecSecurityMaintain' THEN 5

    WHEN 'LedgerBalanceSheetDimMaintain' THEN 4

    WHEN 'LedgerFinancialJournalReportBGenerate' THEN 3

    WHEN 'LedgerBalanceSheetDimPrintGenerate' THEN 3

    WHEN 'LedgerViewFinancialStatement' THEN 2

    END GeneralLedgerRoleType, l.RECID CompanyKey, ui.ENABLE IsEnabled

    from MicrosoftDynamicsAX..USERINFO ui

    inner join MicrosoftDynamicsAX..SECURITYUSERROLE sur on ui.ID = sur.USER_ and ui.PARTITION = sur.PARTITION

    inner join MicrosoftDynamicsAX_Model..SECURITYROLE sr on sur.SECURITYROLE = sr.RECID

    and (GETUTCDATE() between sur.VALIDFROM and sur.VALIDTO OR

    (sur.VALIDFROM = '1/1/1900' and sur.VALIDTO = '1/1/1900'))

    inner join MicrosoftDynamicsAX..SECURITYUSERROLECONDITION c on c.SECURITYUSERROLE = sur.RECID and c.PARTITION = sur.PARTITION

    inner join MicrosoftDynamicsAX..SECURITYROLEALLTASKSVIEW v on v.SECURITYROLE = sr.RECID

    inner join MicrosoftDynamicsAX_Model..SECURITYTASKEXPLODEDGRAPH g on g.SECURITYTASK = v.SECURITYTASK

    inner join MicrosoftDynamicsAX_Model..SECURITYTASK st on g.SECURITYSUBTASK = st.RECID

    inner join (Select l.RECID, l.PARTITION, ci.DATAAREA from MicrosoftDynamicsAX..LEDGER l

    inner join MicrosoftDynamicsAX..DIRPARTYTABLE ci on ci.PARTITION = l.PARTITION and l.PRIMARYFORLEGALENTITY = ci.RECID) l on ui.PARTITION = l.PARTITION and l.DATAAREA = c.DATAAREA

    Where

    ui.EXTERNALUSER = 0 AND

    ui.[SID] != '' AND

    ui.[ACCOUNTTYPE] = 0 AND

    sur.ASSIGNMENTSTATUS = 1 AND

    st.AOTNAME in (

    'SysSecSecurityMaintain',

    'LedgerBalanceSheetDimMaintain',

    'LedgerFinancialJournalReportBGenerate',

    'LedgerBalanceSheetDimPrintGenerate',

    'LedgerViewFinancialStatement')

    union all

    -- get users and their assigned tasks for all companies where the task hasn't been constrained to a company

    select ui.RECID UserKey, ui.NAME, ui.NETWORKALIAS Alias, ui.NETWORKDOMAIN Domain, SID SecurityID,

    CASE st.AOTNAME

    WHEN 'SysSecSecurityMaintain' THEN 5

    WHEN 'LedgerBalanceSheetDimMaintain' THEN 4

    WHEN 'LedgerFinancialJournalReportBGenerate' THEN 3

    WHEN 'LedgerBalanceSheetDimPrintGenerate' THEN 3

    WHEN 'LedgerViewFinancialStatement' THEN 2

    END GeneralLedgerRoleType, l.RECID CompanyKey, ui.ENABLE IsEnabled

    from MicrosoftDynamicsAX..USERINFO ui

    inner join MicrosoftDynamicsAX..SECURITYUSERROLE sur on ui.ID = sur.USER_ and ui.PARTITION = sur.PARTITION

    inner join MicrosoftDynamicsAX_Model..SECURITYROLE sr on sur.SECURITYROLE = sr.RECID

    and (GETUTCDATE() between sur.VALIDFROM and sur.VALIDTO OR

    (sur.VALIDFROM = '1/1/1900' and sur.VALIDTO = '1/1/1900'))

    inner join MicrosoftDynamicsAX..SECURITYROLEALLTASKSVIEW v on v.SECURITYROLE = sr.RECID

    inner join MicrosoftDynamicsAX_Model..SECURITYTASKEXPLODEDGRAPH g on g.SECURITYTASK = v.SECURITYTASK

    inner join MicrosoftDynamicsAX_Model..SECURITYTASK st on g.SECURITYSUBTASK = st.RECID

    inner join (Select l.RECID, l.PARTITION from MicrosoftDynamicsAX..LEDGER l

    inner join MicrosoftDynamicsAX..DIRPARTYTABLE ci on ci.PARTITION = l.PARTITION and l.PRIMARYFORLEGALENTITY = ci.RECID) l on ui.PARTITION = l.PARTITION

    Where

    ui.EXTERNALUSER = 0 AND

    ui.[SID] != '' AND

    ui.[ACCOUNTTYPE] = 0 AND

    sur.ASSIGNMENTSTATUS = 1 AND

    st.AOTNAME in (

    'LedgerBalanceSheetDimMaintain',

    'LedgerFinancialJournalReportBGenerate',

    'LedgerBalanceSheetDimPrintGenerate',

    'LedgerViewFinancialStatement',

    'SysSecSecurityMaintain')

    and not exists (select 1 from SECURITYUSERROLECONDITION c where c.SECURITYUSERROLE = sur.RECID and c.PARTITION = sur.PARTITION)

    union all

    -- get all administrators for all companies where the admin's aren't limited to specific companies

    select ui.RECID, ui.NAME, ui.NETWORKALIAS, ui.NETWORKDOMAIN, SID, 5 RoleType, l.RECID, ui.ENABLE IsEnabled

    from MicrosoftDynamicsAX..USERINFO ui

    inner join MicrosoftDynamicsAX..SECURITYUSERROLE sur on ui.ID = sur.USER_ and ui.PARTITION = sur.PARTITION

    inner join MicrosoftDynamicsAX_Model..SECURITYROLE sr on sr.RECID = sur.SECURITYROLE

    and (GETUTCDATE() between sur.VALIDFROM and sur.VALIDTO OR

    (sur.VALIDFROM = '1/1/1900' and sur.VALIDTO = '1/1/1900'))

    inner join (Select l.RECID, l.PARTITION from MicrosoftDynamicsAX..LEDGER l

    inner join MicrosoftDynamicsAX..DIRPARTYTABLE ci on ci.PARTITION = l.PARTITION and l.PRIMARYFORLEGALENTITY = ci.RECID) l on ui.PARTITION = l.PARTITION

    where

    ui.EXTERNALUSER = 0 AND

    ui.[SID] != '' AND

    ui.[ACCOUNTTYPE] = 0 AND

    sur.ASSIGNMENTSTATUS = 1 AND

    AOTNAME in ('SysSecSecurityAdministrator')

    )

    T

    Group by T.UserKey, T.NAME, T.Alias, T.Domain, T.SecurityID,  T.CompanyKey, T.IsEnabled

    order by T.CompanyKey

  • TrevorG Profile Picture
    TrevorG on at

    -- THIS WILL GIVE ME THE SPID

    ;with x as (

    Select spid,hostname,program_name,nt_username,loginame,cmd, t.text from sys.sysprocesses

    CROSS APPLY( select text from sys.dm_exec_sql_text(sql_handle))t

    )

    select * from x

    where text like 'select T.UserKey, T.NAME%'

    So I actually see two instances of this query running, spid's: 192 and 418

    Hostname: AX2012RA

    program_name: .Net SqlClient Data Provider

    nt_username: Admin

    loginname: CONTOSO\Admin

    But not sure what to do next....

  • TrevorG Profile Picture
    TrevorG on at

    Going to try this:

    stackoverflow.com/.../who-is-executing-this-particular-query

    translate.google.com/translate;hl=en&prev=search&rurl=translate.google.com&sl=fr&sp=nmt4&u=www.mathdax.ca/.../dynamics-ax-2012-management-reporter.html

  • Martin Dráb Profile Picture
    Martin Dráb 237,970 Most Valuable Professional on at

    I think this is related to copying user permissions to Management Reporter.

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