SQL Server 效能調校

Transcription

1 許致學

2 本場次內容 效能調校與規劃 SQL Server 2008 提升效能的新功能 SQL Server 2008 效能調校工具

3 效能不佳之影響 無法滿足使用者需求企業生產力 客戶忠誠度 資訊人員績效不彰頭痛醫頭 腳痛醫腳病急亂投醫, 硬體升級不一定可以獲得改善效能不佳的瓶頸效應會遞延累加更形嚴重硬體資源不足, 過於忙碌, 只能將待處理的要求, 分配更少的資源進行處理伺服器不穩定硬體資源不足, 過於忙碌, 甚至影響硬體壽命

4 效能調校的範圍 找出效能不佳的原因 系統硬體資源 與效能相關的設定 應用程式 實作效能改善方案 找出效能最大瓶頸, 研究是否有方法可以改善 伺服器硬體 架構 資料庫 應用程式 避免效能不佳導致的系統不穩定 適當的規劃達成更佳的效能表現 系統 SQL Server 應用程式

5 效能調校的挑戰 系統硬體資源處理器 記憶體 磁碟 網路單一主機硬體資源升級 vs. 多部主機分擔處理負荷 與效能相關的設定資料庫大小 資料表大小 使用者人數資料庫設計 檔案規劃 索引 資料分割 資料壓縮 tempdb 應用程式應用程式架構 語法 交易與鎖定集中式處理 vs. 分散式處理集合處理 vs. 逐筆處理如何達到影響程度最小, 效能有效提升

6 效能調校與規劃 加速資料庫處理效能增加伺服器處理效能多部伺服器架構

7 加速資料庫處理效能 資料表設計 資料型態 欄位數量 正規化程度 索引設計 叢集索引 vs. 非叢集索引 計算型欄位 + 索引 索引檢視表 XML 索引 全文檢索 資料庫物件 預存程序 使用者自訂函數 CLR 組件 程式設計 資料指標 vs. 資料集 TRUNCATE TABLE BULK INSERT 查詢語法 交易處理 MARS 非同步處理

8 資料庫設計之建議 資料表設計 資料欄位型態與長度要適切 主鍵 ( 叢集索引 ) 與外鍵 正規化與資料表的切割和合併 避免前端程式直接存取基礎資料表 儘量透過預存程序 檢視表以及使用者自訂函數來存取資料, 不要以程式直接存取資料表 效率較佳, 更有彈性的安全設定, 提供修改資料表架構的空間 分開線上交易與線上分析的存取

9 實際案例 : 適當的資料類型與長度 資料類型 資料類型 ( 調整後 ) 差異 欄位數 節省 INT TINYINT 3 Bytes 2 6 Bytes INT SMALLINT 2 Bytes 4 8 Bytes DATETIME SMALLDATETIME 4 Bytes 3 12 Bytes NCHAR(10) CHAR(10) 10 Bytes 1 10 Bytes VARCHAR(x) CHAR(x) 2 Bytes 7 14 Bytes 每筆記錄共節省 :50 Bytes

10 SQL Server 的索引架構 書籍 SQL Server 資料庫儲存方式 內容 目錄 名詞解釋 資料表 (Table) 叢集索引 (Clustered Index) 非叢集索引 (Nonclustered Index) 1. 每個資料表只能有 1 個叢集索引 2. 每個資料表最多可以有 249 個非叢集索引 3. 非叢集索引對應至 Row ID 或 Clustering Key 資料頁 (Data Pages) 索引頁 + 資料頁 索引頁 (Index Pages) 4. 每個索引最多可以包括 16 個欄位, 不得超過 900 Bytes 搭配內含資料行則不在此限

11 叢集索引與非叢集索引 B-Tree Non-Leaf Leaf 叢集索引 索引頁 資料頁 非叢集索引索引頁索引頁 8K Bytes 8K Bytes Non-Leaf Leaf Root Balanced Tree 1. 叢集索引 ( 目錄 ) 會影響資料表實際儲存資料時的排序規則, 適用於經常 ORDER BY GROUP BY 等查詢 2. 非叢集索引 ( 名詞解釋 ) 不會影響資料表實際儲存資料時的排序規則, 適用於查詢資料表部分欄位時

12 索引建立之注意事項 索引建立前將資料庫之復原模式選項變更為 BULK_LOGGED 索引建立的過程不會逐一寫入交易記錄檔可大幅減少磁碟 I/O, 建置時間約可省 40% 先建立叢集索引再建立非叢集索引否則非叢集索引還會再由 Row ID 對應至 Clustering Key 索引建立時機若系統已很忙碌, 且要建立索引的資料表資料量龐大不要馬上建立索引, 避免增加系統的負荷排程於離峰時段建立索引的作業索引建立後將資料庫之復原模式選項變更為 FULL 確認建置的索引發揮足夠的效能改善未使用到的 使用率低的索引, 考慮予以調整或刪除

13 索引建立之準則 考慮建立索引之欄位主要鍵與外部鍵欄位經常需要搜尋某個範圍的值欄位經常需要排序欄位經常需要群組以進行匯總考慮不要建立索引之欄位在查詢中很少用到的欄位欄位的值重複性很高 text, ntext, 或 image 等資料類型

14 實際案例 : 只需保留三個月的資料 以 TRUNCATE TABLE 取代 DELETE WHERE

15 去除 WHERE 子句中不必要的判斷式 調整前 :95.74% 效能相差 22.5 倍 調整前 : 4.26%

16 WHERE 子句中盡量不要用否定條件 調整前 :97.36% 調整前 : 2.64% 效能相差 36.9 倍

17 增加伺服器處理效能 64 位元系統充足的記憶體磁碟規劃磁碟陣列最佳化 Tempdb 之規劃交易記錄檔與資料檔儲存於不同磁碟組多個檔案群組搭配多個資料檔案分割資料表 分割索引 資料表壓縮

18 實際案例 : 硬體升級 + 程式修改 超過 70 個資料庫在同一部主機上某個查詢導致處理器滿載 10 分鐘以上, 嚴重影響所有系統效能硬體升級前處理器 :Itanium * 8 + 記憶體 :128 GB 硬體升級後處理器 :Itanium * 16 + 記憶體 :256 GB 硬體升級前 硬體升級後 程式修改前 程式修改後 查詢 ID 數量 :100 查詢時間 :275 秒 查詢 ID 數量 :100 查詢時間 :49 秒 查詢 ID 數量 :275 查詢時間 :145 秒 查詢 ID 數量 :275 查詢時間 :6 秒

19 資料分割 企業版 支援資料表與索引的分割儲存至不同的檔案群組 將單一資料表 / 索引區分成不同儲存體使用 Range 分割技術進行資料分組歷史資料與線上資料管理更容易提升資料庫備份與還原的效率提升資料刪除與大量資料載入的效率減少歷史資料索引離散與重整的狀況簡化應用程式開發與資料庫管理

20 Tempdb 之規劃 Tempdb 之用途 暫存資料表 子查詢 HASH JOIN ORDER BY GROUP BY SELECT DISTINCT 快照式交易隔離等級 線上索引維護作業 大量使用 Tempdb 時之設定 確保 Tempdb 有足夠的資料檔大小 將 Tempdb 的資料檔指定至不同的磁碟組 如果是 SQL Server 有多個 CPU 時, 建議 Tempdb 的資料檔個數與 CPU 核心數相同 MS KB328551

21 資料庫異動與交易記錄檔 1 藉由應用程式異動資料 3 將異動資料寫入交易記錄檔磁碟 用戶端 緩衝區快取 交易記錄檔磁碟 2 SQL Server 將資料讀出並寫入緩衝區快取, 然後修改 隨機存取 資料檔磁碟 4 循序存取 當 Checkpoint 發生, 將已完成的交易寫入資料庫

22 規劃檔案群組提升效能 將經常要查詢或更新的資料表, 指定存放於不同磁碟組的檔案群組 將非叢集索引, 指定存放於不同磁碟組的檔案群組 將常用的現有資料與歷史資料分割儲存至不同的資料表, 並指定存放於不同磁碟組的檔案群組 或考慮採用分割資料表與資料壓縮

23 將資料庫分為多個檔案.mdf 單一資料檔案只能以單一執行緒執行檔案 I/O, 無法發揮硬碟系統的效能 CPU DISK 將資料庫每個檔案群組分為多個資料檔案, 可充分發揮多 CPU 多核心同時執行檔案 I/O 的多執行緒特性, 使硬碟系統發揮更好的效能 ( 類似 RAID 0).ndf.ndf.ndf.ndf.mdf.ndf.ndf.ndf 注意事項 : 多個資料檔案必須建立為同樣的初始大小, 同樣的成長大小, 否則資料檔案會以檔案大小的比例寫入, 造成磁碟 I/O 不平均

24 磁碟配置建議 ( 一 ) RAID 1 RAID 5 作業系統 SQL Server 檔案群組 (Primary) 檔案 1 檔案 N Tempdb 交易記錄檔 檔案群組 (Index) 檔案 1 檔案 N

25 磁碟配置建議 ( 二 ) RAID 1 RAID 10 作業系統 SQL Server Tempdb 交易記錄檔 RAID 10 檔案群組 (Primary) 檔案 1 檔案 N 檔案群組 (Data1) 檔案 1 檔案 N 檔案群組 (Data2) 檔案 1 檔案 N 檔案群組 (Data3) 檔案 1 檔案 N 檔案群組 (Index) 檔案 1 檔案 N

26 實際案例 : 一個檔案群組搭配多個資料檔案

27 多部伺服器架構 分散式查詢分割檢視表複寫可擴充共用資料庫

28 分散式查詢 建立連結伺服器資料庫物件完整名稱規則 SQL Server: LinkedServer.DB.Schema.Object ORACLE: ORA..Schema.Object Microsoft Access: MsAccess...Object 連結伺服器 SQL Server

29 分割檢視表 企業版 ServerHQ Server1 Server2 Server3 Member Table Member Table Member Table

30 分割檢視表 企業版 成員資料表 CHECK 條件 索引 定序 計算型欄位不可有索引 相同的主鍵定義與 ANSI PADDING 選項設定 為每個執行個體建立連結伺服器 sp_addlinkedserver 建立分割檢視表 可搭配 INSTEAD OF 觸發程序

31 交易式複寫線上交易 + 報表 應用程式伺服器 資料異動 資料查詢 資料複寫 主要伺服器 ( 發行者 ) 報表伺服器 ( 訂閱者 ) 只複寫需要的資料 可採用不同的索引

32 點對點交易式複寫分散查詢 + 容錯 企業版 台北 Log Reader Agent Distribution Agent 上海 Log Reader Agent Distribution Agent 紐約 Log Reader Agent Distribution Agent

33 點對點交易式複寫 SQL Server 2008 增強功能 新的複寫拓樸檢視器加入新的結點無須先將既有的複寫離線資料衝突偵測機制

34 實際案例 : 網路服務公司會員人數急速成長, 約每四個月就會成長 200% 70 萬 130 萬 240 萬會員人數 多部 Web 伺服器主機, 以網路負載平衡設備分散負荷 1 部資料庫伺服器主機, 須應付大量的及時性統計資訊 只得不斷更換處理器更多更強的主機來支撐 (CPU > 90%) Web 伺服器 資料庫伺服器

35 實際案例 : 交易式複寫 ( 向外擴充 ) 增加 3 部 x86 主機, 訂閱 IA64 主機之複寫發行集 資料異動 資料查詢 資料複寫 Web 伺服器 IA64 主機 CPU 從 > 90% 降至 < 40% ( 發行者 ) x86 主機 應付大量的及時性統計資訊 ( 訂閱者 )

36 可擴充共用資料庫 企業版 報表資料量龐大資料倉儲系統 每部主機有各自獨立的處理器 記憶體和 Tempdb 共用的唯讀檔案必須存放在 SAN 儲存設備, 且共用磁碟區必須是唯讀 省下儲存設備費用 維護費用與電費

37 提升效能的新功能 1. 資料表壓縮 2. 備份壓縮 3. 資源管理員 4. 一次可新增多筆資料 5. 常數資料表 6. 更優越的儲存體結構 7. 可擴充共用資料庫

38 資料表壓縮 企業版 通常較適於線上交易 2% - 5% CPU 20% 壓縮比 Row Page 通常較適於資料倉儲 10% - 15% CPU 60% - 70% 壓縮比 Compression white paper Data Warehousing white paper

39 備份壓縮 6 企業版 未壓縮 備份壓縮 1 0 備份大小備份時間還原時間 Backup Compression

40 資源管理員 Backup Admin Tasks SQL Server OLTP Activity High Executive Reports Ad-hoc Reports Admin Workload OLTP Workload Report Workload 企業版 可依條件區分工作負荷應用程式登入帳號資料庫 工作負荷限制條件記憶體授與 % CPU 時間授與逾時最大要求數 Min Memory 10% Max Memory 20% Max CPU 20% Max CPU 90% Admin Pool Application Pool

41 一次可新增多筆資料更快更容易 以前 INSERT INTO customers (custid,name) VALUES (1, 'cust 1'); INSERT INTO customers (custid,name) VALUES (2, 'cust 2'); INSERT INTO customers (custid,name) VALUES (3, 'cust 3'); INSERT INTO customers (custid,name) VALUES (4, 'cust 4'); INSERT INTO customers (custid,name) VALUES (5, 'cust 5'); SQL 2008 INSERT INTO customers (custid, name) VALUES (1, 'cust 1'), (2, 'cust 2'), (3, 'cust 3'), (4, 'cust 4'), (5, 'cust 5');

42 常數資料表使用更簡單 以前 SQL 2008 TABLE (custid INT, name VARCHAR(20)) INSERT (custid,name) VALUES (1, 'cust 1'); INSERT (custid,name) VALUES (2, 'cust 2'); INSERT (custid,name) VALUES (3, 'cust 3'); INSERT (custid,name) VALUES (4, 'cust 4'); INSERT (custid,name) VALUES (5, 'cust 5'); SELECT * SELECT * FROM ( VALUES (1, 'cust 1'), (2, 'cust 2'), (3, 'cust 3'), (4, 'cust 4'), (5, 'cust 5') ) MyCustomers(custid, name);

43 更優越的儲存體結構 // Sparse column Create Table Products(Id int, Type nvarchar(16), Resolution int SPARSE, ZoomLength int SPARSE); // Filtered Indices Create Index ZoomIdx on Products(ZoomLength) where Type = Camera ; // HierarchyID CREATE TABLE [dbo].[folder] ( [FolderNode] HIERARCHYID NOT NULL UNIQUE, [Level] AS [FolderNode].GetLevel() PERSISTED, [Description] NVARCHAR(50) NOT NULL ); HierarchyID 使用者自訂類型疏鬆資料行寬型資料表篩選索引 儲存階層式結構的資料, 並有效地查詢它們使用者自訂類型 (UDTs) 沒有 8K 的限制提供對疏鬆資料行 (Sparse Columns)) 的最佳化的儲存體寬型資料表 (Wide Tables) 支援疏鬆資料行, 資料行總數可達 30,000 個篩選索引 (Filtered indexes) 可為資料表內的資料子集定義索引

44 HierarchyID 資料類型 呈現在階層內的位置, 直覺地展現 : 組織圖 Bill of Materials GL - accounts/subaccounts Content management Forums/mailing lists 壓縮 / 有效率地儲存 支援任意的新增和刪除 強大的查詢方法 GetRoot GetLevel IsDescendantOf GetReparentedValue 等等

45 疏鬆資料行 (Sparse Columns) 資料儲存空間更少 Null 值儲存最佳化 White paper

46 疏鬆資料行 (Sparse Columns) 優點 + Null 值不佔用儲存空間 + 節省儲存空間, 提升效能 + 查詢 /DML 無需修改缺點 - 儲存非 Null 值需要更多的儲存空間適用情境當空間至少節省了 20% ~ 40% 寬型資料表 (Wide Tables), 大部分皆為 Null 值支援 30,000 個資料行可搭配資料行集 (Column Sets) 和篩選索引一起使用

47 bit tinyint smallint int bigint real float smallmoney money smalldatetime datetime uniqueidentifier date datetime2(0) datetime2(7) time(0) time(7) datetimetoffset(0) datetimetoffset (7) decimal/numeric(1,s) decimal/numeric(38,s) 疏鬆資料行以節省 40% 空間為例 ( 資料類型 vs. Null 百分比 ) 100% 80% 60% 40% 20% 0% Null 百分比 98% 86% 76% 69% 69% 64% 64% 64% 64% 57% 60%52%49% 60% 52% 52% 52% 52% 52% 43% 42%

48 SQL Server 2008 索引新功能 壓縮索引 篩選索引

49 篩選索引 (Filtered indexes) 使索引效率再提升

50 篩選索引 ID Type Brand Zoom Resolution WaistSize Inseam Price.. 1 Camera Canon 3x 5 M Pant Dockers Camera Nikon 5x 10M Camera Pentax 3x 3M Pant Polo Pant Dockers 建立篩選索引 當 WHERE 條件符合時, Query Optimizer 會挑選合適的篩選索引 Create Index ZoomIdx on Products(Zoom) where Type = 'Camera'; Create Index PlusSizeIdx on Products(WaistSize) where Type = 'Pant' and WaistSize > Select ProductId, Type, Resolution, ZoomLength where Type = 'Camera' Select ProductId, Type, WaistLength, Inseam where Type = 'Pant' and WaistSize > 只有在需要時,DML 運作會導致維護索引資料 Insert into Products(ProductId, Type, Brand, WaistSize, inseam) values (201, 'Pant', 'Polo', 30, 32); Insert into Products(ProductId, Type, Brand, WaistSize, inseam) values (202, 'Pant', 'Polo', 38, 36);

51 資料表值參數 Table-Valued Parameters Batching 可將多個資料列傳送到 T-SQL 陳述式或常式 ( 預存程序或函數 ), 而不需建立暫存資料表或許多參數以傳址方式將資料表值參數傳遞給常式無需額外再產生資料副本類似於 OLE DB 和 ODBC 中的參數陣列, 但是更加彈性

52 效能調校工具 1. 可靠性與效能監視器 2. SQL Trace 與 SQL Server Profiler 3. Database Engine Tuning Advisor 4. Management Studio 標準報表 5. Performance Dashboard Reports 6. 資料收集器 7. 查詢執行計畫

53 可靠性與效能監視器 效能物件效能計數器例項圖表 線路長條圖列報告區域堆疊區域圖 即時監看長期觀察 資料收集器集合工具排程停止條件工作資料收集器 效能計數器資料收集器事件追蹤資料收集器組態資料收集器效能計數器警訊

54 重要的效能物件與計數器 (OS) 資源效能物件效能計數器瓶頸條件建議的效能調整方法 記憶體 Memory Pages/Sec < 20 增加記憶體大小 記憶體 Memory Available MBytes < 200 MB 同上 處理器 Processor % Processor Time < 75% 1. 升級處理器速度或增加處理器個數 2. 改善耗處理器的瓶頸 處理器 System Processor Queue Length 硬碟 PhysicalDisk Avg. Disk Read Queue Length 硬碟 PhysicalDisk Avg. Disk Write Queue Length < 2 同上 < 2 1. 換更快速的磁碟機 2. 資料庫檔案的檔案群組重新規劃分散於不同的磁碟陣列 < 2 同上

55 重要的效能物件與計數器 (SQL) 資源效能物件效能計數器瓶頸條件建議的效能調整方法 記憶體 記憶體 記憶體 Buffer Manager Memory Manager Memory Manager Buffer Cache Hit Ratio Target Server Memory Total Server Memory < 90 增加記憶體大小 > 實體記憶體 90% > 實體記憶體 90% 增加記憶體大小 增加記憶體大小 tempdb Databases Data File Size 是否持續增加詳見後面說明 tempdb 交易記錄檔 Access Manager Worktables Created/Sec 是否持續增加 Databases Log File Size 10~25% Date File Size 詳見後面說明 備份或清除交易記錄檔, 然後壓縮檔案

56 SQL Trace 與 SQL Profiler SQL Profiler 是圖形化工具, 以監控 SQL Server 的各種事件 SQL Trace Server-side SQL Profiler Client-side 監控 SQL Server 的各種活動, 產生各種紀錄檔供事後分析與 Database Engine Tuning Advisor 搭配使用與 Performance Monitor 整合診斷問題為應用程式除錯重演一次, 以模擬或重新產生問題

57 Profiler 整合效能計數器記錄 建立 Profiler 追蹤與效能監視記錄 開啟追蹤 ( 完成載入 ) 檔案 匯入效能資料 選擇物件 / 計數器 基於時間相互關聯 StartTime 與 EndTime 欄位 指標有相互關聯性, 可在兩個數據集合中任意挑選

58 用 Profiler 找出最常執行的查詢

59 實際案例 : 經驗分享 1. 以 Profiler 擷取應用程式指令, 找出 Top20 查詢, 同時以系統管理工具效能記錄效能計數器

60 實際案例 : 經驗分享 2. 分析 Top20 查詢的執行計畫, 依需求調整索引 3. 再次以 Profiler 擷取應用程式指令, 同時以系統管理工具效能記錄效能計數, 比較效能改善程度

61 實際案例 : 經驗分享

62 Database Engine Tuning Advisor 分析的來源 分析的結果 Table.trc file.sql script DTA Databases

63 如何使用 Database Engine Tuning Advisor 建立一個新的連線指定需要分析的來源設定效能調整選項執行分析檢視分析結果實行分析結果之建議 Table DTA.trc file Databases.sql script

64 Management Studio 報表伺服器

65 Management Studio 報表資料庫

66 Management Studio 活動監視器

67 Performance Dashboard Reports 一組遵循 Reporting Services 定義的客製化報表 整合 SQL Server Management Studio Report Viewer 控制項 針對 SQL Server 2005 SP2 之後的效能監控 藉由存取 SQL Server 2005 所提供的系統物件, 呈現 SQL Server 2005 執行個體當下運作的情形, 藉以分析效能問題

68 Performance Dashboard Reports SQL Server 2005 免費下載 7e0c e419218c1efc&displaylang=en SQL Server 2008 需稍加修改才可使用 下載 PerfDashboardReports.zip 更改預設安裝路徑 C:\Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard C:\Program Files (x86)\microsoft SQL Server\100\Tools\ PerformanceDashboard 將下列二個檔案取代 Performance_dashboard_main.rdl 和 Setup.sql 執行 Setup.sql 前, 建議修改 datediff(ms, ) 變更為 datediff(s, )

69 Performance Dashboard Reports 之應用 硬體資源的情況檢視指令所使用 CPU 和磁碟 I/O 封鎖當下被迫等待的指令索引建議須新增的索引以提升效能

70 實際案例 : 經驗分享 1. 以 Visual Studio 2008 壓力測試, 模擬 1,400 個使用者同時上線存取 2. 用 Performance Dashboard Report 找出 Missing Index 3. 其中 3 組 Missing Index 的 Impact > 90, 依建議建立相關索引 調校前 :50%~80% 調校後 :5%~10%

71 資料收集器 資料來源 SQL Trace 效能計數器 Transact-SQL 集中的資料儲存 效能資料倉儲 低負荷資料收集 效能報表

72 設定管理資料倉儲精靈

73 設定管理資料倉儲精靈

74 檢視管理資料倉儲報表

75 資料收集器自動化作業 系統資料收集組 : 1. 伺服器活動 2. 查詢統計資料 3. 磁碟使用量 資料收集 載入 清除

76 查詢執行計畫與 Query Optimizer SQL Server 資料引擎內建強大之查詢分析功能 Query Optimizer 產生查詢執行計畫的三階段查詢分析階段 WHERE 條件與 JOIN 條件索引選擇階段評估是否有索引存在若有索引, 評估是否可以提升效能 JOIN 選擇階段評估採用下列何種運算方式執行 JOIN LOOP HASH MERGE REMOTE

77 圖形化查詢執行計畫 提昇效能減少 Table Scan 減少 Clustered Index Scan 減少 Sort 磁碟 I/O 效能 1. Nonclustered Index Seek 2. Clustered Index Seek 3. Nonclustered Index Scan

78 查詢與索引之範例 下列查詢最佳之索引是? SELECT column1, column3 FROM tablea WHERE column3 =? 假設條件 : 1. tablea 有 20 個資料欄, 每個資料欄 10 Bytes 2. tablea 共有 1,000,000 筆資料 3. 符合 column3 =? 共有 1,000 筆資料

79 觀察磁碟 I/O 之差異 Table Scan 的磁碟 I/O Nonclustered Index Scan 的磁碟 I/O Clustered Seek 的磁碟 I/O Nonclustered Index Seek 的磁碟 I/O PS. 以下說明的計算範例皆為概算值, 與實際上 SQL Server 所花費之磁碟 I/O 會有些許的差異

80 Table Scan 的磁碟 I/O 1 Data Row = 200 Bytes (10 Bytes 20 Columns) 1 Data Page = 40 Data Rows (8K 200 Bytes) Disk Space for Data Pages = 25,000 Data Pages (1,000,000 rows 40 rows/pages ) = 200 MB (8K 25,000) Table Scan: tablea 沒有任何的索引 Clustered Index Scan: 叢集索引是 column3 以外的欄位 Non-Leaf Root 磁碟 I/O 太大, 極易造成查詢逾時 Leaf

81 Nonclustered Index Scan 的磁碟 I/O Index Scan: 非叢集索引是 column1, column3 1 Index Row = 20 Bytes (10 Bytes 2 Columns) 1 Index Page = 400 Index Rows (8K 20 Bytes) Disk Space for Index Pages = 2,500 Index Pages (1,000,000 rows 400 rows/pages ) = 20 MB (8K 2,500) Non-Leaf Leaf Root 雖然磁碟 I/O 只有 table scan 的 10 %, 但仍有可能逾時

82 Clustered Index Seek 的磁碟 I/O 1 Data Row = 200 Bytes (10 Bytes 20 Columns) 1 Data Page = 40 Data Rows (8K 200 Bytes) Disk Space for Data Pages = 25 Data Pages (1,000 rows 40 rows/pages ) = 200 KB (8K 25) 磁碟 I/O 只有 table scan 的 0.1 %, Index scan 的 1 % Clustered Index Seek: 叢集索引是 column3 的欄位 Non-Leaf Root Leaf

83 Nonclustered Index Seek 的磁碟 I/O 1 Index Row = 20 Bytes (10 Bytes 2 Columns) 1 Index Page = 400 Index Rows (8K 20 Bytes) Disk Space for Index Pages = 3 Index Pages (1,000 rows 400 rows/pages ) Index Seek: 非叢集索引是 column3, column1 = 24 KB (8K 3) Index Seek: 非叢集索引是 column3 + 叢集索引是 column1 磁碟 I/O 只有 Clustered Index seek 的 12 % Non-Leaf 覆蓋索引 : 類似更多資訊的名詞解釋架構, 除了有名詞的頁次, 還包括章 節 類型等資訊 Root Leaf

84 實際案例 : 情境說明 處理器 : 雙核 x 2 記憶體 :8GB MS SQL IIS Web Server 資料庫 :450MB 平日同時上線使用者 < 1,000 人 某日爆量同時上線使用者 > 20,000 人

85 實際案例 : 調校前之效能瓶頸 IIS 主機與 SQL 主機安裝於同一部主機 SQL Server 過於忙碌時, 處理器資源不足, 使用率超過 95%, 導致網站回應逾時 主要效能瓶頸是處理器 資料庫大小約 450MB, 處理器執行查詢時, 可利用伺服器充足的記憶體資源處理, 因此硬碟存取量較小, 磁碟沒有瓶頸發生 但因為沒有適當的索引, 即使資料都在記憶體, 仍需要大量耗費處理器資源, 導致處理器發生效能瓶頸

86 實際案例 : 經驗分享 1. 利用 SQL Server Profiler 找出執行時間過長的指令 2. 分析指令的執行計畫 3. 建立適當的索引 效能提升約 3332 倍 (99.97 / 0.03) 調校前 :99.97% 調校後 :0.03% 於資料表 BOOKLET 新增一個非叢集索引

87 實際案例 : 效能調校前後比較

88 結論 效能調校與規劃 SQL Server 2008 提升效能的新功能 SQL Server 2008 效能調校工具

89

90 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.