SQL Server調優的五個步驟)

步驟1 最佳化應用工作量

      最佳化應用效能的第一步是最佳化工作量。在該部分調優方法論中列出的最佳化步驟能夠解決很多常見的效能和可延展性問題。這些最佳化可以協助降低由於特殊的設計或低效的實施導致的效能瓶頸影響，並且可以保證系統資源能夠充分和有效利用。例如，解決低效率的查詢計劃或低效率的緩衝等問題將會更加有效率地發揮SQL伺服器緩衝機制，從而整體上降低I/O操作。

      ■ 編譯/重新編譯- 資料庫,CPU

      確定是否存在顯著的CPU競爭，如果存在，請關注重新編譯次數多的那些T-SQL語句，它們佔用大量CPU資源。如果應用中SQL代碼重新編譯次數很多，可以考慮下面最佳化方法：

      ●評估有關的語句的作用，將資料修改代碼和資料定義命令相分離。

      ●解決過時的索引統計。

      ●使用變數或其他邏輯替代暫存資料表。微軟忠告：頻繁地編譯/重新編譯會消耗很高的CPU和磁碟I/O資源，會增加整體的工作量競爭。

      ■ 低效率的查詢計劃-資料庫,CPU

      確定是否存在明顯的CPU競爭，如果有，請確定無效率查詢計劃是如何佔用過多的cpu資源。是否存在資料庫模式，應用需求，使用者使用的報表工具，或其它條件促使在生產環境下執行無效率的查詢，使用Hash串連和排序操作的查詢，結果會消耗很高的CPU和I/O。

 

      步驟2 減少讀/寫活動

      一旦你的應用代碼被調優，接下來達到最佳效能就是減少應用運行時讀寫活動量或I/O，一個最常見的應用代碼錯誤是編寫低效率的資料查詢操作；查詢返回很多的資料-太多的列或行-SQLServer會負載很大。無論是應用設計允許使用者建立自己的（通常無效率的），不限定每頁結果的查詢，還是後端代碼使用巢狀查詢，這些查詢會返回很多的資料（包括用視圖或資料表值函式寫的查詢），你的應用做為一個整體可能會訪問更多的遠超過需要的資料。在一些情況下，檢查完你的應用代碼後，你可能會認識到你的代碼將會返回底層表中的所有資料，來滿足查詢需要!分析存在的索引和它們維護模式，確定添加索引是否合適，分析資料庫檔案的增長情況會幫你極大減少應用的讀寫活動量，可以釋放寶貴的磁碟資源。

      ■ 無效率的或缺失的索引-DB I/O

      確定是否存在明顯的磁碟I/O競爭，如果存在，需要分析缺失或或無效率的索引是如何導致磁碟I/O瓶頸的。DBA們必須評估應用的 SQL代碼保證語句儘可能有效率地執行；這項任務通常必需建立索引來最有效地提取資料。如果應用的SQL代碼發生變化，訪問不同的表或從目的表選擇更多的/不同的列，當前的索引可能會不起作用。需要分析說明SQL 代碼無效率使用存在的索引或語句正在用表掃描搜集資料的地方。

      ■ 磁碟I/O-資料庫檔案的增長-DB I/O

      確定是否存在明顯的磁碟I/O競爭，如果存在，需要關注頻繁使用擴充段的資料庫。DBA們應關注在一定的時間視窗內頻繁使用擴充段的資料庫。當SQL Server增大資料庫檔案時，檔案傾向於破碎，操作將非常消耗CPU和I/O。

 

      ■ 磁碟I/O-資料庫檔案配置-DB I/O

      確定是否存在明顯的磁碟I/O競爭，如果存在，請關注配置糟糕的資料庫檔案是如何導致資料庫內鎖競爭的增加,進而形成資源瓶頸，減少應用之間的競爭。DBA應考察可能導致閂競爭的一些資料庫檔案的配置問題，包括：

      ●資料檔案和記錄檔配置在同一磁碟裝置上。

      ●資料庫檔案數量少於可用的CPU數量，特別是TempDB資料庫。

      ●資料庫檔案數量少於可用的磁碟I/O裝置數量。

 

 

      步驟3 減少競爭

      現在，已經最佳化應用的I/O訪問，下一步要完成的效能最佳化就是確保高度的並發不會導致對象競爭情況的增加。即使資料訪問被最佳化了，使用鎖和閂鎖的SQL Server引擎，會同步和保護資料訪問，在高負載下也會出現阻塞問題。智能的事務控制邏輯，可保證事務不會執行過長時間，或者只在適當得資料上加鎖，因而其是達到高並發的關鍵。使用適當的事務隔離層可保證減少不必要的讀操作阻塞，評估鎖提示的需要可保證鎖的不必要的保持，這些都可以極大提高應用的效能。為了減少或消除閂鎖問題，保證應用不要將DDL和DML的操作混在一起。一旦解決這些問題，你就應該分析你的應用時如何訪問資料的，以便確定是否可以通過資料分區的方式提高應用效能。

      ■ 阻塞鎖-對象競爭-資料庫鎖

      確定是否存在明顯的鎖競爭，如果存在，看看經常出現鎖競爭的資料庫表，協助識別故障點和缺失的索引，應用傾向於訪問資料庫中的某些特定的表多一些。當隔離層設定不正確時，事務會執行很長時間，由於涉及到的索引導致不能訪問資料，處理髮生衝突或發生阻塞等。許多應用管理員沒有意識到資料庫遭受阻塞的程度；我們需要分析和發現由頻繁的短期鎖大量累積而導致的明顯競爭。

      ■ 阻塞鎖-鎖類型-資料庫鎖

      確定是否存在明顯的鎖競爭，如果存在，按照資料庫分析鎖的類型。某些應用以不同的方式訪問不同的特定資料庫。其原因可能是不同的開發人員開發的代碼不同，或需求不斷變化等等。按照資料庫顯示不同的SQL Server鎖類型的分析結果，顯示鎖的行為與整體啟用時間的比較分析的重要程度，這些將有助於應用程式開發人員正確地修改他們的應用代碼。

      ■ 記憶體緩衝區閂鎖-資料庫閂鎖

      確定是否存在明顯的記憶體緩衝區閂鎖競爭，如果存在，很多的記憶體緩衝區閂鎖等候是I/O瓶頸和熱頁的跡象。因為記憶體緩衝區閂鎖與I/O競爭沒有直接關係，因而這對SQL Server的可用記憶體數量是很關鍵的。

      ■ 內部快取閂鎖競爭- 資料庫閂鎖

      確定是否存在明顯的內部快取閂鎖競爭，如果存在，識別出哪裡存在大部分競爭。內部快取閂鎖可用在多種不同的情況；可能最常見的例子是內部快取的競爭（不是緩衝池頁），尤其當使用堆,text或兩者同時使用的時候。如果解決LOG和PAGELATCH_UP的競爭後沒有作用，通常將資料分區可以很好緩解內部快取閂鎖的競爭。

 

 

      步驟4 解決資源瓶頸

      到目前為止，你已經確保你的查詢正確地使用了底層的系統資源，並且儘可能有效地訪問資料。現在你應該確定是否有資源瓶頸使你的應用慢下來。在應用上你可以做許多調優工作，在某些情況下外部因素仍是效能最佳化的最後障礙。這部分調優方法描述了特定資源的瓶頸。例如，SQL Server有足夠的記憶體來支援良好的效能嗎？有竊取SQL Server記憶體的外部應用程式嗎？你的硬碟效能能足夠支援你的工作量嗎？你的應用能有效率地記錄日誌嗎，記錄日誌的時間是否需要提高？最後，並行可以協助你的查詢執行更快，還是SQL Server花費更多的時間協調並發線程，從而使得並髮帶來更多的阻礙？應該考慮到應用效能的這些方面，可以保證充分利用底層系統資源，並且可以協助確定哪些硬體需要擴容。

      ■ 記憶體壓力-系統記憶體

      確定是否存在明顯的記憶體壓力，如果存在，請分析：

      ● 外部的記憶體壓力可以影響SQL Server的效能。許多DBA和DBA的經理們不明白病毒檢測軟體的配置不當和在一個exchange server上安裝SQL Server所帶來的影響。

      ● SQL Server沒有足夠的記憶體達到理想的功能。如果SQL Server不能分配給緩衝足夠的記憶體，頁的平均壽命將減少，系統範圍記憶體分頁交換就會增加。

      ■ 日誌等待

      確定是否有明顯的日誌等待，如果有，分析有多少因素減慢SQL Server記錄日誌。

 

 

      步驟5 基準偏離分析

      毫無疑問，對應用效能的最好的比較衡量是它自己過去的效能。需要分析應用效能是在哪裡與過去的所觀測的行為發生了偏離。這意味著你能快速，容易地看出你的應用的伸縮狀況，以及確定應用的構建和系統變化等因素對企業效能的影響。我們可以分析多種主要資源的使用的偏離情況。我們需要分析出哪種主要的資源分類會受到影響，根據分析所提供的邏輯和分析度量值與其它支援資訊，可以更快地理解如何獲得應用的最佳效能。

      ■ CPU使用率偏離，CPU等待時間偏離，I/O等待時間偏離，閂鎖等候時間偏離, 鎖等待時間偏離，工作量偏離

      ● 確定這些指標是否與過去典型的使用方式發生了重要變化。

      ● 這些變化應該再核實確認，確保它們不會成為有問題的變化。