1 / 69

SQL Server 升級 與效能調校

SQL Server 升級 與效能調校. 報告人：李文豪 世新大學電算中心　系統組 組長 2010. 11. 19. 主題一： SQL Server 　　　　升級 2008 R2 64 位元 主題二： SQL Server 效能調校. 主題一： SQL Server 升級 2008 R2 64 位元. 選課案例說明 新舊架構與效能比較 升級程序與問題處理. 大綱 ：. 選課案例說明 - 世新大學為例. 選課第一階段僅為學生選課意願收集 , 不搶課。 選課第二階段為搶課制 , 各班名額有限。 各年級安排在不同天選課 , 早上 9:30 開放選課。

wardah
Download Presentation

SQL Server 升級 與效能調校

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server升級與效能調校 報告人：李文豪 世新大學電算中心　系統組組長 2010. 11. 19

  2. 主題一：SQL Server • 　　　　升級 2008 R2 64位元 • 主題二：SQL Server效能調校

  3. 主題一：SQL Server升級2008 R264位元 • 選課案例說明 • 新舊架構與效能比較 • 升級程序與問題處理 大綱：

  4. 選課案例說明-世新大學為例 • 選課第一階段僅為學生選課意願收集, 不搶課。 • 選課第二階段為搶課制, 各班名額有限。 • 各年級安排在不同天選課, 早上9:30開放選課。 • 大二跟大三第二階段選課為最壅塞的兩天, 早上9:30一開放, 有上千人次不斷湧入且不斷進行查詢與新增作業, 尤其9:30有搶登入所造成的瞬間尖峰, 若無法消化, 後續全會塞車。 • 本校今年第一階段仍用舊架構, 第二階段開始改用新架構。 註: 世新實際選課分為三階段, 但為避免混淆, 僅以兩階段簡單區分搶課與否。

  5. 舊架構 (舊DBServer + 3台WebAP Server) 由學生依學號自行選擇 Web AP Server Server

  6. 全面改進效能, 杜絕塞車問題 • 三台Web Server處理來自學生的Request, 有些人會看到錯誤訊息, 原因是Web Server 太忙了! 而且學生愛選第一台Web, 讓壓力無法分散。(此時SQL Server不是瓶頸, CPU約在80%) • 雖然知道瓶頸在Web Server, 但為避免Web Server通暢後又換成DBServer出現瓶頸, 本校研擬並執行『拔刺計畫』, 全面檢視可改善之處, 多管齊下, 以最少的預算發揮最高效益, 杜絕塞車問題!

  7. 新架構 (新DB Server + 13台Web AP Server+LB) 統一透過負載平衡Server選擇 Web AP Server Server

  8. 升級前:最忙碌時登入要3.8秒 上午10點最慢! 前面登入的人不斷進行 查詢跟新增作業, 讓此時 登入程序從平常的0.3秒 變慢到快接近4秒!! 註: 登入耗時是登入時在主機端的實際耗時, 其間還包含五六次的DB存取動作 (判斷身分、抓權限…), 由於每個人登入的存取程序相近, 可用來判斷整個主機端(Web+DB)的反應時間。

  9. 升級後:登入所需時間大幅縮短 新架構最慢不超過0.13秒! 與舊架構3.8秒相比, 效能相差30倍!!

  10. 其實不只30倍: 升級前登入人次只到3000

  11. 其實不只30倍: 升級後登入人次大量提升 登入人次 上午9:30~10:00 半小時內的登入人次 提升到6700人次!

  12. 升級後:9:30的SQLServer瞬間高峰 • 大二搶課在上午9:30的瞬間達到高峰, 讓DB主機CPU忙碌達到80%, 半小時後降至45%左右且保持穩定, DB主機消化工作游刃有餘, 各Web主機也都服務正常, 前後均無瓶頸, 選課不再塞車!

  13. 從舊架構到新架構的升級重點 • 用虛擬技術增加大量的Web AP Server(前一場次已報告過)。 • 寫一支精簡短小的程式, 擔任負載平衡主機。 • 將SQLServer從2000升級到2008 R2; 硬體也換新。

  14. 負載平衡主機的關鍵程式碼-1 • Global.asax.cs • 在每人第一次登入時的Session_Start事件把人數+1

  15. 負載平衡主機的關鍵程式碼-2 • Default.aspx.cs

  16. DBServer升級前規格 • 硬體 • IBM X366 • Inter XeonMP 7020 2.66GHz * 4(Benchmark: 417 * 4 = 1668) • 16GB RAM • RAID 5(6顆74.8G硬碟) • 軟體 • Windows Server 2003 Enterprise SP2 x86 • SQL Server 2000 Enterprise x86 【Ver.8.00.2282(SP4)】

  17. DB Server升級後規格 • 硬體 • IBM X3650 M2 • Inter Xeon E5640 2.66GHz * 2(Benchmark: 5482 * 2 = 10964)(提升約6倍) • 16GB RAM • RAID 5(6顆300G硬碟) • 軟體 • Windows Server 2008 R2 Enterprisex64 • SQL Server 2008 R2 Enterprise x64【Ver.10.50.1600.1】

  18. DB Server升級程序 • 新主機直接安裝最新的Windows2008 Server與SQL Server 2008 R2 64位元。 • 把舊DB主機的環境(Win與SQL)複製到一台Temp虛擬主機, 在Temp上把SQL2000升級到 2008 R2, 再把升級後的相關系統資料庫移植到新DB主機。 舊DB Server Temp DB Server 新DB Server 3. 檢查相容 5. 設DTS 問題3 4. 移植DB檔 2008 R2 x86 2008 R2 x64 2. 升級2008 R2 1. 複製整個環境 2000 x86 2000 x86 問題2 問題1

  19. 步驟1.複製整個環境 • 依照舊DB Server的Windows跟SQL環境(含升級SP4), 另外建立一台虛擬的TempDBServer 。 • 將舊教務DB Server的master、model、msdb系統資料庫及您的應用系統DB(即『教務系統DB』)共四個Database備份並還原到此Temp主機。 • 在舊DB Server設SQL Profiler並儘可能地使用Web系統, 以記錄一些存取動作, 以供步驟3.檢查語法相容性。 舊DB Server Temp DB Server 1. 複製整個環境 2000 x86 2000 x86

  20. 步驟2.升級SQL 2008 R2 • 利用SQL2008安裝光碟中的升級功能, 把Temp主機中的SQLServer 2000升級到 2008 R2。 • 實際經驗, 此階段會遇到一些問題讓升級失敗, 今天這個研討會就是要讓大家不用碰壁! Temp DB Server SQL Server 2008 R2 x86 2. 升級 SQL Server 2000 x86

  21. 步驟3.檢查語法相容性 • 於http://www.scalabilityexperts.com/下載免費的SQL Server Upgrade Assistant 2008重跑步驟1.SQL Profiler所記錄的T-SQL, 檢查是否有語法不相容的錯誤(微軟亦有類似工具), 有則視狀況修改語法。(語法相容性後續會再說明) Temp DB Server 3. 檢查相容 2008 R2 x86

  22. 步驟4.移植DB • 於新DB Server安裝Windows Server 2008 R2及SQLServer 2008 R2。 • 將之前透過Temp主機升級過的master、model、msdb三個系統DB備份並還原到新主機, 細節見參考資料與連結。 • 將舊DB Server的應用系統.mdf與.ldf檔直接附加成新資料庫 (須短暫停機, 故請先通告或在離峰時間處理)。 舊DB Server Temp DB Server 新DB Server 4. 移植系統DB 2008 R2 x86 2008 R2 x64 4. 移植應用系統DB 2000 x86

  23. 步驟5. 設DTS(Data Transformation Services) • SQL Server 2000的『資料轉換服務』DTS (Data Transformation Services), 到了2008變成SSIS, 所以接著要讓SQL 2008 R2也能執行DTS, 否則下圖中這一堆DTS設定都要重寫。

  24. 步驟5. 設DTS(續) • 於SQL 2008 R2 設DTS的關鍵初始步驟： • 執行SQL 2008 R2安裝光碟, 在左側選擇『安裝』, 在右邊選『新的SQL Server獨立安裝或將功能加入到現有安裝』 • 安裝畫面來到『安裝類型』時,選擇『將功能加入到現有的SQL Server 2008』執行個體 • 安裝『Integration Services』及『用戶端工具回溯相容性』兩項功能, 如有編輯SSIS需要, 可另外選擇『Bussiness Intelligence Development Studio』 • 安裝SQLServer2005_BC.msi(有分32、64bit)及SQLServer2005_DTS.msi。 • 將SEMSFC.DLL、SQLGUI.DLL 和 SQLSVC.DLL複製到相對應的地方 • 因篇幅所限, 請自行參考本主題最後的參考資料與連結

  25. 遇到的問題升級錯誤 • 升級時發生如圖之數個錯誤：

  26. 遇到的問題升級錯誤(續) • 升級時發生如圖之數個錯誤： • 下圖的『管理工具』雖回報『成功』, 但後續使用仍是不正常。

  27. 升級錯誤原因與解決方法 • 原因1. • 還原舊教務DB Server的master、model、msdb及『教務系統』四個Database到Temp主機之後, 還需檢查其db owner是否為sa, 否則在升級時會發生錯誤。可在database的屬性檢查owner(擁有者)相關訊息, 如owner有錯可執行EXEC sp_changedbowner'sa' --去變更擁有者

  28. 升級錯誤原因與解決方法(續) • 原因2. • 步驟1.用虛擬主機複製整個環境時, 需注意虛擬主機的CPU數量必須跟原本舊主機相同, 如果數量不同, 升級時可能會發生錯誤。 • 原因3. • 若只要升級到SQL 2008(非R2版本), 則需先移除MSXML 6 Service Pack2 (KB973683), 否則在升級時將會發生『服務‘SQLBrowser’的啟動要求失敗』之錯誤訊息。若是要升級R2則不會有此問題。

  29. 2000、2005、2008語法相容性問題 • 較會遇到的問題語法如下： • 不支援外部聯結運算子 *=和 =*之語法, 需改為Left Outer Join 或 Right Outer Join。 • 如用了以下SQL2000系統物件, 則需進行改寫：sysindexes、sysobjects、sysfiles、sysperfinfo要改成SQL2008用的:sys.indexes、sys.objects、sys.database_files、sys.dm_os_performance_counters。 • DTS無法100%相容於SSIS：若DTS中有使用ActiveX Script做轉換, 則於SSIS中需再做修正。 • (其餘還有幾十項, 但一般較不會遇到, 可參考DATABASE 相容性層級)

  30. 升級建議 • 利用工具挑選執行效率較優的硬體設備。 • 在舊資料庫用Profiler搜集各種T-SQL再拿到新資料庫確實測試其相容性。 • 一定要做新舊平台的平行測試。

  31. 相關參考資料 • 微軟-如何使用 SQL Server 中的卸離和附加功能將 SQL Server 資料庫移到新位置 • 微軟-移動系統資料庫 • 微軟-如何在執行 SQL Server 的電腦之間移動資料庫 • 微軟-安裝 Data Transformation Services 封裝的支援 • 微軟-Microsoft SQL Server 2005 Feature Pack - 2007 年 2 月 • 如何移動系統資料庫 • SQL 2000 DTS如何在SQL 2008上繼續運作？

  32. 主題二：SQL Server效能調校 • 實際選課過程 • 如何找關鍵問題? • 如何進行效能調校? • 進行壓力測試與再調整 大綱：

  33. 實際選課過程-代碼法 • 選課方式一：『代碼法』, 使用代碼輸入直接加選。 • 鼓勵學生事先挑好想上的課, 把課程簡碼記下來, 選課時就不用再花時間查詢了。

  34. 實際選課過程-查詢法 • 選課方式二：『查詢法』, 讓學生用條件查詢出清單, 勾選後送出。 • 為了效能, 圖中紅色欄位為必選條件。 • 每頁只能勾6筆, 避免學生『先搶再退』之動作, 造成主機負擔。

  35. 實際選課過程-方式比較 • 代碼法： • 優點：減少發生在搶課時的SELECT語法, 主機效能受益, 學生自己也受益(不用查詢, 搶課速度快)。 • 缺點：學生要事先做功課。 • 查詢法： • 優點：假如挑好的課都沒選上; 或學生沒事先做功課, 還是需要用到查詢功能。 • 缺點：讓主機額外增加 SELECT 動作。

  36. 如何找關鍵問題?一些有用的效能調校工具與指令如何找關鍵問題?一些有用的效能調校工具與指令 • SQLServer Profiler • SQL Server 的報表 • 微軟最實用的監測指令 • SQLDiag、SQL Trace • SQLServer 查詢編輯器 • SQLServer『Database Engine Tuning Advisor』 • SQL Server 的活動監視器 • Windows 效能監視器 • 用來追查問題 • 用來協助調校 • 用來監視狀況

  37. 1. 善用SQLServer Profiler • 通常只要追蹤兩個事件, 即可擷取大部分程式造成的T-SQL動作 (含語法與耗時Duration)： • 預存程序的『RPC:Completed』 • T-SQL的『SQL:BatchCompleted』 註: Duration的單位是千分之一秒(ms)

  38. 2. 看懂報表-找出最耗CPU的T-SQL 問題

  39. 3. 微軟-最實用的監測指令 • 微軟Best Practices效能監測T-SQL指令, 其中以跟CPU有關的05最重要(下頁續) • 參考 SQL Server Best Practices Toolbox

  40. 監測指令05-找出最耗CPU的T-SQL

  41. 監測指令05_2-找出較慢的T-SQL語法 問題

  42. 監測指令-會遇到的問題1 • 無法執行監測指令?! • 原因：相容性層級不夠!註: 可參考DATABASE 相容性層級

  43. 監測指令-會遇到的問題1-解法 • 解法一：由model、msdb、tempdb與新加的空的DB這四類的資料庫『新增查詢』即可! • 此法可讓監測指令執行, 但SQL報表仍出不來 • 解法二：至少將相容性層級提高到90以上(即2005)。 • 此法可讓監測指令與SQL報表均能正常執行

  44. 監測指令-會遇到的問題2 • 為何我已知最忙的幾個T-SQL不在前幾名? • 通常, 這表示『你的最忙T-SQL還有改善空間！』,這關係到執行計畫(Plan)重複使用的議題,不在前幾名最常見的原因是直接串T-SQL把WHERE條件都寫好了, 所以不同條件(如學號、科目…)的 T-SQL 均視為不同。 • 解法: • 建議解法1. 就寫成預存程序。 • 解法2.用 Parameter, 即『xxID=@xxID 』或『xxID=? 』 。

  45. 5. 善用查詢編輯器的效能工具 • SQLServer查詢編輯器是開發T-SQL者最實用的效能測試工具, 可顯示執行計畫(Plan); 並可利用『用戶端統計資料』來看不同寫法耗時如何。

  46. 善用查詢編輯器-用『用戶端統計資料』調效能善用查詢編輯器-用『用戶端統計資料』調效能

  47. 善用查詢編輯器-看毫秒耗時的語法 DECLARE @i DATETIME2(7)=SYSDATETIME() DECLARE @j DATETIME2(7)=SYSDATETIME() SELECT DATEDIFF(MS, @i, @j) AS ‘耗時(ms)’--這T-SQL也可用於程式中

  48. 6. 善用Database Engine Tuning Advisor • 此工具可分析資料庫或T-SQL，並提供索引建議

  49. 7. 看懂活動監視器 • 選課開始前, 開啟活動監視器監看DB主機現況

  50. 如何進行效能調校?一些提高效能的觀念或方法如何進行效能調校?一些提高效能的觀念或方法 • 索引與SQL2005進階索引用法 • 較佳的T-SQL寫法 • 利用預存程序提高執行效率 • 避免鎖定並加快查詢 • 調整記憶體與硬碟回寫頻率

More Related