2

I have a 8 core VM machine running SQL Server 2012. The current setting of MAXDOP is set as 5. There is recommendation from my colleagues to change the MAXDOP to 5 for better performance as there has been complaints on the same..

I might be very low on info in this question. But just curious, wont setting MAXDOP to 0 cause to degrade since all processors might not be needed for all the queries right? And is it right to set it as '0' rigth away?

Inputs please

  • Depends on the type of work load as well. Are you running a OLTP or data warehouse? Are you tuning specific queries or changing the configuration of the server? – Daniel Hutmacher Jan 9 '16 at 7:21
  • 1
    Current is 5 and recommendation is 5! Please edit the question to correct. – Michael Green Jan 9 '16 at 10:55
2

This totally depends upon the load on the system, if its OLAP, OLTP or somewhere in between. What we have in our system is , generally higher MAXDOP for OLAP and lower, i.e 0 and 1 set for OLTP workload systems.

But above depends totally on the queries as well along with hardware configuration in terms of logical processors, hyper threading is enabled and NUMA node set up.

A good read on better understanding would be Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Also, what has helped me in the past is following the codes from the discussion on What is a good, repeatable way to calculate MAXDOP on SQL Server?, the one from @Kin and @Max , really helps you getting a start as what can be used initially, thereafter you need to monitor and conclude what setting suits best for you're environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.