SQL Server Performance Forum – Threads Archive
"Maximum degree of parallelism" & # of CPUs
Hello everyone,<br /><br />My prod system has 4 physical processors (8 logical: CPU 0 to CPU 7). I set the "Maximum degree of parallelism" to 4, (from the default of <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, because there were some very large queries that were degrading response times for other users in the system…I figured by setting it to 4, it would reduce the chance of SQL Server trying to perform parallelism on the same physical processor, for which I can’t imagine a benefit.<br /><br />The server is dedicated to SQL Server, and I am wondering if there would be any benefit to "deselecting" logical CPUs 1, 3, 5, and 7?<br /><br />Curious,<br /><br />Eric<br /><br /><pre><br />EXEC sp_configure<br />GO<br /><br />name minimum maximum config_value run_value <br />———————————– ———– ———– ———— ———– <br />affinity mask -2147483648 2147483647 0 0<br />allow updates 0 1 0 0<br />awe enabled 0 1 1 1<br />c2 audit mode 0 1 0 0<br />cost threshold for parallelism 0 32767 5 5<br />Cross DB Ownership Chaining 0 1 0 0<br />cursor threshold -1 2147483647 -1 -1<br />default full-text language 0 2147483647 1033 1033<br />default language 0 9999 0 0<br />fill factor (%) 0 100 0 0<br />index create memory (KB) 704 2147483647 0 0<br />lightweight pooling 0 1 0 0<br />locks 5000 2147483647 0 0<br />max degree of parallelism 0 32 4 4<br />max server memory (MB) 4 2147483647 5120 5120<br />max text repl size (B) 0 2147483647 65536 65536<br />max worker threads 32 32767 255 255<br />media retention 0 365 0 0<br />min memory per query (KB) 512 2147483647 1024 1024<br />min server memory (MB) 0 2147483647 5120 5120<br />nested triggers 0 1 1 1<br />network packet size (B) 512 65536 4096 4096<br />open objects 0 2147483647 0 0<br />priority boost 0 1 1 1<br />query governor cost limit 0 2147483647 0 0<br />query wait (s) -1 2147483647 -1 -1<br />recovery interval (min) 0 32767 0 0<br />remote access 0 1 1 1<br />remote login timeout (s) 0 2147483647 20 20<br />remote proc trans 0 1 0 0<br />remote query timeout (s) 0 2147483647 600 600<br />scan for startup procs 0 1 0 0<br />set working set size 0 1 1 1<br />show advanced options 0 1 1 1<br />two digit year cutoff 1753 9999 2049 2049<br />user connections 0 32767 0 0<br />user options 0 32767 40 40<br /></pre><br />??? I’m not sure why you would do that. If you’re going to go down that route, just disable hyperthreading all together. I wouldn’t leave it on if it’s a dedicated SQL Server. I don’t think the OS benefits would be worth it in that case. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks for the reply, derrickleggett. Disabling the hyperthreading is probably a much better route to take. [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
Wait for Joe Chang oppinion. (you know what I mean Derrick[<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]).<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS†with no warranties for accuracy.</font id="size1"><br /><br />
lol Yep. I love the discussions though. He’s a smart guy. I’ve been talking to a log of the MS people here. It’s interesting to see the varying opinions everyone has. Personal experience combined with technical expertise always lead to an interesting exchange of ideas. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
You are rigth, I respect Joe, all articles are wonderfull.
And in every discussion between you and Joe, I learn something new. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>