一次SQL Server调优经历

前段时间数据库健康检查发现SQL Server服务器的idle时间变少，IO还是比较空闲，估计是遇到了高CPU占用的语句了。 解决这个问题，我的思路是： 找出CPU占用最大的语句。 分析查询计划。 优化。 1、找出语句 使用SQL Server自带的性能报表（不是报表服务），找出CPU占用最大的语句。如图1所示 图1 性能报表   我选取了“性能-按总CPU时间排在前面的查询”，得出以下两张报表，如图2所示：   图2 性能-按总CPU时间排在前面的查询 在报表中不能直接把语句Copy出来，非得让我另存为Excel才能Copy语句；而且经常标示不了是语句属于哪个数据库，不爽 :( 。 费了我九牛二虎之力才找出该条语句在哪个数据库执行，然后马上备份数据库，在另一个非生产数据库上面还原，创造实验环境。 废话少说，我把语句Copy出来，顺便整理了一下格式。如下： select   *   from  network_listen  where   node_code  in       (       select   distinct  node_code        from  view_Log_Network_circsByUnit        where  status = ' 1 '       )   or   node_code =       (        select   top   1  nodeCode        from  TransmissionUnit_LocalInfo      )   and   node_code <>       (        select  parentNodeCode        from  TransmissionUnit_RouterInfo        where  nodeCode =               (                select   top   1  nodeCode                from  TransmissionUnit_LocalInfo              )      )   2、分析语句 执行计划如下： 图太大了，将就着看吧 :( . 图3 查询计划全图 图4 查询计划1 图5 查询计划2 图6 查询计划3 从整个查询计划来看，主要开销都花在了图5的那个部分——两个“聚集索引扫描”。  查看一下这两个数“聚集索引扫描”，搞什么飞机呢？   奇怪了，查询语句里面没有Log_Nwtwork_circs 这个表啊，再仔细分析一下这个执行计划，嫌疑最大的就是view_Log_Network_circsByUnit这个视图了。 查看一下这个试图的定义： CREATE   VIEW   [ dbo ] . [ view_Log_Network_circsByUnit ]   AS   SELECT  B. *   FROM  (       SELECT  node_code,  MAX (end_time)  AS  end_time           FROM  Log_Network_circs           GROUP   BY  node_code       ) A  LEFT   OUTER   JOIN         dbo.Log_Network_circs B  ON       A.node_code  =  B.node_code       AND             A.end_time  =  B.end_time   看着有点晕是吧，那么看看下图     3、优化 SQL写得好不好，咱不说，反正我是不能改SQL的，而且应该可以判断出整个查询最耗时的地方就是用在搞这张试图了。 那就只能针对这个试图调优啦。仔细观察这个试图，实际上就涉及到一个表 Log_Network_circs，下面是该表的表结构： CREATE   TABLE   [ dbo ] . [ Log_Network_circs ] (       [ log_id ]   [ varchar ] ( 30 )  NOT   NULL ,       [ node_code ]   [ varchar ] ( 100 )  NULL ,       [ node_name ]   [ varchar ] ( 100 )  NULL ,       [ server_name ]   [ varchar ] ( 100 )  NULL ,       [ start_time ]   [ datetime ]   NULL ,       [ end_time ]   [ datetime ]   NULL ,       [ status ]   [ varchar ] ( 30 )  NULL ,  CONSTRAINT   [ PK_LOG_NETWORK_CIRCS ]   PRIMARY   KEY   CLUSTERED   (       [ log_id ]   ASC   ) WITH  (PAD_INDEX   =   OFF , STATISTICS_NORECOMPUTE   =   OFF , IGNORE_DUP_KEY  =   OFF , ALLOW_ROW_LOCKS   =   ON , ALLOW_PAGE_LOCKS   =   ON )  ON   [ PRIMARY ]   )  ON   [ PRIMARY ]   数据量有489957条记录，不算太大。 根据 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 感觉上得在 node_code 和 end_time 这两字段上建立一个复合索引，大概定义如下：   CREATE   INDEX   [ idx__Log_Network ]   ON  Log_Network_circs  (      node_code  ASC ,      end_time  ASC   )   保险起见，我把需要调优的语句copy到一个文件里，然后打开“数据库引擎优化顾问”，设置好数据库，得出以下调优结果：   CREATE   STATISTICS   [ _dta_stat_559341057_6_2 ]   ON   [ dbo ] . [ Log_Network_circs ] ( [ end_time ] ,  [ node_code ] ) CREATE   NONCLUSTERED   INDEX   [ _dta_index_Log_Network_circs_24_559341057__K2_K6 ]   ON   [ dbo ] . [ Log_Network_circs ]   (       [ node_code ]   ASC ,       [ end_time ]   ASC   ) WITH  (SORT_IN_TEMPDB  =   OFF , IGNORE_DUP_KEY  =   OFF , DROP_EXISTING  =   OFF , ONLINE  =   OFF )  ON   [ PRIMARY ]   嗯，结果差不多，具体参数再说。 按照“数据库引擎优化顾问”给出的建议，建立 STATISTICS 和 INDEX 。 再看看优化后的执行计划 明显查询 view_Log_Network_circsByUnit 这个视图的执行计划不一样了。 不看广告，看疗效，使用统计功能。执行以下语句：   SET   STATISTICS  IO  on ;  SET   STATISTICS  TIME  on ;    ( 2  行受影响)  表  ' Log_Network_circs ' 。扫描计数  2 ，逻辑读取  13558  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  表  ' TransmissionUnit_RouterInfo ' 。扫描计数  0 ，逻辑读取  2  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  表  ' TransmissionUnit_LocalInfo ' 。扫描计数  3 ，逻辑读取  6  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  表  ' network_listen ' 。扫描计数  1 ，逻辑读取  2  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  SQL Server 执行时间:     CPU 时间  =   719  毫秒，占用时间  =   719  毫秒。  ( 2  行受影响)  表  ' Log_Network_circs ' 。扫描计数  2 ，逻辑读取  9  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  表  ' TransmissionUnit_RouterInfo ' 。扫描计数  0 ，逻辑读取  2  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  表  ' TransmissionUnit_LocalInfo ' 。扫描计数  3 ，逻辑读取  6  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  表  ' network_listen ' 。扫描计数  1 ，逻辑读取  2  次，物理读取  0  次，预读  0  次，lob 逻辑读取  0  次，lob 物理读取  0  次，lob 预读  0  次。  SQL Server 执行时间:     CPU 时间  =   0  毫秒，占用时间  =   2  毫秒。   逻辑读取数，总执行时间都大大缩减，开来调优还是挺成功的 :) 。