技術(shù)手冊(cè)-sql server性能調(diào)優(yōu)

SQLServer能調(diào)優(yōu)sqlserver2013-02-2515:481242(0)SQLServer性能調(diào)優(yōu)SQLcpu相關(guān)的waiteventSignalwaittimeCXPACKET等待CMEMTHREAD等待missindexSARGadhoc

costthresholdforparallelismmaxdegreeofparallelismcpusqlserver中扮演了很重要的角色，雖然cpucpu1個(gè)或多個(gè)cpu滿負(fù)荷運(yùn)行，那么就要了。sqlserver對(duì)cpu的使用無(wú)處不在，所以如果cpu滿負(fù)荷運(yùn)cpucpu性能問(wèn)題的很多，如內(nèi)存不足，數(shù)據(jù)換進(jìn)換出，cpu一路飆高。寫(xiě)操作性能很爛，索引建的不合適，sqlserver配置等問(wèn)題都會(huì)引起cpu過(guò)高的問(wèn)題。所以cpu性能盤(pán)查需要很和不管是什么問(wèn)題的，對(duì)cpu的性能分析就是把問(wèn)題到一個(gè)特定資源，可以使用perfmon，性能視圖，還有sql來(lái)收源。研究cpu對(duì)于cpu壓力的研究一般使用一下工具：perfmon，SQL，動(dòng)態(tài)性能視Processor/%Privileged 核級(jí)別的cpu使用Processor/--用戶幾倍的cpuProcess(sqlservr.exe)/%ProcessorTime --某個(gè)進(jìn)程的cpu用率SQLServer:SQLStatistics/Auto-Param Auto-SQLServer:SQLStatistics/Batch SQLServer:SQL SQLServer:PlanCache/CacheSQLSQL的具體用法就不多講，很多人都已經(jīng)會(huì)用了，SQL在某個(gè)時(shí)間點(diǎn)上用的sql。性能cpuwaitevent。cpu相關(guān)sqlserversys.dm_os_wait_statsSignalwaitwait_time_ms該等待類(lèi)型所有等待時(shí)間。列中，是單純的cpu等待。源的等待時(shí)間?？梢员硎緎qlservercpu密集型查詢。SELECTSUM(signal_wait_time_ms)ASTotalSignalWaitTime,(SUM(CAST(signal_wait_time_msASNUMERIC(20,2)))/SUM(CAST(wait_time_msASNUMERIC(20,2)))* 100)ASPercentageSignalWaitsOfTotalTimeFROMsessionwaiteventsignalwaittime，因此要減去signal_wait_time，作為等待調(diào)度器的時(shí)間。下面三個(gè)waittype這三個(gè)和cpu壓力關(guān)SOS_SCHEDULER_YIELDsqlservercpu，但是如果sys.dm_exec_requests或者sys.dm_os_waiting_tasks的或者增加cpu。CXPACKET當(dāng)同步查詢進(jìn)程，workerCXPACKET等待，特別是發(fā)生dwsql比較少，并且有大量的并發(fā)查詢可以減少執(zhí)行時(shí)間。對(duì)dw來(lái)說(shuō)是正常的，但是在oltp中大多數(shù)是小的sql和事務(wù)，如果發(fā)生大量的并發(fā)，會(huì)導(dǎo)致性能下降。CMEMTHREADCMEMTHREAD等待。調(diào)度隊(duì)sys.dm_os_schedulers，視圖主要的二個(gè)指標(biāo)task，和可運(yùn)行隊(duì)列的長(zhǎng)度?？蛇\(yùn)行隊(duì)列內(nèi)都是等待cputasktaskcurrent_tasks_countsleep或者SELECTscheduler_id,current_tasks_count,FROMsys.dm_os_schedulersWHEREscheduler_id< 如果可運(yùn)行隊(duì)列越長(zhǎng)那么，signaltime的時(shí)間也就越長(zhǎng)，就意味著可能cpucpu密集cpu2個(gè)性能視圖，sys.dm_exec_query_stats花費(fèi)的時(shí)間。SELECTTOP 10SUBSTRING(ST.text,(QS.statement_start_offset/2 )+ ((CASEstatement_end_offsetWHEN- THENELSEEND- QS.statement_start_offset)/2 )+ ASstatement_text,execution_counttotal_worker_time/ 1000AS total_worker_time_ms,(total_worker_time/1000 )/execution_countASavg_worker_time_ms,total_logical_reads,total_logical_reads/ execution_countAS avg_logical_reads,total_elapsed_time/ 1000AS total_elapsed_time_ms,(total_elapsed_time/1000 )/execution_countASavg_elapsed_time_ms,FROM CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle) CROSSAPPLYsys.dm_exec_query_plan(qs.plan_handle)qpORDERBYtotal_worker_timeDESCcache丟失的情況。有一些查詢使用了pile就不會(huì)被保留在cache中。如果你要全局的分析執(zhí)行計(jì)劃，請(qǐng)使用sql，而不要事情清空緩存，特別是在CPU使用率的創(chuàng)建幾種missmissindex是照成大量cpu和io使用的狀況之一，也是最常發(fā)生的狀況。當(dāng)前大量的非必須的數(shù)據(jù)參與到中，會(huì)照成cpu和io的極大浪費(fèi)。那么就以adventureworks2008數(shù)據(jù)庫(kù)作為例子SELECTper.Name,per.LastName,p.Name,p.ProductNumber,OrderDate,LineTotal,FROMSales.SalesOrderHeaderAS INNERJOINSales.SalesOrderDetailsodONsoh.SalesOrderID= JOINProduction.Produ ONsod.ProductID=JOINSales.CustomerASc ONsoh.CustomerID=c.CustomerIDINNERJOIN.AS ONc.ID= WHERELineTotal> SQLServerparseandcompiletime:CPUtime=0ms,elapsedtime=0ms.SQLServerExecutionTimes:CPUtime=452ms,elapsedtime=458CREATENONCLUSTEREDINDEXidx_SalesOrderDetail_LineTotalONSales.SalesOrderDetail(LineTotal)SQLServerparseandcompileCPUtime=0ms,elapsedtime=0ms.SQLServerExecutionTimes:CPUtime=0ms,elapsedtime=8是missindex照成的。統(tǒng)計(jì)數(shù)據(jù)算也就確，這樣就會(huì)導(dǎo)致優(yōu)化器誤判，估計(jì)的花費(fèi)是低的但是并不一定實(shí)nestloop，keylookup，但是實(shí)際的數(shù)距很大那么就是統(tǒng)計(jì)數(shù)據(jù)丟失了，需要及時(shí)更新統(tǒng)計(jì)值。當(dāng)然可以通過(guò)updatestatistics更新統(tǒng)計(jì)值，詳細(xì)的用法可以參見(jiàn)聯(lián)機(jī)文檔。 SARG一不能使用索引，顯而易見(jiàn)cpu飆高了，io堵塞了。隱式類(lèi)型sqlserver的時(shí)候都是從低的優(yōu)先級(jí)轉(zhuǎn)化到高的優(yōu)先級(jí)，比如如果一個(gè)是varchar一個(gè)是SELECTp.Name,p.LastName,FROMSales.Customer JOIN.ASp ONc.ID= WHEREAccountNumber=N'AW00029594'accountnumber就變成索引掃描了，我使用2008r2不是索引掃描。但是當(dāng)我把a(bǔ)ccountnumber禁用掉之后，盡然和書(shū)上發(fā)的執(zhí)行計(jì)劃是一樣的了，讓我深深accountnumber禁用了而沒(méi)發(fā)現(xiàn)呢？我sqlserver2000ok，20002008r2DECLARE@CustIDSET@CustID=SELECTCompanyNameFROMNorthWind.dbo.CustomersWHERECustomerID=這里要注意因?yàn)閏ustomers表的結(jié)構(gòu)是nchar的所以在測(cè)試的時(shí)候先要修--WindowsCollationwillgetaCREATETABLE#T(col1varchar(10)COLLAatin1_General_CI_ASPRIMARYFROM#TWHEREcol1=--SQLCollationwillgetaCREATETABLE#T2(col1varchar(10)COLLATESQL_Latin1_General_CP1_CI_AIPRIMARYKEY);SELECT*FROMWHEREcol1=--YourCollationwillgetaSELECT*FROMWHEREcol1=DROPTABLE#TDROPTABLE#T2DROPTABLE參數(shù)當(dāng)sqlserver為過(guò)程，函數(shù)或者參數(shù)化查詢創(chuàng)建執(zhí)行計(jì)劃的時(shí)候，會(huì)探測(cè)個(gè)batch中被重編譯那么參數(shù)和變量都會(huì)被探測(cè)。下面是一個(gè)運(yùn)行在Adventureworks數(shù)據(jù)庫(kù)的例子最大日期是2011-7-8最小日期是2004-8-7.CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIMESELECTCustomerID,FROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEndCREATENONCLUSTEREDINDEXONSales.SalesOrderHeader(ShipDate)接下來(lái)會(huì)運(yùn)行2次這個(gè)過(guò)程第一次夸多年的，第二次就夸幾天。并查看實(shí)際的DBCCEXECuser_GetCustomerShipDates'2001/07/08',EXECuser_GetCustomerShipDates'2001/07/10',|-- r].[SalesOrderID],0),N'***ERROR***')))這個(gè)是結(jié)果和書(shū)上的不一樣。那么為什么為產(chǎn)生表掃描不是索引查找呢，因雖然是不合適，但是已經(jīng)有執(zhí)行計(jì)劃在了內(nèi)存里面，sqlserver就直接拿來(lái)用了，就照成了這個(gè)問(wèn)題。開(kāi)SET STATISTICSIOon表'SalesOrderHeader'。掃描計(jì)數(shù)1，邏輯700次，物理0次，讀0 次，lob邏輯0 次，lob物理0 預(yù)讀0次。那么把2個(gè)過(guò)程倒過(guò)來(lái)：DBCCEXECuser_GetCustomerShipDates'2001/07/10','2001/07/20'EXECuser_GetCustomerShipDates'2001/07/08','2004/01/01'|puteScalar(DEFINE:([AdventureWorks].[Sales].[SalesOrd[Expr1004])WITHUNORDEREDPREFETCH)OrderHeader].[ShipDate]>=[@ShipDateStart]AND[AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate]<=[@ShipDateEnd])ORDERED SalesOrderHeader].[SalesOrderID],0),N'***ERROR***')))|--SEEK:([Bmk1000]=[Bmk1000])LOOKUPORDEREDFORWARD)描的行太多，如果假定現(xiàn)在樹(shù)是33次你想想。表'SalesOrderHeader'。掃描計(jì)數(shù)1，邏輯17155次，物理0次預(yù)讀0次，lob邏輯0次，lob物理0次，lob預(yù)讀0次。比較一下夸多年的那個(gè)過(guò)程的邏輯讀。標(biāo)記41362--8r2cu2,sqlserver2005sp3cu9中才加入。先前過(guò)了如果開(kāi)了參數(shù) 過(guò)程產(chǎn)生影響。當(dāng)參數(shù)探測(cè)器被停用的時(shí)候4316是如何處理的呢，舉個(gè)例子這里又一個(gè)列X有如下的值1,2,3,3,3,3,3,4,5,5，那么他 OPTIMIZEFORsqlserver2005OPTIMIZEFOR來(lái)優(yōu)化查詢CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIMESELECTCustomerID,FROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEndOPTION( OPTIMIZEFOR( @ShipDateStart= @ShipDateEnd= '2004/01/01'))OPTIMIZEFORsqlserverSQLServer2008OPTIMIZEFORUNKNOWN，sqlserver就不會(huì)再用參數(shù)探測(cè)的功能，它的功效和4316相同，所以這個(gè)方法是比中，但是就有一個(gè)問(wèn)題過(guò)程的執(zhí)行的花費(fèi)就會(huì)變高。CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIME) SELECTCustomerIDFROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEnd如果過(guò)程中只需要一部分重新編譯，那么就可以使用OPTION(PILE)選項(xiàng)放到查詢中即可，相編譯整個(gè)過(guò)程，這樣會(huì)好些。CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIMESELECTCustomerID,FROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEndOPTION( PILE)adhoc參數(shù)化查Adhocsqlserver的時(shí)候優(yōu)化器還是會(huì)從cache查找合適的執(zhí)行計(jì)劃。adhoc費(fèi)特別是CPU。SELECT FROM ASINNERJOINSales.SalesOrderDetailAS ONsoh.SalesOrderID=sod.SalesOrderIDWHEREsoh.SalesOrderNumber= SELECTsoh.SalesOrderNumber,FROM ASINNERJOINSales.SalesOrderDetailAS ONsoh.SalesOrderID=sod.SalesOrderIDWHEREsoh.SalesOrderNumber= SELECTsoh.SalesOrderNumber,FROM ASINNERJOINSales.SalesOrderDetailAS ONsoh.SalesOrderID=sod.SalesOrderIDWHEREsoh.SalesOrderNumber= adhoc但是上面的例子太復(fù)雜了所以沒(méi)辦法。那就會(huì)有2個(gè)問(wèn)題可以用perfmon來(lái)監(jiān)視編譯重編譯的量SQLServer:SQLStatistics:SQLSQLServer:SQLStatistics:Auto-ParamSQLServer:SQLStatistics:FailedAuto-代碼。如果不行那么只能設(shè)置sqlserver來(lái)調(diào)整修改源代關(guān)于修改源代碼就不了，直接給demo自己看mandType=mandText=@"SELECTsoh.SalesOrderNumber,FROMSales.SalesOrderHeaderASsohINNERJOINSales.SalesOrderDetailASsodONsoh.SalesOrderID=sod.SalesOrderIDWHEREsoh.SalesOrderNumber='"+txtSalesOrderNo.Text+"'";dtrSalesOrders=cmd.ExecuteReader();dtrSalesOrders.Close();mandType=mandText=@"SELECTsoh.SalesOrderNumber,FROMSales.SalesOrderHeaderASsohINNERJOINSales.SalesOrderDetailASsodONsoh.SalesOrderID=sod.SalesOrderIDWHEREsoh.SalesOrderNumber=@SalesOrderNo";cmd.Parameters.Add("@SalesOrderNo",SqlDbType.NVarChar,50);cmd.Parameters["@SalesOrderNo"].Value=txtSalesOrderNo.Text;dtrSalesOrders=強(qiáng)制性參ALTERDATABASEAdventureWorksSET以使用如下sql查詢SELECTb.text,c.* CROSSAPPLYsys.dm_exec_sql_text(a.sql_handle) CROSS Optimizeforadadhoc第一次運(yùn)行的時(shí)候sqlserverEXECsp_configure'showadvancedoptions',1EXECsp_configure'optimizeforadhocworkloads',1不合適的并發(fā)當(dāng)一個(gè)查詢被提交到sqlservercostthresholdforparallelismmaxdegreeofparallelismmaxdop對(duì)于并發(fā)的配置參數(shù)有2costthresholdforparallelism，maxdegreeofparallelism第一個(gè)是啟用并發(fā)查詢的閥值，第二個(gè)是最大并發(fā)數(shù)。當(dāng)發(fā)生不1/2，或者減少1/4或者直接設(shè)置為1。當(dāng)然這個(gè)是不理想的解決方案，最理想的解決方案是設(shè)置2個(gè)配置參數(shù)，到一個(gè)比較合理的值。costthresholdforcostthresholdforparallelism沒(méi)超過(guò)就不啟用。costthresholdforparallelism的默認(rèn)值是55costthresholdforparallelism閥值很重要SETTRANSACTIONISOLATIONLEVELREAD MITTED;WITHXMLNAMESPACES(DEFAULT'/sqlserver/2004/07/showplan')SELECTquery_planASCompleteQueryPlan, 'VARCHAR(4000)')AS, ASStatementOptimizationLevel, ASStatementSubTreeCost,n.query('.')ASParallelSubTreeXML,ecp.usecounts,FROM ASCROSSAPPLYsys.dm_exec_query_plan(plan_handle) ASeqpCROSSAPPLYquery_plan.nodesASqn( WHEREn.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]')=thresholdforparallelism。maxdegreeofsqlserver3maxdegreeofsys.dm_os_waiting_taskswaitevent外的io，給你提示io性能的空間。并發(fā)查詢也需要考慮到內(nèi)存的結(jié)構(gòu)體系，在NUMA結(jié)構(gòu)下，最大并發(fā)度設(shè)置在一個(gè)NUMA節(jié)點(diǎn)的可用經(jīng)常。這樣node之間就不會(huì)產(chǎn)生交互，因?yàn)閚ode間的共享當(dāng)然maxdegreeofparallelism sqlserver2008以上的版本還可以使用