SQL Server性能优化案例报告

最新推荐文章于 2021-04-25 08:31:00 发布

ReViSion

最新推荐文章于 2021-04-25 08:31:00 发布

阅读量1.1k

点赞数 1

分类专栏： C# 数据库管理数据库查询语句文章标签： sql server 性能优化数据库优化数据库连接池 sql

数据库查询语句同时被 3 个专栏收录

28 篇文章 0 订阅

订阅专栏

数据库管理

26 篇文章 0 订阅

订阅专栏

8 篇文章 0 订阅

订阅专栏

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