SQL 运行时性能统计信息的获取

打个招呼，这一篇可能不适合 CRUD Boy/Girl. 纯做增删改查的 SQL 编码师可能会觉得偏难。

假设如下的存储过程，有两段 SQL 查询组成。执行时发现，响应很慢。
请问你该怎么办？

有同学说，看阻塞情况，这样的：

找到伤害你的元凶了，该报仇报仇，该抱怨就抱怨。

又有同学说，看执行计划，这样的：

这两种做法都可以尝试，且对调优也有相当的帮助。但今天要探讨的是另外一种方法，运行时获取性能统计信息。这些统计信息包含了编译及执行流失总时间，CPU 执行时间，磁盘 IO 开销。知道了这些有什么用之类的问题，请充分发挥你的想象力。最直接的一点，你可以知道前面存储过程中哪段 SQL 执行的最慢，需要全身心的解决这段 SQL 查询效率。这仅从看查询执行流失总时间即可清晰得做出判断。

获取统计信息的做法：

set statistics time on
set statistics io on

统计信息都打出来了，熟快熟慢不难分解。

IO的读取和存储结构有紧密的关系。数据行是存储在数据页上的，一个页在 SQL Server 中是 8K（其他数据库比 SQL Server 灵活的地方在于数据页大小可调，比如 Oracle 就是，8K, 32K,64K, Hadoop 也是，64MB, 128MB；根据应用选择最恰当的存储单元），读取的页数乘以 8K 就是读取的数据量。越大耗时越长，也就表明这地方要加索引或采用其他优化方法。而磁盘针头读取一般以扇区为单位，512K 也就是 64 个数据页为一次读的最大量，不管是查多少条数据，哪怕一条数据，耗费的都是 512K. 经常郁闷的查几条数据，却耗时那么长，原理就在这儿。明面上查一条数据，其实把很多数据页上的数据都拉到内存里了。这叫预读，Read Ahead.

获取运行时执行计划

有了性能统计信息，我们的矛头指向哪儿就有了明确的目标了。接下来就可以分析这段 SQL 的执行计划了。有时候这段 SQL 非常复杂，你不想复制出来重新单步调执行计划，那么可以采用运行时查看执行计划，这有点 Oracle 的文本执行计划的意思。针对存储过程的多段 SQL 来说，精确获取某段慢查询的执行计划，能更好的提供优化策略。

这时候你需要这命令：

set statistics profile on

比如 PhysicalOp 中出现了 Index Scan ,说明索引效率不高，想办法转换成 Index Seek.

当然，在调试的时候，千万别直接修改原存储过程。建议在原存储过程名后加上_pt (performance tunning 缩写)， 在需要的 SQL 段落前加上 print ’ xxx begins…’ 以明确统计信息的步骤对象归属。

好了，祝你下次遇到多段 SQL 调优时，“目光远大，心狠手辣” （来自二爷语录）