473,320 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

max degree parallelism

hi,
i've set 'max degree of parallelism' to 1 because some sql request hanged.
Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

Thanks

Paul
Dec 15 '06 #1
6 7079
paul (no****@nomail.com) writes:
i've set 'max degree of parallelism' to 1 because some sql request hanged.
Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?
There are only places you can control the degrees of parallelism: at
server level and at query level.

If you have a problem with parallelism going awry with a certain query,
the best is to add OPTION (MAXDOP 1) to that query. If you set "max
degree of parallelism" on server level 1, for any query where you want
parallelism, you need to use OPTION (MAXDOP 0) to get all CPUs, or any
other number to get a certain number of CPUs.

The most common reason to change the configuration parameter to reflect
the number of physical CPU cores when you have hyper-threaded CPU. That
is, if you have two dual-core CPUs that are hyperthread, set the
parameter to 4.

If you have plenty of CPUs, you may still want to reduce the number, so
that a single query cannot monopolize the CPU power.

Setting the parameter to 1 because of a single query is not a good idea.
Better is to fix the query.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 15 '06 #2
thanks for your reply
i've 4 hyperthread cpu
i've effectively set the degree to 1 because one batch (consist of several
queries) runned with ~220 threads
i don't know why the optimiser choosed this but the fact is the server was
unstable ("and many error like Process ID 85:80 owns resources that are
blocking processes on Scheduler 4.")
the batch takes 3h to complete with max degree = 0 and 3mn with max degree =
1
So i've set the max degree = 1 bacause i don't know if others requets (like
dynamic) can also generate ~200 threads


"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
paul (no****@nomail.com) writes:
i've set 'max degree of parallelism' to 1 because some sql request
hanged.
Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

There are only places you can control the degrees of parallelism: at
server level and at query level.

If you have a problem with parallelism going awry with a certain query,
the best is to add OPTION (MAXDOP 1) to that query. If you set "max
degree of parallelism" on server level 1, for any query where you want
parallelism, you need to use OPTION (MAXDOP 0) to get all CPUs, or any
other number to get a certain number of CPUs.

The most common reason to change the configuration parameter to reflect
the number of physical CPU cores when you have hyper-threaded CPU. That
is, if you have two dual-core CPUs that are hyperthread, set the
parameter to 4.

If you have plenty of CPUs, you may still want to reduce the number, so
that a single query cannot monopolize the CPU power.

Setting the parameter to 1 because of a single query is not a good idea.
Better is to fix the query.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dec 15 '06 #3
paul (no****@nomail.com) writes:
i've 4 hyperthread cpu
i've effectively set the degree to 1 because one batch (consist of
several queries) runned with ~220 threads i don't know why the optimiser
choosed this but the fact is the server was unstable ("and many error
like Process ID 85:80 owns resources that are blocking processes on
Scheduler 4.") the batch takes 3h to complete with max degree = 0 and
3mn with max degree = 1 So i've set the max degree = 1 bacause i don't
know if others requets (like dynamic) can also generate ~200 threads
Certainly it would be a good idea to identify the queries in the batch
that gets a wild parallel plan, and OPTION (MAXDOP 1). At least if you
want to be able to use parallelism elsewhere.

As for why the optimizer generates a parallel plan that takes 3h to complete
I don't know, but I've certainly see it happen more than once.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 15 '06 #4
To add on to Erland's response, excessive parallelism can be a symptom that
query and index tuning is needed. The optimizer will sometimes throw
processors at the problem to compensate.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul" <no****@nomail.comwrote in message
news:el**********@s1.news.oleane.net...
thanks for your reply
i've 4 hyperthread cpu
i've effectively set the degree to 1 because one batch (consist of several
queries) runned with ~220 threads
i don't know why the optimiser choosed this but the fact is the server was
unstable ("and many error like Process ID 85:80 owns resources that are
blocking processes on Scheduler 4.")
the batch takes 3h to complete with max degree = 0 and 3mn with max degree
=
1
So i've set the max degree = 1 bacause i don't know if others requets
(like
dynamic) can also generate ~200 threads


"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
>paul (no****@nomail.com) writes:
i've set 'max degree of parallelism' to 1 because some sql request
hanged.
Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

There are only places you can control the degrees of parallelism: at
server level and at query level.

If you have a problem with parallelism going awry with a certain query,
the best is to add OPTION (MAXDOP 1) to that query. If you set "max
degree of parallelism" on server level 1, for any query where you want
parallelism, you need to use OPTION (MAXDOP 0) to get all CPUs, or any
other number to get a certain number of CPUs.

The most common reason to change the configuration parameter to reflect
the number of physical CPU cores when you have hyper-threaded CPU. That
is, if you have two dual-core CPUs that are hyperthread, set the
parameter to 4.

If you have plenty of CPUs, you may still want to reduce the number, so
that a single query cannot monopolize the CPU power.

Setting the parameter to 1 because of a single query is not a good idea.
Better is to fix the query.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dec 15 '06 #5
Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:
To add on to Erland's response, excessive parallelism can be a symptom
that query and index tuning is needed. The optimizer will sometimes
throw processors at the problem to compensate.
Yeah, but in those cases the query usually don't complete in three seconds
if constraint to one CPU!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 15 '06 #6
Yeah, but in those cases the query usually don't complete in three seconds
if constraint to one CPU!
True, but I've seen overly aggressive parallel plans that completed
significantly faster without parallelism. In many of those cases, tuning
eliminated parallelism entirely and the single-threaded query ran much
faster too. IMHO, tuning is a often better than fiddling with MAXDOP.

However, in Paul's case, the difference is much more pronounced (3 minutes
vs. 3 hours) and apparently introduced instability. I'm not sure what
service pack he's running but there are optimizer fixes/enhancements in all
the SQL 2000 SPs.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:
>To add on to Erland's response, excessive parallelism can be a symptom
that query and index tuning is needed. The optimizer will sometimes
throw processors at the problem to compensate.

Yeah, but in those cases the query usually don't complete in three seconds
if constraint to one CPU!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 16 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ryan | last post by:
If SQL Server is designed for multi processor systems, how can running a query in parallel make such a dramatic difference to performance ? We have a reasonably simple query which brings in data...
3
by: jim_geissman | last post by:
I have a function that returns a table of information about residential properties. The main input is a property type and a location in grid coordinates. Because I want to get only a certain...
3
by: kev | last post by:
Hi, I have a sql 2000 server with 8 processors, server settings are as default. I read on Technet that it is good practise to remove the highest no. processors from being used for parallelism,...
5
by: Evan Smith | last post by:
We recently upgraded the box that hosts our main DB2 database to an 8-CPU machine with 8 GB of memory. With all the extra horsepower, we were eager to test performance with intra-parallelism turned...
3
by: Venkata C | last post by:
Hi! We are running DB2 V8 on z/OS in compatibility mode. There is one specific query (embedded as static SQL in a COBOL program) that causes the bind job to abend when we use the DEGREE(ANY)...
6
by: VM | last post by:
I was looking for an MS NG where I could post this question but couldn't find any, so here goes. What's more valuable to companies, a Master of Comp. Engineering degree or a company...
66
by: stryfedll | last post by:
Sorry this isn't directly concerning a programming language but I wanted to reach real programmers. I am in college right now and am not really interested in investing 3 more years of my life for a...
0
by: MTYWONG | last post by:
Hi guys, i got the following query: SELECT 0xFCDE62BD682D6247A2B0D2E1DFFF1EAD, ConfGRAssoc.ConfID, ConfGRAssoc.BlockDate, ConfGRAssoc.ConfGRAssocID, ConfGRAssoc.AllotmentIDPhysical,...
4
by: latalui | last post by:
i want to calulate coverage degree for a particular node having total number of nodes say n=10, in an xy coordinate having say size of x= y=500 and initial position of node is x = y= 0.5 and it...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.