August 27, 2015 at 7:55 pm
I have a server with 2 physical CPU's, hyper threaded ratio at 12 which makes it 24 logical CPU's.
Currently, Maxdop is set to default.
Should I be making a change as per the MS best practice
August 27, 2015 at 8:36 pm
IMHO yes. Let others know what you're doing first so they have the right of refusal, but otherwise while other DBAs will tell you to test thoroughly first, this is non trivial and I am of the opinion that you should always START by enforcing best practices and only modify from there onwards. However that's in part because we manage hundreds of servers.
Get coreinfo.exe from Microsoft and use it to print out the core/socket/numa/hyper threading configuration. Pick the lowest of cores per socket / 2 for hyperthreading, or the number of cores per numa node / 2 for hyperthreading, or 8.
We also set the cost threshold for parallelism. 5 is extremely low. We set 25 but others do 50. There is a very cool script to go through the plan cache and show you which buckets all of the queries fall into so that you can tweak, and 25 seems to be very reasonable for us.
August 27, 2015 at 10:36 pm
codykonior (8/27/2015)
IMHO yes. Let others know what you're doing first so they have the right of refusal, but otherwise while other DBAs will tell you to test thoroughly first, this is non trivial and I am of the opinion that you should always START by enforcing best practices and only modify from there onwards. However that's in part because we manage hundreds of servers.Get coreinfo.exe from Microsoft and use it to print out the core/socket/numa/hyper threading configuration. Pick the lowest of cores per socket / 2 for hyperthreading, or the number of cores per numa node / 2 for hyperthreading, or 8.
We also set the cost threshold for parallelism. 5 is extremely low. We set 25 but others do 50. There is a very cool script to go through the plan cache and show you which buckets all of the queries fall into so that you can tweak, and 25 seems to be very reasonable for us.
Thanks. Can you suggest a value for my case?
August 27, 2015 at 11:26 pm
Is this an OLTP system ? the defaults are the best usually when you don't know what value to set. SQL automatically decides when to use a parallel plan and it normally doesnt require any input from the user.
If you set MAXDOP at the server level it applies to all queries executed on the sever, instead I would suggest you had a query hint when needed and leave the rest at defaults.
for an OLTP system you very rarley need parallelism for DW you almost always need parallelism but how many CPUs to use it best left for SQL ti decide.
August 31, 2015 at 6:53 am
In addition to changing the Maxdop, make darned sure you change the cost threshold for parallelism. The default of 5 is insanely low.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 31, 2015 at 6:59 am
How many physical cores per processor? 6?
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 31, 2015 at 3:25 pm
GilaMonster (8/31/2015)
How many physical cores per processor? 6?
2 physical CPU's, hyper threaded ratio at 12 which makes it 24 logical CPU's.
Is there a script I can use to pull the information you are asking? I used one already
SELECT cpu_count AS Logical_CPU_Count , cpu_count / hyperthread_ratio AS Physical_CPU_Count FROM sys.dm_os_sys_info ;
As per this, we have 2 physical CPU's and 24 Logical CPU's
August 31, 2015 at 3:27 pm
Jayanth_Kurup (8/27/2015)
Is this an OLTP system ? the defaults are the best usually when you don't know what value to set. SQL automatically decides when to use a parallel plan and it normally doesnt require any input from the user.If you set MAXDOP at the server level it applies to all queries executed on the sever, instead I would suggest you had a query hint when needed and leave the rest at defaults.
for an OLTP system you very rarley need parallelism for DW you almost always need parallelism but how many CPUs to use it best left for SQL ti decide.
This is a OLAP server..
September 1, 2015 at 2:57 am
How many physical cores per processor? Don't look at SQL, check the specs of the processor you have.
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
September 1, 2015 at 5:51 pm
GilaMonster (9/1/2015)
How many physical cores per processor? Don't look at SQL, check the specs of the processor you have.
I see 2 physical processors when I run the msinfo.exe.
6 cores per processor making it 12 logical processors.
September 2, 2015 at 3:00 am
So 6 then.
In that case, you can try 6, 9 or 12 as maxdop, see which works best for you (calced as 1x, 1.5x and 2x the number of physical cores/NUMA node)
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
September 2, 2015 at 8:36 am
GilaMonster (9/2/2015)
So 6 then.In that case, you can try 6, 9 or 12 as maxdop, see which works best for you (calced as 1x, 1.5x and 2x the number of physical cores/NUMA node)
Somewhat related : How would this differ if we're running on a (Hyper-V) VM?
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
September 2, 2015 at 8:51 am
It wouldn't, though identifying cores/NUMA node might be a tad more challenging, and if the VM admin's screwed up the config, could be harder still.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply