August 18, 2014 at 12:37 pm
Hi DBA's,
I was looking for a general practice you follow in order to set MAXDOP setting on a SQL instance. I usually follow No. or cores/2. Please let me know your opinion on this.
Thanks.
August 18, 2014 at 12:50 pm
Here is a script that covers various scenarios. I can't remember where I got it, but the author name is still attached.
/*************************************************************************
Author : Kin Shah
Purpose : Recommend MaxDop settings for the server instance
Tested RDBMS : SQL Server 2008R2
**************************************************************************/
declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int
select @logicalCPUs = cpu_count -- [Logical CPU Count]
,@hyperthreadingRatio = hyperthread_ratio -- [Hyperthread Ratio]
,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
,@HTEnabled = case
when cpu_count > hyperthread_ratio
then 1
else 0
end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);
select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
and parent_node_id < 64
group by parent_node_id
option (recompile);
select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes
where [status] = 'VISIBLE ONLINE'
and parent_node_id < 64
-- Report the recommendations ....
select
--- 8 or less processors and NO HT enabled
case
when @logicalCPUs < 8
and @HTEnabled = 0
then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
--- 8 or more processors and NO HT enabled
when @logicalCPUs >= 8
and @HTEnabled = 0
then 'MAXDOP setting should be : 8'
--- 8 or more processors and HT enabled and NO NUMA
when @logicalCPUs >= 8
and @HTEnabled = 1
and @NoofNUMA = 1
then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
--- 8 or more processors and HT enabled and NUMA
when @logicalCPUs >= 8
and @HTEnabled = 1
and @NoofNUMA > 1
then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
else ''
end as Recommendations
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2014 at 2:25 pm
There are applications such as SharePoint that can't deal with parallel queries at all (MAXDOP=1), which would override any magic number generated from the hardware configuration.
August 18, 2014 at 2:43 pm
True. Sharepoint should be set to maxdop of 1. AX and other flavors of Dynamix also work better with maxdop 1.
That said, it is best to know your data and data access patterns.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2014 at 8:02 pm
As far as I am concerned there is a hard limit for most every server out there and that is the number of physical cores in each NUMA node. Outside of that I watch for parallelism issues and go up or down within the 1 - N range as necessary.
Don't forget the other part of the equation - Cost Threshold for Parallelism. The default of 5 is universally too low.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 18, 2014 at 10:18 pm
TheSQLGuru (8/18/2014)Don't forget the other part of the equation - Cost Threshold for Parallelism. The default of 5 is universally too low.
What's a good way to determine what the Cost Threshold should be?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 19, 2014 at 2:28 am
MyDoggieJessie (8/18/2014)
TheSQLGuru (8/18/2014)Don't forget the other part of the equation - Cost Threshold for Parallelism. The default of 5 is universally too low.
What's a good way to determine what the Cost Threshold should be?
It should be higher than 5. 😀
If you really want a number, identify every query which is running in parallel, test them with maxdop 1 and look at their serial costs. Then run each and every one both serially and letting them parallel and see which is faster. Then, from the large amount of data, extrapolate a value for cost threshold (based on the serial costs you recorded at the beginning) which allows for queries that are faster in parallel to run in parallel and those faster without parallelism to run without parallelism
Have a nice month. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2014 at 2:29 am
RPSql (8/18/2014)
I was looking for a general practice you follow in order to set MAXDOP setting on a SQL instance.
0 unless I have a good reason to change it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2014 at 2:33 am
TheSQLGuru (8/18/2014)
As far as I am concerned there is a hard limit for most every server out there and that is the number of physical cores in each NUMA node.
I had a long chat with Adam a while back on this topic and in his testing he'd discovered that 1.5*cores/node was the sweet spot. I don't think he's published his research, but you could ask him for the details
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2014 at 8:52 am
GilaMonster (8/19/2014)
MyDoggieJessie (8/18/2014)
TheSQLGuru (8/18/2014)Don't forget the other part of the equation - Cost Threshold for Parallelism. The default of 5 is universally too low.
What's a good way to determine what the Cost Threshold should be?
It should be higher than 5. 😀
If you really want a number, identify every query which is running in parallel, test them with maxdop 1 and look at their serial costs. Then run each and every one both serially and letting them parallel and see which is faster. Then, from the large amount of data, extrapolate a value for cost threshold (based on the serial costs you recorded at the beginning) which allows for queries that are faster in parallel to run in parallel and those faster without parallelism to run without parallelism
Have a nice month. 😉
Month??
That would be incredibly fast.:w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 9:07 am
GilaMonster (8/19/2014)
TheSQLGuru (8/18/2014)
As far as I am concerned there is a hard limit for most every server out there and that is the number of physical cores in each NUMA node.I had a long chat with Adam a while back on this topic and in his testing he'd discovered that 1.5*cores/node was the sweet spot. I don't think he's published his research, but you could ask him for the details
Linchi Shea also has some good posts on maxdop. It seems they are similar to what Adam has in his posts.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply