SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MAXDOP setting


MAXDOP setting

Author
Message
sotn
sotn
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 469
I know that this is slighty a 'depends' question but our server is heaver user transactional server.
Our SQL 2008R2 server has a Xeon 5765, so that's 2 phsyicals CPUs 6 cores each and hyperthreading.
So in SSMS that shows as NUMA Node 0 with 0-11 processors and NUMA Node 1 with 12-23 processors.

As a percentage we get a lots of CXPACKETS so I have changed MAXDOP from the default of 0 to 12,

CXPACKETS have dropped now, but should I reduce the value futher, as this MS article is not compeltely clear to me?

MS article http://support.microsoft.com/kb/329204 says
•For servers that use more than eight processors, use the following configuration: MAXDOP=8.
•For servers that have eight or less processors, use the following configuration where N equals the number of processors: MAXDOP=0 to N.
•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
•For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors

So with all of these rules does this mean that I should set it to a value of 2, (when looking at the last rule)
I guess it depends on the exact meaning of 'processors'.

Thanks In Advance.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
It should say "physical cores" instead of "physical processors." I would set it to 6 for the number of physical cores per NUMA node. If you're still seeing too much parallelism for your taste look into increasing the Cost Threshold for Parallelism.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
jyuan68
jyuan68
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 91
A few months ago,there is the same issue in our live DB.
But I do not want to set MAXDOP and the cost threshold for parallelism.
Because such actions are palliatives, so we must find out all poor efficiency query plans in our database.

You can run these commands.


select top 100 b.text,c.client_net_address,a.last_wait_type,a.*
from sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(a.sql_handle) b
inner join sys.dm_exec_connections c on a.session_id=c.session_id
where a.session_id>50 and a.session_id<>@@spid
--
select top 10 total_worker_time/execution_count as avg_cpu_cost,
plan_handle,execution_count,
(select substring(text,statement_start_offset/2+1,(case when statement_end_offset=-1
then len(convert(nvarchar(max),text))*2
else statement_end_offset
end -statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
order by avg_cpu_cost desc


TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12724 Visits: 8564
opc.three (12/24/2012)
It should say "physical cores" instead of "physical processors." I would set it to 6 for the number of physical cores per NUMA node. If you're still seeing too much parallelism for your taste look into increasing the Cost Threshold for Parallelism.


1) physical cores is the MAX you should EVER set MAXDOP on NUMA hardware. Don't leave it at zero either.

2) if you can, test your server with Hyperthreading disabled, especially it if is a data warehouse box (or possibly mixed-use server).

3) I never, ever leave cost threshhold for parallelism at 5 for any client I do performance tuning at. It is universally an inappropriate number for modern hardware. It should be larger. I do analytics to try to identify a 'best start' value for each client's server, but without further information I might try 15 for an oltp box and 40 for a DW box.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
TheSQLGuru (12/25/2012)
opc.three (12/24/2012)
It should say "physical cores" instead of "physical processors." I would set it to 6 for the number of physical cores per NUMA node. If you're still seeing too much parallelism for your taste look into increasing the Cost Threshold for Parallelism.


1) physical cores is the MAX you should EVER set MAXDOP on NUMA hardware. Don't leave it at zero either.

I agree 100%, the documentation should be updated to say:

MS article http://support.microsoft.com/kb/329204 says
•For servers that use more than eight processors, use the following configuration: MAXDOP=8.
•For servers that have eight or less processors, use the following configuration where N equals the number of processors: MAXDOP=0 to N.
•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs physical cores that are assigned to each NUMA node.
•For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors cores


2) if you can, test your server with Hyperthreading disabled, especially it if is a data warehouse box (or possibly mixed-use server).

I have had varied results with disabling Hyperthreading. One experiment with a DW workload on Server 2003 running SQL 2005 did not make one bit of difference. IN other cases it helped performance, in some it hurt. Specific cases are anecdotal at best so it is important to test with your workload. General consensus seems to be to disable it by default if you do not have the time to invest. If you want to know for sure the right way to go test, test, test.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sotn
sotn
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 469
Thanks for the replies, much appreciated.

I have now reduced it to 6 from my 'temp' setting of 12, it was previously the default, 0.
CXPACKET has now dropped from about 170 change per minute to 6.

The phsyical cores/processor mistake in the documentation helps explain this to me.
I will leave HT on for now, but wil investigate that if needed, thanks for the pointer.

The server is OLTP.

For NUMA harware, every SQL Server I've looked at, in processors, says NUMA node 0 etc and then split into the cores/processors. does this mean it is NUMA enabled?
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
sotn (12/27/2012)
For NUMA harware, every SQL Server I've looked at, in processors, says NUMA node 0 etc and then split into the cores/processors. does this mean it is NUMA enabled?

More like NUMA aware. SQL Server will see the NUMA nodes as they are presented by Windows per the hardware. If there is no NUMA present you'll still see Numa Node 0 as the only CPU grouping.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search