.Net+SQL Server企業應用性能優化筆記3——SQL查詢語句

在上一篇文章中我們使用了幾種方法來确定瓶頸，找到瓶頸，下面再回顧一下：

LoadRunner壓力測試+Windows計數器，這種方法主要是找出大概的性能問題是在哪台伺服器，主要是哪個資源緊張。

ANTS Profiler+SQL Server Profiler，這兩個工具的完美搭配可以準确的定位性能是出在哪個函數，哪個SQL語句上。

如果性能問題是出在程式上，那麼就要根據業務對程式中的函數進行調整，可能是函數中的寫法有問題，算法有問題，這種調整如果不能解決問題的話，那麼就要從架構上進行考慮，我們是不是應該使用這種技術，有沒有替代的方案來實作同樣的業務功能？舉個簡單的例子，假設經過跟蹤發現，一個負責生成圖表的函數存在性能問題，尤其是在壓力測試情況下性能問題尤為嚴重。原來的圖表生成是完全基于GDI+在Web伺服器上根據資料進行複雜的繪圖，然後将繪出的圖檔儲存在磁盤上，然後在HTML中添加Img标簽來引用圖檔的位址。現在使用GDI+會消耗大量記憶體和CPU，而算法上也沒有太大的問題，那麼這種情況下我們就需要考慮修改架構，不使用GDI+ 繪圖的方式，或者是使用異步繪圖的方式。既然繪圖會消耗大量的伺服器資源，那麼一種解決辦法就是将繪圖的操作從伺服器轉移到用戶端。使用SilverLight技術，在使用者打開網頁是隻是下載下傳了一個SilverLight檔案，該檔案負責調用Web伺服器的Web服務，将繪圖所需的資料擷取下來，然後在用戶端繪圖展現出來。這樣伺服器隻提供WebService的資料通路接口，不需要做繪圖操作。

.net上的優化我暫時不表，今天主要講資料庫的優化。使用ANTS Profiler+SQL Server Profiler我們可以精确定位某個業務操作對應的資料庫腳本或者存儲過程。ANTS Profiler告訴我們一個方法在調用的時候花了10秒的時間，那麼我們就可以使用VS打開源代碼，找到該放入，然後找到對應調用的存儲過程，這裡也許一個方法裡面調用了多個資料層方法，調用了多個存儲過程。将調用的這些存儲過程記下了，然後在SQL Server Provider的跟蹤檔案裡面去找調用該存儲過程花費的Duration。

ANTS Provider跟蹤出調用該方法的時間-SUM(所有調用的存儲過程的Duration)=C#中進行邏輯處理的時間+Web伺服器和資料庫伺服器之間網絡傳輸資料的時間

一般企業應用或小型應用中資料庫伺服器和Web伺服器要不是就在同一個機房，同一個區域網路，或者幹脆是同一台機器，這種情況下網絡傳輸速度是很快的，是以我們不考慮網絡傳送上面的時間。那麼就得出：

C#中進行邏輯處理的時間=ANTS Provider跟蹤出調用該方法的時間-SUM(所有調用的存儲過程的Duration)

代碼中的時間得到了，SQL Server中的時間（也就是Duration字段）得到了，那麼就可以判斷出打開該頁面各個伺服器所花費的時間，進而找到我們要優化的方向，是存儲過程還是C#代碼。如果是存儲過程，那麼通過查詢SQL Server Profiler中内容可以找到具體是哪一個存儲過程消耗的時間最長。

“射人先射馬，擒賊先擒王。”多個存儲過程被調用，如果性能出在資料庫伺服器上，那麼進行性能優化時首先要調優的是最大Duration最大的存儲過程，另外還有就是Reads很大的存儲過程。如果Duration很大但是Reads和Writes都不算特别大，那麼有可能是以下原因：

這個存儲過程相關的資源正在被其他事務占用，也就是說該存儲過程被阻塞是以才花了那麼多時間。這種情況隻需要把該存儲過程提出，多執行幾次，看是不是仍然Duration很大但Reads不大。

存儲過程本身很複雜，裡面的T-SQL語句就是五六百行，編譯出的執行計劃也是一堆，裡面進行了大量的邏輯判斷、大量函數的調用，這種情況下進行調優就比較痛苦了。實際上這次我調優的這個項目就是如此，抓取出來的存儲過程盡是複雜的邏輯，少則兩三百行代碼，多則五六百行，裡面還有大量的使用者定義函數的調用。對于這種存儲過程，我接下來會專門寫篇部落格介紹下我們這個項目是如何調優的。

程式讀取的資料不多，但是需要對資料進行大量的運算。哈希聯接、聚合函數、DISTINCT、UNION等都是比較耗CPU的。如果是這種情況那就看能不能建立索引或者改寫法進行調優。

前面說的是Duration大而Reads小的情況，當然更常見的情況是Duration和Reads都很大。那麼我們就将主要精力集中在如何減小Reads上。造成Reads很多的原因大概有以下幾種：

沒有建立相應的索引。對表t1進行查詢，條件是where c2='abc'傳回c1,c2,c3三個字段，那麼這種情況下如果沒有對c2建立非聚集索引（c1是主鍵，建立了聚集索引），那麼這個查詢将會進行“聚集索引掃描”，本來可能隻查出幾條記錄的，結果要把表的所有記錄都掃描一篇，自然Reads就高了。解決辦法就是建立相應的索引，比如這裡隻需要對c2字段建立非聚集索引，然後将c3字段作為包行列就行了。如果隻是最c2字段建立非聚集索引，那麼前面說到的查找在進行了“非聚集索引查找”後還會進行“鍵查找”來找到c3列的值，是以要建立的正确的索引才行。

不符合SARG原則。查詢如果不符合SARG原則，那麼即使建立了索引也沒法使用。SARG就是查詢參數的意思，具體怎麼寫才符合SARG，大家可以百度，已經有很多相關文章了，我就不累述。

涉及的業務資料量大。也就是說即使建立了正确的索引，查詢也符合SARG使用到了該索引，但是由于涉及的資料量太大了，是以Reads仍然很大。這種情況就不能再從索引和查詢入手，而隻能從資料庫的設計入手。是否能夠增加适當的備援字段，對資料庫進行反範式化，或者如果資料的實時性要求不高的話則可以建立中間彙總表，使用SQL作業來維護這個中間彙總表，查詢的時候隻查詢該中間彙總表即可。或者是否可以建立索引視圖或者計算列，然後在計算列中建立索引的方式進行一個預運算，減小實際查詢時涉及的資料量。

使用了不當的視圖。如果對視圖的定義很複雜，涉及的表很多，在查詢的時候使用了該視圖，但是實際上隻用到了視圖中的一張或兩張表，對視圖的查詢會造成系統根據視圖定義查詢其他與該查詢不相關的表。是以在使用視圖的時候一定要知道視圖的定義，不用貪圖一時的友善而随便使用視圖。

不正确的使用了使用者定義函數。一個存儲過程中幾百行代碼，出于編寫友善，大量的調用了一個使用者定義表值函數，而該函數是進行了複雜的查詢和運算才傳回結果的。如果數次或者數十次的調用該使用者定義表值函數，那麼就會進行很多這種複雜的查詢和運算，自然Reads也就很大了。解決辦法是盡量減少對這種複制函數的調用，比如一次調用後就将解決儲存在表變量或臨時表中，接下來再使用的話就使用該表變量或臨時表即可。

如果Duration并不大，但是Reads卻很大的查詢仍然需要需要進行優化。雖然表現出來消耗的時間并不大，但是由于Reads很多，那麼說明要進行大量的IO，在高并發的情況下大量的IO處理不過來會加重磁盤的負擔，造成CPU占用率上升，性能降低，這時其Duration就會變大。關于Duration不大但是Reads很大的情況仍然是前面說到的幾點情況，建立相關索引、修改查詢語句等便可解決。

本文轉自深藍居部落格園部落格，原文連結：http://www.cnblogs.com/studyzy/archive/2008/11/24/1339772.html，如需轉載請自行聯系原作者