braju (9/21/2012)
I have a question on Degree of Parallelism. I basically want to know the best practice or guidelines to set the degree of parallelism. I looked at the Microsoft Sites for some guide which I found but only confusing.So let me start with a simple queston.
If we have set the degree of parallelism to just 1, we suppress the ability of SQL server running query in parallel when it needs to be. Will that impact heavy processing stored procedures or SQLs? Because we do see some issues such as timeouts etc in the batch process we run during the night.
We are perfomance tuning at the same time and doing what we can do with indexes statistics, but still that does not help.
What are the disadvantages of setting the degree of parallelism to 1?
This is something i see quite a lot on systems i have encountered over the years.
If you're going to set MAXDOP server wide to 1 why not just rip out all the servers CPUs bar 1 and save yourself some licensing costs too 😀
Rather than restricting the CPU resources, try setting the Cost Threshold for Parallelism to an alternate higher value.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉