SQLServer2008R2監控與調優解決方案PerformanceTurningSQLServer2008R2的架構概要性能調優性能監控性能調優的方法學調優順序最困難但最有成效最簡單但是收效最少架構設計表查詢優化存儲過程視圖索引優化覆蓋查詢并發控制鎖事務存儲優化文件組分區服務器優化內存處理器親和度性能最優化如何設計良好的關系型數據庫架構對數據熱區的判斷根據數據熱區定義索引、表分割定義優化SELECT查詢盡量將數據存儲在同一張表中使用索引及索引覆蓋策略優化UPDATE事務盡量將需要更新的數據放在一張較小的表中優化DELETE事務在大規模刪除中評估分區的效果優化INSERT事務減少對自動編號的依賴性能調優的方法學調優順序最困難但最有成效最簡單但是收效最少架構設計表查詢優化存儲過程索引視圖索引優化覆蓋查詢并發控制鎖事務存儲優化文件組分區服務器優化內存處理器親和度性能最優化使用有效的查詢參數SARGs（查詢參數的有效格式）限制以完全符合、一個范圍的值、或是以AND連接兩個以上的項目來定義搜尋。格式數據字段

部分的運算符<常數或變量><常數或變量>部分的運算符數據字段符合SARGs的范例FirstName=‘王’60000<SalaryFirstName=‘王’

and

Salary>60000容易犯的錯誤對數據字段進行運算Select*from

Employees

where

LastName+’,’+FirstName=‘Davolio,Nancy進行負向查詢Not

Exists、NotIN、NotLike、!=<>!>!<等在where語句中對數據字段使用函數Select*from[orderdetails]whereABS(quantity-100)<1使用OR運算符使用OR做運算符，則需要所有的字段都有可用的索引使用T-SQL的注意事項Select語法盡量不要傳回所有的數據表內字段且不配置過濾條件若使用復合索引，則索引順序上的第一個字段才適合當作過濾條件Distinct，Orderby等語法盡量到查詢結果需要時才使用UnionAll要比Union好若未將連接事務級別降低到ReadUncommited，或是通過鎖提示NOLOCK來降低阻塞的機會，最好配置SETLOCK_TIMEOUT選項，避免用戶無盡等待使用T-SQL的注意事項Insert、Delete和Update大量批處理數據操作時，無Log的行為一定比逐筆數據由兩次寫入（先記錄Log再寫數據庫）快。在Update和Delete采用Where子句時，記得條件也要符合SARGs格式查詢調優選項使用OPTION子句調用表提示或視圖提示OPTION(TABLEOPTION(dbo.Orders,IDNEX(IX_OrderID)))FORCESEEK提示FROM

OrdersWITH(FORCESEEK)OPTION(TABLEHINT(dbo.Orders,IDNEX(0),FORCESEEK))sys.fn_validate_plan_guide函數用于驗證強制計劃的有效性計劃指南支持XMLShowPlan參數為監控強制計劃設計了新的事件類(PlanGuideSuccessful和PlanGuideUnsuccessful)以及性能計數器(SQLServerSQL

Statistics對象下的GuidedPlanExecutions/sec計數器和MisguidedPlanExecutions/sec計數器)性能調優的方法學調優順序最困難但最有成效最簡單但是收效最少架構設計表查詢優化存儲過程視圖索引優化覆蓋查詢并發控制鎖事務存儲優化文件組分區服務器優化內存處理器親和度性能最優化是否值得建索引選擇性數據密度數據分布統計在多個字段上使用索引復合索引索引覆蓋查詢（include)使用工作載荷分析數據性能提供圖形化和命令行兩種方式什么是數據庫引擎優化顧問?報表和建議工作載荷數據庫引擎優化顧問數據庫和數據庫對象索引碎片碎片如何產生的當數據被修改或者導致索引頁面分裂，SQLServer會重組索引頁面解決方法<=30%碎片=Reorganize>30%碎片=Rebuild篩選索引應用場合對特殊的屬性值進行索引對指定分區的值進行索引常見場合產品目錄僅對熱門商品的屬性進行索引數據倉庫僅對最近三個月的銷售訂單進行索引倉儲系統僅對未標記為空的商品進行索引篩選索引的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbCREATE

INDEXIX_AONT(att1)WHEREName=‘A’ORName=‘E’SELECTNameFROMTWHEREatt1=‘a’篩選索引的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbCREATE

INDEXIX_AONT(att4)WHEREName=‘C’ORName=‘G’SELECTNameFROMTWHEREatt4=‘t’CREATE

INDEXIX_AONT(att1)WHEREName=‘A’ORName=‘E’SELECTNameFROMTWHEREatt1=‘a’索引視圖用途對大型數據表進行連接以及匯總重復同一種模式查詢重復對相同的數據表，相同的鍵值作連接性能調優的方法學調優順序最困難但最有成效最簡單但是收效最少架構設計表查詢優化存儲過程視圖索引優化覆蓋查詢并發控制鎖事務存儲優化文件組分區服務器優化內存處理器親和度性能最優化鎖與事務鎖事務不可分割性(Atomicity)一致性(Consistency)隔離性(Isolation)持久性(Durability)并發事務隔離未提交讀已提交讀可重復讀序列化讀提交快照快照鎖升級粒度行頁分區表鎖與并發行版本支持READ_COMMITTED_SNAPSHOT事務隔離級別ALLOW_SNAPSHOT_ISOLATION數據庫選項行版本的收益讀操作可以獲得一致的數據庫快照數據在讀操作的過程中SELECT語句不會鎖住數據(讀不會阻塞寫，反之亦然)SELECT語句可以獲得最近一次其他事務更新數據并提交的值減少了死鎖事務需要的鎖減少了，則系統用于管理鎖的負荷減少了減少了鎖升級的發生SQLServer2008R2中鎖的增強對分區表和索引優化了鎖的升級使用事務的建議事務持續時間越短越好事務期間避免與用戶互動查詢數據期間，盡量不要啟用事務活用事務隔離級別和鎖提示死鎖循環死鎖轉換死鎖分布式死鎖鎖的原因和相關處理費時的查詢或事務不正確的事務或事務隔離級別事務未正確處理未檢測到的分布式死鎖鎖定數據粒度太高或者太低基本原則防止鎖住他人事務不要跨批次，且越短越好，事務期間不要和用戶互動小心處理超時放棄，或執行錯誤等狀況建立合適的索引，數據表最有有聚集索引盡量不要啟動隱性事務，避免長時間打開事務盡量降低事務隔離級別如果允許，可以嘗試使用鎖提示基本原則防止與處理死鎖盡量避免或盡快處理阻塞訪問數據的順序要相同讓不同的連接使用相同的鎖提供不同的數據訪問路徑發生死鎖后的解決設置Deadlock優先級，讓不重要的事務自動放棄性能調優的方法學調優順序最困難但最有成效最簡單但是收效最少架構設計表查詢優化存儲過程視圖索引優化覆蓋查詢并發控制鎖事務存儲優化文件組分區服務器優化內存處理器親和度性能最優化磁盤子系統設計RAID0RAID1RAID3RAID5RAID0+1文件組規劃數據庫文件提升性能默認每個數據庫文件可以同時處理32個異步I/O1個數據庫文件=32個讀取+32個寫入2個數據庫文件=64個讀取+64個寫入將數據文件和事務日志文件分開儲存至不同磁盤陣列數據文件：RAID5或RAID0+1事務日志文件：RAID1利用文件組提升性能若性能瓶頸為DiskI/O，可考慮下列方法將經常要查詢或更新的數據表，指定存放于不同磁盤陣列的文件組將非簇索引，指定存放于不同磁盤陣列的文件組將常用的現有數據與歷史數據分割儲存至不同的數據表，并指定存放于不同磁盤陣列的文件組RAID+文件組磁盤控制器文件組磁盤控制器文件E文件F文件G文件H文件A文件B文件C文件D事務日志事務日志磁盤控制器操作系統磁盤控制器CustomerIDIndexCustomerIDIndexCustomerIDIndex根據訂單日期水平分區:OrderDate<‘2003-01-01’OrderDate>=‘2003-01-01’andOrderDate<‘2004-01-01’OrderDate>=‘2004-01-01’Filegroup

DATA_2002Filegroup

DATA_2003Filegroup

DATA_2004Filegroup

IDX_2002Filegroup

IDX_2003Filegroup

IDX_2004OrdersCustomerIDOrderDateAmount…OrderIDOrder

HistoryCustomerIDOrderDateAmount…OrderIDOrderHistoryTableOrderHistoryTableOrderHistoryTable表分區TempDB存放以下對象:內部對象版本存放區用戶自定義對象建議根據需要自動擴大設置合理的原始大小將文件增長百分比設置成合理的大小放在快速的I/O系統上創建多個數據庫文件，個數和服務器的CPU數目相同，文件大小相同性能調優的方法學調優順序最困難但最有成效最簡單但是收效最少架構設計表查詢優化存儲過程視圖索引優化覆蓋查詢并發控制鎖事務存儲優化文件組分區服務器優化內存處理器親和度性能最優化內存管理內存管理X86系列CPU，32位操作系統2GB<=服務器物理內存<=4GBBoot.ini加上/3GB4GB<=服務器物理內存<=16GB

Boot.ini加上/3GB/PAE服務器物理內存>=16GBBoot.ini加上/PAESQLServer啟用AWE（4GB以上內存）

LocalDB內存處理器和線程SQLServer關系引擎OpenDataServices存儲引擎Processor0Processor1ProcessornIOCompletionPortThreadThreadThreadThreadThreadThreadThreadThreadThread行集IOCompletionPortThreadThreadThreadThreadThreadThreadIOCompletionPortThreadThreadThreadThreadThreadThreadUMS(UserModeScheduler)SchedulerUMSSchedulerUMSScheduler23IOCompletionPortThreadThreadThreadThreadThreadThreadThreadThreadThread查詢查詢查詢查詢14ThreadsSQLServer維護一個線程池來處理用戶的需求如查詢或是連接使用自己的調度而非操作系統的，來決定哪個處理器執行哪條線程處理器處理查詢從內存或是硬盤中取出數據，并將這些結果返回存儲引擎將線程返回IOCompletionPort處理器處理器處理器關聯I/O關聯最大工作線程數提升SQLServer的優先級默認優先級為7，提升以后優先級為13僅在服務器同時安裝多個應用程序時使用使用Windows纖程（輕型池）有多個CPU的大型服務器所有的CPU都以接近最大容量在執行內容切換(contextSwitches）的次數過高動態配置熱插拔內存熱插拔CPU在數據庫服務器聯機的情況下添加硬件資源SQLServer如何從硬盤讀取數據SQLServer緩沖管理器

Windows2003I/OBuffer(64KB)8-KBincrements本地數據庫內存緩沖頁面CEADFHGBABCDEFGH

CEADFHGB123456781234567873186425

12345678硬盤相關設置硬盤恢復間隔數據庫檢查點(Checkpoint)事件發生的頻率檢查點的作用是把數據庫緩存中標記為Dirty的數據頁面與日志頁面寫入硬盤數據壓縮主要目標縮小數據倉庫事實標的尺寸第二目標增強查詢性能可以在單個表或索引上啟用支持分區需要在處理器資源和存儲及IO帶寬之間進行取舍數據壓縮的工作原理Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400SQLServer2005SP2推出了vardecimal存儲選項允許decimal數據以變長方式存儲Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400數據壓縮的工作原理SQLServer2008R2將變長的存儲機制擴展到了所有定長數據類型上Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400數據壓縮的工作原理Date_IDProduct_IDUnit_PriceAmount120071202MA_3587_132110.0010132210.00100014CF_7253_140.005015MA_3659_140.00400SQLServer2008R2還增加了前綴壓縮機制(行壓縮)：常見的前綴被存儲在頁面中的一個前綴列表中列中的值將利用前綴列表中的標號進行替代數據壓縮的工作原理Date_IDProduct_IDUnit_PriceAmount1(2007120)2(MA_3587_)1(10.00)2(40.00)3(1)

4(3)142311014221100014CF_7253_325015MA_3659_32400SQLServer2008R2還增加了字典壓縮機制(頁壓縮)：將常見的值編制成詞典存儲在頁中列中的常見值利用詞典中的標號進行替代對于正式的數據可以達到2-7倍的壓縮率壓縮率的大小依賴于數據值的模式Date_IDProduct_IDUnit_PriceAmount120071202MA_3587_132110.0010132210.00100014CF_7253_140.005015MA_3659_140.00400稀疏列應用場合半結構化數據：屬性集常見場合產品目錄不同類別的商品擁有不同的屬性集文檔管理系統用戶對文檔設置的自定義屬性GPS/地圖系統不同地圖標記位置的屬性稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbNull值不占用空間，其他值則會產生2-4字節的額外開銷同時數據訪問效率會受到輕微影響(att1,att3)(a,x)(att2,att8)(d,f)(att4,att5)(t,j)(att2,att7)(d,u)(att1,att9)(k,i)(att2,att6,att9)(t,k,o)(att4)(w)(att2,att8)(d,u)(att5)(b)稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91A2B3C4D5E6F7G8H9I(att1,att3)(a,x)(att25873,att35578)(d,f)(att42674,att52358)(t,j)(att245,att78856)(d,u)(att16,att9357)(k,i)(att28772,att65339,att99998)(t,k,o)(att467)(w)(att23,att57468)(d,u)(att5)(b)稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91A2B3C4D5E6F7G8H9I…Att100,000在單張表中支持100,000個稀疏列稀疏列的優勢提高存儲和訪問效率支持那些存儲稀疏值的超多列表稀疏列組ColumnSet可以將稀疏列看作一個組進行處理稀疏列組在表中定義的稀疏列的邏輯組CREATETABLEProduct

(IDint,Namevarchar(200),

ProductPropertiesXMLCOLUMN_SET

FORALL_SPARSE_COLUMNS);稀疏列組作為可更新的XML計算列SELECT*語句可以返回所有非稀疏列和稀疏列組通過XML語法可以獲取或更新稀疏列組中的特定稀疏列概要性能調優性能監控性能排錯的方法學監視整個數據庫環境縮小性能問題到特定的數據庫環境區域縮小性能問題到特定的數據庫環境對象排錯單個問題實現解決方案工欲善其事，必先利其器

SQLServer2005Windows系統監視器SQL跟蹤探查器動態管理視圖&動態管理函數性能儀表板報表活動監視器SQLServer代理警報數據引擎優化顧問查詢執行計劃關系型數據庫性能

PerformanceStudio底開銷收集性能數據數據提供者SQL跟蹤性能計數器T-SQL集中化的性能數據存儲可管理的數據倉庫綜合的性能數據報表資源調控器SQLServer管理工作負荷備份管理任務報表工作負荷OLTP工作負荷OLTP活動行政報表即席報表高最小內存10%最大內存20%最大CPU20%管理資源池最大

CPU90%應用程序資源池能區別對待不同的工作負荷例如使用應用程序名，登陸名等.每個請求限制重要性最大內存百分比最大CPU時間授予超時值最大請求數資源監控性能實例1(調優前)服務器處理器4個，內存8GB性能對象與計數器：System:ProcessorQueueLength等于0Memory:pages/sec超過2400Memory:AvailableMbytes超過5000PhysicalDisk:Avg.ReadDiskQueueLength超過110PhysicalDisk:Avg.WriteDiskQueueLength超過200Boot.ini已設置/3GB與/PAE參數未啟動SQLServer的AWE模式DiskI/O?Memory?性能實例1(調優后)服務器處理器4個，內存8GB性能對象與計數器：System:ProcessorQueueLength等于0Memory:pages/sec低于20Memory:AvailableMbytes維持約500PhysicalDisk:Avg.ReadDiskQueueLength低于2PhysicalDisk:Avg.WriteDiskQueueLength低于2Boot.ini設置