June 16, 2018 at 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
June 17, 2018 at 3:45 am
alex.sqldba - Saturday, June 16, 2018 2:30 PMEvening 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?
😎
June 18, 2018 at 1:52 pm
Also, you may have some queries running that have MAXDOP hints in them.
Is this something you are able to check ?
June 19, 2018 at 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.
June 19, 2018 at 4:55 am
alex.sqldba - Tuesday, June 19, 2018 4:53 AMApologies for my slow reply: Settings are the default as follows:Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 0There 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
June 22, 2018 at 3:44 am
How does one best guess or arrive at a value for Cost Threshold for Parallelism?
Cheers
June 22, 2018 at 4:04 am
alex.sqldba - Friday, June 22, 2018 3:44 AMHow 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