对Sql Server 的性能测试数据解密

　　别让对Sql Server 的性能测试数据迷惑你

　　数据库的性能测试可以帮助你提前知道你的系统的负载能力，可以帮助你改进系统的实施或设计，可以帮助你确定一些设计和编程原则.但是，这里面也有陷阱.如果不小心，你会自己把自己陷进去，却最终不明白是什么原因.这里，我拿一位先生为例，来看看他怎么自己把自己搞糊涂的.

　　最近, 想起在存储过程中究竟是使用临时表还是使用表变量对性能更为有利的问题.我想这个问题的关键涉及到数据库是否对其进行transaction管理的问题, 如果进行transaction管理, 那么在改变表中的记录时就会使用write-ahead transaction log策略, 这样数据改变操作就会变慢.所以, 如果数据库engine仅对一种类型的表进行事物管理, 那么使用不同类型的表就会体现出性能差别.于是, 我就在网上搜了一下, 还真查出一篇特别对口的文章,Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance.

　　总结Tsuranoff的这篇文章,关于临时表和表变量, 理论上说,有三点:

　　1.数据库engine对临时表进行事务管理，但不对表变量进行事务管理.

　　2.表变量是完全局域的，因此，不需要任何的locking.

　　3.表变量相对临时表而言，比较少引起重编译

　　然后，Tsuranoff便展示了性能测试的结果，转录如下：

　　Table 2:Using SQL Server 2005 (time in ms).

　　为了读者阅读方便，我这里给出上表的解释：

　　1. T1, T2, T3,V1, V2代表了不同的存储过程，他们的逻辑和功能完全相同，仅仅是实现手法上略有差别. T1, T2, T3对应使用临时表的存储过程，其差别在于,T1不使用索引，T2使用预先定义的索引，T3先对临时表倒入数据，在进行查询前再建索引. V1使用表变量，但不使用索引，V2使用表变量并使用索引. "N"一列的数值是试验时对这些存储过程所采用的参数.

　　2.结果显示，使用表变量的存储过程的性能并不比使用临时表的存储过程性能更好，相反，当输入参数N(即处理的行数)变大时，性能完全变坏.

　　毫无疑问，试验数据和理论推测的结果相反.然而作者不去追究其深层的原因，就糊里糊涂的给出一堆结论.可想而知，连数据都是错的，那给出的结论还不误人子弟吗?这里就不重复他的结论了.

　　当我看到这些数据时，我就怀疑.我猜，这些数据的产生可能是因为数据库engine使用了不同的查询计划导致的.于是，我就用Sql Server Management Studio来显示这些查询的计划，果然如我所猜.

　　存储过程T2的计划：

　　比较上面两个计划，我们就可以看出，Tsuranoff先生会得出那样的实验数据一点都不奇怪.

　　找出了问题的原因，解决方法就有了.为此， 我先用sp_recompile 把cache中相应的查询计划清掉，然后，我改变了实验中使用参数的次序，转而先使用大的N参数值，再使用小的N参数值,得出一系列的实验数据.结果表明,确实, 一般而言,使用表变量的性能同等情况下比使用临时表略好.

　　这里解释一下，为什么改变了使用参数大小的次序，就能够得出正确的结果呢?这是因为Sql Server Engine针对具体的参数值进行了优化，并把产生的查询计划缓存在系统中.先使用大的参数，Engine就把对大参数优化的查询计划缓存在系统,并重复使用之.

　　由此又产生了一个新的问题，那就是在生产环境中，先出现大参数或小参数完全是随机的，这样一来，如果参数出现的次序不对，那岂不是会让一个本来能够绰绰有余的负载客户量的系统变的完全瘫痪掉? 这显然是不可取的.

　　不用担心，Sql Server还提供了其他一些途径来控制系统如何产生查询计划.这其中，包括Query hint,和Join hint.如果这些还不够，你还可以更进一步使用Plan Guide.关于这些，不在此多讲.

原文转自：http://www.ltesting.net