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
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.
