<返回更多

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

2021-07-19    风影互联
加入收藏

众所周知SQL SERVER是微软的数据库拳头产品,有着图形化友好界面、操作门槛低、部署难度小,一键式安装的特点,受到全球开发者及企业的青睐。从历代版本说起,经典2000版本是划时代的里程碑作品,至今还能在各种财务软件、ERP偶遇一回。

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

但随着软件的升级换代,早期的2000版本从性能、功能上已经难以满足现行需求的发展,随之出现了2005、2008、2008R2、2012、2014、2016、2017、2019版本,2005和2008作为2000的实力升级版本,已经在数据库界扛了多年把子。当然,也经常与另外两大佬Oracle和MySQL轮流坐老大。

真实案例分析

在很多生产环境中,随着日积月累,数据量越来越庞大,如今信息爆炸的大数据时代来临,给它也增加了不小的压力。

这些年来,对SQL SERVER卡顿、查询慢、死锁的问题也经常耳闻。

但说起方法,五花八门,无外乎复杂SQL语句优化,面对庞大的ERP、OA系统,改造难度极大。

所以,在这几年工作经验中,总结出一些简单却有奇效的方法与大家分享。

2020年年末,朋友紧急邀请,处理了一个棘手案例:

一家本地物流企业,ERP上线三年,系统出现频繁卡死、一个简单查询十几分钟,内存占用却仅8G,企业一度停工,IT部门技术员百思不得其解,老板几近抓狂。

经过分析,基本情况如下:

Win2012系统,2008R2 32位数据库,普通机械硬盘,内存16G,仅占用8G,CPU占用稳定4%左右,windows自身反应很慢,桌面窗口屡次残影。

根据经验,第一时间查看任务管理器的“性能监视器”,发现硬盘I/O一直居高不下,满载100%

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

再看看高I/O进程,居然是SQL SERVER的tempdb文件。

然后大致浏览了几个大表对应的索引,大部分为空或者碎片率100%

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

看到这,随行的一位ERP实施工程师皱了皱眉头,感慨ERP系统语句可能需要进行大幅优化调整。老板一听,脸顿时沉了下来,这意味着有很多不可预估的风险发生,并且花费大量时间。

老板要求在不停工、停业、伤筋动骨改代码的情况下,先让系统运转起来即可。

大家听完直摇头,因为距离天亮开工还不到11个小时了。

简单粗暴,10个方法调一剂良方

时间一分一秒过去,现场氛围略微沉寂,让我有机会冷静片刻。

既然修改代码不现实,那就从外围解决问题。

方法1:SQL SERVER 32位版本开启AWE,并拆机扩容内存到64G

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

32位程序无法使用超过4G的内存,数据库无法有效地利用内存,导致频繁的硬盘读写。开启后将允许32位数据库锁定内存页,允许使用超过4G的内存,利用内存充分释放性能。另外,注意在组策略中将sqlserver.exe进程的用户授予允许锁定内存页权限。

方法2:将系统临时数据库tempdb.mdf移动到独立的物理存储容器

很多SQL语句,诸如 select * into #tmp from table,这里的临时表#tmp就储存于tempdb。如果tempdb与主数据库存在于同一个物理硬盘,当临时表使用频繁时,可能造成互相争夺资源的现象发生,导致硬盘I/O瓶颈。tempdb内的表在SQL SERVER进程每次启动时会清空。可以理解为它只是mssql的一个临时草稿纸。

当然,你也可以用Primo Ramdisk等软件将内存一部分虚拟成磁盘分区,然后转移tempdb库。性能将比SSD更强悍。

方法3:全表索引碎片整理

右键管理>维护计划>新建维护计划,创建一个全库全表重新生成索引任务,暂时设置为手动。计划创建完成后右键执行,漫长的等待执行完毕。

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

方法4:关闭计算机虚拟内存

系统虚拟内存会在硬盘上虚拟一块区域作为虚拟内存,而硬盘的读写速度远远慢于内存速度。关闭虚拟内存将强制所有程序使用高速内存运行,降低硬盘I/O。但是关闭虚拟内存可能导致一些问题,非紧急需要或对服务器上各类软件非常了解,慎重操作。

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

这里ERP采用服务端占用大量虚拟内存,关闭虚拟内存强制使用更快的物理内存。当然,前提是物理内存足够大。

方法5:电源管理设置为高性能模式+性能优先级

控制面板,电源管理,将方案设置为高性能模式,再将计算机性能优先级设置为后台服务。这两点看似不起眼,但作用明显。如果电源方案为节能或默认的平衡,CPU将工作在降频模式,硬件散热系统也工作在低频环境,如果温度过高,还会进一步降低CPU频率,导致性能下降,win2008以上版本请选择“卓越性能”,开启方法请自行搜索。

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

方法6:SQL Server Profiler跟踪器找出问题语句

用mssql自带的工具SQL Server Profiler,设置时间大于2.5秒的执行全部列出,再将截获的语句复制到SQL Server Managemen,右键“在数据库引擎优化顾问中分析查询”,它将全自动地帮你分析出索引方案来优化耗时长的语句,只要在分析结果点击应用,即可获得大幅性能提升。而这些操作,无需懂任何SQL编程就能简单完成。性能提升简直是质的飞跃。

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 


无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 


无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

揪出那些拖垮你性能的语句,一键优化它。优化效果非常惊艳。可能原本需要十几分钟的查询现在只需三秒。如上图,预计估计提高程度:80%,80%这是个什么概念。

方法7:当然别忘了磁盘碎片整理(机械硬盘)

机械硬盘需要定期整理磁盘碎片,否则会降低硬盘读写性能,有条件的上SSD,建议组Raid或上云服务器采用云SSD盘。这个就不需要配图了吧。

方法8:修改数据库事务隔离级别

MSSQL数据库隔离级别最低级别为read committed snapshot,它可以将数据库死锁概率降到最低,看自身情况开启。DBCC USEROPTIONS语句可以查看当前的隔离级别

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

方法9:数据库设置及服务设置

在管理器中右键服务器,属性>处理器,勾选“提升SQL SERVER优先级”。

再选中数据库,右键属性>选项,恢复模式:简单,兼容模式选择合适的兼容级别

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

方法10:升级高版本的SQL SERVER ,更换x64位系统

如果现行使用的是SQL SERVER2008及以下版本,建议升级到2014或更高版本,我曾在同一台服务器上测试装过2008和2014、2019三个版本,进行10万条数据的写入、更新、删除,得出的结果如下:

无需改代码,提高SQL SERVER数据库性能的10个最简单方法

 

可以明显发现,2014相对于2008有很大提升,2019相对于2014提升并不是非常明显。2014版本加入了内存表的概念,预计使用得当对性能提升有一定帮助。

毕竟三者相差了多个大版本,微软总不能只更新了版本号吧~

一顿操作猛如虎

跟踪器及数据库引擎优化顾问优化了将近三百多条语句,总共花费了四个多钟头时间,全部完成后,老板从宿舍召集一些老员工通宵测试优化结果。

一番操作测试下来,丝滑般顺畅,所有卡顿完全消除,查询基本都是秒出结果,死锁消失,硬盘I/O稳定维持在2MB/S以下。一筹莫展的脸上露出了久违的舒坦,可以安心地睡个觉。

之后,IT部门除了每周定时执行索引维护外,没有其他操作,至今未见卡顿。

总结10个简单有效的方法分享给大家,希望能给正在为此类问题水深火热的人带来曙光。

欢迎收藏以备不时之需,欢迎转发,评论,指正!

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>