iT邦幫忙─調校資料庫效能的實務方法

以下，我們將摘錄「資料庫效能調校實戰經驗大募集」的內容，讓實戰專家與獲獎邦友分享他們的技巧與經驗。

領域知識占重要地位
效能調校需要有廣泛的經驗與知識，不單單對資料庫本身的經驗，還需對於其他相關知識，都應當有基本了解，才能在複雜的系統中，找到癥結所在。

資料庫維持良好效能，有賴平時定期調整
資料庫效能調校，絕非是一時半刻就可以完成的工作，也不是只作一次就行，必須隨著資料成長、系統環境改變等因素，定期調整來維持資料庫的效能。

3個面向合併觀看，找出效能瓶頸
系統調校不是一件容易的工作，要徹底解決資料庫效能問題，還是應該從資料庫、應用程式與作業系統等因素，合併觀察與考量，才能得出較佳的效果。

由內而外的調校方式
定期重建（Rebuild）和重整（Reorg）資料庫索引、定期更新Statistics、抓出較慢的SQL語法，都是調校資料庫效能的好方法。

圖形化工具讓效能調校更簡單
Microsoft SQL Server 2005中的Management Studio提供許多內建的資料庫報表，可供DBA概觀地分析目前資料庫的運作狀況。透過圖形化的介面查看系統狀態，就不用去記憶許多查詢語法，讓調校更簡單。領域知識占重要地位

實戰專家 胡百敬：
從我多年使用資料庫的經驗，以及參與多家企業效能調校的實務歷練得知，打從系統分析與應用程式設計的階段開始，就需要時時小心。

倘若基礎架構設計不當，一旦積弊日久，等到需要調校資料庫時，就會因牽涉到前端應用程式與使用者習慣等問題，而大幅增加複雜度。如果設計時沒有預留調校空間，等到發生問題時，說不定改善的程度就很有限。

領域知識占重要地位
跟資料庫系統效能有關的面向，我整理了一部分內容，大要如下：
應用領域知識（Domain Know-how)
系統的好壞，大部分取決於對領域知識的熟悉程度，或是分析設計的深度。領域知識能幫助你了解該儲存資料的類型，以及可以切割的部分
資料庫設計
如資料切割、正規化與反正規化、資料庫物件的使用、Archive、維護計畫、tempdb系統資料庫的設定與使用，以及避免過大的資料表或欄位、少用觸發（Trigger）等。
軟體系統
要熟悉運作原理，懂得正確設定、規畫資源使用與監控等。
硬體架構
注意設備規格、負載平衡、日常維護、災難復原等。
SQL 語法
善用集合處理、可搜尋的條件（SARG），避免不正確地使用Cursor等。
索引與統計
索引的數量要適量，且需要日常維護、覆蓋、檢視（Index View）等。
交易與鎖定
避免交易過大、過久，或死結等。
應用程式存取
注意程式的設計與撰寫技巧、資料物件的使用，以及批次、多層次、Connection Pooling，避免大量資料結果傳遞，少用Cursor造成過多的網路來回等。
驗證與測試
如單元測試、壓力測試等，執行上務必擬真、徹底。
利用工具程式去觀察與分析
監控資料庫存取方式、效能計數器、事件記錄、網路流量等。
解決問題的能力
除了正面解決問題，萬一行不通時，也要能提出種種替代方案。

從上述的面向可知，效能調校需要有廣泛的經驗與知識，不單單對資料庫本身的經驗，還要對商業邏輯、系統架構設計、撰寫應用程式、作業系統、網路環境、各種偵測與監控工具程式、安全與防毒等等，都應當有基本了解，才能在複雜的系統中，找到癥結所在。

架構設計得當，更勝升級硬體成效
各種影響效能的因素中，最重要的是基礎設計。但大多數的案子受限於時程急迫、對技術的認知有限、需求不明確等因素，導致系統結構沒擴充性，僅能一味地升級硬體，但整體效能仍不能大幅改善。

加強硬體運算力對系統的效能當然會有幫助，但結果不一定能讓人滿意，例如交易鎖定過多且耗時久、資料表欄位多且太長、所下的 SQL 語法無法有效地平行運算、不能將工作分成多個批次同時執行等，像這些情況，只是增加處理器的數量，也無法解決問題。

因此光是升級多顆處理器、加大記憶體或高速的硬碟等方式，就以為一定能對效能產生幫助，其實是一種迷思。再加上，資料庫系統本身提供的負載平衡相關機制有局限性，效果也有限。

換句話說，一般談到提升效能的基本做法，一是Scale Up（擴充單一臺伺服器），另一個是Scale Out（以多臺伺服器提供服務），在缺乏好的設計架構的前提下，2種幫助皆不大。

由廣而深，5階段找出癥結
在我多次參與效能整頓的經驗中，經常會有貿然認定效能問題的情形。常常是程式設計師一口咬定資料庫有問題，等到一一清理相關環結後，最後證明，問題出在程式設計與撰寫不當。因此不論發出這方面調校需求的是系統管理者或開發人員，往往會有盲點，認定問題發生在本身較不熟的領域，且無法對系統有整體觀。

事實上，效能問題往往不如表面般單純，讓人一眼就能看出答案。所以不要僅憑直覺猜測，就一頭栽入，如果猜測所在並非最大瓶頸點，將耗盡精力而無明顯效果。

舉個簡單的例子，當你看到處理器很忙碌，長時間處在百分之百的使用率，一般的直覺判斷就會認為處理器效能不足。但其實問題也可能出在硬碟效能不足，或是記憶體容量不夠，從而讓處理器一直在I/O上做工。從這個例子可以說明，效能問題的癥結，通常不是表象所呈現的，從不同的面向去看，往往會有不同的解釋。

大型系統的效能調校需要有耐心、細心、恆心與毅力，且方式要循序漸進，也需與使用者充分地溝通協調，地毯式地檢視所有細節，每個步驟都得小心翼翼，並盡量擴充團隊知識的廣度與深度。

確定效能問題的主因是資料庫系統時，可以逐漸縮小效能問題範圍，再去探尋追查。範圍縮小了，問題才會逐一浮現，不至於大海撈針。此外，由廣而深的作法，才能在追查的過程中，不至於錯過、疏漏了主要問題。

要顧及求助者的感受
進行效能調校的過程中，不要貿然行動，而迷失在自己產生的困擾中。這與海森堡的「測不準原理」相同，每一個測試動作都會影響系統本身，看到的現象都是系統與你互動的結果。

像是測試效能時，使用工具程式產生大量的資料，而這驗證的動作不單是資料庫，也包括網路與前端程式，這些層面都會牽動系統整體如何受到影響。因此調校過程一定要有步驟，確定每次的動作都讓你更接近目標，妥善蒐集各種資訊。

提醒一點，雖然調校過程必須三思而後行，但同時也要顧慮到求助者的感受。太過於著重過程分析，有可能蒐集了太多的資訊，反而千頭萬緒，不知從何下手，因而遲遲沒有行動。在一段時間之後，求助者仍然看不到改善成果，就會失去耐心。

因此要在取得資訊、細心分析、構想計畫、謹慎執行、反省結果、以及安撫求助者中，應該取得適切平衡，但這也並非容易的事。

因此效能調校大不易，它考驗其實是團隊的總體經驗，尤其面對的若是大型系統，集思廣益才是上策，而不要僅是一個人苦思對策。

作者簡介
資料庫效能調校實戰專家─胡百敬
現任職恆逸資訊資深講師，並擔任微軟、Ascentn、睿智資訊等多家企業技術顧問，曾出版多本資料庫相關專著。資料庫維持良好效能，有賴平時定期調整

實戰王alexc（IT邦初學者9級）：
資料庫的效能要打好基礎，必須先從依循正規化準則設計資料庫邏輯開始，然後才思考資料庫的實體設計。例如是否要反正規化？要建立哪些索引？哪些資料要儲存在 RAID？該用什麼等級的伺服器？其他像是應用程式使用的架構或資料存取技術（選用ADO或OLEDB）等，或多或少都會影響。當然個人端電腦與伺服器端的硬體組態設定，像：記憶體與處理器等，也都會影響資料庫的效能。

確認調校目的才不會走錯方向
進行資料庫效能調校之前，應該要先確認效能調校的目的為何？是為了加快讀取速度？還是為了讓寫入效能更好？抑或是需要簡單、容易管理的資料庫架構，而非增進讀取跟寫入的效能。

在確認效能調校目的後，可以嘗試將資料分割（Partition），視需求可以進行水平資料分割或垂直資料分割，甚至是考慮替資料庫建立多個次要資料檔，並且設定檔案群組，來讓這些資料庫檔案分散在不同的實體磁碟機中。實務上為了效能，通常會將關鍵資料表與索引放在高效能的 RAID 裡。

如果情況允許，應該設法將資料庫對磁碟存取的動作，平均分散在多部 RAID 中，以免硬碟存取活動較多的那部 RAID 提早掛點。

監控效能找出效能低落原因
基本架構設計好，就不怕「樹尾作颱風」了，這才是開始調校資料庫的時候。其實要監控效能，從實體的硬碟、邏輯的 I/O 到看不到的網路封包都應該要監控，所以在監控時，一般會分成網路、記憶體（包含快取）、處理器與磁碟I/O這幾個大項目，去進行監控。

以Microsoft SQL Server為例，可用的監控工具有 Windows 內建的 Performance Monitor、SQL Server的DBCC、SQL Server Profiler，以及 到了SQL Server 2005 才加入的動態管理檢視表。
為了熟悉使用這些工具的基本概念，可以先從底層的網路開始著手，然後是處理器使用率，接著是記憶體與快取的使用率，最後才是磁碟I/O 的使用率。

如果先天體質不良（網路、處理器、記憶體、磁碟都不是我們所能改變的事實），那就靠後天——使用SQL Server 本身所提供的選項來改善效能，例如SQL Server用來作為資料倉儲之用，可能會載入大量資料，供使用者用做複雜的查詢，所以在多處理器的伺服器上，可用下面的指令來平行查詢：exec sp_configure 'cost threshold for parallelism'， 2

DTA可協助調整效能最佳化語法
除了調整SQL Server 所提供的選項設定之外，也可以透過 DTA （Database Engine Tuning Advisor） 這個好用的輔助工具，它可以針對資料分割、索引、資料表結構給予建議。一般會使用 GUI 介面的 DTA來操作，如果需要定期執行，還可以寫個批次檔，透過命令列的方式來進行。

DTA能夠分析整個資料庫，或僅處理資料庫中的某個資料表。如果已經確認某個資料表是效能瓶頸，就可以執行 DTA 分析該資料表。

至於在程式開發時，開發者應該使用查詢最佳化工具，找出一個能夠以最少的處理時間來存取資料的查詢計畫，以便撰寫出更好的查詢指令、選用較佳的索引，並找出影響效能的潛在因素。

資料庫效能調校，絕非是一時半刻就可以完成的工作，也不是只作一次就行，因為隨著資料成長、系統環境改變等因素，都會影響原有的效能。所以必須定期進行。3個面向合併觀看，找出效能瓶頸

認真獎davistai（IT邦好手1級）：
系統調校不是一件容易說得清楚的工作，大多時候都是根據既有的資訊與經驗，一次選擇一個參數微調。接著，觀察系統反應狀況，後續再調整。

由於「資料庫」是安裝在「作業系統」之上，而資料庫上層還有「應用程式」，因此遇到效能下降，應該要從這3方面去檢視當時的作業情形。

依照過往的經驗，最常遇到的是應用程式的狀況。這與應用程式的設計有很大的關係，往往應用程式一對最大的資料表執行全掃描，所有的I/O就會全部卡住；有時是因為沒建索引，導致撈取資料速度慢，程式回應自然就跟著變慢。通常遇到此狀況，先從修改應用程式著手，修改有問題的設定或語法，都可以恢復到不錯的效能。

如果問題不在應用程式，接下來就要從資料庫的記錄檔觀察，檢查alert.log或alert.trc檔案，看看是否有問題。

再來就是觀察線上作業情形，通常以v$session_event 跟 v$session_wait為目標，或使用statspack工具來進一步的分析。

最後，作業系統雖然比較不影響資料庫效能，不過有時還是會發生。曾經遇過類似的情況，與作業系統廠商溝通甚久，最後他們在所謂的內部文件發現到關鍵，有一個參數的確會影響資料庫運作效能。

另外，資料庫本身的設定值，必須配合作業系統支援的數值。通常原廠都會有相關資料，需依它提供的公式範例計算，再加上經驗值定出。

有些公司預算充足，IT主管為了省事及時效，會直接擴充硬體，例如先購置新的處理器與記憶體，不過它們能提升的效果為期不久，往往經歷一陣子之後，效能又會陷入瓶頸。

所以，要徹底解決資料庫效能問題，還是應該從資料庫、應用程式與作業系統等因素，合併觀察與考量，才能得出較佳的效果。由內而外的調校方式

認真獎andycheng（IT邦初學者10級）：
我目前用的是Microsoft SQL Server，就自己的經驗提供一些建議：
定期可做的事
定期做資料庫索引重建（Rebuild）和重整（Reorg），並定期更新Statistics。
分析SQL語法
如果系統變得較慢，可以用SQL Profiler抓出較慢的SQL語法，用工具去分析是否該改寫SQL或增加索引。另外，索引並非「多就是好」。曾經遇過使用20幾個索引，檢視之後刪掉無用的，系統反而變快。
監控系統效能
可開啟Windows的Performance Monitor或其他效能監控軟體，觀察資料庫和OS的效能，調整系統參數。
其他外在因素
效能差，也有可能不是資料庫造成的，我們可以從網路，硬體等因素調查。

圖形化工具讓效能調校更簡單

doggy（IT邦初學者9級）：
在Microsoft SQL Server 2005的環境中，最常用的工具應該是Management Studio了，除了大部分的維護工作都會透過它操作外，它還提供了許多內建的資料庫報表，可供DBA概觀地分析目前資料庫的運作狀況。透過圖形化的介面，查看系統狀態真的非常方便，不用去記憶許多查詢語法。

另外，SQL Server 2005也內建兩個很棒的效能調校工具，可以幫助你分析資料庫的效能與提供調校的建議，分別是SQL Server Profiler與Database Engine Tuning Advisor(DTA)。

SQL Server Profiler會將資料庫執行的狀況全部記錄下來，儲存的副檔名為「追蹤檔（Trace File）」（*.trc），也叫做「工作負載記錄檔」。這個檔案可以提供給Database Engine Tuning Advisor進一步分析這些工作負載的詳細資料，並提供一組最佳的索引、索引檢視和資料分割建議。

執行SQL Server Profiler之後，就會錄製應用程式對資料庫的所有動作，這時就可以開始測試應用程式，執行一段時間後，就可以停止錄製，並改用Database Engine Tuning Advisor分析 *.trc 檔。

利用錄製完成的「工作負載檔案」進行分析，DTA會評估調校之後的效能會改進的百分比，如果可以接受，就可以儲存建議，將系統產生的T-SQL指令碼存下來，然後再到Management Studio執行這段語法，就可以完成調校。