讯享网

 <p id="04G5C0VK">众所周知SQL SERVER是微软的数据库拳头产品，有着图形化友好界面、操作门槛低、部署难度小，一键式安装的特点，受到全球开发者及企业的青睐。从历代版本说起，经典2000版本是划时代的里程碑作品，至今还能在各种财务软件、ERP偶遇一回。</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F8a9b0f39j00qwmqdy002kc000hp00cnm.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C0VN">请点击输入图片描述（最多18字）</p><p id="04G5C0VP">但随着软件的升级换代，早期的2000版本从性能、功能上已经难以满足现行需求的发展，随之出现了2005、2008、2008R2、2012、2014、2016、2017、2019版本，2005和2008作为2000的实力升级版本，已经在数据库界扛了多年把子。当然，也经常与另外两大佬Oracle和mysql轮流坐老大。</p><p id="04G5C0VQ">真实案例分析</p><p id="04G5C0VR">在很多生产环境中，随着日积月累，数据量越来越庞大，如今信息爆炸的大数据时代来临，给它也增加了不小的压力。</p><p id="04G5C0VS">这些年来，对SQL SERVER卡顿、查询慢、死锁的问题也经常耳闻。</p><p id="04G5C0VT">但说起方法，五花八门，无外乎复杂SQL语句优化，面对庞大的ERP、OA系统，改造难度极大。</p><p id="04G5C0VU">所以，在这几年工作经验中，总结出一些简单却有奇效的方法与大家分享。</p><p id="04G5C0VV">2020年年末，朋友紧急邀请，处理了一个棘手案例：</p><p id="04G5C100">一家本地物流企业，ERP上线三年，系统出现频繁卡死、一个简单查询十几分钟，内存占用却仅8G，企业一度停工，IT部门技术员百思不得其解，老板几近抓狂。</p><p id="04G5C101"><strong>经过分析，基本情况如下：</strong></p><p id="04G5C102">Win2012系统，2008R2 32位数据库，普通机械硬盘，内存16G，仅占用8G，CPU占用稳定4%左右，Windows自身反应很慢，桌面窗口屡次残影。</p><p id="04G5C103">根据经验，第一时间查看任务管理器的“性能监视器”，发现硬盘I/O一直居高不下，满载100%</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F6e1510c1j00qwmqdy001ac000bm0091m.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C106">请点击输入图片描述（最多18字）</p><p id="04G5C108">再看看高I/O进程，居然是SQL SERVER的tempdb文件。</p><p id="04G5C109">然后大致浏览了几个大表对应的索引，大部分为空或者碎片率100%</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F719e3df2j00qwmqdy001ac000dp006tm.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C10C">请点击输入图片描述（最多18字）</p><p id="04G5C10E">看到这，随行的一位ERP实施工程师皱了皱眉头，感慨ERP系统语句可能需要进行大幅优化调整。老板一听，脸顿时沉了下来，这意味着有很多不可预估的风险发生，并且花费大量时间。</p><p id="04G5C10F">老板要求在不停工、停业、伤筋动骨改代码的情况下，先让系统运转起来即可。</p><p id="04G5C10G">大家听完直摇头，因为距离天亮开工还不到11个小时了。</p><p id="04G5C10H">简单粗暴，10个方法调一剂良方</p><p id="04G5C10I">时间一分一秒过去，现场氛围略微沉寂，让我有机会冷静片刻。</p><p id="04G5C10J">既然修改代码不现实，那就从外围解决问题。</p><p id="04G5C10K"><strong>方法1：SQL SERVER 32位版本开启AWE，并拆机扩容内存到64G</strong></p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2Fade938a9j00qwmqdy0014c000f00087m.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C10N">请点击输入图片描述（最多18字）</p><p id="04G5C10P">32位程序无法使用超过4G的内存，数据库无法有效地利用内存，导致频繁的硬盘读写。开启后将允许32位数据库锁定内存页，允许使用超过4G的内存，利用内存充分释放性能。另外，注意在组策略中将sqlserver.exe进程的用户授予允许锁定内存页权限。</p><p id="04G5C10Q"><strong>方法2：将系统临时数据库tempdb.mdf移动到独立的物理存储容器</strong></p><p id="04G5C10R">很多SQL语句，诸如 select * into #tmp from table，这里的临时表#tmp就储存于tempdb。如果tempdb与主数据库存在于同一个物理硬盘，当临时表使用频繁时，可能造成互相争夺资源的现象发生，导致硬盘I/O瓶颈。tempdb内的表在SQL SERVER进程每次启动时会清空。可以理解为它只是mssql的一个临时草稿纸。</p><p id="04G5C10S">当然，你也可以用Primo Ramdisk等软件将内存一部分虚拟成磁盘分区，然后转移tempdb库。性能将比SSD更强悍。</p><p id="04G5C10T"><strong>方法3：全表索引碎片整理</strong></p><p id="04G5C10U">右键管理&gt;维护计划&gt;新建维护计划，创建一个全库全表重新生成索引任务，暂时设置为手动。计划创建完成后右键执行，漫长的等待执行完毕。</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F84a334daj00qwmqdy0013c000hk009em.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C111">请点击输入图片描述（最多18字）</p><p id="04G5C113"><strong>方法4：关闭计算机虚拟内存</strong></p><p id="04G5C114">系统虚拟内存会在硬盘上虚拟一块区域作为虚拟内存，而硬盘的读写速度远远慢于内存速度。关闭虚拟内存将强制所有程序使用高速内存运行，降低硬盘I/O。但是关闭虚拟内存可能导致一些问题，非紧急需要或对服务器上各类软件非常了解，慎重操作。</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2Fd9e8fee3j00qwmqdy0019c0009g0086m.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C117">请点击输入图片描述（最多18字）</p><p id="04G5C119">这里ERP采用服务端占用大量虚拟内存，关闭虚拟内存强制使用更快的物理内存。当然，前提是物理内存足够大。</p><p id="04G5C11A"><strong>方法5：电源管理设置为高性能模式+性能优先级</strong></p><p id="04G5C11B">控制面板，电源管理，将方案设置为高性能模式，再将计算机性能优先级设置为后台服务。这两点看似不起眼，但作用明显。如果电源方案为节能或默认的平衡，CPU将工作在降频模式，硬件散热系统也工作在低频环境，如果温度过高，还会进一步降低CPU频率，导致性能下降，win2008以上版本请选择“卓越性能”，开启方法请自行搜索。</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2Faaj00qwmqdy001ec000kc009km.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C11E">请点击输入图片描述（最多18字）</p><p id="04G5C11G"><strong>方法6：SQL Server Profiler跟踪器找出问题语句</strong></p><p id="04G5C11H">用mssql自带的工具SQL Server Profiler，设置时间大于2.5秒的执行全部列出，再将截获的语句复制到SQL Server Managemen，右键“在数据库引擎优化顾问中分析查询”，它将全自动地帮你分析出索引方案来优化耗时长的语句，只要在分析结果点击应用，即可获得大幅性能提升。而这些操作，无需懂任何SQL编程就能简单完成。性能提升简直是质的飞跃。</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2Fb49835abj00qwmqdy003qc000np00fcm.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C11L">请点击输入图片描述（最多18字）</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F590de4cdj00qwmqdy0025c000h7008gm.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C11Q">请点击输入图片描述（最多18字）</p><p id="04G5C11S">揪出那些拖垮你性能的语句，一键优化它。优化效果非常惊艳。可能原本需要十几分钟的查询现在只需三秒。如上图，预计估计提高程度：80%，80%这是个什么概念。</p><p id="04G5C11T"><strong>方法7：当然别忘了磁盘碎片整理（机械硬盘）</strong></p><p id="04G5C11U">机械硬盘需要定期整理磁盘碎片，否则会降低硬盘读写性能，有条件的上SSD，建议组Raid或上云服务器采用云SSD盘。这个就不需要配图了吧。</p><p id="04G5C11V"><strong>方法8：修改数据库事务隔离级别</strong></p><p id="04G5C120">MSSQL数据库隔离级别最低级别为read committed snapshot，它可以将数据库死锁概率降到最低，看自身情况开启。DBCC USEROPTIONS语句可以查看当前的隔离级别</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2Fa535cf22j00qwmqdy001ic0009r00a9m.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C123">请点击输入图片描述（最多18字）</p><p id="04G5C125"><strong>方法9：数据库设置及服务设置</strong></p><p id="04G5C126">在管理器中右键服务器，属性&gt;处理器，勾选“提升SQL SERVER优先级”。</p><p id="04G5C127">再选中数据库，右键属性&gt;选项，恢复模式：简单，兼容模式选择合适的兼容级别</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F34fc02adj00qwmqe00012c000hl0064m.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C12A">请点击输入图片描述（最多18字）</p><p id="04G5C12C"><strong>方法10：升级高版本的SQL SERVER ，更换x64位系统</strong></p><p id="04G5C12D">如果现行使用的是SQL SERVER2008及以下版本，建议升级到2014或更高版本，我曾在同一台服务器上测试装过2008和2014、2019三个版本，进行10万条数据的写入、更新、删除，得出的结果如下：</p><p class="f_center"><img src="https://nimg.ws.126.net/?url=http%3A%2F%2Fdingyue.ws.126.net%2F2021%2F0722%2F2001e433j00qwmqe0001pc000f40091m.jpg&thumbnail=660x&quality=80&type=jpg"/><br/></p><p id="04G5C12G">请点击输入图片描述（最多18字）</p><p id="04G5C12I">可以明显发现，2014相对于2008有很大提升，2019相对于2014提升并不是非常明显。2014版本加入了内存表的概念，预计使用得当对性能提升有一定帮助。</p><p id="04G5C12J">毕竟三者相差了多个大版本，微软总不能只更新了版本号吧~</p><p id="04G5C12K">一顿操作猛如虎</p><p id="04G5C12L">跟踪器及数据库引擎优化顾问优化了将近三百多条语句，总共花费了四个多钟头时间，全部完成后，老板从宿舍召集一些老员工通宵测试优化结果。</p><p id="04G5C12M">一番操作测试下来，丝滑般顺畅，所有卡顿完全消除，查询基本都是秒出结果，死锁消失，硬盘I/O稳定维持在2MB/S以下。一筹莫展的脸上露出了久违的舒坦，可以安心地睡个觉。</p><p id="04G5C12N">之后，IT部门除了每周定时执行索引维护外，没有其他操作，至今未见卡顿。</p><p id="04G5C12O">总结10个简单有效的方法分享给大家，希望能给正在为此类问题水深火热的人带来曙光。</p>