Transcript SQL%20Server升級與效..

報告人：李文豪
世新大學電算中心
系統組組長
2010. 11. 19
• 選課案例說明
• 新舊架構與效能比較
• 升級程序與問題處理
• 選課第一階段僅為學生選課意願收集, 不搶課。
• 選課第二階段為搶課制, 各班名額有限。
• 各年級安排在不同天選課, 早上9:30開放選課。
• 大二跟大三第二階段選課為最壅塞的兩天, 早上
9:30一開放, 有上千人次不斷湧入且不斷進行查
詢與新增作業, 尤其9:30有搶登入所造成的瞬間
尖峰, 若無法消化, 後續全會塞車。
• 本校今年第一階段仍用舊架構, 第二階段開始改
用新架構。
註: 世新實際選課分為三階段, 但為避免混淆, 僅以兩階段簡單區分搶課與否。
由學生依學號自行選擇
Web AP Server
Server
• 三台Web Server處理來自學生的Request, 有些
人會看到錯誤訊息, 原因是Web Server 太忙了!
而且學生愛選第一台Web, 讓壓力無法分散。
(此時SQL Server不是瓶頸, CPU約在80%)
• 雖然知道瓶頸在Web Server, 但為避免Web
Server通暢後又換成DB Server出現瓶頸, 本校研
擬並執行『拔刺計畫』, 全面檢視可改善之處,
多管齊下, 以最少的預算發揮最高效益, 杜絕塞
車問題!
統一透過負載平衡
Server選擇
Web AP Server
Server
8/18大二選課各小時登入平均耗時(秒)
(舊DB Server + 3台Web Server)
秒
4
3
2
1
0
0
1
2
3
4
5
6
7
8
9
10 11
12
13
14
註: 登入耗時是登入時在主機端的實際耗時, 其間還包含五六
次的DB存取動作 (判斷身分、抓權限…), 由於每個人登入的存
取程序相近, 可用來判斷整個主機端(Web+DB)的反應時間。
15
16
17
18
19
20
21
22
23
9/13大二搶課各小時登入平均耗時(秒)
(新DB Server + 13台Web Server )
秒
4
3
2
1
0
0
1
2
3
4
5
6
7
8
9
10 11
12
13
14
15
16
17
18
19
20
21
22
23
8/18大二選課各小時登入人次
(舊DB Server + 3台Web Server)
登入人次
7000
6000
5000
4000
3000
2000
1000
0
0
1
2
3
4
5
6
7
8
9
10 11
12
13
14
15
16
17
18
19
20
21
22
23
9/13大二搶課各小時登入人次
(新DB Server + 13台Web Server )
登入人次
7000
6000
5000
4000
3000
2000
1000
0
0
1
2
3
4
5
6
7
8
9
10 11
12
13
14
15
16
17
18
19
20
21
22
23
• 大二搶課在上午9:30的瞬間達到高峰, 讓DB主機
CPU忙碌達到80%, 半小時後降至45%左右且保持穩
定, DB主機消化工作游刃有餘, 各Web主機也都服
務正常, 前後均無瓶頸, 選課不再塞車!
1. 用虛擬技術增加大量的Web AP Server (前一場次
已報告過)。
2. 寫一支精簡短小的程式, 擔任負載平衡主機。
3. 將SQL Server從2000升級到2008 R2; 硬體也換新。
• Global.asax.cs
– 在每人第一次登入時的Session_Start事件把人數+1
• Default.aspx.cs
• 硬體
– IBM X366
– Inter Xeon MP 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)】
• 硬體
– IBM X3650 M2
– Inter Xeon E5640 2.66GHz * 2
(Benchmark: 5482 * 2 = 10964)(提升約6倍)
– 16GB RAM
– RAID 5(6顆300G硬碟)
• 軟體
– Windows Server 2008 R2 Enterprise x64
– SQL Server 2008 R2 Enterprise x64
【Ver.10.50.1600.1】
• 新主機直接安裝最新的Windows 2008 Server與
SQL Server 2008 R2 64位元。
• 把舊DB主機的環境(Win與SQL)複製到一台Temp
虛擬主機, 在Temp上把SQL 2000升級到 2008 R2,
再把升級後的相關系統資料庫移植到新DB主機。
舊DB Server
Temp DB Server
新DB Server
3. 檢查相容
5. 設DTS
4. 移植DB檔
問題3
2. 升級2008 R2
1. 複製整個環境
問題1
問題2
• 依照舊DB Server的Windows跟SQL環境(含升級SP4), 另
外建立一台虛擬的Temp DB Server 。
• 將舊教務DB Server的master、model、msdb系統資料
庫及您的應用系統DB(即『教務系統DB』)共四個
Database備份並還原到此Temp主機。
• 在舊DB Server設SQL Profiler並儘可能地使用Web系統,
以記錄一些存取動作, 以供步驟3.檢查語法相容性。
舊DB Server
Temp DB Server
1. 複製整個環境
• 利用SQL 2008安裝光碟中的升級功能, 把Temp
主機中的SQL Server 2000升級到 2008 R2。
– 實際經驗, 此階段會遇到一些問題讓升級失敗, 今
天這個研討會就是要讓大家不用碰壁!
Temp DB Server
2. 升級
• 於http://www.scalabilityexperts.com/
下載免費的SQL Server Upgrade Assistant 2008
重跑步驟1. SQL Profiler所記錄的T-SQL, 檢查是
否有語法不相容的錯誤 (微軟亦有類似工具), 有
則視狀況修改語法。(語法相容性後續會再說明)
Temp DB Server
3. 檢查相容
• 於新DB Server安裝Windows Server 2008 R2及SQL Server
2008 R2。
• 將之前透過Temp主機升級過的master、model、msdb三
個系統DB備份並還原到新主機, 細節見參考資料與連結。
• 將舊DB Server的應用系統.mdf與.ldf檔直接附加成新資料
庫 (須短暫停機, 故請先通告或在離峰時間處理)。
舊DB Server
Temp DB Server
新DB Server
4. 移植系統DB
4. 移植應用系統DB
• SQL Server 2000的『資料轉換服務』DTS (Data
Transformation Services), 到了2008變成SSIS,
所以接著要讓SQL 2008 R2也能執行DTS, 否則下
圖中這一堆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複製到相對應的地
方
– 因篇幅所限, 請自行參考本主題最後的參考資料與連結
• 升級時發生如圖之數個錯誤：
• 升級時發生如圖之數個錯誤：
– 下圖的『管理工具』雖回報『成功』, 但後續使用
仍是不正常。
• 原因1.
– 還原舊教務DB Server的master、model、msdb及
『教務系統』四個Database到Temp主機之後, 還需
檢查其db owner是否為sa , 否則在升級時會發生
錯誤。可在database的屬性檢查owner(擁有者)相
關訊息, 如owner有錯可執行
EXEC sp_changedbowner 'sa' --去變更擁有者
• 原因2.
– 步驟1.用虛擬主機複製整個環境時, 需注意虛擬主
機的CPU數量必須跟原本舊主機相同, 如果數量不
同, 升級時可能會發生錯誤。
• 原因3.
– 若只要升級到SQL 2008(非R2版本), 則需先移除
MSXML 6 Service Pack2 (KB973683), 否則在升級時
將會發生『服務‘SQLBrowser’的啟動要求失敗』之
錯誤訊息。若是要升級R2則不會有此問題。
• 較會遇到的問題語法如下：
– 不支援外部聯結運算子 *= 和 =* 之語法, 需改為Left
Outer Join 或 Right Outer Join。
– 如用了以下SQL2000系統物件, 則需進行改寫：
sysindexes、sysobjects、sysfiles、sysperfinfo
要改成SQL2008用的:
sys.indexes、sys.objects、sys.database_files、
sys.dm_os_perform ance_counters 。
– DTS無法100%相容於SSIS：若DTS中有使用ActiveX
Script做轉換, 則於SSIS中需再做修正。
– (其餘還有幾十項, 但一般較不會遇到, 可參考
DATABASE 相容性層級)
• 利用工具挑選執行效率較優的硬體設備。
• 在舊資料庫用Profiler搜集各種T-SQL再拿到新資
料庫確實測試其相容性。
• 一定要做新舊平台的平行測試。
• 微軟-如何使用 SQL Server 中的卸離和附加功能
將 SQL Server 資料庫移到新位置
• 微軟-移動系統資料庫
• 微軟-如何在執行 SQL Server 的電腦之間移動資
料庫
• 微軟-安裝 Data Transformation Services 封裝
的支援
• 微軟-Microsoft SQL Server 2005 Feature Pack 2007 年 2 月
• 如何移動系統資料庫
• SQL 2000 DTS如何在SQL 2008上繼續運作？
•
•
•
•
實際選課過程
如何找關鍵問題?
如何進行效能調校?
進行壓力測試與再調整
• 選課方式一：『代碼法』, 使用代碼輸入直接
加選。
– 鼓勵學生事先挑好想上的課, 把課程簡碼記下來,
選課時就不用再花時間查詢了。
• 選課方式二：『查詢法』, 讓學生用條件查詢出
清單, 勾選後送出。
– 為了效能, 圖中紅色欄位為必選條件。
– 每頁只能勾6筆, 避免學生『先搶再退』之動作, 造
成主機負擔。
• 代碼法：
– 優點：減少發生在搶課時的SELECT語法, 主機效能
受益, 學生自己也受益(不用查詢, 搶課速度快)。
– 缺點：學生要事先做功課。
• 查詢法：
– 優點：假如挑好的課都沒選上; 或學生沒事先做功
課, 還是需要用到查詢功能。
– 缺點：讓主機額外增加 SELECT 動作。
1.
2.
3.
4.
SQL Server Profiler
SQL Server 的報表
微軟最實用的監測指令
SQLDiag、SQL Trace
5. SQL Server 查詢編輯器
6. SQL Server『Database
Engine Tuning Advisor』
7. SQL Server 的活動監視器
8. Windows 效能監視器
用來追查問題
用來協助調校
用來監視狀況
• 通常只要追蹤兩個事件, 即可擷取大部分程式造
成的T-SQL動作 (含語法與耗時Duration)：
– 預存程序的『RPC:Completed』
– T-SQL的『SQL:BatchCompleted』
註: Duration的單位是千分之一秒(ms)
問題
• 微軟Best Practices效能監測T-SQL指令, 其中以
跟CPU有關的05最重要(下頁續)
• 參考 SQL Server Best Practices Toolbox
問題
• 無法執行監測指令?!
• 原因：相容性層級不夠!
註: 可參考DATABASE 相容性層級
• 解法一：由model、msdb、tempdb與新加的空的
DB這四類的資料庫『新增查詢』即可!
– 此法可讓監測指令執行, 但SQL報表仍出不來
• 解法二：至少將相容性層
級提高到90以上(即2005)。
– 此法可讓監測指令與SQL報
表均能正常執行
• 為何我已知最忙的幾個T-SQL不在前幾名?
– 通常, 這表示『你的最忙T-SQL還有改善空間！』,
這關係到執行計畫(Plan)重複使用的議題,
不在前幾名最常見的原因是直接串T-SQL把WHERE
條件都寫好了, 所以不同條件(如學號、科目…)的
T-SQL 均視為不同。
• 解法:
– 建議解法1. 就寫成預存程序。
– 解法2.用 Parameter , 即『xxID=@xxID 』或
『xxID=? 』 。
• SQL Server查詢編輯器是開發T-SQL者最實用的
效能測試工具, 可顯示執行計畫(Plan); 並可利用
『用戶端統計資料』來看不同寫法耗時如何。
DECLARE @i DATETIME2(7)=SYSDATETIME()
DECLARE @j DATETIME2(7)=SYSDATETIME()
SELECT DATEDIFF(MS, @i, @j) AS ‘耗時(ms)’ --這T-SQL也可用於程式中
• 此工具可分析資料庫或T-SQL，並提供索引建議
• 選課開始前, 開啟活動監視器監看DB主機現況
1. 索引與SQL2005進階索引用法
2. 較佳的T-SQL寫法
3. 利用預存程序提高執行效率
4. 避免鎖定並加快查詢
5. 調整記憶體與硬碟回寫頻率
• 索引分為
– 叢集(CLUSTERED)索引：每個Table只有1個叢集索
引, 常跟主鍵相同
– 非叢集(NONCLUSTERED)索引：最多249個
• 可透過ALTER INDEX ALL重建某Table所有索引
• 叢集索引的資料分頁就
是資料表所有欄位的資
料, 排列方式就依照叢集
索引的順序來排。
• 每個資料分頁大小是8K
Bytes 。
• 叢集索引是越小越好 (且
要『唯一』), 假如您正
在SD階段, 建議用單一
的int…等數字類欄位放
流水號當叢集索引。
• 非叢集索引最後仍要透過叢集索引去取得其他欄
位的資料。(所以叢集索引越小對兩者均有利)
• 若非叢集索引的分頁已包含所要抓取(SELECT)的
所有欄位, 則有時反而會更快, 因為可省去再去
抓資料分頁的時間。
• 非叢集索引放多一點欄位雖然會快, 但卻會
讓非子葉層索引資料變大;
且新增修改時會花時間排序, 弄不好反而變慢。
• 可利用SQL新功能『包含的資料行』解決以上問
題, 只把WHERE中用到的欄位(或部分欄位,視常
用狀況)放在索引, 其他放『包含的資料行』, 會
一起放到子葉層加快抓取, 且無上述缺點。
• 儘量不要用運算欄位(computed column)或加了
函數的欄位當WHERE條件, 如:
– SELECT myID, myName, myType FROM tTable
WHERE ISNULL(myType, ‘’)=‘’
• 較佳用法:
– 不要讓該欄位出現Null值(最好對該欄位設預設值且
不可Null), 然後改用:
SELECT myID, myName, myType FROM tTable
WHERE myType=‘’
• 運算欄位錯誤用法最常遇到把多個欄位串起來,
如:
– SELECT ID1, ID2 FROM tTable WHERE
ID1 + ‘_’ + ID2 = ‘A_01’
• 較佳用法:
– SELECT ID1, ID2 FROM tTable WHERE
ID1 = ‘A’ AND ID2 = ‘01’
• 儘量不要用負向查詢, 像『NOT IN』『<>』
『NOT LIKE』…, 如:
– SELECT ID1, ID2 FROM tTable WHERE
ID1 <>2
• 較佳用法:
– SELECT ID1, ID2 FROM tTable WHERE
(ID1 >2 OR ID1 <2)
• 有些查詢可用JOIN或用subquery (子查詢), 結果
相同但效能可能不同, 而且兩者不一定誰快。
– 若確定將是較忙的T-SQL, 建議兩種寫法都寫出來,
再用SQL查詢去比較兩者所花的時間。
• UNION時, 若確定不會重複,
就用『UNION ALL』, 不要只用『UNION』, 因
為『UNION』還須多一個動作去看資料有無重複
• 將較忙的T-SQL寫成預存程序, 如此有利於SQL選
擇較優的執行計畫, 可提高執行效率。
– 就算用了View或複雜的SELECT, 也可把最後產出結
果的那一句 SELECT 寫進預存程序, 再呼叫預存程
序。
– 注意: 若預存程序中使用了暫存資料表; 或者預存程
序語法長度超過8KB, 則其執行計畫仍須每次重新編
譯。
• 假如JOIN時確定相關資料表不會被異動(像參數檔),
或user看到異動前後的資料也沒差, 為了效能跟避免
鎖死問題, 可以在相關資料表後面加上『 WITH
(NOLOCK) 』,
如:
– SELECT tA.myID, tB.myName FROM tA
WITH (NOLOCK) JOIN tB WITH (NOLOCK) ON
tA.myID = tB.myID
• 若某T-SQL所有的資料表都不鎖, 可使用:
– SET TRANSACTION ISOLATION READ UNCOMMITTED
• 注意: 若user看到異動前後資料有差的(像搶課時, 某
個課目前已選人數的COUNT; 帳務)就不能加。
• 若您的系統最忙的是新增資料(如留言) 且叢集
索引為流水號, 可適度把復原間隔調成1~10分鐘
1. 如何利用VS2008進行壓力測試
2. 利用壓力測試報告與先前提到的工具, 再檢視
可調整的部分
1. 利用免費網頁封包擷取軟體Fiddler2錄製壓力測
試時所要重覆執行的腳本。(VS2008也有該功能)
2. 將腳本匯出成webtest。
3. 用VS2008建立一個新的【測試文件】專案。
4. 將步驟2的webtest加入專案。
5. 在C:\Program Files (x86)\Microsoft Visual
Studio 9.0\Common7\IDE\有一
loadtestresultsrepository.sql, 請在SQL Express
或其它SQL執行, 程式會自動建立一個LoadTest
資料庫(如OS是32Bit, 其位置在C:\Program
Files\Microsoft Visual Studio
9.0\Common7\IDE\)
6. 設定【管理Test Controller】的資料庫位置, 將
位置指到步驟所建立的LoadTest。
7. 新增一個【負載測試】, 將步驟4的webtest加到
【情節】中。
8. 在【負載測試】(副檔名:loadtest)選擇【執行測
試】, 即可開始進行壓力測試。
9. 待測試完畢後, 系統會立即帶出結果。事後也可
從【開啟和管理結果】查看歷史記錄。
• 了解效能調校的觀念與新技術: 在規劃、開發資
料庫系統時, 能發現問題並據以解決。
• 善用檢測工具: 壓力測試時可善用先前所述的工
具蒐集資料, 進行問題發掘與改善。
• 落實壓力測試計畫: 使用VS2008的壓力測試工具
找出問題點, 再進行評估與效能調校。