技术手册-sql server性能调优

SQLServer能调优sqlserver2013-02-2515:481242(0)SQLServer性能调优SQLcpu相关的waiteventSignalwaittimeCXPACKET等待CMEMTHREAD等待missindexSARGadhoc

costthresholdforparallelismmaxdegreeofparallelismcpusqlserver中扮演了很重要的角色，虽然cpucpu1个或多个cpu满负荷运行，那么就要了。sqlserver对cpu的使用无处不在，所以如果cpu满负荷运cpucpu性能问题的很多，如内存不足，数据换进换出，cpu一路飙高。写操作性能很烂，索引建的不合适，sqlserver配置等问题都会引起cpu过高的问题。所以cpu性能盘查需要很和不管是什么问题的，对cpu的性能分析就是把问题到一个特定资源，可以使用perfmon，性能视图，还有sql来收源。研究cpu对于cpu压力的研究一般使用一下工具：perfmon，SQL，动态性能视Processor/%Privileged 核级别的cpu使用Processor/--用户几倍的cpuProcess(sqlservr.exe)/%ProcessorTime --某个进程的cpu用率SQLServer:SQLStatistics/Auto-Param Auto-SQLServer:SQLStatistics/Batch SQLServer:SQL SQLServer:PlanCache/CacheSQLSQL的具体用法就不多讲，很多人都已经会用了，SQL在某个时间点上用的sql。性能cpuwaitevent。cpu相关sqlserversys.dm_os_wait_statsSignalwaitwait_time_ms该等待类型所有等待时间。列中，是单纯的cpu等待。源的等待时间。可以表示sqlservercpu密集型查询。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，作为等待调度器的时间。下面三个waittype这三个和cpu压力关SOS_SCHEDULER_YIELDsqlservercpu，但是如果sys.dm_exec_requests或者sys.dm_os_waiting_tasks的或者增加cpu。CXPACKET当同步查询进程，workerCXPACKET等待，特别是发生dwsql比较少，并且有大量的并发查询可以减少执行时间。对dw来说是正常的，但是在oltp中大多数是小的sql和事务，如果发生大量的并发，会导致性能下降。CMEMTHREADCMEMTHREAD等待。调度队sys.dm_os_schedulers，视图主要的二个指标task，和可运行队列的长度。可运行队列内都是等待cputasktaskcurrent_tasks_countsleep或者SELECTscheduler_id,current_tasks_count,FROMsys.dm_os_schedulersWHEREscheduler_id< 如果可运行队列越长那么，signaltime的时间也就越长，就意味着可能cpucpu密集cpu2个性能视图，sys.dm_exec_query_stats花费的时间。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就不会被保留在cache中。如果你要全局的分析执行计划，请使用sql，而不要事情清空缓存，特别是在CPU使用率的创建几种missmissindex是照成大量cpu和io使用的状况之一，也是最常发生的状况。当前大量的非必须的数据参与到中，会照成cpu和io的极大浪费。那么就以adventureworks2008数据库作为例子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照成的。统计数据算也就确，这样就会导致优化器误判，估计的花费是低的但是并不一定实nestloop，keylookup，但是实际的数距很大那么就是统计数据丢失了，需要及时更新统计值。当然可以通过updatestatistics更新统计值，详细的用法可以参见联机文档。 SARG一不能使用索引，显而易见cpu飙高了，io堵塞了。隐式类型sqlserver的时候都是从低的优先级转化到高的优先级，比如如果一个是varchar一个是SELECTp.Name,p.LastName,FROMSales.Customer JOIN.ASp ONc.ID= WHEREAccountNumber=N'AW00029594'accountnumber就变成索引扫描了，我使用2008r2不是索引扫描。但是当我把accountnumber禁用掉之后，尽然和书上发的执行计划是一样的了，让我深深accountnumber禁用了而没发现呢？我sqlserver2000ok，20002008r2DECLARE@CustIDSET@CustID=SELECTCompanyNameFROMNorthWind.dbo.CustomersWHERECustomerID=这里要注意因为customers表的结构是nchar的所以在测试的时候先要修--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参数当sqlserver为过程，函数或者参数化查询创建执行计划的时候，会探测个batch中被重编译那么参数和变量都会被探测。下面是一个运行在Adventureworks数据库的例子最大日期是2011-7-8最小日期是2004-8-7.CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIMESELECTCustomerID,FROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEndCREATENONCLUSTEREDINDEXONSales.SalesOrderHeader(ShipDate)接下来会运行2次这个过程第一次夸多年的，第二次就夸几天。并查看实际的DBCCEXECuser_GetCustomerShipDates'2001/07/08',EXECuser_GetCustomerShipDates'2001/07/10',|-- r].[SalesOrderID],0),N'***ERROR***')))这个是结果和书上的不一样。那么为什么为产生表扫描不是索引查找呢，因虽然是不合适，但是已经有执行计划在了内存里面，sqlserver就直接拿来用了，就照成了这个问题。开SET STATISTICSIOon表'SalesOrderHeader'。扫描计数1，逻辑700次，物理0次，读0 次，lob逻辑0 次，lob物理0 预读0次。那么把2个过程倒过来：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)描的行太多，如果假定现在树是33次你想想。表'SalesOrderHeader'。扫描计数1，逻辑17155次，物理0次预读0次，lob逻辑0次，lob物理0次，lob预读0次。比较一下夸多年的那个过程的逻辑读。标记41362--8r2cu2,sqlserver2005sp3cu9中才加入。先前过了如果开了参数 过程产生影响。当参数探测器被停用的时候4316是如何处理的呢，举个例子这里又一个列X有如下的值1,2,3,3,3,3,3,4,5,5，那么他 OPTIMIZEFORsqlserver2005OPTIMIZEFOR来优化查询CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIMESELECTCustomerID,FROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEndOPTION( OPTIMIZEFOR( @ShipDateStart= @ShipDateEnd= '2004/01/01'))OPTIMIZEFORsqlserverSQLServer2008OPTIMIZEFORUNKNOWN，sqlserver就不会再用参数探测的功能，它的功效和4316相同，所以这个方法是比中，但是就有一个问题过程的执行的花费就会变高。CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIME) SELECTCustomerIDFROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEnd如果过程中只需要一部分重新编译，那么就可以使用OPTION(PILE)选项放到查询中即可，相编译整个过程，这样会好些。CREATEPROCEDUREuser_GetCustomerShipDates@ShipDateStartDATETIME,@ShipDateEndDATETIMESELECTCustomerID,FROMWHEREShipDateBETWEEN@ShipDateStart AND@ShipDateEndOPTION( PILE)adhoc参数化查Adhocsqlserver的时候优化器还是会从cache查找合适的执行计划。adhoc费特别是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但是上面的例子太复杂了所以没办法。那就会有2个问题可以用perfmon来监视编译重编译的量SQLServer:SQLStatistics:SQLSQLServer:SQLStatistics:Auto-ParamSQLServer:SQLStatistics:FailedAuto-代码。如果不行那么只能设置sqlserver来调整修改源代关于修改源代码就不了，直接给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=强制性参ALTERDATABASEAdventureWorksSET以使用如下sql查询SELECTb.text,c.* CROSSAPPLYsys.dm_exec_sql_text(a.sql_handle) CROSS Optimizeforadadhoc第一次运行的时候sqlserverEXECsp_configure'showadvancedoptions',1EXECsp_configure'optimizeforadhocworkloads',1不合适的并发当一个查询被提交到sqlservercostthresholdforparallelismmaxdegreeofparallelismmaxdop对于并发的配置参数有2costthresholdforparallelism，maxdegreeofparallelism第一个是启用并发查询的阀值，第二个是最大并发数。当发生不1/2，或者减少1/4或者直接设置为1。当然这个是不理想的解决方案，最理想的解决方案是设置2个配置参数，到一个比较合理的值。costthresholdforcostthresholdforparallelism没超过就不启用。costthresholdforparallelism的默认值是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性能的空间。并发查询也需要考虑到内存的结构体系，在NUMA结构下，最大并发度设置在一个NUMA节点的可用经常。这样node之间就不会产生交互，因为node间的共享当然maxdegreeofparallelism sqlserver2008以上的版本还可以使用