Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Physical CPU or Logical Processors ?
Add to briefcase
25 posts, Page 1 of 3
123»»»

Physical CPU or Logical Processors ? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
awu
Posted Wednesday, August 17, 2011 1:46 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:01 AM
Points: 559, Visits: 320
BOL: max degree of parallelism Option

you can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

Question is :

What is the "number of processors" here really mean ? It's physcial or logical ? I have box with 2 Physical Inter Xeon Processors, but both are 4 Cores, so 8 Logical Processors, do i have 2 processors or 8 processors ?

we got lots of timeouts, and CXPACKET is No 1 contributed to waitng list, so , i am thinking: should I limit the max degree of parallelism to 1 ( if we have 2 processors ) or to 4 ( if we have 8 processors) ?

Any imput will be highly appreciated !


Post #1161407
SQLRNNR
Posted Wednesday, August 17, 2011 2:00 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733, Visits: 12,330
You have 2 sockets and 8 processors.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1161414
Leo.Miller
Posted Wednesday, August 17, 2011 2:01 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:44 PM
Points: 442, Visits: 1,300
If you look at how many processors SQL sees, you will see it says 8. If you want to set MAXDOP to try elliminate the CXPacket waits then 1 is the correct setting. Try 4 and you will see the problem doesn't go away....

However, from SQL 2008 on I'd not be in a hurry to set this to 1 (unlike 2005 where I'd tend to recommend it). It may be worth your while to investigate the problem queries and rather use the MAXDOP = 1 query hint just on that those sections of the query that are causing an issue. This has a less impact on the rest of the environment, much of which may be benifitting from the high MADOP setting.

Cheers

Leo
Post #1161415
Leo.Miller
Posted Wednesday, August 17, 2011 2:03 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:44 PM
Points: 442, Visits: 1,300
SQLRNNR (8/17/2011)
You have 2 sockets and 8 processors.

Except if you licence Per Processor, you only pay for 2 licences, not 8.

This took me for ever to get my head around.

Leo
Post #1161419
SQLRNNR
Posted Wednesday, August 17, 2011 2:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733, Visits: 12,330
Leo.Miller (8/17/2011)
SQLRNNR (8/17/2011)
You have 2 sockets and 8 processors.

Except if you licence Per Processor, you only pay for 2 licences, not 8.

This took me for ever to get my head around.

Leo


Correct. Licensing is a bit different (and may be changing again from rumors I heard). Licensing is socket based for now.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1161423
Perry Whittle
Posted Wednesday, August 17, 2011 2:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201, Visits: 11,150
Oracle licence per core and get away with it, shouldn't think it will be long before MS capitalise on it too

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1161441
awu
Posted Wednesday, August 17, 2011 5:56 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:01 AM
Points: 559, Visits: 320
From BOL:

"The maximum value for thedegree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored. and for SQL Server 2008 Standard, the value is 4"

Now for my server which has 2 physical CPUs and 8 logical processors, how many "the actual number of available processors" is ? is it 8 or 2 ?


Post #1161499
SQLRNNR
Posted Wednesday, August 17, 2011 6:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733, Visits: 12,330
awu (8/17/2011)
From BOL:

"The maximum value for thedegree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored. and for SQL Server 2008 Standard, the value is 4"

Now for my server which has 2 physical CPUs and 8 logical processors, how many "the actual number of available processors" is ? is it 8 or 2 ?


I answered that question. You have 8 physical cpus and 2 sockets. Each core is a cpu. Logical cpus comes into play if you have hyper-threading enabled.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1161501
awu
Posted Wednesday, August 17, 2011 7:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:01 AM
Points: 559, Visits: 320
Thanks Jason, but I am still puzzling over something here, if you right , it's 8, then i can set : max degree of parallelism = 6, leave two CPUs to something else all the time , right ? But on the other hand, it's saying max numbers of CPUs the SQL Server 2008 R2 Standard edition support is 4, is that mean i should set the number less than 4 ? Confusing ....


Post #1161509
SQLRNNR
Posted Wednesday, August 17, 2011 9:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733, Visits: 12,330
I would start with setting it at 4, test, baseline and then try a different value if necessary.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1161526
« Prev Topic | Next Topic »

Add to briefcase
25 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.