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

Max DOP Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 3:27 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: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
Hi,

We have 4 processors on SQL serve box.

Question: what is the difference in keeping the MAXDOP value to default value and setting it to 4?

Is there any difference or both means same?

Thanks

Post #1509957
Posted Wednesday, October 30, 2013 3:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
4 processors or 4 cores?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1509964
Posted Wednesday, October 30, 2013 3:52 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: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
4 cores
Post #1509966
Posted Wednesday, October 30, 2013 3:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
Then they'll be the same.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1509970
Posted Wednesday, October 30, 2013 5:09 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: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
Then they'll be the same.


Thanks Gail.

But I heard different opinion from Microsoft support engineer that having default value for MAXDOP and setting it 4 is not same.

If we keep MAXDOP to default, each operator can spread the work across as many threads as it can. But when we set it to 4, each operator can only spread the work across maximum of 4 threads.

So I just wanted to get more information on this.
Post #1509982
Posted Thursday, October 31, 2013 1:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
If you keep maxdop at 0, then there can be as many active threads for each query as you have cores. If you put maxdop to 4, then each query can only have 4 executing threads at a time.

MAXDOP does not limit number of threads per query, only the number of concurrently executing threads.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1510049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse