背景
有程序员跑来说我们的群组最近好像有些卡。看看内存CPU都还算正常,IO也没有比平时高多少。
总体上没什么问题,接下来看看细的。于是用Profiler抓了一把Duration超过1秒的存储过程,抓了20来分钟,竟然一个都没有。好家伙,看样子在我们平日的精心呵护下,那些存储过程跑得还算可以的嘛,卡的主要原因应该不是DB这块的事情啦,自我满足一下先。
再一想,群组帖子内容多,平日里读取还是蛮厉害的,既然叫我查了,总得找点优化的东东不。于是又抓了一把Reads比较大的,不到一会马上一个可疑的存储过程出现了:“***GetPostByTag”,reads有些时候超过10多万,而且很频繁。看样子得看看他了!
全文谓词Contains的执行情况
打开过程定义,看到里面的语句其实很简单,看了下实际的执行计划,主要的开销就是下面的这个语句。为了介绍方便,这里省去其他逻辑,把变量替换为开销比较大的一个值,只写出最主要的语句。
SELECT TOP 9 PlateForum.Title,Tag,idx as fidx,groupidx
FROM PlateForum with(nolock)
WHERE CONTAINS(Tag,'朋友') and state<>2
order by NEWID()
打开“包含实际的执行计划”选项,执行如上语句,发现开销最大的是索引查找和键查找,分别占了47%和48%,加起来就是95%了。而作为主要查找条件的Contains部分1%。于是仔细看了下,他的实际行数竟然有13076行,而我们只需要返回9行而已,再顺着执行计划往后走,发现索引查找和键查找都有12000多行。
具体的执行计划,以及关键步骤的执行属性如下图所示:
优化思路
找到了最大的问题,现在的关键任务就是怎么样减少全文操作、索引查找、键查找的实际行数。
由于符合搜索条件的项有很多(示例中就有1.3万),如果能够使用这些信息按排名对匹配项进行排序,并最多只返回我需要的数目的匹配项,不就可以大幅度提高性能,从而解决我的问题了嘛。
查阅相关资料,发现全文函数ContainsTable正好符合我的要求,于是开始改写。
全文函数ContainsTable的执行情况
改下之后的语句如下:
SELECT FT_TBL.Title,Tag,idx as fidx,groupidx
FROM PlateForum AS FT_TBL INNER JOIN
CONTAINSTABLE (PlateForum, Tag, '朋友'
, 9
) AS KEY_TBL
ON FT_TBL.IDX = KEY_TBL.[KEY]
WHERE state<>2 and RANK>100
ORDER BY NEWID()
迫不及待地打开“包含实际的执行计划”选项看看是否真的提升了性能,结果如下图:
果然不负我望:资源消耗竟然降低了19倍。
接下来就是验证,性能提升是否是因为减少了实际行数?具体的执行计划如下所示:
通过上图可以看出,全文操作、索引查找、键查找的实际行数确确实实已经减少到我们需要的9行,而全文操作在总的语句里面的开销比例也从1%增加到了15%。
由于对表做了分区存储,由于减少了实际行数,“实际分区计数”也从原来的5个减少到1个。
优化结果
用上面的方法改到存储过程里面,用profiler再次抓取,发现CPU开销降低了10倍、IO读取将近减少为原来的1/6、而原先的延时更是降低了将近20倍。至此,优化基本完成。
看懂并深入分析执行计划太重要了!
更多文章请访问:http://blog.csdn.net/jerrynet