No.18

先說明我不是個DBA，所以在這個Case裡只是盡一切我所會的並了解的而為

這個Case是來自於一個實際上線多年系統裡的某一個批次作業功能，且大約會有

近百個Request同時要進行該功能的執行，而這個批次作業功能是以SQL Procedure

方式進行的，也就是前端AP面只是進行該SQL Procedure的呼叫，並給予該給的參數

，此次遇上的問題是使用者無法在同時間進行該作業功能的執行，此外單一使用者

作業也需要花費太久的時間，而且會有TimeOut現象，簡單來說就是效能不佳

在接獲這個任務時，大致擬定了一下攻略

(1).由於該SQL Procedure內是以一個主要的SQL Procedure依序呼叫數十個其它

SQL Procedure，因此找出效能不佳的Procedure是第一要務

(2).透過SQL 2008 Database Engine Tuning Advisor分析所錄製SQL Profile

，進行分析及改善

怎麼找出效能不佳的Procedure此部份沒有什麼招，透過分解及Test Run不難找出來

，其中發現某一段SQL SELECT執行的相當久，原先的語法大致如下

select .....
    ,(select tablea.c1 from tableaa where tableaa.xx = tablea.xx and .....) as xx
from tablea  
inner join tableb  on tablea.id=tableb.aid
where 條件

此Select語法用了個Sub Select，而且不幸的是該Sub Select的Table有著大約千萬筆的

資料，當下分析了一下Table Schema理應可以用join來取代該Sub Select

，改寫大致如下

select .....
    ,tablec.xx 
from tablea  
inner join tableb  on tablea.id=tableb.aid
left join (select ... from .... where ...) tablec on .....
where 條件

經由執行計劃相比二者語法所花費的Cost相差甚多

實測結果

接著雖然該SQL SELECT效能已有幅提升，但仍不夠理想，於是接著利用建立

NONCLUSTERED INDEX以及Index with Included Columns來改善

查詢效能，就是以空間來換取時間囉，有關NONCLUSTERED INDEX

及Index with Included Columns詳細可參考

CREATE INDEX

Index with Included Columns

Creating Indexes with Included Columns

Index Statistics

(Index with Included Columns須SQL2005版本以上才有支援)

藉由以上的調整，找出約10萬筆資料僅需花費24秒，

而原先大約1個小時都還不見得可以完成

此外SQL SERVER運行的方式是會先決定執行計劃，再依據執行計劃進行

最佳化查詢，根據MSDN寫道：

『如果您的應用程式執行一系列的查詢，而且各項查詢中只有某些

常數不同，您就可以使用參數化查詢來改善效能，參數化查詢可

藉由單次編譯查詢及多次執行已編譯計畫，進而產生較佳的效能。』

因此程式中如能使用參數化方式執行SQL statement，將有助於提升

重覆執行的SQL statement，當然使用參數化方式執行SQL statement

更可有效避免SQL Injection

而Database Engine Tuning 則是SQL Server的效能調校工具程式，

利用它配合所錄製SQL Profile，可以快速產生系統建議

A.先利用SQL Profiler錄製要分析的內容(需有sa權限)，選擇你所有需要

追蹤的相關事件，並選擇儲存為檔案

B.錄製完成後，執行Database Engine Tuning

C.設定一下微調的選項，一般情況我是使用預設值

D.執行結束後，會產生建議項目(含相關語法)，並且會有估計的效能改善值

，通常情況下我會稍微看一下它的建議(避免Create一堆不實用的index)，接著就會直接套用了

最後發現Table Lock情況相當頻繁，這在多個Request作業下會造成等待太久的現象

因為後來的Request必須等待前一個Request把這個Table釋放掉，才能進行相關操作

，一般情況下Lock的機制會由SQL SERVER自行依情況調配，不過在必要的情況

下，我們可以使用with (nolock)及 with (rowlock)，使得Lock範圍縮小，避免等待

時間過長，但使用with (nolock)及 with (rowlock)必須要經過評估，視情況而訂，

並且Lock範圍縮小會增加SQL SERVER的負擔，因為它必須花更多的空間來記錄

這些Lock的情況此外萬一該Lock的資料沒有處理好，反而被其它Request給modify了

，那麼將會造成資料錯誤的情況囉

在這個Case裡可說是幾乎是利用SQL SERVER所提供的工具來協助分析及調校，

當然對於SQL statement的寫法得靠自各的功力了，對我來說，大部份情況下

我不是太愛用Sub Select的方式來串SQL statement，能透過join方式我會盡

量使用join來完成，不過這並非絕對，大部份時候如果資料量相當的大，此時

再用到Sub Select，通常不會有太好結果，再者join v.s Sub Select誰快誰慢

我的經驗告訴我這取決一些外部的因素，例如index等等，沒有一定誰快誰慢，

所以通常還是得視個案來看，幸好SQL SERVER提供了執行計劃評估的功能，可以

協助開發人員做一些事先的評估

此外在SQL SERVER 本身也有所改進(哈，我可不是在幫微軟推銷喔)，

像Index with Included Columns機制，在本次的調校過程中，幫助相當的大，

雖然得用空間換取時間啦，不過我認為要二者兼顧本來就不容易，如果可以

犧牲點空間換取到最好的效能，倒也滿值得的吧

相關資源：

CREATE INDEX

Index with Included Columns

Creating Indexes with Included Columns

Index Statistics

鎖定資料粒度和階層

索引統計資料

Statistics Used by the Query Optimizer in Microsoft SQL Server