SQL Server性能调优杂记(一)----傻瓜机的失效效应

      最近,下面的一个项目遇到紧急问题,我这匹老马也要和年轻人一起奋斗一下。问题是当把一倍压力数据灌入数据库,很多查询都奇慢无比。

      说道这里必须要说一下性能问题的基本准则。性能问题Tunning的次序

    1)架构设计(软件架构和数据库设计,糟糕的设计几乎是致命的)

    2)代码缺陷(导致性能问题的90%)

    3)增加索引(这个是要根据实际情况来确定)

    4)资源调优(CPU->内存->Disk IO)

    这里网络不是考虑因素。

    把程序的SQL文拿出来一看,有的一看一堆子查询构成的JOIN,基本上一眼就可以断定,需要重写。我们这个运用系统把SQL文都配置成动态的,这个设计给现在的调优带来了方便。

    突然出现了一个很有趣的现象。有一个查询很慢(一分钟才出来),检查SQL文。这句SQL文是这样

    

 

SELECT ISNULL(a.CWB_NO,b.CWB_NO) AS CWB_NO,a.IMPORT_AWB_NO,

a.IMPORT_BWB_NO,ISNULL(a.PCS,0) AS RS2PCS,ISNULL(b.PCS,0) AS DECPCS,a.CCC_STATUS

FROM  

(SELECT * FROM   TB_CWB WHERE  IMPORT_AWB_NO = @IMPORT_AWB_NO) a

FULL JOIN  

(SELECT * FROM   TP_DECSUMMARY WHERE  AWB_NO = @IMPORT_AWB_NO) b

ON   a.CWB_NO = b.CWB_NO AND   b.AVAILABLE = 'Y'

WHERE  a.AVAILABLE = 'Y'

 

 

 

 

 

 

 

FULL JOIN不是问题核心(因为业务规则就是这样),也不是SELECT *,其实SELECT *和指定字段或许有差异,但是绝对不会有很大差别。

我在后台运行了一下,0秒都不到。但是另外一个程序员说同样运行要59秒。奇怪!!!

拿过来对比一下,就发现差异了。

因为,我们的系统采取的是用.NET中cmd指定参数的写法,转换成后台sql文,等于运行sp_executesql的方法。更简单说就是替换变量。

即等价的SQL文应该是

SELECT ISNULL(a.CWB_NO,b.CWB_NO) AS CWB_NO,a.IMPORT_AWB_NO,

a.IMPORT_BWB_NO,ISNULL(a.PCS,0) AS RS2PCS,ISNULL(b.PCS,0) AS DECPCS,a.CCC_STATUS

FROM  

(SELECT * FROM   TB_CWB WHERE  IMPORT_AWB_NO = '25200000011') a

FULL JOIN  

(SELECT * FROM   TP_DECSUMMARY WHERE  AWB_NO ='25200000011') b

ON   a.CWB_NO = b.CWB_NO AND   b.AVAILABLE = 'Y'

WHERE  a.AVAILABLE = 'Y'

 

而我调试用的替换SQL文

DECLARE @IMPORT_AWB_NO VARCHAR(20)
SET @IMPORT_AWB_NO='25200000011'

SELECT ISNULL(a.CWB_NO,b.CWB_NO) AS CWB_NO,

a.IMPORT_AWB_NO,

a.IMPORT_BWB_NO,

ISNULL(a.PCS,0) AS RS2PCS,

ISNULL(b.PCS,0) AS DECPCS,

a.CCC_STATUS

FROM   (

SELECT *

FROM   TB_CWB

WHERE  IMPORT_AWB_NO = @IMPORT_AWB_NO

) a

FULL JOIN   (SELECT *

FROM   TP_DECSUMMARY

WHERE  AWB_NO = @IMPORT_AWB_NO) b

ON   a.CWB_NO = b.CWB_NO

AND   b.AVAILABLE = 'Y'

WHERE  a.AVAILABLE = 'Y'

            这个即相当于SP的写法。

            这2句的结果导致就是执行计划完全不一样。从而产生天壤之别的效果。这个问题和SQL文的好坏没有关系。我对比了者2个执行计划,个人认为由于SQL Server的CBO分析这个“傻瓜机”失效效应导致。

           我们先看一下2种的执行计划的区别。我用第2种(即SP)方法的执行计划。

          

    2个执行计划的唯一差别就是图中的2个Paralism(数据库服务器是8个CPU)。即我的那句SQL采用了并行装载数据的方法。而第一中没有使用并行装载。原因很简单,因为执行计划分析器认为前面这句SQL执行代价很小,而我的执行代价很高。因此进行了区别对待,前者不需要动用硬件资源,后者则需要动用硬件资源。这就是失效效应。而一般认为慢就是因为硬件不够,在这里充分体现是无稽之谈。至少性能问题只有5%甚至更低的情况才和硬件有关。

   至于这个代价估算的计算方法,只有查具体的技术文件,大体应该和数据大小,索引大小,逻辑读写,物理读写,CPU资源有关。因为是估算,因此有偏差很正常。

   那么解决这个问题,加索引才是正途。这就是我前面讲到的原则。通过加索引,执行计划发生了变化。见下图。

 

 

    增加索引后的速度结果飞快。

   Paralism和索引的概念就好比,在南京路上找某户户主是王五,Paralism是出动16个警察,而索引就好比一个警察拿户籍登记册找。一个是拥有资源多而致胜,一个是工作方法好而致胜。

    从这里可以看出,性能调优起始是一个根据实际情况进行平衡选择的过程,武断认为就是加索引,提升硬件都说明你不了原理。对了也只是运气好。

    总结这个例子,失效效应就是CBO带来的一个有趣的现象。显然CBO要用,但是要去学习明白它的原理和特点。就好比傻瓜照相机还是用的人多,但是你要懂得傻瓜机也有判断失误的时候,所以要用“白加黑减”的曝光补偿。

 

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 4

打赏作者

enhydraboy

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值