May 14, 2013 at 7:27 am
Hello,
Does anyone have any idea what is the better way to set MAXOP settings for OLAP system. Here is my environment info:
SQLServer: 2008 R2
LOGICAL CPU Count: 40
Hyperthread ration : 10
Physical CPU Count : 4
Physical memory : 260 GB
I have seen one of the forumale and not so sure is this right setting:
--MAX DEGREE OF PARALLELISM FORUMLAE
--'4' NUMBER OF PROCESSESSORS- 4/2 = 2
select
case
when cpu_count / hyperthread_ratio > 2 then 2
else cpu_count / hyperthread_ratio
end as optimal_maxdop_setting
from sys.dm_os_sys_info;
O/P: 2
--Another formulae:
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
O/P: 2
Not so sure, are these correct or not. Need suggestions please. Thanks
May 15, 2013 at 12:40 pm
May 16, 2013 at 5:14 pm
I have already gone through it. But, looking for OLAP settings only. Currently i am testing with my environment by increasing and decreasing values.
May 16, 2013 at 5:55 pm
Depends on how much concurrency you want to be able to support. Looking to load a lot of dimension tables simultaneously? Or are you looking just to run one massive process at a time?
Personally, I would follow the recommendations. Especially the NUMA related ones.
Do you realy want to allow a single process to take over the entire CPU power? Do want to spend a large chunk of time in CXPacket waits for little gain?
Parallelism should be the answer of last resort. Only after you have done everything else optimally should you be looking to parallelism to improve performance.
May 23, 2013 at 10:36 am
We have both OLAP and OLTP systems. Config looks around same for us too except double the memory and double the logical processes.
We set it to 1 in general and increase to 4 when we are creating bunch of indexes, or run update statistics etc.
But, you don't want it to be 0 for sure.
January 10, 2014 at 2:08 pm
Just to note: SQL 2008 scheduler does not reguard NUMA for thread assignment in parallelism. The thread has a chance of running on the same node, or it may run on a seperate node. Linchi Shea had a pretty good article about this.
With that being said, Linchi also has a follow up article that states that it doesn't appear to make a big difference to performance relative to other performance isues.
I find it interesting that the last article mentions having SQL Server listener set up to listen on multiple ports. I've been doing some tinkering around with Soft NUMA. There does seem to be some benefits with creating affinity with multiple NICs listening on seperate subnets, each NIC affinitized to one core, and having SQL Server listener port mapped to the same core (soft NUMA), and loading tables in parallel for ETL. Basically Microsoft did a case study with this where they loaded 1Tb of data into SQL Server in about 30 mins using commodity class hardware.
However I would think such a configuration would be ultimately difficult to support in a production environment, as in order to make such a thing work correctly, you'd have to have soft NUMA set up (which isn't painless), then have the listener listen on seperate ports, one for each soft NUMA node affinitized to each physical NIC, affinitized to each CPU core. Then in your ETL package you'd have to use something like the Balanced Data Distributor and multiple connections to SQL Server set up on seperate NIC subnets on your SSIS machine to make sure the traffic gets split and evenly balanced down each parallel stream.
Then you'd have to have a set of NICs set up to listen on a single port configured on another subnet for all the NUMA nodes so you could properly manage end user requests or you risk overloading one of the soft NUMA nodes. I don't think you'd want to mess with using a load balancer to balance the request across the nodes respectively for end-user activity. At the end of the day, thats a lot of NICs going into SQL Server, and a lot of configuration to the SQL listener to map TCP ports to soft NUMA nodes, each soft NUMA node mapped to a CPU core... You see where I'm going with this. Configuration management becomes quite a nightmare.
Or you could just buy PDW 2012 and get all that out of the box... Choices choices.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply