Max Degree of Parallelism最大并行度配置

Max Degree of Parallelism最大并行度配置

 

由于公司的业务在急速增长中，发现数据库服务器已经基本撑不住这么多并发。一方面，要求开发人员调整并发架构，利用缓存减少查询。一方面从数据库方面改善并发。数据库的并行度可设置如下：

1）cost threshold for parallelism：数据库引擎在编译的时候，预估执行计划的cost；若此cost消耗超过cost threshold for parallelism设置的值；sqlserver就会使用并行执行计划。

2）max degree of parallelism：并行执行计划最多能使用多少线程并行执行语句。默认值为0

在OLTP环境中，会存在大量的并发查询，因此会存在以下两种情况：

1）单个查询的并发过高，可能会导致等待并发同步的消耗

若SQL Server中有大量的CXPACKET等待类型，则可能由于是并发过高的原因，CXPACKET等待类型是由于等待一个或多个线程同步，而出现的等待类型。

然而并非说CXPACKET等待类型等待就是不好，我们只是需要减少此等待类型。具体查询如下

SELECT '查看百分比是否>10%,如果大于10%，考虑降低并行度'    
select cast([signal_wait_time_ms] as decimal(30,2))/[wait_time_ms] as [百分比],*     
from sys.dm_os_wait_stats (nolock)    
where [wait_time_ms]<>0 AND  wait_type='CXPACKET'   

2）单个查询的并发过高，占用大量cpu资源，导致其他线程无法使用

若有许多查询长期处于Runnable的状态，则可能是由于并发的影响，其他进程拿不到cpu执行。从而大量线程无法running。具体查询如下

SELECT '查看cpu任务'    
SELECT scheduler_id, current_tasks_count, runnable_tasks_count    
FROM sys.dm_os_schedulers (nolock)    
WHERE scheduler_id < 255  

如何设置合理的并行度呢？

--配置最大并行度
sp_configure 'max degree of parallelism',1
go
--使设置生效
Reconfigure
go

sp_configure 'cost threshold for parallelism',10--设置为10s
go
--使设置生效
Reconfigure
go

1）在OLTP服务器环境中

a）对并发度要求高，对每个用户请求都要及时响应，一般建议将max degree of parallelism  设置为1；因此cost threshold for parallelism  的值没有作用，默认为5即可

b）对并发度要求不高，经常会有复杂查询的数据库，若cpu个数<8,一般建议将max degree of parallelism 设置为cpu数量;若cpu个数>=8建议将值设为8；cost threshold for parallelism 默认即可

2）在OLAP服务器环境中

由于查询均比较复杂，则可将max degree of parallelism 设置为0，同时如果考虑到等待消耗问题，则可以考虑将cost threshold for parallelism 的值设置为10，以降低并发执行的可能

3）在OLTP和OLAP混合环境中

a）对并发度要求高，则可以考虑将max degree of parallelism  设置为<=4，cost threshold for parallelism 值默认即可

b）对并发度要求不高，经常会有复杂查询的数据库，若cpu个数<8,一般建议将max degree of parallelism 设置为cpu数量;若cpu个数>=8建议将值设为8；cost threshold for parallelism 默认即可