【精华】（案例说明）30分钟带你熟练SQL SERVER性能优化的那点儿事儿

前言

　　性能优化是数据库运维人员和中、高级软件开发人员的必备技能，很多时候老司机和新司机的区别就在写出的东西是否优化。

　　博主接触过近千家客户的系统，这些系统都存在着各种各样的性能问题。那么如何透彻的了解我们的数据库性能问题？今天就用一个案例来说明性能优化的那点儿事儿。

　　PS:很多技术人员对优化有一套自己的理解，在阅读本文前请放下你自己的理解。

　　正所谓：跟着博主不迷路，博主带你上高速！

 　 点开案例跟着博主的思路看看优化这些事儿 ： 本文案例Demo

　　

 

了解系统环境

　　优化首先要知道数据库在一个什么样的硬件/软件环境下运行？配置是怎么样的？内存、CPU这些是否能完全被应用？数据库体量多大？

　　首先我们先看一下系统的配置：

　　软件层面，我们要知道我们的操作系统版本，SQL Server版本，以及对应版本的硬件限制（如32位系统不开AWE无法使用超过4G内存、server 2008 标准版最大支持32G内存等）

 

　　本例中我们可以看出，系统环境没有异常问题，SQL Server的补丁不是最新的，CPU资源不充足，可能CPU会成为系统的瓶颈。

全局层面看性能

　　全局层面看问题主要指综合服务器的各种指标表象定位系统的瓶颈或问题，在性能优化中最忌讳的就是看到一个指标马上就下手，针对一个指标的判断是盲目的，很可能使问题偏离本身的根本原因，也可能使优化根本无法解决根本问题而只是表象得到了缓解。

　

性能计数器

　　CPU：在大量时间内CPU的使用率达到100%，说明CPU已经成为瓶颈。

　　

　　内存：内存计数器生命周期在11点时已经降到0，惰性写入器也彪高，说明内存也存在压力，而且比较严重。

　　

　　磁盘：磁盘的平均队列很高（一般系统最佳情况队列应该低于2），并且读队列和写队列都很高。由于内存存在压力，所以现在无法判断磁盘的压力是由于内存不足引起的还是磁盘速度不能满足需要。

　　

 

 

 　　其他计数器：

　　可以看到系统中全表扫面的次数比较多，这表明很多查询没有应用索引。

　　

　　系统在11点左右和11点24左右发生大量锁等待，并且等待时间很长（超过150s）

    

　　通过很多类计数器能综合看出系统的问题。这里不一一细说了

　　

　

系统等待

　　等待是另一个可以全局层面看系统的指标，系统运行的卡慢问题很大部分是因为等待而引起的，那么等待的类型也是可以很直观的反映出系统的问题。

　　几个主要等待：　　

　　ASNC_NETWORK_IO：一般反应出有部分查询可能返回大量数据，请加查具体的等待语句是否需要返回如此多的数据。

　　WAITFOR ：可能是配置了CDC发布订阅或程序中使用了语句waitfor delay

　　CXPACKET：CPU的调度等待。

　　LCK_M_U ：更新语句之间的语句阻塞。

　　WRITELOG：说明程序中有循环的插入跟新操作而频繁的写入日志，磁盘速度不能满足写入频率而造成。

　　

 

　

综合分析

　　综合系统等待和性能计数器，我们基本可以判定出来系统存在以下问题：

　　系统的CPU、内存、磁盘均存在较大的压力，尤其CPU负荷接近100%，系统中存在大量表扫面可能缺失比多索引。系统中有的语句可能要返回大量的不必要数据，系统锁情况严重，等待时间很长，语句执行时间也必然很长。

　　语句执行的整体情况：由于上述的问题影响，那么系统中必然存在大量的长时间语句！

　　

 

解决问题

　　问题的定义是很重要的一步，从全局的多项指标综合分析，让所有问题无所遁形。定位问题后我们先来看一下解决这些问题的基本步骤。

　　本案例是自己模拟的一个情况，所以虽然在表象上来看资源压力很大，但实际在运行的语句不多，场景也有限，但在生产系统如果存在这样的表象，那么说明你的系统性能问题非常严重急需一次详细的优化了。

　　那么下面也介绍一下生产系统遇到这样的问题应该怎么优化，有哪些必要的步骤。

步骤一 针对系统问题对数据库进行全面的优化，提升整体效率

　　很多人优化可能直奔语句，认为语句就可以解决性能的所有问题，其实这样的观点是不全面的，系统的配置，数据库的配置，索引的规划等都是解决性能的必要步骤。

　　例如：系统中的语句都是最佳的，数据库运行还是很慢，可能就是因为你的CHECKDB配置的问题，也有可能因为你自动收缩没有关闭而导致的性能问题。

 

优化操作系统配置

　　针对服务器进行配置检查，查看是否有配置不合理或可以优化的配置项，比如是否配置了虚拟内存？服务器层面是否限制的资源使用？服务器是否高性能模式运行？

 

优化数据库层面的配置

　　针对数据库参数进行合理配置使硬件充分发挥硬件功能，优化不合理配置，降低对数据库造成冲击的可能性。比如：最大并行度？最大内存？

　　

 

 

是否大量缺失索引

　　大量索引缺失必然导致语句性能不佳，并且消耗大量的系统资源，很可能就会造成上面服务器高压力的表象

　　

 

删除无用索引

　　针对数据库中无用的索引进行删除。提升更新操作的时间。

 

删除重复索引

　　针对数据库中重复的索引进行删除。提升更新操作的时间。

　　

 

对重点语句建索引

　　针对系统中消耗大的语句或执行次数多的语句进行分析，评估语句性能问题，并建立合适的索引提，降低语句的资源消耗，升语句运行效率。

 

解决阻塞

　　解决语句间的阻塞，这需要分析语句的阻塞链，到底语句被什么样的操作阻塞了，为什么会阻塞？

　　很多新手经常问的问题：为什么我有的时候查很快有的时候查就很慢？ 答：大多数情况就是你的语句被阻塞了。

　　

 

优化TempDB

　　针对TempDB调优，减少TempDB资源争用导致的压力。本例中可以死看到有TempDB的争用等待，所以对TempDB的优化也是必要的。

 

 

优化日志碎片

　　针对日志增大，带来的日志碎片问题进行优化。

清除索引碎片

　　检查系统的索引维护情况，并针对碎片过大的表进行碎片清除操作。主要体现在系统中有老化的索引，索引的老化导致索引的性能不高或失效。

 

一阶段预期效果

 

　　一阶段的优化是对性能的整体提升，性能提升也会很明显，针对不同系统提升一般在2-3倍。

步骤二 处理热点问题

　　处理热点问题主要是在阶段一的基本优化后针对重点的语句进行调优，可能包含创建索引，修改写法，查询提示，计划向导等等。

　　在语句调优中请主要关注：是否有缺失索引，是否存在隐式转换，语句的执行时间、CPU、逻辑读写量、物理读写量、占用TempDB空间等信息。

 

　　例：这样一条语句经过第一阶段的优化并没有太大的提升，而且资源消耗依然很大，那么我们可以针对这条语句进行详细的二阶段优化。

　
　

简单的优化一下

　　　

　　只是简单的改了下语句的写法时间有7秒变成1秒，内存消耗从300+MB 变成 1MB

 

 

二阶段预期效果

    阶段二的优化属于细致的优化步骤，要针对更为具体的语句、具体的情况。经过本阶段优化可以使系统中大部分语句从写法、配置、运行指标都趋于优化值。

 

步骤三 针对业务

　　这个步骤需要配合开发人员，到底哪些功能依然慢？执行了哪些语句？是领导用的功能？还是一般可以慢的功能？如果大领导用的功能，那可能你就需要多花些心思了。这部分这里就不展开说了。

 

三阶段预期效果

    第三阶段属于最细致的阶段，可以结合业务真正点对点的消灭系统中存在问题。

导图

　　针对性能优化奉上几个图希望能帮助数据库从业者梳理一下优化的思路（个人思路仅供参考，不完善的地方也请见谅）

　　

    CPU：

　　

 

 

 

　　内存：

 　　

 

 

 

　　磁盘：

　　

 

 

 

　　等待：

　　

 

 总结

　　在性能优化中最忌讳的就是看到一个指标马上就下手，针对一个指标的判断是盲目的，很可能使问题偏离本身的根本原因，也可能使优化根本无法解决根本问题而只是表象得到了缓解。

　　本文只是通过一个例子简述一下优化的基本思路，希望帮助更多数据库从业者，了解性能优化。

　　本文只阐述了思路，具体的各部分解决方式请参见我的系列文章：SQL SERVER全面优化-------Expert for SQL Server 诊断系列

　　性能的调优是一个持续性的工作，不是一次解决了问题以后就可以高枕无忧了，定期的巡检也是数据库从业者必要的工作之一，做到及早发现及早解决。

　　巡检系列文章请参见：轻松精通数据库管理之道——运维巡检系列

 

 ----------------------------------------------------------------------------------------------------

注：此文章为原创，欢迎转载，请在文章页面明显位置给出此文链接！
若您觉得这篇文章还不错请点击下右下角的推荐，非常感谢！