SQL Server性能优化案例报告

wocaosinima

1. 问题分析

1.1 现象描述
某企业客户内部知识管理系统基于微软SharePoint服务器产品并进行了应用扩展开发，NLB负载均衡部署，后台数据库采用SQL Server 2000 企业版，双核 4C 8G内存两节点群集。在两三年的使用过程中，随着系统用户的增多，出现了数据库服务器CPU占用过高的情况，导致前端访问响应速度慢，经常超时等问题。

1.2 性能计数器分析


用户连接
经过对SQL Server关键性能指标的采集和分析，发现用户连接指标数值过大。用户连接的数据基本保持在700-1000之间，不仅是在忙时段(AM:10)，且在闲时段(PM: 6)也基本保持不变，基本可以确定是数据库连接池配置不当或有代码没有释放可用连接，需要通过应用代码进行问题排查。
锁请求/秒
经过向用户的了解，该系统为多数读取，少数写入的系统，但从性能计数器的观测值发现锁请求/秒的指标值平均约为158418.485，最高值可达到558870.266，锁操作总体过大，应该从应用层面进行分析优化。
完全扫描/秒
完全扫描/秒计数器指示有多少不使用索引而进行的全表扫描，测量过程中显示平均值达到100左右，最高值达到832.998，应分析SQL查询语句和数据库索引的对应关系，追加必要的索引以减少全表扫描的次数。
1.3 SQL工具分析

通过使用SQL 事件探查器和查询分析器等工具对SQL Server内部语句执行的性能状况列出了明细，并可将其中的CPU占用较高的任务列出，如第一行显示的大量数据连接导致CPU占用较高、第二行复杂子查询Join下存在部分索引未创建、wf_Instance_track表有大量过期的历史数据时变慢等问题。
1.4 应用代码分析
经过对系统源代码的粗略分析，发现以下一些问题：
a. SqlHelper中的GetConnection每次都是创建一个全新的数据库连接而返回给调用代码，导致连接无法被重用，每次全新创建也会增加服务器的负担；
b. SqlHelper中的TestConnection每次都是创建一个全新的数据库并且打开连接以测试连接的可用性，但是并不关闭就返回了。
c. AcceptUpdate中的SelectDb调用SqlHelper中的GetConnection获得连接后进行数据库查询操作，但使用后并不关闭相应连接
d. AcceptUpdate中的UpdateDs调用SqlHelper中的GetConnection获得连接后进行数据库更新操作，但使用后并不关闭相应连接
e. ColSelect.aspx中的btn_Ok_ServerClick调用SqlHelper中的GetConnection获得连接后进行数据库更新操作，但使用后并不关闭相应连接

2. 优化方案
2.1 代码优化
a. 由统一的代码管理数据库连接；
b. 使用数据库连接池技术管理连接；
c. 使用后必须关闭数据库连接；
d. 减少全新创建数据库连接的次数（如减少不必要的TestConnection操作）
e. 优化SQL语句，减少表锁；
f. 优化SQL语句，使查询能尽量使用索引，减少全表扫描；
g. 适当使用临时表，以减少SQL复杂度和子查询；
h. 其他与数据库性能有关的代码排查；

2.2 数据库优化
a. 创建经常被查询用到的索引；
b. 适当调整SQL 实例性能相关的参数，以使资源使用最大化（但要考虑为操作系统保留小部分资源）；
c. 备份和分离过期的历史数据(如2006年的状态跟踪数据)，并建立定期的数据库清理机制；
d. 定期观测和记录SQL性能计数器，了解性能状况变化；
e. 升级到更高版本的SQL Server 产品，使用分区表等新技术能够发挥更佳的服务器性能；

2.3 优化工作量估算
代码优化和测试验证：约需10-15个工作日（依原有代码质量和数量决定）
数据库优化和测试验证：约需5-7个工作日
3. 优化实施
3.1 代码优化
对代码结构进行了性能分析，发现了一些代码质量问题。
　　

目录名	文件名	方法名
App_Code\Site	AcceptUpdate.cs	SelectDb
App_Code\Site	AcceptUpdate.cs	UpdateDs
FramePage	ColSelect.aspx.cs	btn_Ok_ServerClick
App_Code	SqlHelper.cs	GetConnection

分析、修改、部署共计3人天

注：尚未对存储过程进行优化

3.2 数据库优化
对执行性能差但使用频率较高的部分数据表进行了索引创建。
　　

表名	索引列	索引名
略

分析、修改、部署和测试和报告共计5人天

4. 优化总结
4.1 性能对比

　　

性能参考对象	优化前	优化后（闲）	优化后（忙）	说明
系统CPU利用率	86.235%左右	15.183%左右	45.583%左右	具体截图如下图1
完全扫描/秒	109.337左右	23.175左右	42.965左右	具体截图如下图2
锁请求/秒	158418.485	37101.090	69444.232	具体截图如下图3
索引搜索/秒	98472	25374	43653	具体截图如下图4
用户连接数	800-1200	541	820

图0

图1

图2

图3

图4

4.2 待决问题
由于担心影响业务逻辑的正确性和测试的复杂性，没有对以下几个部分进行优化：
1. 数据库连接较多的问题，整体解决需要重新架构设计
2. 复杂度较高的SQL语句以及视图的优化
3. 存储过程的优化，防止表锁
4. 工作流引擎内部机制不了解
4.3 系统建议
数据库中表的数据量不是很大，单个简单的查询对整个系统的影响较小，较复杂的视图或存储过程优化有性能问题，随着数据量的增大影响而更明显，所以可定期清除不需要的历史数据。
4.4 总结
通过增加对数据量较大的表以及查询较频繁的表增加索引，能够减轻数据库完全扫描的压力，使CPU利用率下降。以上对比显示，优化效果较明显。