SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Degree of parallelism in SQL Server


Degree of parallelism in SQL Server

Author
Message
braju
braju
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 13
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?
scspl
scspl
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 59
Hi
Performance of MaxDop (maximum degree of parallelism) depends on your hardware configuration and if any other applications running on same server too. If you use parallelism, it will introduce some overhead to small queries. Now it depends on how many db instance you have and what type of operations (select queries & insert/update/delete queries) are being executed.

Regarding indexes, if it suites you, you may use maintenance plan to rebuild indexes on suitable intervals. However it may not solve your performance problems always.

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14943 Visits: 14396
The disadvantages of setting Max Degree of Parallelism to 1 is that queries that would have benefited from executing with a parallel plan will not be able to and that will likely reduce performance. In some cases it can make a previously parallelized process run orders of magnitude longer and monopolize a single-CPU at 100% in the process.

Was Max Degree of Parallelism set to 1 on your server or are you exploring the possibility of making a change?

If you're deciding whether to set it to 1 or evaluating whether that was the right decision consider leaving parallelism enabled at the default (i.e. set to 0) but raising the bar for parallel plan to be chosen by changing the Cost Threshold for Parallelism

Also know that with a Max Degree of Parallelism of 1 you can always override the setting for specific queries you know will benefit from executing with a parallel plan using the MAXDOP query hint.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19972 Visits: 17243
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 :-D
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" ;-)
braju
braju
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 13
Well, it was set to default one time, but we experienced some timeouts, further investigating the cause of the timeouts, we saw some OLTP processing such as Search stored procedures cause more CXPacket values. so we decided to set it to 1, but that does not help during batch processes in the night time, becasue we get CPU spiked at 100% and make the system unresponsive forother concurrent tasks.

I'm exploring the option of setting back to default (0) at least during the night time 10 PM to 6 AM.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19972 Visits: 17243
look closely at the cost threshold parameter and also check for rogue queries and tune them ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
braju
braju
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 13
From what I have seen as reponses, I think we should set the MAXDOP to default to 0 and adjust cost threshold of parallelism if needed or even ahve MAXDOP query hint inside a specific SQL processing. At the same time fine tune the SQL queries that might cause the bottlenecks.

Please note that the SQL server is in VM environment on 32 bit OS server.

Thanks
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19972 Visits: 17243
braju (9/24/2012)
From what I have seen as reponses, I think we should set the MAXDOP to default to 0 and adjust cost threshold of parallelism if needed or even ahve MAXDOP query hint inside a specific SQL processing. At the same time fine tune the SQL queries that might cause the bottlenecks.

That's what i said above ;-)



braju (9/24/2012)
Please note that the SQL server is in VM environment on 32 bit OS server.

Thanks


Which version and edition of the Windows OS?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
braju
braju
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 13
Windows OS - Win 2003 SP2 Enterprise Edition
SQL Server - 2005 Standard Edition.


Our client is plaaning to move onto 64 bit envoronment but still keep the servers in VM.

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14943 Visits: 14396
Perry Whittle (9/24/2012)
braju (9/24/2012)
From what I have seen as reponses, I think we should set the MAXDOP to default to 0 and adjust cost threshold of parallelism if needed or even ahve MAXDOP query hint inside a specific SQL processing. At the same time fine tune the SQL queries that might cause the bottlenecks.

That's what i said above ;-)

That's funny because I thought the same thing when I read your initial post :-D

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search