SQLServerR監(jiān)視與調(diào)優(yōu)解決方案

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

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

and

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

Employees

where

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

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

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

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

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

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

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

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

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

Windows2003I/OBuffer(64KB)8-KBincrements本地數(shù)據(jù)庫內(nèi)存緩沖頁面CEADFHGBABCDEFGH

CEADFHGB123456781234567873186425

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

4(3)142311014221100014CF_7253_325015MA_3659_32400SQLServer2008R2還增加了字典壓縮機制(頁壓縮)：將常見的值編制成詞典存儲在頁中列中的常見值利用詞典中的標號進行替代對于正式的數(shù)據(jù)可以達到2-7倍的壓縮率壓縮率的大小依賴于數(shù)據(jù)值的模式Date_IDProduct_IDUnit_PriceAmount120071202MA_3587_132110.0010132210.00100014CF_7253_140.005015MA_3659_140.00400稀疏列應用場合半結(jié)構化數(shù)據(jù)：屬性集常見場合產(chǎn)品目錄不同類別的商品擁有不同的屬性集文檔管理系統(tǒng)用戶對文檔設置的自定義屬性GPS/地圖系統(tǒng)不同地圖標記位置的屬性稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbNull值不占用空間，其他值則會產(chǎn)生2-4字節(jié)的額外開銷同時數(shù)據(jù)訪問效率會受到輕微影響(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個稀疏列稀疏列的優(yōu)勢提高存儲和訪問效率支持那些存儲稀疏值的超多列表稀疏列組ColumnSet可以將稀疏列看作一個組進行處理稀疏列組在表中定義的稀疏列的邏輯組CREATETABLEProduct

(IDint,Namevarchar(200),

ProductPropertiesXMLCOLUMN_SET

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

SQLServer2005Windows系統(tǒng)監(jiān)視器SQL跟蹤探查器動態(tài)管理視圖&動態(tài)管理函數(shù)性能儀表板報表活動監(jiān)視器SQLServer代理警報數(shù)據(jù)引擎優(yōu)化顧問查詢執(zhí)行計劃關系型數(shù)據(jù)庫性能

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

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