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要用，但是要去学习明白它的原理和特点。就好比傻瓜照相机还是用的人多，但是你要懂得傻瓜机也有判断失误的时候，所以要用“白加黑减”的曝光补偿。