超过建议时的MAXDOP设置

Question

我有一个在VMWare上运行的Sql Server2014企业版，分配了32个逻辑处理器。根据Microsoft的建议，MAXDOP应设置为8。但是，当我对多个大表运行大型查询时：

MAXDOP在8: 7分钟，MAXDOP在16: 3分钟

我的问题是，如果我将MAXDOP设置为16，这是逻辑处理器数量的一半，我会遇到什么问题？

提前感谢

Answer 1

“MAXDOP取决于”取决于您的处理器占用空间和NUMA配置，下面是一个快速生成MAXDOP设置建议的TSQL脚本

   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))l
           else ''
           end as Recommendations

如果将MaxDop设置为16，会发生什么情况

最大并行度，如果设置为更高的值，可以在运行单个查询时执行得更好，这一点您已经注意到了。但是设置较高的maxdop会更快地消耗您的sql server线程，并且您可能会在数据负载较重的生产环境中受到线程匮乏的影响。请确保为并行度设置开销阈值，这将告诉sql 在超过您为并行度开销阈值设置的阈值时执行并行性。