MSSQLServer如何增大并行度
daniellin17(Q7):你好石沫大神: 还有一个问题,能否根据你多年的经验 谈谈如何增大并行度。 比如说从db的设计层面,还有物理设备,参数配置等等….
石沫(A7):这个问题我不知道是否是两个问题,一个是并行度,另一个是并发,我更多理解是吞吐量,单就并行度而言。
提高并行度需要考虑的因素有:
1. 可用于SQL SERVER的CPU数量
2. SQL SERVER的版本(32位/64位)
3. 可用内存
4. 执行的查询类型
5. 给定的流中处理的行数
6. 活动的并发连接数量
7. sys.configurations参数:affinity mask/max server memory (MB)/ max degree of parallelism/cost threshold for parallelism
以DOP的参数控制并行度为例,设置如下:
SELECT *
FROM sys.configurations WITH(NOLOCK)
WHERE name = ‘max degree ofparallelism’
EXEC sp_configure ‘max degree ofparallelism’,2
RECONFIGURE WITH OVERRIDE
经过测试,DOP设置为2是一个比较适中的状态,特别是OLTP应用。如果设置高了,会产生较多的SUSPEND进程。我们可以观察到资源等待资源类型是:CXPACKET
你可以用下列语句去测试:
DBCCSQLPERF(‘sys.dm_os_wait_stats’,CLEAR)
SELECT *
FROM sys.dm_os_wait_stats WITH(NOLOCK)
ORDER BY 2 DESC ,3 DESC
如果是吞吐量的话。优化的范围就很广了。优化是系统性的。硬件配置我们选择的话,大多根据业务量来预估,然后考虑以下:
1. RAID的划分,RAID1适合存放事务日志文件(顺序写),RAID10/RAID5适合做数据盘,RAID10是条带化并镜像,RAID5条带化并奇偶校验
2. 数据库设置,比如并行度,连接数,BUFFER POOL
3. 数据库文件和日志文件的存放规则,数据库文件的多文件设置规则
4. TEMPDB的优化原则,这个很重要的
5. 表的设计方面根据业务类型而定
6. CLUSTERED INDEX和NONCLUSTERED INDEX的设计
7. 阻塞分析
8. 锁和死锁分析
9. 执行计划缓冲分析
10. 存储过程重编译
11. 碎片分析
12. 查询性能分析,这个有很多可以优化的方式,比如OR/UNION/类型转换/列上使用函数等等
我这里列举一个高并发的场景:
比如,我们的订单,比如搞活动的时候,订单刷刷刷地增长,单个实例可能每秒达到很高很高,我们分析到最后最常见的问题是HOT PAGE问题,其等待类型是PAGE LATCH竞争。这个过程可以这么来处理,简单列几点,可以参考很多涉及高并发的案例:
1. 数据库文件和日志文件分开,存放在不同的物理驱动器磁盘上
2. 数据库文件需要与CPU个数形成一定的比例
3. 表设计可以使用HASH来作为表分区
4. 表可以设置无序的KEY/INDEX,比如使用GUID/HASH VALUE来定义PRIMARY KEY CLUSTER INDEX
5. 我们不能将自增列设计为聚集INDEX
这个场景只是针对高并发的插入。对于查询而言,是不适合的。但这些也可能导致大量的页拆分。只是在不同的场景有不同的设计思路。这里抛砖引玉。