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

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

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

and

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

Employees

where

LastName+’,’+FirstName=‘Davolio,Nancy進(jìn)行負(fù)向查詢Not

Exists、NotIN、NotLike、!=<>!>!<等在where語句中對(duì)數(shù)據(jù)字段使用函數(shù)Select*from[orderdetails]whereABS(quantity-100)<1使用OR運(yùn)算符使用OR做運(yùn)算符，則需要所有的字段都有可用的索引使用T-SQL的注意事項(xiàng)Select語法盡量不要傳回所有的數(shù)據(jù)表內(nèi)字段且不配置過濾條件若使用復(fù)合索引，則索引順序上的第一個(gè)字段才適合當(dāng)作過濾條件Distinct，Orderby等語法盡量到查詢結(jié)果需要時(shí)才使用UnionAll要比Union好若未將連接事務(wù)級(jí)別降低到ReadUncommited，或是通過鎖提示NOLOCK來降低阻塞的機(jī)會(huì)，最好配置SETLOCK_TIMEOUT選項(xiàng)，避免用戶無盡等待使用T-SQL的注意事項(xiàng)Insert、Delete和Update大量批處理數(shù)據(jù)操作時(shí)，無Log的行為一定比逐筆數(shù)據(jù)由兩次寫入（先記錄Log再寫數(shù)據(jù)庫）快。在Update和Delete采用Where子句時(shí)，記得條件也要符合SARGs格式查詢調(diào)優(yōu)選項(xiàng)使用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ù)用于驗(yàn)證強(qiáng)制計(jì)劃的有效性計(jì)劃指南支持XMLShowPlan參數(shù)為監(jiān)控強(qiáng)制計(jì)劃設(shè)計(jì)了新的事件類(PlanGuideSuccessful和PlanGuideUnsuccessful)以及性能計(jì)數(shù)器(SQLServerSQL

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

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

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

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

CEADFHGB123456781234567873186425

12345678硬盤相關(guān)設(shè)置硬盤恢復(fù)間隔數(shù)據(jù)庫檢查點(diǎn)(Checkpoint)事件發(fā)生的頻率檢查點(diǎn)的作用是把數(shù)據(jù)庫緩存中標(biāo)記為Dirty的數(shù)據(jù)頁面與日志頁面寫入硬盤數(shù)據(jù)壓縮主要目標(biāo)縮小數(shù)據(jù)倉庫事實(shí)標(biāo)的尺寸第二目標(biāo)增強(qiáng)查詢性能可以在單個(gè)表或索引上啟用支持分區(qū)需要在處理器資源和存儲(chǔ)及IO帶寬之間進(jìn)行取舍數(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存儲(chǔ)選項(xiàng)允許decimal數(shù)據(jù)以變長(zhǎng)方式存儲(chǔ)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將變長(zhǎng)的存儲(chǔ)機(jī)制擴(kuò)展到了所有定長(zhǎng)數(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還增加了前綴壓縮機(jī)制(行壓縮)：常見的前綴被存儲(chǔ)在頁面中的一個(gè)前綴列表中列中的值將利用前綴列表中的標(biāo)號(hào)進(jìn)行替代數(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還增加了字典壓縮機(jī)制(頁壓縮)：將常見的值編制成詞典存儲(chǔ)在頁中列中的常見值利用詞典中的標(biāo)號(hào)進(jìn)行替代對(duì)于正式的數(shù)據(jù)可以達(dá)到2-7倍的壓縮率壓縮率的大小依賴于數(shù)據(jù)值的模式Date_IDProduct_IDUnit_PriceAmount120071202MA_3587_132110.0010132210.00100014CF_7253_140.005015MA_3659_140.00400稀疏列應(yīng)用場(chǎng)合半結(jié)構(gòu)化數(shù)據(jù)：屬性集常見場(chǎng)合產(chǎn)品目錄不同類別的商品擁有不同的屬性集文檔管理系統(tǒng)用戶對(duì)文檔設(shè)置的自定義屬性GPS/地圖系統(tǒng)不同地圖標(biāo)記位置的屬性稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbNull值不占用空間，其他值則會(huì)產(chǎn)生2-4字節(jié)的額外開銷同時(shí)數(shù)據(jù)訪問效率會(huì)受到輕微影響(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個(gè)稀疏列稀疏列的優(yōu)勢(shì)提高存儲(chǔ)和訪問效率支持那些存儲(chǔ)稀疏值的超多列表稀疏列組ColumnSet可以將稀疏列看作一個(gè)組進(jìn)行處理稀疏列組在表中定義的稀疏列的邏輯組CREATETABLEProduct

(IDint,Namevarchar(200),

ProductPropertiesXMLCOLUMN_SET

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

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

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

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