《效能调整》PPT课件.ppt

,Insights and Answers for IT Professionals,SQL Server 2000 效能調整,胡百敬 臺灣微軟顧問 (恆逸資訊教育訓練處),講座大綱,SQL Server 2000 運行架構剖析 執行效能瓶頸監控 索引機制的使用與效能考量 交易與查詢調校 應用程式設計的技巧與注意事項 案例研討 結論,使用者與SQL Server的溝通過程,查詢,結果集,結果集,1,2,3,4,5,硬體限制,記憶體 儲存子系統 中央處理器 網路系統,SQL Server 2000 如何使用記憶體,Server Net-Libraries,Open Data Services,SQL Server Service,Distributed Query OLE DB Providers,OLE Automation Objects,Extended Stored Procedures,System-Level Data Structures,Procedure Cache,Buffer Cache,Log Caches,Connection Context,監控記憶體的使用,SQL Server 2000 如何從硬碟讀取資料,SQL Server Buffer Manager,Windows 2000 I/O Buffer (64 KB) 8-KB increments,Local Database,Memory Buffer Cache Pages,C,E,A,D,F,H,G,B,A,B,C,D,E,F,G,H,C,E,A,D,F,H,G,B,7,3,1,8,6,4,2,5,控制器的傳輸速率 最大傳輸量 = 每個控制器 (每秒多少 MB) (每秒多少 MB) 連接的最大硬碟數,估算每一個裝置的 I/O 處理量 估算每一個磁碟控制器(SCSI或RAID)控制硬碟的量 估算 PCI Bus 所能使用的控制器的量,範例 40(控制器的傳輸速率 9.6(最大傳輸量 = 4 (每個控制器 每秒多少 MB) 每秒多少 MB) 連接的最大硬碟數),每秒傳輸次數 x 資料區塊大小 = 最大傳輸量 (KB) (每秒多少 MB),範例 150(循序存取 x 64(資料區塊大小) = 9.6 MB(最大傳輸量 每秒傳輸次數 KB) 每秒多少 MB),計算磁碟子系統的效能瓶頸,PCI bus 傳輸速率 控制器的傳輸速率 = 連接的控制器數目 (每秒多少 MB) (每秒多少 MB),範例 266(PCI bus 40(控制器的傳輸速率 = 6 (連接的控制 傳輸速率 每秒多少 MB) 每秒多少 MB) 器數目),監控硬碟的使用,硬碟子系統的使用建議,設定適當的檔案與 log 的初始大小與增長的量 將資料檔與記錄檔分開不同硬碟存放 使用 FileGroup 使用 RAID 使用 Partition View,Local DB,記憶體,SQL 2000 如何使用中央處理器和執行緒(Threads),SQL Server,Relational Engine,Open Data Services,Storage Engine,Processor 0,Processor 1,Processor n,Rowsets,UMS (User Mode Scheduler) Scheduler,UMS Scheduler,UMS Scheduler,2,3,SQL Server 維護一個 Thread Pool 來處理使用者的需求 如查詢或是連結,使用自己的排程 而非作業系統的， 來決定哪個處理器 執行哪條執行緒,處理器處理查詢 從記憶體或是硬 碟中取出資料， 並將這些結果還 回Storage Engine,將執行緒還回 IOCompletion Port,監控執行緒和中央處理器的使用,效能瓶頸監控的建議,設定 Performance Counter 的警告訊息 利用 Profiler 監控忙碌時期的運作找出最耗資源的運作,設定索引,索引的運作方式 以非叢集索引查詢，且資料表沒有叢集索引 以叢集索引查詢 以非叢集索引查詢，但資料表有叢集索引 設定與維護索引的建議,SELECT lastname, firstname FROM member WHERE lastname BETWEEN Masters AND Rudd,利用非叢集索引在 Heap 中找尋符合的記錄,sysindexes,在叢集索引中找記錄,SELECT lastname, firstname FROM member WHERE lastname = Ota,Martin,Martin,透過非叢集索引在叢集索引內找記錄,sysindexes,SELECT lastname, firstname, phone FROM member WHERE firstname = Mike,Nagata,設定與維護索引的建議(一),索引利於查詢，對於新增、修改、刪除則要很精準地建立 小心選擇叢集索引，因為 SQL Server 2000的預設是最好要有叢集索引，所以當你以 SQL 語法建置主鍵時(PK)，預設是同時建立叢集索引。可以在宣告主鍵時使用 NONCLUSTERED 關鍵字，以保留叢集索引給更好的用途。 非叢集索引應該具備資料密度低，也就是下 WHERE 條件時具有高選擇性(SELECTIVITY) 若查詢的欄位少，且長度短。建立複合索引來涵蓋查詢所有的欄位，如此可以不必讀取實體資料頁。,設定與維護索引的建議(二),設定 Fillfactor 以減少在交易時的 split 動作 定期刪掉並重建索引、更新索引，或是至少自動維護索引的統計值 考慮刪掉一些為平常不執行的動作建立的索引，如季報表、年報表。但在要建立報表時再重新建立索引。以減少日常資料庫進行交易時的維護動作。 若是以查詢為主的資料庫可以 DBCC SHOWCONTIG 來檢視索引的不連續狀況 若是 WHERE 子句包含 AND 的查詢，可以考慮建立複合索引，包含所有子句內的欄位，查詢利用該索引就可以判讀記錄符合與否,設定與維護索引的建議(三),若是 WHERE 子句包含 OR 的查詢，最好是所有子句內的欄位都有索引，否則 SQL Server 2000 會以 Table Scan 來完成查詢。 對於 WHERE 條件是否定的查詢，如 胡xx、NOT( Salary 30000) 等等 (以 % 開頭 Like 也是一樣)，索引是沒有用的。必須要使用 Table Scan 利用 Index Analysis 來選擇為某句查詢語法建立索引 利用 Index Tuning Wizard 來了解最常執行的查詢，與建議該建立的索引 對於常查詢但不常改變的 View 建立索引 了解 sysindexes 資料表，以了解索引與統計的概況,Sysindexes 部分欄位架構,10 萬筆記錄 Pentium iii 750 256MRAM select top 10 * from testtable order by col1 desc 不透過索引 1.6 sec 透過 Nonclustered Index 0.14 sec,Indexed View Clustered Index 和 Nonclustered Index 的比較,交易與查詢調校,查詢最佳化程式的執行步驟 Join 的運作方式 解讀圖形化執行計劃的意義 解讀統計資料的內容 交易與查詢調校建議,查詢最佳化程式的執行步驟,解析：檢查語法正確性，並將語法 拆解成 relational engine 能理解的小 單元。輸出 parsed query tree,Transact-SQL,結果集,Join 的運作方式,Nested Loop Join 從外層的資料表取出一筆記錄 使用這個記錄掃描內層的資料表 再回到外層的資料表，重複上述的步驟 Merge Join 使用兩個資料表用來 Join 的欄位既有的索引 兩邊的資料表以游標由小到大比較，一邊移動到比另一個資料表大時，換移另一個資料表 Hash Join 處理大量、未排序、無索引的資料 以資料少的資料表的 Join 欄位建立 Hash 值 對應的資料表計算 Join 欄位的 Hash ，再與前一個資料表做比對,解讀圖形化執行計劃的意義,解讀 Query,解讀統計資料的內容,CPU time CPU 計算 所花時間 Elapsed time 總時間,SHOWPLAN_ALL 的結果在加上 Rows 每一個運算子真 實傳回的記錄數 Executes 運算子被執行的次數,Table 被運作的資料表名稱 Scan count 運作中被掃描的次數 Logical reads 從資料快取中讀取的 頁數 Physical reads 從硬碟中讀取的數目 Read-ahead 因為這個查詢被放進 資料快取的頁數,交易與查詢調校的建議(一),儘量不要使用 SELECT *(不指定欄位名稱) 且沒有 WHERE 子句的查詢。 若查詢需要超過四個以上的 Join ，考慮作反正規化(denormalized)。但若單一 Table 太大，雖然該 Table 已經符合三階正規，仍可以考慮做分割。 為參加 Join 的欄位建立索引，考慮為冗長的 Join 欄位建立整數型別的代替鍵，以方便比對，且欄位簡短。 SQL Server 2000 會自動為主鍵建立索引，但不會為外鍵建立索引，考慮為常用的外鍵建立索引。 無須針對所有的交易查詢語法調校，但要為高執行次數的語法或執行時間過長的語法做效能調校。確定交易的關鍵語法。,交易與查詢調校的建議(二),考慮使用 Trigger 替代聚合函數(aggregation)如 Sum、Avg 等等 保持整筆記錄及索引鍵值儘量簡短，以提昇一個 Page 內能容納的數量，減低 I/O 的運作。 設計以資料表或欄位為橫軸、交易所使用的SQL 查詢語句為縱軸，內含交叉資料擺放 Insert-Select-Update-Delete 的 ISUD 表，以找尋可能最熱的資料存取。,應用程式設計的建議(一),將基本的資料庫運作建立成預存程序(Stored Procedure)，以 Command 搭配 Parameters 來重複執行 少開 Connection，開啟的 Connection 儘快利用 Close 方法關閉。 Connection 資源會被使用者的機器透過 ODBC 或 OLE DB Pool 住 儘量不要使用 Recordset(不是不能使用) 使用 COM+/MTS 透過中層來設定安全機制,應用程式設計的建議(二),可以利用 SET LOCK_TIMEOUT 來設定單一連線的 Lock timeout 時間。以避免過長的 Lock，但要做錯誤碼 1222 的錯誤處理 在開啟交易(BeginTran)後，不要讓使用者沒有執行完成(Commit)或是回復(Rollback)交易的動作就逕行離開交易 交易時間儘量短，在交易的過程中不要有與使用者互動的動作，避免一個人出去吃午餐，全部的人都要出去吃午餐 當使用到的資料量非常大，但不是即時性的動作考慮使用批次作業(如製作年、季、月報表)在離峰時間執行。,處理 Lock 的指令與執行結果,系統效能測試(一),測試系統的資料應儘量保持與上線系統資料的相似性，因為不同的資料型態會導致不同的查詢最佳化行為，而造成預估的錯誤。 測試的運作方式與壓力應與上線運作時相似，以檢視 Lock 的狀況。 在測試時可能需執行如 DBCC DROPCLEANBUFFERS、DBCC FREEPROCCACHE 等指令清除快取區的資料，以求得最差的 I/O 狀況。,系統效能測試(二),系統效能測試應該在開發過程中持續完成，若等到系統完成再做效能調校，可能導致大量的系統變更，與程式碼重寫。 建立效能運作底限，之後的調校以該底限為基準。 測試時一切以時間、數據為準，不要憑感覺判斷快慢，以及猜測瓶頸在哪裡。,案例研討 案情一,大型主機傳到 PC 上，一個檔案 500 MB，沒有 Relation，僅僅憑某個欄位更新另一個欄位。 系統 HP 的 PC 伺服器 PIII 4 顆 SCSI HD 256 MRAM 單一 Update 就需要十幾秒,案例研討 案情二,使用 DAO 存取 SQL Server，以月份加日期當 Table 名稱，到 1 月 1 號時效率大減,案例研討 案情三,四百多人同時存取的系統，依 DNA 架構建置。前端使用者下了命令約兩分鐘後才回應。 上線前如何評估機器的規格 前端應用程式需要確認是哪一行程式產生瓶頸 中層系統要監控效能，與壓力測試 DB 需要監控 Lock，Index，瓶頸的運作,結論 Conceptual 的考量,兩方面分析： 從商業邏輯領域來分析瓶頸所在，需要領域的專業人才將商業邏輯合理化。 從資料庫的實體運作分析，利用各種計數器與工具程式找出整體實體運作瓶頸(從使用者端到伺服器端，包含網路) 考量資料庫實體設計的差異，例如系統整體運作是偏向查詢還是偏向交易，若同時具備，考慮將系統切成兩個伺服器的運作。 某些參考到大量資料但重複的運算，如薪資計算要配合勞、健、團保、考勤、福利貸款、考績、紅利等等，可以考慮將資料先一次下載到某台電腦，在該台電腦上做所有的計算，以便不影響其他作業。,結論 監控的考量,考量尖峰時間的效能需求，而非平均