| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2009-02-11 : 12:46:23
|
| When setting MAXDOP at the server level what does this actually mean? I always thought it gave you the ability to restrict the number of processors any one SQL process can use so for example if you have an 8 proc server and set MAXDOP to 5, no process could use more then 5 CPUs at a time. However, I was told yesterday it does not limit the number of CPUs available to a process, but it restricts process threads. But aren't they the same thing? I know there are sub-threads that you can see by looking at the ecid column in sysprocesses, so is that what you are really restricting when using MAXDOP or are you restricting the number of procs?Thanks, Dave |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-11 : 22:41:36
|
| MAXDOP is Max degree of parallelism, you cannot restrict the number of processors. I think Booksonline has very good explanation of MAXDOP. |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-02-12 : 01:38:18
|
| BOL's expalanation is what caused me to believe the number of processors can be restricted. 2-64 Limits the number of processors to the specified value. Fewer processors may be used depending on the current workload. If a value larger than the number of available CPUs is specified, the actual number of available CPUs is used. andComputers that have multiple microprocessors or CPUs can execute one thread per CPU at the same time. For example, if a computer has eight CPUs, it can execute eight threads at the same time. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-12 : 07:03:27
|
quote:
Originally posted by saurabhsrivastava
MAXDOP is Max degree of parallelism, you cannot restrict the number of processors. I think Booksonline has very good explanation of MAXDOP.
Correct. It has to do with Affinity Mask Configuration. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-12 : 16:04:07
|
MAXDOP does restrict the number of processors, but it is at the query level and not at the SQL Server process level. From BOL:quote:
When SQL Server 2005 runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
Also check out this article on what value to set it to: http://support.microsoft.com/default.aspx?scid=kb;EN-US;329204We just moved to a 4 quad-core CPU system and are seeing very high CPU utilization. We are going to change the value to 8 per the article since the OS sees 16 CPUs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-02-12 : 16:07:15
|
| Cross posthttp://www.dbforums.com/microsoft-sql-server/1638197-help-understanding-maxdop-setting.html |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-12 : 16:09:18
|
quote:
Originally posted by tkizer
MAXDOP does restrict the number of processors, but it is at the query level and not at the SQL Server process level. From BOL:quote:
When SQL Server 2005 runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
Also check out this article on what value to set it to: http://support.microsoft.com/default.aspx?scid=kb;EN-US;329204We just moved to a 4 quad-core CPU system and are seeing very high CPU utilization. We are going to change the value to 8 per the article since the OS sees 16 CPUs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I guess OP is looking at Server Level. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-02-13 : 18:49:45
|
| I'm referring to the server-wide setting. Nice link Tara. It was helpful. Do the recommendations for an optimal MAXDOP setting pertain to total physical processors or do you also factor in the cores. For example, if I have a 2 CPU quad-core server is the Microsoft recommendation to set MAXDOP to 2 or to 8?I'll Google for an answer, but thought I would check with you also.In regards to the cross post MC what wrong with that? I cross post all of the time because each forum has a different group of members, with some overlap, and it is a good way to get a variety of opinions and facts on a topic.Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-13 : 18:58:37
|
I got the link from a Microsoft engineer just this week. It's a coincidence that I also was wondering about the MAXDOP setting, so I was inquiring through our "dedicated support engineer". It is my understanding that it pertains to the cores as that's how many processors SQL Server can see. The only exception would be a hyper-threaded system: quote:
For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
Hyper-threaded systems aren't as common anymore.There is no problem with cross-posting to other sites, but it would be very helpful if you could show us the cross-posts by posting the links for them. That way we can see if something has already been discussed and not have to waste our time typing the same answer out.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-14 : 23:04:02
|
quote:
Originally posted by tkizer
MAXDOP does restrict the number of processors, but it is at the query level and not at the SQL Server process level. From BOL:quote:
When SQL Server 2005 runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
Also check out this article on what value to set it to: http://support.microsoft.com/default.aspx?scid=kb;EN-US;329204We just moved to a 4 quad-core CPU system and are seeing very high CPU utilization. We are going to change the value to 8 per the article since the OS sees 16 CPUs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I never recommend changing MAXDOP at server level at first place, it affects the whole system. If server is dedicated to run SQL Server WHY should one restrict the processor or force queries to tun on certain number of processors. Recommendations:1) Find out the queries benefit from Parallelism and set MAXDOP at query level. Changing MAXDOP at server level will be applicalbe to all queries whereas all queries/operation may not benefit from that change. 2) In case of IO bound operations I would allocate 4-8 processors for IO operation based on tetsing and try to find out optimal number for my system. 3)I would never change system advanced settings based on articles; these are generic articles, not focused on specific error/warning messages. Moving to 4-quad core system caused high CPU utilization may be due to some other reason. How many CPU's system had previously? What was the CPU utilization? What was the speed of those processor, L1, L2 cache etc.?HBA card specification?Batch request/sec on older system?Batch request/sec on new system?TPC.org rating for both old and new processors |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-14 : 23:20:37
|
| Forgot to add to above post:It is not necessary that MAXDOP is always effective. For Example if you set maxdop to 8 and enough resources (Memory, IO, CPU) are not available on the server in that scenario your query may execute serially or use lesser number of processors, no matter what MAXDOP setting is. Regarding HYPERTHREADED system: Itanium system which are considered as a high end servers(processor code name Montavale) are still HYPERTHREAD capable, some customers use it some don't based on testing. Most of the time HYPERTHREADING degrades performance, not always. And I would say 20%-25% customers are getting benefits of HYPERTHREAD processors, again testing decides which side of fence one falls. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-02-17 : 11:49:35
|
| Nothing is wrong with it. The members may want to see what other answers you are getting. |
|
|
|
vivienxing
Starting Member
3 Posts |
Posted - 2009-02-28 : 12:03:46
|
quote:
Originally posted by tkizer
MAXDOP does restrict the number of processors, but it is at the query level and not at the SQL Server process level.
Tara, Can you please explain further what does "SQL Server Process" mean here? Or any resources can help to better understand the above quote? Is "checkpoint", "backup" or "reindex" SQL Server process or not? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Help Understanding MAXDOP