maximum degree of parallelism

Last Post 29 May 2014 05:24 AM by rm. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ctseah
New Member
New Member

--
22 May 2014 07:13 PM
Hi,

Would like to find out what value should I set for maximum degree of parallelism.

Current default value = 0 (imply maximum parallelism; using all CPUs to the maximum).

I have 2 physical CPU quad core, so in my task manager, I see 8 CPUs.

There is a high CXPACKET waittype percentage of 60% to 70% taken from sys.dm_os_wait_stats.
Due to this there is a performance impact.

Thanks in advance.
rm
New Member
New Member

--
23 May 2014 05:51 AM
Should set to 3 if they are NUMA.
ctseah
New Member
New Member

--
23 May 2014 08:00 AM

Hi rm,

May I know why I should set MAXDOP = 3 ?
And how can I check is NUMA being configured in my system ?

After reading up, I am a bit clearer now in term of cores and processors now.
I have 2 processors each is a quad core.
So in my task manager I see 8 cores.
Technically it sound more accurate I hope.

Thanks in advance

Thanks
Regards
rm
New Member
New Member

--
24 May 2014 10:19 AM
You can find if processors are NUMA or not in task manager. For NUMA, it has independent cache and bus. If parallel process cross NUMA, machine has to move cached data between NUMA that affects performance. Reason to set maxdop to 3 is that you like to leave a free process in each NUMA to accept new session.
ctseah
New Member
New Member

--
28 May 2014 11:27 PM

Hi ,

In task manager, under the 'view' tab --> CPU history, I do not see anything about NUMA listed there.

Thus I feel that NUMA is not configured or existed in my system. Right ?

In view of this what Maxdop should I set ?

Thanks in advance

thanks
regards
rm
New Member
New Member

--
29 May 2014 05:24 AM
Can start with 3, that way sql can have 2 parallel processes and still has 2 processors for new sessions.
You are not authorized to post a reply.

Acceptable Use Policy