AX On SQL Server 2008

  • Evening All,

    Just inherited an AX 2009 sql server, that has intermittent performance problems.

    I've seen some people suggesting that setting maxdop to 1 helps significantly.

    I am seeing 75% CXPACKET waits on the server.

    Do you think Setting MAXDOP to 1 will help any?

    The SQL Server only hosts this DB so it is fairly isolated.

    Cheers
    Alex

  • alex.sqldba - Saturday, June 16, 2018 2:30 PM

    Evening All,

    Just inherited an AX 2009 sql server, that has intermittent performance problems.

    I've seen some people suggesting that setting maxdop to 1 helps significantly.

    I am seeing 75% CXPACKET waits on the server.

    Do you think Setting MAXDOP to 1 will help any?

    The SQL Server only hosts this DB so it is fairly isolated.

    Cheers
    Alex

    What are the relevant configuration settings and the specs of the server, i.e. cost threshold for parallelism?
    😎

  • Also, you may have some queries running that have MAXDOP hints in them. 

    Is this something you are able to check ?

  • Apologies for my slow reply: Settings are the default as follows:

    Cost Threshold for Parallelism = 5
    Max Degree of Parallelism = 0

    There are no explicit MAXDOPS set that I can see via Profiler or Spotlight.

  • alex.sqldba - Tuesday, June 19, 2018 4:53 AM

    Apologies for my slow reply: Settings are the default as follows:

    Cost Threshold for Parallelism = 5
    Max Degree of Parallelism = 0

    There are no explicit MAXDOPS set that I can see via Profiler or Spotlight.

    Would as a minimum set a higher Cost Threshold for Parallelism, start at 50 I'd suggest.
    MS Premier Field Engineers, on AX suggested we set ours to MAXDOP = 1 on the instance level, for AX SQL Instances, your's might be different though.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • How does one best guess or arrive at a value for Cost Threshold for Parallelism?

    Cheers

  • alex.sqldba - Friday, June 22, 2018 3:44 AM

    How does one best guess or arrive at a value for Cost Threshold for Parallelism?

    Cheers

    It is very common to start with the value 50 and adjust it further based on monitoring and behaviour. It is impossible to guess the which is the right value as it depends on so many factors.
    😎 

    Question, what is the configuration of the tempdb and are you seeing any excessive waits there?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply