数据库性能监控与分析

数据库性能监控与分析 微博 陈能广州亿能测试技术服务有限公司 作为测试人员 需要关注数据库的哪些方面的知识 数据库整体架构 工作原理数据库性能监控和分析方法数据库性能优化常用方法 SQLServer数据库性能监控方法 LoadRunner 计数器PerfMonSQLServer自带监控方法第三方监控工具 SQLServer自带监控方法 动态管理视图DMV 系统表SQLServerProfiler 基于等待时间的性能分析PerformancetroubleshootingbasedonwaittimesSELECTwait type SUM wait time ms 1000 AS wait time s FROMsys dm os wait statsDOWSWHEREwait typeNOTIN SLEEP TASK BROKER TASK STOP SQLTRACE BUFFER FLUSH CLR AUTO EVENT CLR MANUAL EVENT LAZYWRITER SLEEP GROUPBYwait typeORDERBYSUM wait time ms DESC 有利于诊断方向的选取 CPU问题的症兆 CXPACKETSOS SCHEDULER YIELD CXPACKET 当为SQL查询创建一个并行操作时 会有多个线程去执行这个查询 每个查询处理不同的数据集或行集 因为某些原因 一个或多个线程滞后 而产生了CXPACKET等待状态 有一个组织 协调 organizer coordinator 线程 Thread0 它需要等待所有线程完成并聚合数据来呈现给客户端 组织线程必须等待所有线程完成处理才能进行下一步 由于组织线程等待缓慢的线程完成处理所产生的等待 就叫CXPACKET等待 可调的参数 costthresholdforparallelism 创建和运行并行查询计划的阈值 SOS SCHEDULER YIELD Session的Swapping产生SOS SCHEDULER YIELD等待事件SOS SCHEDULER YIELD的等待值偏高 那么说明有cpu密集型查询 需要优化sql或者增加cpu 结合sys dm exec query stats定位CPU消耗高的SQL SELECTTOP3total worker time execution count total worker time execution countAS AvgCPUTime CASEWHENdeqs statement start offset 0ANDdeqs statement end offset 1THEN seeobjectTextcolumn ELSE query CHAR 13 CHAR 10 SUBSTRING execText text deqs statement start offset 2 CASEWHENdeqs statement end offset 1THENDATALENGTH execText text ELSEdeqs statement end offsetEND deqs statement start offset 2 ENDASqueryTextFROMsys dm exec query statsdeqsCROSSAPPLYsys dm exec sql text deqs plan handle ASexecTextORDERBYdeqs total worker timeDESC 从Connections Sessions和Requests从手调查 sys sysprocessessys dm exec connectionssys dm exec sessionssys dm exec requestssys dm tran session transactions 谁连接到数据库了 GetacountofSQLconnectionsbyIPaddressSELECTdec client net address des program name des host name des login name COUNT dec session id ASconnection countFROMsys dm exec sessionsASdesINNERJOINsys dm exec connectionsASdecONdes session id dec session id WHERELEFT des host name 2 WK GROUPBYdec client net address des program name des host name des login name HAVINGCOUNT dec session id 1ORDERBYdes program name dec client net address 这些连接正在执行什么SQL语句 SELECTdec client net address des host name dest textFROMsys dm exec sessionsdesINNERJOINsys dm exec connectionsdecONdes session id dec session idCROSSAPPLYsys dm exec sql text dec most recent sql handle dest WHEREdes program nameLIKE MicrosoftSQLServerManagementStudio ORDERBYdes program name dec client net address Sessionsthatareopenbuthavebeeninactiveformorethan5days DECLARE days oldSMALLINTSELECT days old 5SELECTdes session id des login time des last request start time des last request end time des status des program name des cpu time des total elapsed time des memory usage des total scheduled time des total elapsed time des reads des writes des logical reads des row count des is user processFROMsys dm exec sessionsdesINNERJOINsys dm tran session transactionsdtstONdes session id dtst session idWHEREdes is user process 1ANDDATEDIFF dd des last request end time GETDATE days oldANDdes status Running ORDERBYdes last request end time 连接泄漏问题 目前连接正在执行什么SQL语句 Retrievingthetextforacurrentlyexecutingadhocquery SELECTdest text dest dbid dest objectidFROMsys dm exec requestsASderCROSSAPPLYsys dm exec sql text der sql handle ASdestWHEREsession id spid Retrievingthetextforacurrentlyexecutingbatch SELECTdest textFROMsys dm exec requestsASderCROSSAPPLYsys dm exec sql text der sql handle ASdestWHEREsession id spidANDtextLIKE waitfor ReturningthetextofanexecutingstoredprocedureSELECTdest dbid dest objectid dest encrypted dest textFROMsys dm exec requestsASderCROSSAPPLYsys dm exec sql text der sql handle ASdestWHEREobjectid object id test p WhoisrunningwhatatthisinstantSELECTdest textAS Commandtext des login time des host name des program name der session id dec client net address der status mand DB NAME der database id ASDatabaseNameFROMsys dm exec requestsderINNERJOINsys dm exec connectionsdecONder session id dec session idINNERJOINsys dm exec sessionsdesONdes session id der session idCROSSAPPLYsys dm exec sql text sql handle ASdestWHEREdes is user process 1 Whoisrunningwhat Sp who2 Abettersp who2 SELECTdes session id des status des login name des HOST NAME der blocking session id DB NAME der database id ASdatabase name mand des cpu time des reads des writes dec last write des program name der wait type der wait time der last wait type der wait resource CASEdes transaction isolation levelWHEN0THEN Unspecified WHEN1THEN ReadUncommitted WHEN2THEN ReadCommitted WHEN3THEN Repeatable WHEN4THEN Serializable WHEN5THEN Snapshot ENDAStransaction isolation level OBJECT NAME dest objectid der database id ASOBJECT NAME SUBSTRING dest text der statement start offset 2 CASEWHENder statement end offset 1THENDATALENGTH dest text ELSEder statement end offsetEND der statement start offset 2 AS executingstatement deqp query planFROMsys dm exec sessionsdesLEFTJOINsys dm exec requestsderONdes session id der session idLEFTJOINsys dm exec connectionsdecONdes session id dec session idCROSSAPPLYsys dm exec sql text der sql handle destCROSSAPPLYsys dm exec query plan der plan handle deqpWHEREdes session id SPIDORDERBYdes session id 查找正在执行的最耗CPU的SQL并取出执行计划进行分析 RequestsbyCPUconsumption SELECTder session id DB NAME der database id ASdatabase name deqp query plan SUBSTRING dest text der statement start offset 2 CASEWHENder statement end offset 1THENDATALENGTH dest text ELSEder statement end offsetEND der statement start offset 2 AS statementexecuting der cpu time der granted query memory der wait time der total elapsed time der readsFROMsys dm exec requestsderINNERJOINsys dm exec sessionsdesONdes session id der session idCROSSAPPLYsys dm exec sql text der sql handle destCROSSAPPLYsys dm exec query plan der plan handle deqpWHEREdes is user process 1ANDder session id spidORDERBYder cpu timeDESC ORDERBYder granted query memoryDESC ORDERBYder wait timeDESC ORDERBYder total elapsed timeDESC ORDERBYder readsDESC 深入定位SQL性能问题 分析执行计划 执行计划的重用情况调查调查重用度最高的查询执行计划及SQL调查具体某个存储过程的执行计划重用情况调查重用度低的SQL调查资源消耗高的SQL AnoverviewofplanreuseSELECTMAX CASEWHENusecountsBETWEEN10AND100THEN 10 100 WHENusecountsBETWEEN101AND1000THEN 101 1000 WHENusecountsBETWEEN1001AND5000THEN 1001 5000 WHENusecountsBETWEEN5001AND10000THEN 5001 10000 ELSECAST usecountsASVARCHAR 100 END ASusecounts COUNT AScountInstanceFROMsys dm exec cached plansGROUPBYCASEWHENusecountsBETWEEN10AND100THEN50WHENusecountsBETWEEN101AND1000THEN500WHENusecountsBETWEEN1001AND5000THEN2500WHENusecountsBETWEEN5001AND10000THEN7500ELSEusecountsENDORDERBYCASEWHENusecountsBETWEEN10AND100THEN50WHENusecountsBETWEEN101AND1000THEN500WHENusecountsBETWEEN1001AND5000THEN2500WHENusecountsBETWEEN5001AND10000THEN7500ELSEusecountsENDDESC Investigatingthemost usedplansSELECTTOP2WITHTIESdecp usecounts decp cacheobjtype decp objtype deqp query plan dest textFROMsys dm exec cached plansdecpCROSSAPPLYsys dm exec query plan decp plan handle ASdeqpCROSSAPPLYsys dm exec sql text decp plan handle ASdestORDERBYusecountsDESC ExaminingplanreuseforasingleprocedureSELECTusecounts cacheobjtype objtype OBJECT NAME dest objectid FROMsys dm exec cached plansdecpCROSSAPPLYsys dm exec sql text decp plan handle ASdestWHEREdest objectid OBJECT ID ANDdest dbid DB ID ORDERBYusecountsDESC 调查具体某个存储过程的执行计划重用情况 Examiningsingle useplansinthecache Findsingle use ad hocqueriesthatarebloatingtheplancacheSELECTTOP 100 text cp size in bytesFROMsys dm exec cached plansAScpCROSSAPPLYsys dm exec sql text plan handle WHEREcp cacheobjtype CompiledPlan ANDcp objtype Adhoc ANDcp usecounts 1ORDERBYcp size in bytesDESC 对于只重用1次的SQL 要注意是否未正确参数化导致 FindingtheCPU intensivequeriesSELECTTOP3total worker time execution count total worker time execution countAS AvgCPUTime CASEWHENdeqs statement start offset 0ANDdeqs statement end offset 1THEN seeobjectTextcolumn ELSE query CHAR 13 CHAR 10 SUBSTRING execText text deqs statement start offset 2 CASEWHENdeqs statement end offset 1THENDATALENGTH execText text ELSEdeqs statement end offsetEND deqs statement start offset 2 ENDASqueryTextFROMsys dm exec query statsdeqsCROSSAPPLYsys dm exec sql text deqs plan handle ASexecTextORDERBYdeqs total worker timeDESC 关注执行次数 平均CPU时间 Investigatinglogicalreadsperformedbycachedstoredprocedures TopCachedSPsByTotalLogicalReads SQL2008only LogicalreadsrelatetomemorypressureSELECTTOP 25 p nameAS SPName deps total logical readsAS TotalLogicalReads deps total logical reads deps execution countAS AvgLogicalReads deps execution count ISNULL deps execution count DATEDIFF Second deps cached time GETDATE 0 AS Calls Second deps total elapsed time deps total elapsed time deps execution countAS avg elapsed time deps cached timeFROMsys proceduresASpINNERJOINsys dm exec procedure statsASdepsONp object id deps object id WHEREdeps database id DB ID ORDERBYdeps total logical readsDESC 存储过程的逻辑读多的话跟内存压力有关系 从Transaction角度调查性能问题 锁 阻塞检测sys dm tran locks AnuncommittedupdateoftheProductiontableinAdventureWorks BEGINTRANSACTIONUPDATE Production ProductCategory SET Name Parts WHERE Name Components ROLLBACKTRANSACTION AsimplequeryagainsttheProductCategorytable whichwillbeblocked SELECT FROM Production ProductCategory 检测锁SELECT resource type DB NAME resource database id AS DatabaseName CASEWHENDTL resource typeIN DATABASE FILE METADATA THENDTL resource typeWHENDTL resource type OBJECT THENOBJECT NAME DTL resource associated entity id DTL resource database id WHENDTL resource typeIN KEY PAGE RID THEN SELECTOBJECT NAME object id FROMsys partitionsWHEREsys partitions hobt id DTL resource associated entity id ELSE Unidentified ENDASrequested object name request mode resource description FROMsys dm tran locksDTLWHEREDTL resource type DATABASE Whichsessionsarecausingblockingandwhatstatementaretheyrunning SELECTDTL request session id AS session id DB NAME DTL resource database id AS Database DTL resource type CASEWHENDTL resource typeIN DAT