Setting correct MAX DOP for a SQL instance with 16 sockets and 32 Virtual processors (SQL Server 2008 R2)

Question

Dear Experts,

On this SQL instance, the MaxDOP is set to 8 and cost threshold for parallelism is 200.
The server has 16 sockets and 32 Virtual processors, it is a virtual server running on its own dedicated host.

Version is SQL Server 2008 R2 (Standard) running on Windows Server 2012 Standard.

Min memory is 64 GB and Max memory set on this instance is 67 GB but available on server is 128 GB. As per MS, there is a memory limit on this edition (capped at 64 GB). ms143685(v=sql.105)

On checking wait stats, the highest wait is on CXPacket and second is on resource_semaphore.

Could you please advise the correct MAX DOP setting and cost threshold for parallelism for this server, is there any?

Thanks

Answer 1

Hi @mo boy ,

Quote from MS document Configure the max degree of parallelism Server Configuration Option;

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I want to add some considerations...

you wrote that you are running a SQL Server 2008 R2 STANDARD Edition

SQL Server 2008 R2 Standard Edition also has a license limit of four processor sockets.

https://www.sqlskills.com/blogs/glenn/upgrading-sql-server-day-6/

So your SQL Server will never use those amount of cores... as it is limited to 4 sockets (in your configuration => only 8 cores)

I would expect that your MaxDoP has to be set to 2
Multiple NUMA nodes - less than 8 per NUMA node => MaxDoP = Cores per NUMA node = 2

Answer 3

There are only certain reasons to change MAX DOP. Do not change MAX DOP just because of "CXPacket" wait state.

"CXPacket" is not a wait state you need to worry about. That just means parallel threads are waiting on other threads to complete. This is completely normal and expected behavior.