1、SQL Server 2008 監(jiān)控與調優(yōu)解決方案,Performance Turning,SQL Server 2008的架構,概要,性能調優(yōu) 性能監(jiān)控,性能調優(yōu)的方法學,調優(yōu)順序,最困難 但最有成效,最簡單 但是收效最少,如何設計良好的關系型數據庫架構,對數據熱區(qū)的判斷 根據數據熱區(qū)定義索引、表分割定義 優(yōu)化SELECT查詢 盡量將數據存儲在同一張表中(如建立冗余) 使用索引及索引覆蓋策略 優(yōu)化UPDATE事務 盡量將需要更新的數據放在一張較小的表中 優(yōu)化DELETE事務 在大規(guī)模刪除中評估分區(qū)的效果 優(yōu)化INSERT事務 減少對自動編號的依賴,性能調優(yōu)的方法學,調優(yōu)順序,最困難 但最有成

2、效,最簡單 但是收效最少,使用有效的查詢參數,SARGs（查詢參數的有效格式） 限制以完全符合、一個范圍的值、或是以AND連接兩個以上的項目來定義搜尋。 格式 數據字段 部分的運算符 部分的運算符 數據字段 符合SARGs的范例 FirstName=王 6000060000,容易犯的錯誤,對數據字段進行運算 Select * from Employees whereLastName+,+FirstName=Davolio,Nancy 進行負向查詢 Not、Exists、Not IN、Not Like、!= ! !等 在where語句中對數據字段使用函數 Select * from order

3、details where ABS(quantity-100)1 使用OR運算符 使用OR做運算符，則需要所有的字段都有可用的索引,使用T-SQL的注意事項,Select語法 盡量不要傳回所有的數據表內字段且不配置過濾條件 若使用復合索引，則索引順序上的第一個字段才適合當作過濾條件 Distinct，Order by等語法盡量到查詢結果需要時才使用 Union All要比Union好 若未將連接事務級別降低到Read Uncommited，或是通過鎖提示NOLOCK來降低阻塞的機會，最好配置SET LOCK_TIMEOUT選項，避免用戶無盡等待,使用T-SQL的注意事項,Insert、Dele

4、te和Update 大量批處理數據操作時，無Log的行為一定比逐筆數據由兩次寫入（先記錄Log再寫數據庫）快。 在Update和Delete采用Where子句時，記得條件也要符合SARGs格式,查詢調優(yōu)選項,使用OPTION子句調用表提示或視圖提示 OPTION (TABLE OPTION(dbo.Orders, IDNEX(IX_OrderID) FORCESEEK提示 FROM Orders WITH (FORCESEEK) OPTION (TABLE HINT(dbo.Orders, IDNEX(0), FORCESEEK) sys.fn_validate_plan_guide函數用于驗

5、證強制計劃的有效性 計劃指南支持XML ShowPlan參數 為監(jiān)控強制計劃設計了新的事件類(Plan Guide Successful和Plan Guide Unsuccessful)以及性能計數器(SQL Server SQL Statistics對象下的Guided Plan Executions/sec計數器和Misguided Plan Executions/sec計數器),性能調優(yōu)的方法學,調優(yōu)順序,最困難 但最有成效,最簡單 但是收效最少,是否值得建索引,選擇性 數據密度 數據分布 統計,在多個字段上使用索引,復合索引 索引覆蓋查詢（include),使用工作載荷分析數據性能 提

6、供圖形化和命令行兩種方式,什么是數據庫引擎優(yōu)化顧問?,報表和建議,工作載荷,數據庫引擎 優(yōu)化顧問,數據庫和數據庫對象,索引碎片,碎片如何產生的 當數據被修改或者導致索引頁面分裂，SQL Server會重組索引頁面 解決方法 30% 碎片 = Rebuild,篩選索引,應用場合 對特殊的屬性值進行索引 對指定分區(qū)的值進行索引 常見場合 產品目錄 僅對熱門商品的屬性進行索引 數據倉庫 僅對最近三個月的銷售訂單進行索引 倉儲系統 僅對未標記為空的商品進行索引,篩選索引的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,a,x,

7、2,B,d,f,3,C,t,j,4,D,m,u,5,E,k,l,6,F,t,k,o,7,G,w,8,H,h,u,9,I,b,CREATE INDEX IX_A ON T(att1) WHERE Name = A OR Name = E SELECT Name FROM T WHERE att1 = a,篩選索引的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,a,x,2,B,d,f,3,C,t,j,4,D,m,u,5,E,k,l,6,F,t,k,o,7,G,w,8,H,h,u,9,I,b,CREATE INDEX IX

8、_A ON T(att4) WHERE Name = C OR Name = G SELECT Name FROM T WHERE att4 = t,CREATE INDEX IX_A ON T(att1) WHERE Name = A OR Name = E SELECT Name FROM T WHERE att1 = a,索引視圖,用途 對大型數據表進行連接以及匯總 重復同一種模式查詢 重復對相同的數據表，相同的鍵值作連接,性能調優(yōu)的方法學,調優(yōu)順序,最困難 但最有成效,最簡單 但是收效最少,鎖與事務,鎖 事務 不可分割性(Atomicity) 一致性(Consistency) 隔離性(

9、Isolation) 持久性(Durability),并發(fā),鎖與并發(fā),使用事務的建議,事務持續(xù)時間越短越好 事務期間避免與用戶互動 查詢數據期間，盡量不要啟用事務 活用事務隔離級別和鎖提示,死鎖,循環(huán)死鎖 轉換死鎖 分布式死鎖,鎖的原因和相關處理,費時的查詢或事務 不正確的事務或事務隔離級別 事務未正確處理 未檢測到的分布式死鎖 鎖定數據粒度太高或者太低,基本原則,防止鎖住他人 事務不要跨批次，且越短越好，事務期間不要和用戶互動 小心處理超時放棄，或執(zhí)行錯誤等狀況 建立合適的索引，數據表最有有聚集索引 盡量不要啟動隱性事務，避免長時間打開事務 盡量降低事務隔離級別 如果允許，可以嘗試使用鎖提示

10、,基本原則,防止與處理死鎖 盡量避免或盡快處理阻塞 訪問數據的順序要相同 讓不同的連接使用相同的鎖 提供不同的數據訪問路徑 發(fā)生死鎖后的解決 設置Deadlock優(yōu)先級，讓不重要的事務自動放棄,性能調優(yōu)的方法學,調優(yōu)順序,最困難 但最有成效,最簡單 但是收效最少,磁盤子系統設計,RAID 0 RAID 1 RAID 3 RAID 5 RAID 0+1,文件組,規(guī)劃數據庫文件提升性能,默認每個數據庫文件可以同時處理32個異步I/O 1個數據庫文件= 32個讀取+ 32個寫入 2個數據庫文件= 64個讀取+ 64個寫入 將數據文件和事務日志文件分開儲存至不同磁盤陣列 數據文件：RAID 5或RAI

11、D 0 + 1 事務日志文件：RAID 1,利用文件組提升性能,若性能瓶頸為DiskI/O，可考慮下列方法 將經常要查詢或更新的數據表，指定存放于不同磁盤陣列的文件組 將非簇索引，指定存放于不同磁盤陣列的文件組 將常用的現有數據與歷史數據分割儲存至不同的數據表，并指定存放于不同磁盤陣列的文件組,RAID+文件組,根據訂單日期水平分區(qū):,Order Date 2003-01-01,Order Date = 2003-01-01 and Order Date 2004-01-01,Order Date = 2004-01-01,Filegroup DATA_2002,Filegroup DATA_

12、2003,Filegroup DATA_2004,Filegroup IDX_2002,Filegroup IDX_2003,Filegroup IDX_2004,表分區(qū),TempDB,存放以下對象: 內部對象 版本存放區(qū) 用戶自定義對象 建議 根據需要自動擴大 設置合理的原始大小 將文件增長百分比設置成合理的大小 放在快速的I/O系統上 創(chuàng)建多個數據庫文件，個數和服務器的CPU數目相同，文件大小相同,性能調優(yōu)的方法學,調優(yōu)順序,最困難 但最有成效,最簡單 但是收效最少,內存管理,內存管理,X86系列CPU，32位操作系統 2GB=16GB Boot.ini加上/PAE SQL Server啟

13、用AWE（4GB以上內存）,Local DB,內存,處理器和線程,SQL Server,關系引擎,Open Data Services,存儲引擎,Processor 0,Processor 1,Processor n,行集,UMS (User Mode Scheduler) Scheduler,UMS Scheduler,UMS Scheduler,2,3,SQL Server 維護一個線程池 來處理用戶的需求 如查詢或是連接,使用自己的調度 而非操作系統的， 來決定哪個處理器 執(zhí)行哪條線程,處理器處理查詢 從內存或是硬盤 中取出數據， 并將這些結果返回 存儲引擎,將線程返回 IOComple

14、tion Port,處理器,處理器,處理器關聯 I/O關聯 最大工作線程數 提升SQL Server的優(yōu)先級 默認優(yōu)先級為7，提升以后優(yōu)先級為13 僅在服務器同時安裝多個應用程序時使用 使用Windows纖程（輕型池） 有多個CPU的大型服務器 所有的CPU都以接近最大容量在執(zhí)行 內容切換(context Switches）的次數過高,動態(tài)配置,在數據庫服務器聯機的情況下添加硬件資源,SQL Server如何從硬盤讀取數據,SQL Server 緩沖管理器,Windows 2003 I/O Buffer(64 KB) 8-KB increments,本地數據庫,內存緩沖頁面,C,E,A,D,F

15、,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,硬盤相關設置,硬盤,恢復間隔 數據庫檢查點(Checkpoint)事件發(fā)生的頻率 檢查點的作用是把數據庫緩存中標記為Dirty的數據頁面與日志頁面寫入硬盤,數據壓縮,主要目標 縮小數據倉庫事實標的尺寸 第二目標 增強查詢性能 可以在單個表或索引上啟用 支持分區(qū) 需要在處理器資源和存儲及IO帶寬之間進行取舍,數據壓縮的工作原理,SQL Server 2005 SP2推出了vardecimal存儲選項 允許decimal數據以變長方式存儲,數據壓縮的工作原理,SQL Server 2008

16、將變長的存儲機制擴展到了所有定長數據類型上,數據壓縮的工作原理,SQL Server 2008還增加了前綴壓縮機制(行壓縮)： 常見的前綴被存儲在頁面中的一個前綴列表中 列中的值將利用前綴列表中的標號進行替代,數據壓縮的工作原理,SQL Server 2008還增加了字典壓縮機制(頁壓縮)： 將常見的值編制成詞典存儲在頁中 列中的常見值利用詞典中的標號進行替代,對于正式的數據可以達到2-7倍的壓縮率 壓縮率的大小依賴于數據值的模式,稀疏列,應用場合 半結構化數據：屬性集 常見場合 產品目錄 不同類別的商品擁有不同的屬性集 文檔管理系統 用戶對文檔設置的自定義屬性 GPS/地圖系統 不同地圖標記

17、位置的屬性,稀疏列的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,a,x,2,B,d,f,3,C,t,j,4,D,m,u,5,E,k,l,6,F,t,k,o,7,G,w,8,H,h,u,9,I,b,Null值不占用空間，其他值則會產生2-4字節(jié)的額外開銷 同時數據訪問效率會受到輕微影響,(att1, att3) (a, x),(att2, att8) (d, f),(att4, att5) (t, j),(att2, att7) (d, u),(att1, att9) (k, i),(att2, att6, att9

18、) (t, k, o),(att4) (w),(att2, att8) (d, u),(att5) (b),稀疏列的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,2,B,3,C,4,D,5,E,6,F,7,G,8,H,9,I,(att1, att3) (a, x),(att25873, att35578) (d, f),(att42674, att52358) (t, j),(att245, att78856) (d, u),(att16, att9357) (k, i),(att28772, att65339, a

19、tt99998) (t, k, o),(att467) (w),(att23, att57468) (d, u),(att5) (b),稀疏列的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,2,B,3,C,4,D,5,E,6,F,7,G,8,H,9,I,Att100,000,在單張表中支持100,000個稀疏列,稀疏列的優(yōu)勢,提高存儲和訪問效率 支持那些存儲稀疏值的超多列表 稀疏列組Column Set可以將稀疏列看作一個組進行處理,稀疏列組,在表中定義的稀疏列的邏輯組 CREATE TABLE Product(ID

20、 int, Name varchar(200), ProductProperties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS); 稀疏列組作為可更新的XML計算列 SELECT *語句可以返回所有非稀疏列和稀疏列組 通過XML語法可以獲取或更新稀疏列組中的特定稀疏列,概要,性能調優(yōu) 性能監(jiān)控,性能排錯的方法學,工欲善其事，必先利其器,Windows 系統監(jiān)視器 SQL 跟蹤探查器 動態(tài)管理視圖 & 動態(tài)管理函數 性能儀表板報表 活動監(jiān)視器 SQL Server 代理警報 數據引擎優(yōu)化顧問 查詢執(zhí)行計劃,關系型數據庫性能Performance Studio,資

21、源調控器,SQL Server,管理工作負荷,備份,管理任務,報表工作負荷,OLTP 工作負荷,OLTP 活動,行政報表,即席報表,高,能區(qū)別對待不同的工作負荷 例如使用應用程序名，登陸名等. 每個請求限制 重要性 最大內存百分比 最大 CPU 時間 授予超時值 最大請求數 資源監(jiān)控,性能實例1(調優(yōu)前),服務器處理器4個，內存8 GB 性能對象與計數器： System: Processor Queue Length等于0 Memory: pages/sec超過2400 Memory: Available Mbytes超過5000 PhysicalDisk: Avg. Read Disk Qu

22、eue Length超過110 PhysicalDisk: Avg. Write Disk Queue Length超過200,Boot.ini已設置/3GB與/PAE參數 未啟動SQL Server的AWE模式,Disk I/O ?,Memory ?,性能實例1(調優(yōu)后),服務器處理器4個，內存8 GB 性能對象與計數器： System: Processor Queue Length等于0 Memory: pages/sec低于20 Memory: Available Mbytes維持約500 PhysicalDisk: Avg. Read Disk Queue Length低于2 Phys

23、icalDisk: Avg. Write Disk Queue Length低于2,Boot.ini設置/3GB與/PAE參數 啟動SQL Server的AWE模式,Disk I/O, OK,Memory, OK,性能實例2(調優(yōu)前),服務器硬件資源 處理器2個，內存2 GB 共有1 + 14個存儲過程，共約有30000行指令的復雜系統，需要執(zhí)行4 hr 21 min 使用者需求：必須于2 hr內執(zhí)行完畢,性能實例2(調優(yōu)后),僅將其中一個最慢的查詢，不必要的table variable查詢技巧，變更為直接從數據表的查詢，并且針對查詢中所需要的數據表字段建立復合字段的非簇索引 運行時間大幅縮短至1 hr 15 min，性能提升71%，已滿足使用者需求之期望 后續(xù)建議 性能對象與計數器： Processor: % Processo