hardware effect on maximum degree of parallelism / sql server 2008

According to my understanding, the default maximum degree of parallelism is set to zero. This enables SQL Server to determine the maximum degree of parallelism. Setting the maximum degree of parallelism to zero allows SQL Server to use all the available processors, i.e. up to 64-processors. when adding a lot of hardware (from a single processor to a multi-process system) to a sql server 2008 system, does the maximum degree of parallelism automatically update to the maximum degree of parallelism based on the new hardware?

How long does it take for the new maximum degree of parallelism to take effect? Please just consider the variables provided. Suggesting others is fine.

I'm hoping someone with experience with this provides some feedback.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul MacDonaldDirector, Information SystemsCommented:
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
I'm not certain I understand the question, but I'll try to answer what I think you're asking.
First, I assume you are really only asking about the case where MaxDOP is set to 0, and the number of CPUs available to the system changes.

In most cases, adding a CPU would require the hardware to be turned off.  When the OS and SQL loads, assuming CPU affinities are not set, the additional processors are considered immediately.

In cases where Hot Add CPUs are available (I find this more frequently with virtual machines), then assuming the virtual hardware model accepts the added CPU, and the Operating System handles the added CPU, then SQL does not immediately consider the CPU for it's use.
If you are running the Enterprise or Datacenter edition of SQL 2008 (or 2008 R2), and 64 bit... you can run RECONFIGURE to get SQL to make use of the additional CPU(s).

Now, that said, SQL won't automatically start breaking up every query to spread the query across several CPUs.  The Query Optimizer will at least consider parallel plans, and only make use of one if it determines that a given query is a good candidate for parallelism.  If you have some old execution plans in cache that don't make use of parallelism, you may find SQL not making use of the additional CPU when it might benefit from it.  Clearing the cache, obviously, would fix that possible issue... and of course... if the CPU is added 'cold', it's never even a possible issue.  :-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
>>does the maximum degree of parallelism automatically update to the maximum degree of parallelism based on the new hardware?
    Yes. SQL Server will use the new CPU added to your server as long as the operating system can see it, and as long as the "Affinity Mask" is set to 0 within SQL Server. You need to run EXEC master.dbo.sp_configure 'affinity mask' from a query window and look at the results of the config_value. As long as it's 0, then SQL will automatically use the new CPU added.

>>How long does it take for the new maximum degree of parallelism to take effect?
Ideally this will come into effect as soon as the SQL Server sees the newly added CPU.

Hope this helps!
pae2Author Commented:
Razmus, thanks so much for the great explanation!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.