SQL Server调优经历(ZT)

前段时间数据库健康检查发现SQL Server服务器的idle时间变少，IO还是比较空闲，估计是遇到了高CPU占用的语句了。
介绍一下背景，我们公司负责运维N多的应有系统，负责提供良好的软、硬件环境，至于应用的开发质量，我们就无能为力了
解决这个问题，我的思路是：
找出CPU占用最大的语句。
分析查询计划。
优化。
1、找出语句
使用SQL Server自带的性能报表（不是报表服务），找出CPU占用最大的语句。如图1所示

图1 性能报表
我选取了“性能-按总CPU时间排在前面的查询”，得出以下两张报表，如图2所示：

图2 性能-按总CPU时间排在前面的查询
在报表中不能直接把语句Copy出来，非得让我另存为Excel才能Copy语句；而且经常标示不了是语句属于哪个数据库，不爽 :( 。
费了我九牛二虎之力才找出该条语句在哪个数据库执行，然后马上备份数据库，在另一个非生产数据库上面还原，创造实验环境。
废话少说，我把语句Copy出来，顺便整理了一下格式。如下：
select*
fromnetwork_listen
where
node_codein
(
selectdistinctnode_code
fromview_Log_Network_circsByUnit
wherestatus='1'
)　
or
node_code=
(
selecttop1nodeCode
fromTransmissionUnit_LocalInfo
)　
and
node_code<>
(
selectparentNodeCode
fromTransmissionUnit_RouterInfo
wherenodeCode=
(
selecttop1nodeCode
fromTransmissionUnit_LocalInfo
)
)

2、分析语句
执行计划如下：
图太大了，将就着看吧 :( .

图3 查询计划全图

图4 查询计划1

图5 查询计划2

图6 查询计划3
从整个查询计划来看，主要开销都花在了图5的那个部分——两个“聚集索引扫描”。
查看一下这两个数“聚集索引扫描”，搞什么飞机呢？

奇怪了，查询语句里面没有Log_Nwtwork_circs 这个表啊，再仔细分析一下这个执行计划，嫌疑最大的就是view_Log_Network_circsByUnit这个视图了。
查看一下这个试图的定义：
CREATEVIEW[dbo].[view_Log_Network_circsByUnit]
AS
SELECTB.*
FROM(
SELECTnode_code,MAX(end_time)ASend_time
FROMLog_Network_circs
GROUPBYnode_code
)A
LEFTOUTERJOIN
dbo.Log_Network_circsB
ON
A.node_code=B.node_code
AND
A.end_time=B.end_time

看着有点晕是吧，那么看看下图
　
3、优化
SQL写得好不好，咱不说，反正我是不能改SQL的，而且应该可以判断出整个查询最耗时的地方就是用在搞这张试图了。
那就只能针对这个试图调优啦。仔细观察这个试图，实际上就涉及到一个表 Log_Network_circs，下面是该表的表结构：
CREATETABLE[dbo].[Log_Network_circs](
[log_id][varchar](30)NOTNULL,
[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]PRIMARYKEYCLUSTERED
(
[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 这两字段上建立一个复合索引，大概定义如下：
CREATEINDEX[idx__Log_Network]
ONLog_Network_circs
(
node_codeASC,
end_timeASC
)
保险起见，我把需要调优的语句copy到一个文件里，然后打开“数据库引擎优化顾问”，设置好数据库，得出以下调优结果：


CREATESTATISTICS[_dta_stat_559341057_6_2]ON[dbo].[Log_Network_circs]([end_time],[node_code])
CREATENONCLUSTEREDINDEX[_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 这个视图的执行计划不一样了。

不看广告，看疗效，使用统计功能。执行以下语句：
SETSTATISTICSIOon;
SETSTATISTICSTIMEon;

(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次。
SQLServer执行时间:
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次。
SQLServer执行时间:
CPU时间=0毫秒，占用时间=2毫秒。
逻辑读取数，总执行时间都大大缩减，开来调优还是挺成功的 :) 。