.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都不算特別大，那麼有可能是以下原因：

1. 這個預存程序相關的資源正在被其他事務佔用，也就是說該預存程序被阻塞所以才花了那麼多時間。這種情況只需要把該預存程序提出，多執行幾次，看是不是仍然Duration很大但Reads不大。
2. 預存程序本身很複雜，裡面的T-SQL語句就是五六百行，編譯出的執行計畫也是一堆，裡面進行了大量的邏輯判斷、大量函數的調用，這種情況下進行調優就比較痛苦了。實際上這次我調優的這個項目就是如此，抓取出來的預存程序儘是複雜的邏輯，少則兩三百行代碼，多則五六百行，裡面還有大量的使用者定義函數的調用。對於這種預存程序，我接下來會專門寫篇部落格介紹下我們這個項目是如何調優的。
3. 程式讀取的資料不多，但是需要對資料進行大量的運算。雜湊聯結、彙總函式、DISTINCT、UNION等都是比較耗CPU的。如果是這種情況那就看能不能建立索引或者改寫法進行調優。

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

1. 沒有建立相應的索引。對錶t1進行查詢，條件是where c2='abc'返回c1,c2,c3三個欄位，那麼這種情況下如果沒有對c2建立非叢集索引（c1是主鍵，建立了叢集索引），那麼這個查詢將會進行“叢集索引掃描”，本來可能只查出幾條記錄的，結果要把表的所有記錄都掃描一篇，自然Reads就高了。解決辦法就是建立相應的索引，比如這裡只需要對c2欄位建立非叢集索引，然後將c3欄位作為包行列就行了。如果只是最c2欄位建立非叢集索引，那麼前面說到的尋找在進行了“非叢集索引尋找”後還會進行“鍵尋找”來找到c3列的值，所以要建立的正確的索引才行。
2. 不符合SARG原則。查詢如果不符合SARG原則，那麼即使建立了索引也沒法使用。SARG就是查詢參數的意思，具體怎麼寫才符合SARG，大家可以百度，已經有很多相關文章了，我就不累述。
3. 涉及的業務資料量大。也就是說即使建立了正確的索引，查詢也符合SARG使用到了該索引，但是由於涉及的資料量太大了，所以Reads仍然很大。這種情況就不能再從索引和查詢入手，而只能從資料庫的設計入手。是否能夠增加適當的冗餘欄位，對資料庫進行反範式化，或者如果資料的即時性要求不高的話則可以建立中間匯總表，使用SQL作業來維護這個中間匯總表，查詢的時候只查詢該中間匯總表即可。或者是否可以建立索引檢視表或者計算資料行，然後在計算資料行中建立索引的方式進行一個預運算，減小實際查詢時涉及的資料量。
4. 使用了不當的視圖。如果對視圖的定義很複雜，涉及的表很多，在查詢的時候使用了該視圖，但是實際上只用到了視圖中的一張或兩張表，對視圖的查詢會造成系統根據視圖定義查詢其他與該查詢不相關的表。所以在使用視圖的時候一定要知道視圖的定義，不用貪圖一時的方便而隨便使用視圖。
5. 不正確的使用了使用者定義函數。一個預存程序中幾百行代碼，出於編寫方便，大量的調用了一個使用者定義資料表值函式，而該函數是進行了複雜的查詢和運算才返回結果的。如果數次或者數十次的調用該使用者定義資料表值函式，那麼就會進行很多這種複雜的查詢和運算，自然Reads也就很大了。解決辦法是盡量減少對這種複製函數的調用，比如一次調用後就將解決儲存在表變數或暫存資料表中，接下來再使用的話就使用該表變數或暫存資料表即可。

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