SQLServer性能调优⽅法⼩结
数据库性能优化的应⽤场景相当⼴泛,但SQL语句与业务联系紧密,代码层⾯的优化可能需要花费相当多的时间与精⼒。除了代码层⾯,语句执⾏层⾯的优化、更佳的SQL语句使⽤执⾏计划、运⾏在⼀个稳定⾼效的环境,同样是⾼效也更符合运维的⼀种优化⼿段。下⾯我分享⼀些SQL Server在配置⽅⾯的性能优化思路,从CPU、内存、I/O、执⾏计划等层⾯,内容包含了最⼤并⾏度、资源调控器、查询提⽰⼏个功能的介绍与配置⽅法。
⼀、最⼤并⾏度(MAXDOP)
1.介绍
最⼤并⾏度是指会话可以使⽤的最⼤线程数,对于⼤批量查询,例如⼤表的扫描,使⽤多个线程同时扫描能成倍地提⾼效率;但是对于⼩型查询,例如只修改⼩表⾥⼀⾏的内容,则没必要使⽤多个线程。
⼀般情况下,会话最终使⽤多少个线程是由查询优化器决定的(可以通过option查询⼦句进⾏⼲预)。查询语句提交到SQL Server后会先进⾏解析,然后进⾏优化和简化(例如⼦查询转为对应连接、优先应⽤筛选条件),⽣成⼀系列执⾏计划,最后根据统计信息计算开销,选择合适的执⾏计划。最终预估的开销决定了会话使⽤多少并⾏度。
sql优化的几种方式
在服务器配置选项中,我们能通过“最⼤并⾏度”、“并⾏的开销阈值”两个配置进⾏调整,最⼤并⾏度的默认值是0,即不限制并⾏度,最⼤能使⽤到与CPU核数相等的并⾏度。但是对于明显有性能问题的系统,则需要考虑调整这个⾼级选项进⾏优化:
2.配置⽅法
检查/配置“最⼤并⾏度”设置
检查/配置“最⼤线程数”设置
3.注意事项
以下列举了⼀些场景作为参考:
OLTP系统
单纯的OLTP系统由⾼并发的⼩事务组成,不适合使⽤太⾼的并⾏度,可以将最⼤并⾏度设置为1,即不开启并⾏查询;如果调整后明显感觉到执⾏时间太长,应⽤反应变慢,则可以逐步提⾼到2、4、8再进⾏观察。(对于这类语句执⾏频繁的⼩事务,执⾏计划的选择也是⾮常重要的优化⽅向,需要结合语句单独分析)
OLAP系统
单纯的OLAP系统由只读长事务组成,事务执⾏时间都较长,例如报表统计、历史数据导出。这类事务的特点是会连接⼤量表、读取⼤量数据、进⾏⼤量计算,对于语句执⾏效率来说并⾏度越⾼越好。尽管官⽅⽂档推荐8核以上的服务器也使⽤并⾏度8,但在没达到CPU瓶颈的情况下可以尽可能提⾼OLAP系统的最⼤并⾏度,或者不限制最⼤并⾏度。
混合系统
实际中更常见的是读写混合的系统,在承载应⽤写操作的同时也承载⼀些⼩型报表的查询,这类系统则需要进⾏反复的调整以达到最佳的并⾏度设置:写操作通常开销较⼩,只会⽤1个并⾏度;普通的检索开销也⼀般不⼤,使⽤较低并⾏度;报表通常开销较⼤,会使⽤较⾼并⾏度。
在CPU资源有限的情况下,配置最⼤并⾏度为1可以保证最关键的写操作能获得⾜够的资源;但如果读操作需要使⽤并⾏来提⾼效率(maxdop=1时语句执⾏太慢),可以适当调到2并逐步增加;如果只需要提⾼那些执⾏时间很长的查询,可以提⾼“并⾏的开销阈值”,只让⾼开销的查询使⽤并⾏。
“并⾏的开销阈值”是⼀个相对值,没有单位,默认是5,只能通过⼀步步测试调整来选⽤最佳的设置。
最⼤连接数默认值为0,但不是没上限,⽽是根据CPU核数递增,官⽅给出的计算公式为Default Max Workers + ((logical CPUs -
4) * Workers per CPU)。
例如:⼀个64核的SQL2016最⼤线程数默认为1472,默认最⼤并⾏度为64,如果⼀个会话引发了阻塞,被阻塞的会话并⾏度都很⾼,那么积累了⼏⼗个会话之后线程数就满了,这在繁忙的系统上可能只会花⼏分钟的时间。线程数满了以后新的连接⽆法建⽴,应⽤开始报错,直到阻塞源消失才会恢复。
这种时候普通⽤户⽆法连接数据库,我们可以通过管理员专⽤通道(DAC)进⾏连接,在连接实例的名称前加上admin:即可,例如admin:127.0.0.1,DAC连接只能同时存在1个。线程占满的根本原因还是阻塞源的处理,提⾼最⼤线程数只是⼀种⽆奈之举。
注:⽇常运维不建议使⽤DAC连接,因为DAC连接有更⾼的CPU优先级,服务器压⼒较⼤时有可能会抢占普通线程,引发阻塞。
调整最⼤并⾏度的优点是快速,修改配置后⽆需停机即时⽣效,但⽆法进⾏颗粒度更细的资源分配,⽽下⾯的资源调控器则可以做到。⼆、资源调控器(RESOURCE GOVERNOR)
1.介绍
这是⼀个SQL Server 2008开始的功能,可以通过登录名(函数user_name())、当前时间(函数getdate())等会话属性进⾏筛选,对会话使⽤的CPU、物理 I/O 和内存进⾏⼈为限制,保证关键功能有充⾜的资源可⽤。
开启资源调控器后(默认关闭),会话发出请求会先通过“分类器函数”进⾏分类,路由到相应的“⼯作负荷组”,每个⼯作负荷组都映射到⼀个“资源池”,再根据资源池中设置的CPU、I/O、内存阈值来决定会话的资源分配。
资源池
可以看作是⼀个虚拟的SQL Server实例,默认有两个资源池(内部资源池和默认资源池),⽀持⽤户⾃⾏创建;
注:外部资源池定义的是外部进程的资源,如R 服务的、,与本次讨论的内部资源⽆关。
⼯作负荷组
相当于具有分类标准的会话容器,我们可以根据⼯作负荷组对会话进⾏聚合监控。每个⼯作负荷组都只处于⼀个资源池中,默认有两个⼯作负荷组(内部⼯作负荷组和默认⼯作负荷组),⽀持⽤户⾃⾏创建;
分类
对传⼊会话进⾏分类,分配到⼯作负荷组。
注:资源调控器不向专⽤管理员连接 (DAC) 施加任何控制。⽆需对在内部⼯作负荷组和资源池中运⾏的 DAC 查询进⾏分类。
2.创建与配置资源调控器
通过图形界⾯创建较为直观,如需指定I/O相关的限制,则必须脚本创建,可以在图形界⾯⽣成脚本再进⾏修改。
这⾥新建了⼀个资源池vip_pool、⼯作负荷组vip_group,最⼩CPU预留了5%,最⼤不超过20%,内存⽆限制,资源池中还创建了⼯作负荷组vip_group;(注意下⽅脚本指定了cap_cpu_percent=20,就是说即使系统空闲也不会使⽤超过20%的CPU)
USE [master]
GO
CREATE RESOURCE POOL [vip_pool] WITH(min_cpu_percent=5,
max_cpu_percent=20,
min_memory_percent=0,
max_memory_percent=100,
cap_cpu_percent=20,
AFFINITY SCHEDULER = AUTO
,
min_iops_per_volume=0,
max_iops_per_volume=0)
GO
USE [master]
GO
CREATE WORKLOAD GROUP [vip_group] WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [vip_pool], EXTERNAL [default]
GO
新建分类器函数(此处指定了登录名vip的会话,将路由到⼯作负荷组vip_group,其余会话都将在默认的default组)

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。