SQL Server常見效能問題的最佳化

      SQL Server的常見效能問題，可大體從三個方面入手考慮，作業系統和資料庫的配置，表/索引/儲存的設定，SQL語句等。對於已交付的系統，往往由於實際情況的限制，SQL語句的最佳化比較困難。本文總結了SQL Server效能最佳化的實用方法。

與作業系統相關的最佳化

      作業系統效能的好壞直接影響資料庫的使用效能，如果作業系統存在問題，如cpu過載、過度記憶體交換、磁碟i/o瓶頸等，在這種情況下，單純進行資料庫內部效能調整是不會改善系統效能的。我們可以通過windows nt的系統監視器(system monitor)來監控各種裝置，發現效能瓶頸。

      cpu 一種常見的效能問題就是缺乏處理能力。系統的處理能力是由系統的cpu數量、類型和速度決定的。如果系統沒有足夠的cpu處理能力，它就不能足夠快地處理事務以滿足需要。我們可以使用system monitor確定cpu的使用率，如果以75%或更高的速率長時間運行，就可能碰到了cpu瓶頸問題，這時應該升級cpu。但是升級前必須監視系統的其他特性，如果是因為sql語句效率非常低，最佳化語句就有助於解決較低的cpu利用率。而當確定需要更強的處理能力，可以添加cpu或者用更快的cpu 替換。

      記憶體 sql server可使用的記憶體量是sql server效能最關鍵因素之一。而記憶體同i/o子系統的關係也是一個非常重要的因素。例如，在i/o操作頻繁的系統中，sql server用來快取資料的可用記憶體越多，必須執行的物理i/o也就越少。這是因為資料將從資料緩衝中讀取而不是從磁碟讀取。同樣，記憶體量的不足會引起明顯的磁碟讀寫瓶頸，因為系統緩衝能力不足會引起更多的物理磁碟i/o。

      可以利用system monitor檢查sql server的buffer cache hit ratio計數器，如果命中率經常低於90%，就應該添加更多的記憶體。

      i/o子系統 由i/o子系統發生的瓶頸問題是資料庫系統可能遇到的最常見的同硬體有關的問題。配置很差的i/o子系統引起效能問題的嚴重程度僅次於編寫很差的sql語句。i/o子系統問題是這樣產生的，一個磁碟機能夠執行的i/o操作是有限的，一般一個普通的磁碟機每秒只能處理85次i/o操作，如果磁碟機超載，到這些磁碟機的i/o操作就要排隊，sql的i/o延遲將很長。這可能會使鎖持續的時間更長，或者使線程在等待資源的過程中保持空閑狀態，其結果就是整個系統的效能受到影響。

      解決i/o子系統有關的問題也許是最容易的，多數情況下，增加磁碟機就可以解決這個效能問題。

精確定位元據庫效能瓶頸的根源

      效能問題往往與資料庫的設計與SQL語句有關。使用者作效能最佳化時，目標不是讓我們對硬體設施提出升級的建議。我們需要找到效能的真正根源，而不是似是而非的借口。

      雖然效能問題各有不同，但採用一般性方法，能診斷分析大部分問題。如果您在某種程度上具有資源瓶頸，應盡量找出產生這一瓶頸的所有原因。例如，如果由於 CPU 使用過度而導致輸送量明顯降低（即具有 CPU 瓶頸），建議首先確定伺服器上的五大 CPU 使用者。如果由於 I/O 飽和而導致輸送量明顯降低（即具有 I/O 瓶頸），建議首先確定伺服器上的五大 I/O 使用者。

      是否出現了鎖阻塞（鎖競爭）？建議首先分析正在爭用哪些資源（表和索引），並確定該爭用所涉及的五大 SQL 陳述式。雖然未說明問題是出自 SQL Server 2000 還是 SQL Server 2005，但確定不同瓶頸產生原因的方法在各平台之間只是略有不同。這些一般性經驗法則應該可以對您有所協助。在任一平台上，您都可以將調查範圍限制到特定的資料庫中。

在 SQL SERVER 資料庫中減少鎖使用

      鎖的使用是導致效能的一個重要因素，過多地鎖可能導致效能的嚴重下降，甚至出現死結的現象。同時，鎖在任何具有多個使用者的資料庫應用程式中都是不可避免的。但從另一方面來說，如果應用程式出現大量鎖，您可以採取相應措施。

      除了重新設計系統之外，首選的措施就是評估索引編製策略。如果您的應用環境中存在聚簇索引，請確保它們在 8K 大小頁面上儲存資料，並彼此遠離。甚至於還需要確保"填滿因數"（和Pad Index）均設定為非預設值，可能是 75% 到 80%，這樣可以在資料頁之間插入更多空間。作為輔助措施，請確保您的事務獲得最少量的必需鎖。同時盡量限制對萬用字元（例如 SELECT * FROM...）的使用，尤其是限制 INSERT、UPDATE 和 DELETE 事務保持開啟和未提交狀態的時間。

      其次，可通過使用 SET DEADLOCK_PRIORITY 命令設定一個事務或一組事務總是低優先順序（或總是高優先順序）。

      最後，您可變更給定串連的預設鎖定行為，具體途徑包括使用 SET TRANSACTION ISOLATION LEVEL 命令，或使用諸如 NOLOCK 的查詢提示來修改單個查詢的行為以按查詢進行變更。如果您沒有謹慎考慮變更 SQL Server 資料庫固有行為所帶來的後果，建議您不要執行此操作。

合理使用索引

      索引是資料庫中重要的資料結構，它的根本目的就是提高查詢效率。索引的使用要恰到好處，其使用原則如下：

      在經常進行串連，但是沒有指定為外鍵的列上建立索引，而不經常串連的欄位則由最佳化器自動產生索引；在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引；在條件運算式中經常用到的不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如在僱員表的"性別"列上只有"男"與"女"兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。 如果待排序的列有多個，可以在這些列上建立複合索引。

常見SQL語句技巧

      1.如果有獨特的索引，那麼帶有"="操作符的WHERE子句效能最好，其次是封閉的區間(範圍)，再其次是開放的區間。

      2.從資料庫訪問的角度看，含有不連續串連詞(OR和IN)的WHERE子句一般來說效能不會太好。所以，最佳化器可能會採用R策略，這種策略會產生1個工作表，其中含有每個可能匹配的執行的標識符，最佳化器把這些行標誌符(頁號和行號)看做是指向1個表中匹配的行的"動態索引"。最佳化器只需掃描工作表，取出每一個行標誌符，再從資料表中取得相應的行，所以R策略的代價是產生工作表。

      3.包含NOT、<>、或!　=的WHERE子句對於最佳化器的索引選擇來說沒有什麼用處。因為這樣的子句是排斥性的，而不是包括性的，所以在掃描整個原來資料表之前無法確定子句的選擇性。

      4.限制資料轉換和串操作，最佳化器一般不會根據WHERE子句中的運算式和資料轉換式產生索引選擇。例如:

      paycheck * 12>36000 or substring(lastname,1,1)="L"

      如果該表建立了針對paycheck和lastname的索引，就不能利用索引進行最佳化，可以改寫上面的條件運算式為:

      paycheck<36000/12 or lastname like "L%"

      5.如果沒有包含合并子句的索引，那麼最佳化器構造1個工作表以存放合并中最小的表中的行。然後再在這個表上構造1個分簇索引以完成一個高效的合并。這種作法的代價是工作表的產生和隨後的分族索引的產生，這個過程叫REFORMATTING。　　所以應該注意RAM中或磁碟上的資料庫tempdb的大小(除了SELECT INTO語句)。另外，如果這些類型的操作是很常見的，那麼把tempdb放在RAM中對於提高效能是很有好處的。