《效能调整》PPT课件

,InsightsandAnswersforITProfessionals,SQLServer2000效能調整,胡百敬臺灣微軟顧問(恆逸資訊教育訓練處),講座大綱,SQLServer2000運行架構剖析執行效能瓶頸監控索引機制的使用與效能考量交易與查詢調校應用程式設計的技巧與注意事項案例研討結論,使用者與SQLServer的溝通過程,查詢,結果集,結果集,1,2,3,4,5,硬體限制,記憶體儲存子系統中央處理器網路系統,SQLServer2000如何使用記憶體,ServerNet-Libraries,OpenDataServices,SQLServerService,DistributedQueryOLEDBProviders,OLEAutomationObjects,ExtendedStoredProcedures,System-LevelDataStructures,ProcedureCache,BufferCache,LogCaches,ConnectionContext,監控記憶體的使用,SQLServer2000如何從硬碟讀取資料,SQLServerBufferManager,Windows2000I/OBuffer(64KB)8-KBincrements,LocalDatabase,MemoryBufferCachePages,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)控制硬碟的量估算PCIBus所能使用的控制器的量,範例40(控制器的傳輸速率9.6(最大傳輸量=4(每個控制器每秒多少MB)每秒多少MB)連接的最大硬碟數),每秒傳輸次數x資料區塊大小=最大傳輸量(KB)(每秒多少MB),範例150(循序存取x64(資料區塊大小)=9.6MB(最大傳輸量每秒傳輸次數KB)每秒多少MB),計算磁碟子系統的效能瓶頸,PCIbus傳輸速率控制器的傳輸速率=連接的控制器數目(每秒多少MB)(每秒多少MB),範例266(PCIbus40(控制器的傳輸速率=6(連接的控制傳輸速率每秒多少MB)每秒多少MB)器數目),監控硬碟的使用,硬碟子系統的使用建議,設定適當的檔案與log的初始大小與增長的量將資料檔與記錄檔分開不同硬碟存放使用FileGroup使用RAID使用PartitionView,LocalDB,記憶體,SQL2000如何使用中央處理器和執行緒(Threads),SQLServer,RelationalEngine,OpenDataServices,StorageEngine,Processor0,Processor1,Processorn,Rowsets,UMS(UserModeScheduler)Scheduler,UMSScheduler,UMSScheduler,2,3,SQLServer維護一個ThreadPool來處理使用者的需求如查詢或是連結,使用自己的排程而非作業系統的，來決定哪個處理器執行哪條執行緒,處理器處理查詢從記憶體或是硬碟中取出資料，並將這些結果還回StorageEngine,將執行緒還回IOCompletionPort,監控執行緒和中央處理器的使用,效能瓶頸監控的建議,設定PerformanceCounter的警告訊息利用Profiler監控忙碌時期的運作找出最耗資源的運作,設定索引,索引的運作方式以非叢集索引查詢，且資料表沒有叢集索引以叢集索引查詢以非叢集索引查詢，但資料表有叢集索引設定與維護索引的建議,SELECTlastname,firstnameFROMmemberWHERElastnameBETWEENMastersANDRudd,利用非叢集索引在Heap中找尋符合的記錄,sysindexes,在叢集索引中找記錄,SELECTlastname,firstnameFROMmemberWHERElastname=Ota,Martin,Martin,透過非叢集索引在叢集索引內找記錄,sysindexes,SELECTlastname,firstname,phoneFROMmemberWHEREfirstname=Mike,Nagata,設定與維護索引的建議(一),索引利於查詢，對於新增、修改、刪除則要很精準地建立小心選擇叢集索引，因為SQLServer2000的預設是最好要有叢集索引，所以當你以SQL語法建置主鍵時(PK)，預設是同時建立叢集索引。可以在宣告主鍵時使用NONCLUSTERED關鍵字，以保留叢集索引給更好的用途。非叢集索引應該具備資料密度低，也就是下WHERE條件時具有高選擇性(SELECTIVITY)若查詢的欄位少，且長度短。建立複合索引來涵蓋查詢所有的欄位，如此可以不必讀取實體資料頁。,設定與維護索引的建議(二),設定Fillfactor以減少在交易時的split動作定期刪掉並重建索引、更新索引，或是至少自動維護索引的統計值考慮刪掉一些為平常不執行的動作建立的索引，如季報表、年報表。但在要建立報表時再重新建立索引。以減少日常資料庫進行交易時的維護動作。若是以查詢為主的資料庫可以DBCCSHOWCONTIG來檢視索引的不連續狀況若是WHERE子句包含AND的查詢，可以考慮建立複合索引，包含所有子句內的欄位，查詢利用該索引就可以判讀記錄符合與否,設定與維護索引的建議(三),若是WHERE子句包含OR的查詢，最好是所有子句內的欄位都有索引，否則SQLServer2000會以TableScan來完成查詢。對於WHERE條件是否定的查詢，如胡xx、NOT(Salary30000)等等(以%開頭Like也是一樣)，索引是沒有用的。必須要使用TableScan利用IndexAnalysis來選擇為某句查詢語法建立索引利用IndexTuningWizard來了解最常執行的查詢，與建議該建立的索引對於常查詢但不常改變的View建立索引了解sysindexes資料表，以了解索引與統計的概況,Sysindexes部分欄位架構,10萬筆記錄Pentiumiii750256MRAMselecttop10*fromtesttableorderbycol1desc不透過索引1.6sec透過NonclusteredIndex0.14sec,IndexedViewClusteredIndex和NonclusteredIndex的比較,交易與查詢調校,查詢最佳化程式的執行步驟Join的運作方式解讀圖形化執行計劃的意義解讀統計資料的內容交易與查詢調校建議,查詢最佳化程式的執行步驟,解析：檢查語法正確性，並將語法拆解成relationalengine能理解的小單元。輸出parsedquerytree,Transact-SQL,結果集,Join的運作方式,NestedLoopJoin從外層的資料表取出一筆記錄使用這個記錄掃描內層的資料表再回到外層的資料表，重複上述的步驟MergeJoin使用兩個資料表用來Join的欄位既有的索引兩邊的資料表以游標由小到大比較，一邊移動到比另一個資料表大時，換移另一個資料表HashJoin處理大量、未排序、無索引的資料以資料少的資料表的Join欄位建立Hash值對應的資料表計算Join欄位的Hash，再與前一個資料表做比對,解讀圖形化執行計劃的意義,解讀Query,解讀統計資料的內容,CPUtimeCPU計算所花時間Elapsedtime總時間,SHOWPLAN_ALL的結果在加上Rows每一個運算子真實傳回的記錄數Executes運算子被執行的次數,Table被運作的資料表名稱Scancount運作中被掃描的次數Logicalreads從資料快取中讀取的頁數Physicalreads從硬碟中讀取的數目Read-ahead因為這個查詢被放進資料快取的頁數,交易與查詢調校的建議(一),儘量不要使用SELECT*(不指定欄位名稱)且沒有WHERE子句的查詢。若查詢需要超過四個以上的Join，考慮作反正規化(denormalized)。但若單一Table太大，雖然該Table已經符合三階正規，仍可以考慮做分割。為參加Join的欄位建立索引，考慮為冗長的Join欄位建立整數型別的代替鍵，以方便比對，且欄位簡短。SQLServer2000會自動為主鍵建立索引，但不會為外鍵建立索引，考慮為常用的外鍵建立索引。無須針對所有的交易查詢語法調校，但要為高執行次數的語法或執行時間過長的語法做效能調校。確定交易的關鍵語法。,交易與查詢調校的建議(二),考慮使用Trigger替代聚合函數(aggregation)如Sum、Avg等等保持整筆記錄及索引鍵值儘量簡短，以提昇一個Page內能容納的數量，減低I/O的運作。設計以資料表或欄位為橫軸、交易所使用的SQL查詢語句為縱軸，內含交叉資料擺放Insert-Select-Update-Delete的ISUD表，以找尋可能最熱的資料存取。,應用程式設計的建議(一),將基本的資料庫運作建立成預存程序(StoredProcedure)，以Command搭配Parameters來重複執行少開Connection，開啟的Connection儘快利用Close方法關閉。Connection資源會被使用者的機器透過ODBC或OLEDBPool住儘量不要使用Recordset(不是不能使用)使用COM+/MTS透過中層來設定安全機制,應用程式設計的建議(二),可以利用SETLOCK_TIMEOUT來設定單一連線的Locktimeout時間。以避免過長的Lock，但要做錯誤碼1222的錯誤處理在開啟交易(BeginTran)後，不要讓使用者沒有執行完成(Commit)或是回復(Rollback)交易的動作就逕行離開交易交易時間儘量短，在交易的過程中不要有與使用者互動的動作，避免一個人出去吃午餐，全部的人都要出去吃午餐當使用到的資料量非常大，但不是即時性的動作考慮使用批次作業(如製作年、季、月報表)在離峰時間執行。,處理Lock的指令與執行結果,系統效能測試(一),測試系統的資料應儘量保持與上線系統資料的相似性，因為不同的資料型態會導致不同的查詢最佳化行為，而造成預估的錯誤。測試的運作方式與壓力應與上線運作時相似，以檢視Lock的狀況。在測試時可能需執行如DBCCDROPCLEANBUFFERS、DBCCFREEPROCCACHE等指令清除快取區的資料，以求得最差的I/O狀況。,系統效能測試(二),系統效能測試應該在開發過程中持續完成，若等到系統完成再做效能調校，可能導致大量的系統變更，與程式碼重寫。建立效能運作底限，之後的調校以該底限為基準。測試時一切以時間、數據為準，不要憑感覺判斷快慢，以及猜測瓶頸在哪裡。,案例研討案情一,大型主機傳到PC上，一個檔案500MB，沒有Relation，僅僅憑某個欄位更新另一個欄位。系統HP的PC伺服器PIII4顆SCSIHD256MRAM單一Update就需要十幾秒,案例研討案情二,使用DAO存取SQLServer，以月份加日期當Table名稱，到1月1號時效率大減,案例研討案情三,四百多人同時存取的系統，依DNA架構建置。前端使用者下了命令約兩分鐘後才回應。上線前如何評估機器的規格前端應用程式需要確認是哪一行程式產生瓶頸中層系統要監控效能，與壓力測試DB需要監控Lock，Index，瓶頸的運作,結論Conceptual的考量,兩方面分析：從商業邏輯領域來分析瓶頸所在，需要領域的專業人才將商業邏輯合理化。從資料庫的實體運作分析，利用各種計數器與工具程式找出整體實體運作瓶頸(從使用者端到伺服器端，包含網路)考量資料庫實體設計的差異，例如系統整體運作是偏向查詢還是偏向交易，若同時具備，考慮將系統切成兩個伺服器的運作。某些參考到大量資料但重複的運算，如薪資計算要配合勞、健、團保、考勤、福利貸款、考績、紅利等等，可以考慮將資料先一次下載到某台電腦，在該台電腦上做所有的計算，以便不影響其他作業。,結論監控的考量,考量尖峰時間的效能需求，而非平均值。考量整體系統回應時間，尋求瓶頸所在。在整個開發週期中要時時對prototype作效能測試，而非系統完成才開始做。以SQLServ