SQL Server性能优化案例报告

 SQL Server性能优化案例报告收藏
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利用率下降。以上对比显示,优化效果较明显


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/redbirdli/archive/2009/03/11/3980810.aspx

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值