亿能测试性能监控工具quest central5.0学习笔记

QuestCentralforQuestCentralforSQLServerincludesmanyusefulfeaturesforSQLServerdevelopersandDBAs.Itscomponentsinclude:DatabaseAdministrationforSQLPerformanceDiagnosticsforSQLKnowledgeExpertforSQLSpaceManagementforSQL ysisforSQL ysisforSQLQuestCentralforSQLServer-SQLWithDatabaseAdministration,youEdit,test,save,andrunSQLstatementsandBrowseSQLManageandeditdatabaseSpaceSpaceManagementforSQLServer™isaneasy-to-usesolutionforoptimizingspaceuseandcapacityinSQLServerinstancesandtheirdatabases.Graphicdiagnostictoolsallowyoutoquicklydetecthighfragmentationandlowscan-density—space-useproblemsthatimpactI/Operformance.Automatedreorgtoolsletyoueasilyresolvetheseproblemsforimprovedperformanceandefficientuseofspace.An ctivecapacityreportprovidesmultipleoptionsforprojectingfuturespaceneedsbasedonhistoricgrowthtrends. DatabaseysisistheQuestCentraldatabasetuningcomponentforSQLServerdatabases.ItisdesignedtoyzeSQLServerdatabasesusingspeciallydesignedperformancerules.Thisysisgeneratesreportsandinformationyoucanusetoimproveandmaintainhealthydatabaseperformance.WithDatabaseysisyouSpecifytheperformancerulesyouwantDatabase ysistousewhenityzesadatabase.Run Viewandprinttheresultsofdatabase yses,togetherwithsuggestionsonimprovingdatabaseperformance. SQLAsperformancerequirementsandthecomplexityofdatabaseapplicationsincrease,moreandmoredemandiscedondevelopment,QA,andproductionteamstoproduceandmaintaintop-performingapplications.AmajorfactorindatabaseapplicationperformanceisSQL,whichisresponsibleforupto80%ofapplicationperformanceproblems.ThishighlightsthesignificanceofSQLtuningandtheimportanceofoptimizingyourSQLstatementstopreventdatabaseperformanceproblems.OptimizingyourSQLcodeoffersimmediateandbestpossibleROIintermsofproductivity.QuestCentralSQLServer-SQLTuningizescurrentITresourcesandestablishesbenarksandbestperformancestandardswithinanorganization.QuestCentralSQLServer-SQLTuningassuresoptimalSQLperformancebyautomatingtheprocessofidentifyingproblematicSQL,rewriting,andbenarkingSQLforumapplicationperformance.QuestCentralSQLServer-SQLTuningconsistsoftheSQLTheSQLScannerisanon-intrusivewaytoidentifySQLstatementsfromsourcecodeanddatabaseobjectswithoutexecution.OncetheSQLstatementsareidentified,theSQLScanneryzesandcategorizesthemaccordingto edlevelsofperformanceproblems.SyntacticalSQLTheSyntacticalSQLOptimizerautomatestheoptimizingofSQLstatements.ItfirstyzesthesourceSQLstatementandthenusesArtificialInligence(AI)toreproducealistofsemanticallyequivalentandsyntacticallycorrectSQLstatements.BytestrunningtheseSQLstatements,itisthenpossibletoidentifywhichSQLstatementbestsuitstheneedsofyourdatabaseenvironment.BenarkSQLTheexecutionoftheSQLstatementsenablesyoutotestruntheoriginalandoptimizedSQLstatementstoselectwhichSQLstatementgivesthebestperformance.ThesetimeshelpyouidentifywhichSQLstatementismostsuitablefortheneedsyourdatabaseapplicationIdentificationofproblematicOptimizetheSQLBenarktheSQLImproveSQLcodingskillsandoptimizationRecepoorperformingSQL作用。对于同一条件下的SQL语句写法有很多，其中一些写法往往对性能又有很大影响。但是每个人掌握SQL语言的水平不同，如何才能保证写出高性能的SQLSQL这样一款工具:SQLTuningforSQLServer。SQLTuningSQLTurning是Quest公司的QuestCentral软件中的一个工具QuestCentral（1）是一款集成化、图形化、跨平台的数据库管理解决Oracle、DB2SQLserver?数据库管理?数据库（Monitoring?数据库诊断(Spotlight?数据库分析(Database?SQL(SQL?空间管理(Space?压力测试(Load?数据生成(Data?PL/SQL开发?专家建议(KnowledgeSQL(SQLTuningforSQLServer)图 questcentral使用SQLTuning化下面我们用SQLServerNorthwindSQLTuningSQL。QuestCentralDatabase”树上选择“SQLServer”，然后在下方出现的“Tools”框中选择“SQLTuning”选项，打开“LanchSQLTuningforSQLServerConnections”框（图2）。我们在这里建立数据2框双击“NewConnection”图标，在弹出窗口中输入数据库的信息，单击SQL在打开窗口的“OriangalSQL”SQLselectDISTINCT from[OrderDetails]od1,[OrderDetails]od2,Orderso1,o2,Customersc,Productswhereod1.UnitPrice<>od2.UnitPriceandod1.ProductID=od2.ProductIDandod1.OrderID=o1.OrderIDandando1.CustomerID=o2.CustomerIDando1.CustomerID=C.CustomerIDSQL，SQLTuningSQL示到界面上（3）3SQL现在我们点击上的“OptimizeStatement”按钮,让SQLTuning开始优化SQL，完成SQLTuning34SQL等价的优化方案（44SQL拉菜单，选择“ExecuteSelectedSQL运行完成后,点击界面左方的“Tuning52%（5“TuningResolutionSQLSQL2SQL语句，SQLTuning会将它们之间的不同之处以SQL语句的执行计划的不同，来了解其中的差异（66“CompareScenariosSQLTuningSQL优化工具的出现，为我们节省出大量的时间和精力。借SQL语句将不再是难事。AutoTuneSQLTheSQLScannerextractsSQLstatementsembeddedindatabaseobjects,textandbinaryfiles,SQLServerProfilertracefiles/tables,andapplicationsourcecodes.ItretrievesandyzestheexecutionnsfortheextractedSQLstatements. ItthencategorizestheSQLstatementsaccordingtothecomplexityoftheexecutionnandwhetherithasthecharacteristicsthattypicallycauseperformanceproblems.TheSQLScannerallowsyoutoquicklyreviewSQLstatementsinexistingcodeanddetectpotentialSQLperformanceproblems.Withthisapproach,theSQLScannerallowsyoutobeproactiveinthedetectionofperformanceproblems.Thetaskofextracting,reviewing,andyzingmanySQLstatementsissimplifiedandautomatedwiththeSQLScanner.ThisprocessidentifiestheSQLstatementsthatneedtobeoptimizedwithoutphysicalexecutionoftheapplicationsormanualintervention.DatabaseapplicationstypicallycontainthousandsofSQLstatements.WithouttheSQLScanner,youhavetofindandextracteachSQLstatementmanually—averytediousandtime-consumingtask.OncetheSQLstatementshavebeenextracted,youneedtoyzethenofeachSQLstatement toseeiftheexecutionnrepresentsapotentialperformanceproblem.TheSQLScannerrelievesyouofthistedioustask.OncetheproblematicSQLstatementshavebeenidentified,youcancopythesestatementstotheSQLOptimizationConsoletodeterminethebestsolution.SendtoSQLAutoSELECTTotal=SUM(ty)FROMProductsP,[OrderDetails]OD,OrdersO,CustomersCWHEREC.CustomerID=@CustomerIDANDC.CustomerID=O.CustomerIDANDO.OrderID=OD.OrderIDANDOD.ProductID=P.ProductIDGROUPBYProductNameSELECTTotal=SUM(ty)FROMProductsP,[OrderDetails]OD,OrdersO,CustomersCWHEREC.CustomerID>=@CustomerIDANDC.CustomerID<=@CustomerIDANDC.CustomerID=O.CustomerIDANDCOALESCE(O.OrderID,O.OrderID)=ANDOD.ProductID=P.ProductIDGROUPBYProductNameCOALESCE(expression[,...n]nexpression返回。NULLCOALESCENULLCOALESCE(expression1,...n)CASEWHEN(expression1ISNOTNULL)THENWHEN(expressionNISNOTNULL)THENexpressionNELSENULLwages表：hourly_wage、salarycommission。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的工资总额，请使用COALESCE函数接受在hourly_wage、salary和commission中找到的SETNOCOUNTONUSEIFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME='wages') identity, INSERTwagesVALUES(20.00,NULL,NULL,NULL)INSERTwagesVALUES(30.00,NULL,NULL,NULL)INSERTwagesVALUES(40.00,NULL,NULL,NULL)INSERTwagesVALUES(NULL,10000.00,NULL,NULL)INSERTwagesVALUES(NULL,20000.00,NULL,NULL)INSERTwagesVALUES(NULL,30000.00,NULL,NULL)INSERTwagesVALUES(NULL,40000.00,NULL,NULL)INSERTwagesVALUES(NULL,NULL,15000,3)INSERTwagesVALUES(NULL,NULL,14000,4)SETNOCOUNTOFFSELECTCAST(COALESCE(hourly_wage*40*52,commission*num_sales)ASmoney)AS'TotalSalary'FROMwages(12row(s)SyntacticalSQLOptimizerDuringoptimization,theSyntacticalSQLOptimizeremploysauniqueenginethatusesArtificialInligencetoexecuteseveralSQLsyntaxrulestoproduceagroupofsemanticallyequivalentSQLstatements.ThefirststepofthisenginetransformstheoriginalSQLstatementandproducesagroupofalternativeSQLstatementswherethesyntaxwastransformedbutallalternativeSQLstatementsstillprovidedtheexactsameresults.Then,theSyntacticalSQLOptimizerrewriteseachnewlycreatedSQLstatementtoproduceanothergroupofalternatives.TheenginecontinuesrewritingeachalternativeuntilalltheSQLstatementscannotberewrittenanyfurtheroruntilasetofuser-definedquotasarereached.AftertheSyntacticalSQLOptimizerhasexhaustedrewritingthesyntaxoftheSQLstatement,theSQLServeroptimizationhintsareappliedtoeachoftheSQLalternativesuntilallhintshavebeenappliedtoalltheSQLalternativesoruntilasetofuser-definedquotashasbeenreached.OneofthesyntaxtransformationrulesisillustratedinthefollowingSELECTFROMWHEREtable_a.keyIN(SELECTtable_b.keyFROMtable_b)Iftable_b.keyisanindexedcolumn,thefollowingtransformationruleisapplied:SELECT*FROMWHEREEXISTS(SELECTFROMWHEREtable_b.key=AlthoughtheabovetwoSQLstatementsproducethesameresult,SQLServermayusetwodifferentexecutionnstoretrievethedata.ItisdifficulttodecidewhichSQLstatementwillrunfasterwithouttakingintoaccountthedatabasestructure,indexes,anddatavolume,soitisimportanttotesttheSQLalternativesinyourdatabaseenvironmenttodeterminethebestSQLalternative.BenarktheSQLTheonlywaytoknowwhichSQLstatementisthefastestistoactuallyrunit.UsetheExecutefunctiontobenarkthesourceandtheSQLalternativestoobtaintheruntimeinformationandlocatetheSQLalternativethatismostappropriatetoyourneeds.ImproveSQLcodingskillsandoptimizationUsetheCompareScenariolayouttoviewside-by-sidethealternativeSQLstatementsinordertolearnwhatalternateSQLsyntax plishesthesameresult.Also,viewtheexecutionnsside-by-sidetoimproveyourknowledgeofhowSQLServerexecutesaSQLstatementbasedontheSQLsyntax.Usethecontextsensitivehelpforeachkeywordintheexecutionntofurtheryourunderstandingofexecutionnsteps.RecepoorperformingOnceyouhavefoundthebestSQLforyourapplication,copytheSQLalternativetothesourcecode.YoucanalsocreateareportwiththealternativeSQLstatementsandexecutionns.For 目前版本7.0.1(June17, SpotlightForSQLServerFeaturesofSpotlightonSQLSomeofthemainfeaturesandbenefitsofSpotlightonSQLServerarethatProvidesavisualrepresentationofprocessflowswithinSQLServer,allowingyoutoobserveactualdatabaseandserveractivityinrealtime.Visuallyidentifiesbottlenecks,andprovidesextensivedrilldownDisysthedetailsofproblemareas,includingactivesessions,locks&deadlocks,diskI/O,andWindowsserverstatisticsforrapidproblemresolution.ProvidesvisualandaudiblewarningstoalertyouwhenperformancemetricsexceedacceptableProvidesdetailedinformationaboutspecificcomponentsthroughtheuseofdrilldowns,thereforeallowingyoutopinpointthesourceofproblems.Assessesthenormalrateofprocessflowsviaacalibrationprocess,andsetsthedisyspeedofthevisualindicatorsaccordingly.Hasaquickandsimpleinstallation注意被机上的RemoteRegistry服务要启动，否则不能获取Windows性能计数器CalibrationSQLServerThefollowingdiagramsummarizesthearchitectureofSQLServer.ThisarchitectureisthebasisforthedesignofthemainSpotlightonSQLServerwindow.ThenumberedlabelsintheSQLServerarchitecturediagramcorrespondwiththefollowing1SQL applicationsends"batches"ofSQLstatementstoSQLServerfor2SyntaxTheParserchecksthesyntaxoftheSQL3CacheTheParserchecksthesyntaxoftheSQLstatements,andcallstheSQLManagertoseeifSQLisalreadyintheProcedureCache.Ifamatchisfound,theOptimizeris4IfnomatchingSQLisfoundintheProcedureCache,theParsertranslatesthebatchintoaTreeandpassesittothe5TheOptimizertakestheQueryTreeandproducesanoptimal nbasedondataindexes,jointechniquesandCPU,I/O&memory6TheQueryExecutorrunstheExecutionn,often ctingheavilywiththeAccessManagerandTransaction7TheQueryExecutorpassesresultsbackto8ODSbufferstheresultsandreturnsthemto onlywhenthebufferfillsorthebatchSQLstatements9RowTheAc