MS SQL 数据库调优小记

典

几个月前陆陆续续做了一个主要数据库的调优，写出来给自己留个记号（已经忘记掉很多了），也希望对初学者有参考意义。

这个MS SQL2000数据库越来越慢，主要原因是每年在系统上加应用模块，而且业务数据不断增加。主要的表现有两个：在外地的２００多销售人员在Sync数据的时候长达5-10分钟，每天都有好多DeadLock。 而且有一种越来越差的发展趋势。

我们这里没有专职的ＳＱＬDBA，我只好自己上马，用了一部分时间做了些调优，取得了不错的效果，现在Sync的时间大部分都在３０秒内，很少有DeadLock，几个月过去了高枕无忧。

1. 　数据库硬件升级，买了一个很好的服务器，使用64位系统（原来是32位），40多G内存，windows2008 / SQL Enterprise 2008

２　Index优化, 对几个大的表调整了index,调整后效果非常明显，使用statistics比较就知道，一个同样的查询，从ＳＣＡＮ几十万降到ｓｃａｎ几千。调的时候仔细阅读代码，从代码中发现蛛丝蚂迹， where 或者join on所需要的ｉｎｄｅｘ　比较好找，ｓｅｌｅｃｔ　后面隐含的ｉｎｄｅｘ需求比较难点。　主要的工具就是ｅｘｅｃｕｔｉｏｎ　ｐｌａｎ

３　优化修改了一批stored proc,　尤其是有些代码带有很多子查询，仔细阅读后发现了一些不合理的地方，进行改写并比较ｓｔａｔｉｓｔｉｃｓ，效果明显；一些无法避免的大查询，在业务许可的时候加入ｒｅａｄｕｎｃｏｍｍｉｔｔｅｄ　ｈｉｎｔ，避免ｌｏｃｋ　

４　稍微修改了一下系统，
４．１　一些比较静态的查询（比如过去几个月客户们的销售历史），在半夜的时候用ｊｏｂ建立临时表，并给临时表分配ｉｎｄｅｘ，这样查起来飞快，效果很明显；
４．２　一些计算：需要用到大量ｊｏｉｎ或子查询，直接改到客户端去做计算，有好几个这样的成功例子。其中有一个网页查询，每次需要延迟１０几秒才能有结果，用户非常不爽，经过分析后我用Ｃ＃给写了一段代码，把计算逻辑放到网页内部去完成，结果用户感觉不到任何延迟（延迟低于１秒），
４．３　差不多同上，干脆把一些中间计算结果保存到数据表中，其它地方需要的时候就不用算来算去，直接取出来

５．有些比较新的技术对执行效率并没有好处，比如Common Table Expression - CTE，　我做了很多ｓｔａｔｉｓｔｉｃｓ比较，效率并没有任何提高，只是使代码读起来非常简洁而已

本来还有几个慢的ｓｔｏｒｅｄ　ｐｒｏｃ程序，已经准备了优化方案，考虑到方方面面的用户已经对系统很满意了，所以暂时不管了。

ericlgq

不错不错，感谢楼主分享。

flyspirit

谢谢楼主经验分享。

顺便请教下，你们公司数据库有没有水平扩展能力。能不能加服务器数量来提高性能的？

rdcwayx

改用oracle 会不会要好些？

wtf

03年也做过一个。其实SqlServer的优化主要应该针对超过4个表的链接。4个表之下的链接查询不管写的多烂，其实SqlServer会自动优化效果不会太差。记得好像Oracle等也是，针对4个表之下的查询，基本上不用调整啥子查询什么的，99%的子查询运行时已经被 dbms 自动优化了

不知道现在咋样，当时用的是sqlserver2k

另外lz说的index也是大头。

其他更多还是关注逻辑。有的年代久远的stored procedure里面真的是有一些无用或者没有与时俱进的逻辑，吧这样的地方去掉最爽了

[ 本帖最后由 wtf 于 2010-2-19 15:12 编辑 ]

jl162401

性能调优是件有意思的事情，有时候可以把跑三个小时的SQL调成2分钟，当然要看逻辑上是否还正确.

当然不是什么都可以调优的，如在CDR话单上sum计算总通话时长的，没有太好的办法，数量太大，只能请客户多加几个节点

典

原帖由 flyspirit 于 2010-2-19 13:22 发表
谢谢楼主经验分享。

顺便请教下，你们公司数据库有没有水平扩展能力。能不能加服务器数量来提高性能的？

以后可能会考虑，现在才不到100G的数据，用户数才200多，算是个小系统，所以暂时不用增加服务器。而且已经把报表系统分到其它 的服务器了。

典

原帖由 rdcwayx 于 2010-2-19 13:57 发表
改用oracle 会不会要好些？

主要还是应用系统本身设计和编程方面问题，毕竟我们的系统不大，用SQL显然足足有余

中间人

各位数据库行家，查询时避免lock超时，是不是加上no lock就行了？

典

原帖由 中间人 于 2010-2-19 17:33 发表
各位数据库行家，查询时避免lock超时，是不是加上no lock就行了？

要具体看了，
    首先还是要考虑优化 ，
    其次呢，因为那样做会带来不好的后果，要看能不能接受

中间人

各位数据库行家，你们说的statistice在sql server 2008下是不是就是打开 Include Client Statistice?

中间人

原帖由 典 于 2010-2-19 18:38 发表
要具体看了，
    首先还是要考虑优化 ，
    其次呢，因为那样做会带来不好的后果，要看能不能接受

可以详述吗？什么不好的后果？

bffbffbff

原帖由 典 于 2010-2-19 18:38 发表

要具体看了，
    首先还是要考虑优化 ，
    其次呢，因为那样做会带来不好的后果，要看能不能接受

４．２　一些计算：需要用到大量ｊｏｉｎ或子查询，直接改到客户端去做计算，有好几个这样的成功例子。其中有一个网页查询，每次需要延迟１０几秒才能有结果，用户非常不爽，经过分析后我用Ｃ＃给写了一段代码，把计算逻辑放到网页内部去完成，结果用户感觉不到任何延迟（延迟低于１秒），

好贴～
能不能具体说说什么样的计算在数据库里10几秒而在客户端只有不到1秒呢？

jl162401

汇总计算（sum,avg等等）算10秒，返回一个数，不需要1秒

bffbffbff

原帖由 中间人 于 2010-2-19 18:40 发表


可以详述吗？什么不好的后果？

我猜是如果把uncommited data都读了，结果出来的数据有可能不会被接受。

bffbffbff

原帖由 jl162401 于 2010-2-20 23:42 发表
汇总计算（sum,avg等等）算10秒，返回一个数，不需要1秒

这个还真没注意时间会差那么多啊～

jl162401

10秒太长了，你说的是cpu时间吗，如果是10秒的话，表的大小估计有1TB，

典

当需要复杂计算的时候，用.Net比较好，所以SQL2005开始增加了一个用.net 写函数和stored proc的功能。

我举的那个例就是要从几个大表里，取些子集，然后按不同的条件计算后再去另外的大表里取不同组合的子集再做一些计算，等等。当然用SQL stored proc 可能也可以写出高效率的来，但比较头疼。用.net写则非常容易。

典

原帖由 bffbffbff 于 2010-2-20 22:43 发表


我猜是如果把uncommited data都读了，结果出来的数据有可能不会被接受。

差不多是这个意思吧，