-
sql语句大全之数据库优化案例——————某市
记得在自己学习数据库知识的时候特别喜欢看案例，因为优化的手段是容易掌握的，但是整体的优化思想是很难学会的。这也是为什么自己特别喜欢看案例，今天也开始分享自己做的优化案例。
最近一直很忙，博客产出也少的可怜，今天整理了一下自己做过优化或各种方案的客户已经超过100家了，今天分享的案例算是在这些客户中比较典型的了！没有什么高大上都是常见的问题！在之前的博客中都有过提及，那么本篇我们就结合之前的技术点来看看这个案例。学习优化手段的看官们可以参见我的优化系列：
SQL SERVER全面优化-------Expert for SQL Server 诊断系列
系统环境
首先我们来看一下这个系统配置及现状，为什么说这个客户经典？那就是因为这个客户已经达到可以慢的地方都慢，不该慢的地方也慢！
首先这是一套医院的HIS系统，慢到什么程度呢？各种功能卡死不管是交款、医嘱、开药一些列几乎所有的功能都慢。但是卡慢的现象只出现在上午的高峰期！
先来看看系统配置 ：
数据库版本是SQL SERVER 2008R2，数据量大概1个多T，服务器64CPU 、128G内存，服务器只运行数据库。
咋一看服务器确实有点老了，数据量也大了，内存和CPU什么的明显不够用了！
数据库指标
那么我们再看一下数据库的一些表象：
每秒请求数量：
语句执行情况：
等待情况：
等待时间：
CPU指标：
内存一些指标：
磁盘队列：
-------------------还很多指标就不一一展示了------------------
看到这些基本的指标，除了慢你能看出什么？问题出在哪里？怎么样快速解决？能有一个优化的步骤呈现在眼前么？
优化阶段一（常规优化）
很多时候系统慢要究其原因，难道上线时候就这么慢？那不可能，厂商根本无法交付的！那么问题来了，什么时候开始慢的？对系统做过哪些调整？
简单的调研开始...给我的只有不到半天的调研时间...得知的基本问题就是系统在最近一月增加了很多功能，有上线了很多其他系统接口！
那么直接就搞新功能、新程序接口语句？ 我认为并不是这样，从一名数据库从业人员来说，看到这样的系统一定要先解决大面积等待问题！个人经验来看很多系统大面积等待解决系统会有个很大的提升和改善！
配合一些常规的调优手段阶段一开始了,主要给系统大面积创建影响高开销大的索引，调整系统参数，优化tempDB、开启快照读等....具体不细说了，前面系列文章中都有！
预期：
一般系统上面一轮优化会有明显的改善，我认为这一轮以后系统会明显变快，语句CPU会下降到70%左右，内存压力也会有所减少。
结果：
自信满满的我第二天去了各个科室....部分功能依然超时还是各种慢...CPU依然90%以上，内存压力依然明显。但是收集的数据来看，长时间语句数量已经大幅降低，系统等待阻塞情况也明显好转。
优化前
优化后
优化前
优化后
优化阶段二（针对语句）
再次分析解决大面积语句阻塞的系统，发现现在的情况，主要有如下几个：
- 由于内存不足导致的IO压力。
- 系统CPU依然彪高。
- 部分功能语句依然慢，消耗的资源很高。
再次对系统调研：
- 哪些功能慢，执行的语句是什么。
- 系统的接口语句问题。
- 系统中还有哪些消耗资源高的语句，是否能优化。
调研后，我遇到了最常见也是最大的问题： 语句慢由于程序！很多人看到这会说程序慢就改呗，那有啥问题？ 问题就在于你来做优化直接了当的和人家开发人员说你程序太烂必须改！如果你是程序开发人员你会有什么样的反应？
他会说：对不起，影响太大改不了！
那么这个优化项目黄了，或者你要付出更大的代价绕过这样的问题。
分析中发现程序使用了大量各种自定义函数，有一定经验的人都应该知道，语句在筛选的列上使用函数是没有办法使用索引查找的，这样相对于这种单表数据就几百甚至几千万的表，是何等的灾难！但是不能冒然突出修改程序，那还能怎么优化呢？大概分析后得出结论，程序主要消耗在几部分：
- 部分业务功能语句慢。
- 接口语句慢（主要是视图，供其他程序调用）。
- 还有报表程序。
针对第一部分在不能改程序的情况下，尝试添加计划向导改变语句执行情况；
针对第二部分修改接口视图，包括替换掉函数、添加索引等；
针对第三部分报表这东西不是短期就可以优化的，所以再原有镜像的方案上添加快照，实现了简单的读写分离，直接分走；
语句优化的效果：
优化前
优化后
优化前
优化后
预期：
90%消耗高的语句都得到了优化，系统应该可以快起来了，CPU、内存指标也应该正常了！
结果：
语句的消耗和时间都降下来了，系统卡慢现象有明显好转，但是CPU依然90%以上、内存压力依然明显，磁盘队列还是很高！系统性能问题依然存在。
优化阶段三（深入指标分析）
经过前两个阶段的优化一般系都会明显好转，并且指标正常，这也是前面提到的可以慢的地方慢已经解决，那么为什么CPU、内存压力没有缓解？难道真的是64CPU、128G内存不能支持了？需要加内存换CPU？难道要做负载均衡？各种拆分？
CPU分析
首先我对CPU压力进行了分析，综合语句的CPU消耗和CPU的表象来看，很大一部分应该不是语句执行消耗的！那么服务器上确实也没有跑其他程序，CPU资源哪里去了？
看看这个计数器：
SQL的编译次数高峰时间段达到每秒2000多次！很多书上写过，相信很多看官也知道，语句不参数化会给CPU造成压力，这就是个鲜活的例子！那么解决办法也是比较粗暴，程序无法修改那么就在数据库上开启强制参数化。
看下效果：
我想不用多说什么了！
内存分析
看到了CPU的现象那么内存的问题也有眉目了，这么多编译即席查询，首先看一下内存中缓存了那些数据：
SQLOPTIMIZER Singlepage占到了80多个G，而在查询数据页的缓存只有20个G，而且仍然在被不断压缩，那么内存没压力就怪了！这个SQLOPTIMIZER Singlepage尝试了一下是无法通过DBCC FREExxxxx的操作释放的，所以在半夜直接重启了SQL 服务！将近2年没有重启的SQL服务就这么折在我的手里了！
重启后页生命周期：
内存这个问题，不知道是不是微软的一个小BUG，查询计划的缓存个人理解不会一直压榨数据缓存的，客户的数据库没有补丁，但是查阅08的各个补丁也没有找到相关问题的修复。
也请遇到过或了解的朋友给点提示！
预期：
语句已经优化，阻塞情况也被解决，CPU、内存、磁盘压力也没有了，系统肯定快起来了！
结果：
系统快起来了！
总结 ： 文章只是简单的描述了一下某医院HIS系统的优化过程，当然一周的工作仅仅通过一篇文章写出全过程细节必然不那么详尽，还望看官们见谅！
整个的优化过程是程序只修改了2条语句，其他都是通过数据库优化手段完成。而且没有添加任何硬件资源！
优化过程主要分为：
- 系统整体调研 ：和科室用户沟通慢的情况，系统最近变更情况，并收集数据。
- 常规优化 ： 调整数据库参数配置，添加索引，解决阻塞。
- 再次调研：系统慢功能，慢语句。
- 针对语句优化：写法不足，是否缺失索引，是否能加提示、计划向导等
- 整体压力是否缓解：如果仍然压力很大找到瓶颈，是否可以解决？如果不能解决才考虑添加硬件或选用分离、分离等方案。
文章用用到的 Expert FOR SQLSERVER 工具下载链接：http://zhuancloud.com/ReceptionViews/Install.html
