Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Degree of parallelism in SQL Server Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:51 AM
Points: 5, 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?
Post #1362850
Posted Friday, September 21, 2012 11:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 9:20 AM
Points: 8, Visits: 47
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
Post #1363074
Posted Sunday, September 23, 2012 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 7,135, Visits: 12,745
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
Post #1363233
Posted Sunday, September 23, 2012 1:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1363286
Posted Monday, September 24, 2012 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:51 AM
Points: 5, 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.




Post #1363444
Posted Monday, September 24, 2012 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1363462
Posted Monday, September 24, 2012 7:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:51 AM
Points: 5, 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

Post #1363470
Posted Monday, September 24, 2012 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1363494
Posted Monday, September 24, 2012 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:51 AM
Points: 5, 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
Post #1363507
Posted Monday, September 24, 2012 11:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 7,135, Visits: 12,745
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


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363648
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse