Max Degree of Parallelism - 24 Core processor 2 NUMA nodes
-
Hello Everyone,
We have a server configured with 24 core processor with 0(Zero) MAXDOP, and queries are running with parallelism can any one suggest to set this configuration,
1) How to find NUMA configuration in SQL?
2) How to set MAXDOP based on NUMA?
3) Do we need to restart SQL Server services after setting MAXDOP to 8 or 12 ?
4) Will there be any performance improved if we change MAXDOP?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
info.sqldbamail (5/2/2016)
Hello Everyone,We have a server configured with 24 core processor with 0(Zero) MAXDOP, and queries are running with parallelism can any one suggest to set this configuration,
1) How to find NUMA configuration in SQL?
2) How to set MAXDOP based on NUMA?
3) Do we need to restart SQL Server services after setting MAXDOP to 8 or 12 ?
4) Will there be any performance improved if we change MAXDOP?
Quick question, what is the "cost threshold for parallelism" setting on the server?
😎
-
cost threshold for parallelism is set to 5.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Fix that first. 5 is insanely low. Set it higher.
As for the rest, the numa config is written into the error log on startup. The guidelines are either number of physical cores per numa node, 1.5*number of physical cores per numa node or the total number of cores per numa node, depending who you listen to.
Will it give a performance improvement? Probably not.
Since you seem to be looking for the setting silver bullet, let me save you some time. There isn't one. If you want better performance, you're going to have to look at your queries and indexing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
info.sqldbamail (5/2/2016)
cost threshold for parallelism is set to 5.The default value of 5 is very low, normally recommend starting with 50 and work from there.
😎
-
If i change that value to [highlight="#FFF000"]50[/highlight] do i need to restart SQL Server Services on production.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
I suggest you get familiar with the SQL documentation (Books Online)
https://msdn.microsoft.com/en-us/library/ms189631.aspx answers both that and earlier questions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for your time I got the answer, There is no need to restart the SQL Server services if we change the Cost Threshold Parallelism value from 5, If estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism then this problem of parallelism occurs.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Parallelism isn't a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
info.sqldbamail (5/2/2016)
Thanks for your time I got the answer, There is no need to restart the SQL Server services if we change the Cost Threshold Parallelism value from 5, If estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism then this problem of parallelism occurs.You don't need to restart sql service, but keep in mind once you change the settings it will clear the procedure cache on that sql instance.
-
15-20 on CTFP
Number of PHYSICAL cores in each NUMA node for MAXDOP (or lower). If it is a pure OLAP box you may benefit from higher.
Remember you can override MAXDOP at the statement level if necessary with OPTION (MAXDOP NN). This can be done both to give a specific query more or especially FEWER threads for parallelism. I often do this for clients to keep massive "all-data" report queries from overwhelming a server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply