別讓對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便展示了性能測試的結果，轉錄如下：
N | T1 | T2 | T3 | V1 | V2 |
10 | 0.5 | 0.5 | 5.3 | 0.2 | 0.2 |
100 | 2 | 1.2 | 6.4 | 61.8 | 2.5 |
1000 | 9.3 | 8.5 | 13.5 | 168 | 140 |
10000 | 67.4 | 79.2 | 71.3 | 17133 | 13910 |
100000 | 700 | 794 | 659 | Too long! | Too long! |
1000000 | 10556 | 8673 | 6440 | Too long! | Too long! |
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.kjueaiud.com/