三通it學院-performance tuning distilled技術來就夠了

學it技術來三通itSQLServer2008QueryPerformanceTuningGrantFritcheyandSajal學it技術來三通itSQLServer2008QueryPerformanceTuningDistilledCopyright?2009byGrantFritcheyandSajalDamAllrights.Nopartofthisworkmaybereproducedortransmittedinanyformorbyanymeans,electronicormechanical,includingphotocopying,recording,orbyanyinformationstorageorretrievalsystem,withoutthepriorwrittenpermissionofthecopyrightownerandthepublisher.ISBN-13(pbk):978-1-4302-1902-ISBN-13(electronic):978-1-4302-1903-PrintedandboundintheUnitedStatesofAmerica98765432Trademarkednamesmayappearinthisbook.Ratherthanuseatrademarksymbolwitheveryoccurrenceofatrademarkedname,weusethenamesonlyinaneditorialfashionandtothebenefitofthetrademarkowner,withnointentionofinfringementofthetrademark.LeadEditor:JonathanGennickEditorialBoard:ClayAndres,SteveAnglin,MarkBeckner,EwanBuckingham,TonyGaryCornell,JonathanGennick,MiceLowman,MatthewMoodie,JeffreyPepper,FrankPohlmann,BenRenow-Clarke,DominicShakeshaft,MattWade,TomWelshCopyEditor:KimWimpsettAssociateProductionDirector:KariBrooks-CoponyProductionEditor:KellyWinquistCompositor:PatrickCunninghamProofreader:AprilEddyIndexer:John:AprilCoverDesigner:KurtKramesDistributedtothebooktradeworldwidebySpringer-VerlagNewYork,Inc.,233SpringStreet,6thFloor,NewYork,NY10013.Phone1-800-SPRINGER,fax ,e-mailkn`ano)ju<olnejcan)o^i*_ki,orvisitdppl6++sss*olnejcankjheja*_ki.Forinformationontranslations,pleasecontactApressdirectlyat2855egraphAvenue,Suite600,Berkeley,CA94705. , ,e-mailejbk<]lnaoo*_ki,orvisitApressandfriendsofEDbooksmaybepurchasedinbulkforacademic,corporate,orpromotionaluse.eBookversionsandlicensesarealsoavailableformosttitles.Formoreinformation,referenceourSpecialBulkSales–eBookLicensingwebpageatdppl6++sss*]lnaoo*_ki+ejbk+^qhgo]hao.Theinformationinthisbookisdistributedonan“asis”basis,withoutwarranty.Althougheveryprecau-tionhasbeentakeninthepreparationofthiswork,neithertheauthor(s)norApressshallhaveanyliabilitytoany orentitywithrespecttoanylossordamagecausedorallegedtobecauseddirectlyorindi-rectlybytheinformationcontainedinthiswork.Thesourcecodeforthisbookisavailabletoreadersat學it學ititContentsataAboutthe AbouttheTechnical CHAPTER SQLQueryPerformance CHAPTER System CHAPTER SQLQuery CHAPTER Index CHAPTER DatabaseEngineTuning CHAPTER Bookmark CHAPTER CHAPTER CHAPTER n CHAPTER Stored CHAPTER Query CHAPTER CHAPTER Deadlock CHAPTER Cursor CHAPTER DatabaseWorkload CHAPTER SQLServerOptimization 學it學ititAboutthe AbouttheTechnical CHAPTER SQLQueryPerformance ThePerformance-Tuning TheCore tingthe Performancevs. Performance “GoodEnough” Performance WheretoFocus SQLServerPerformance Poor Inaccurate ExcessiveBlockingand Non-Set-Based PoorQuery PoorDatabase Excessive Nonreusable Poor pilationof ImproperUseof ImproperConfigurationoftheDatabase ExcessiveUseorImproperConfigurationof v NCONTENTS

學it技術來三通itCHAPTER System PerformanceMonitor DynamicManagement HardwareResource Identifying Bottleneck Memory SQLServerMemory Available Pages/secandPageFaults/sec BufferCacheHit PageLife Checkpoint Lazy MemoryGrants TargetServerMemory(KB)andTotalServerMemory MemoryBottleneck OptimizingApplication AllocatingMoreMemorytoSQL IncreasingSystem Changingfroma32-bittoa64-bit Enabling3GBofProcess UsingMemoryBeyond4GBWithinSQL Disk Disk %Disk CurrentDiskQueue Disk Disk Avg.DiskSec/ReadandAvg.Disk DiskBottleneck OptimizingApplication UsingaFasterDisk UsingaRAID UsingaSAN AligningDisks UsingaBattery-BackedController AddingSystem 學it技術來三通it

NCONTENTS CreatingMultipleFilesand cingtheTableandIndexonSeparate SavingLogFilestoaSeparatePhysical Partitioning Processor %Processor %Privileged ProcessorQueue Context Batch SQL ProcessorBottleneck OptimizingApplication Eliminating UsingMoreorFaster UsingaLargeL2/L3 RunningMoreEfficient NotRunningUnnecessary Network Bytes %Net NetworkBottleneck OptimizingApplication AddingNetwork ModeratingandAvoiding SQLServerOverall Missing Database Nonreusable General Creatinga CreatingaReusableListofPerformance CreatingaCounterLogUsingtheListof MinimizingPerformanceMonitor System ysisAgainst

NCONTENTS

學it技術來三通itCHAPTER SQLQuery TheSQLProfiler Profiler Data TraceTem Trace Trace CapturingaTraceUsingthe CapturingaTraceUsingStored CombiningTraceandPerformanceMonitor SQL LimitingtheNumberofEventsandData DiscardingStartEventsfor LimitingtheTraceOutput AvoidingOnlineDataColumn RunningProfilerRemo LimitingtheUseofCertain QueryPerformanceMetricsWithout Costly IdentifyingCostly IdentifyingSlow-Running yzingaQuery IdentifyingtheCostlyStepsinan yzingIndex yzingJoin Actualvs.Estimated n Query Execution STATISTICS CHAPTER WhatIsan TheBenefitof Index 學it技術來三通it

NCONTENTS Index ExaminetheWHEREClauseandJoinCriteria UseNarrow ExamineColumn ExaminetheColumnData ConsiderColumn ConsidertheTypeof Clustered Heap RelationshipwithNonclustered Clustered Nonclustered NonclusteredIndex DefiningtheBookmark Nonclustered Clusteredvs.Nonclustered BenefitsofaClusteredIndexoveraNonclustered BenefitsofaNonclusteredIndexoveraClustered AdvancedIndexing Covering Index Index Filtered Indexed Index SpecialIndex Full- AdditionalCharacteristicsof DifferentColumnSort IndexonComputed IndexonBITDataType CREATEINDEXStatementProcessedAsa ParallelIndex OnlineIndex ConsideringtheDatabaseEngineTuning NCONTENTS

學it技術來三通itCHAPTER DatabaseEngineTuning DatabaseEngineTuningAdvisor DatabaseEngineTuningAdvisor Tuninga TuningaTrace DatabaseEngineTuningAdvisor CHAPTER Bookmark PurposeofBookmark DrawbacksofBookmark yzingtheCauseofaBookmark ResolvingBookmark UsingaClustered UsingaCovering UsinganIndex CHAPTER TheRoleofStatisticsinQuery StatisticsonanIndexed BenefitsofUpdated DrawbacksofOutdated StatisticsonaNonindexed BenefitsofStatisticsonaNonindexed DrawbackofMissingStatisticsonaNonindexed yzing StatisticsonaMulticolumn StatisticsonaFiltered Statistics Automatic Manual StatisticsMaintenance yzingtheEffectivenessofStatisticsfora ResolvingaMissingStatistics ResolvinganOutdatedStatistics 學it技術來三通it

NCONTENTS BackwardCompatibilityof AutoCreate AutoUpdate AutomaticUpdateStatistics AmountofSamplingtoCollect CHAPTER Causesof PageSplitbyanUPDATE PageSplitbyanINSERT Fragmentation yzingtheAmountof yzingtheFragmentationofaSmall Fragmentation DropandRe-creatingthe Re-creatingtheIndexwiththeDROP_EXISTING ExecutingtheALTERINDEXREBUILD ExecutingtheALTERINDEXREORGANIZE SignificanceoftheFill Automatic CHAPTER n n n Componentsofthe Execution Agingofthe yzingthe n n AdHoc Prepared nReusabilityofanAdHoc nReusabilityofaPrepared

NCONTENTS

學it技術來三通it nHashandQuery n ExplicitlyParameterizeVariablePartsofa CreateStoredProcedurestoBusiness Codewithsp_executesqltoAvoidProcedure ImplementthePrepare/ExecuteModelAvoidResendingaQuery AvoidAdHoc Prefersp_executesqloverEXECUTEforDynamic ParameterizeVariablePartsofQuerieswith DoNotAllowImplicitResolutionofObjectsin CHAPTER Stored BenefitsandDrawbacks IdentifyingtheStatement yzingCauses SchemaorBindings Statistics DeferredObject SETOptions n ExplicitCall ExplicitUseof PILE DoNotInterleaveDDLandDML pilationsCausedbyStatistics UsingTable AvoidingChangingSETOptionsWithinaStored UsingOPTIMIZEFORQuery n 學it技術來三通it

NCONTENTS CHAPTER Query Query OperatingonSmallResult LimittheNumberofColumnsin UseHighlySelectiveWHERE UsingIndexes AvoidNonsargableSearch AvoidArithmeticOperatorsontheWHEREClause AvoidFunctionsontheWHEREClause AvoidingOptimizer JOIN INDEX andReferential NOTNULL DeclarativeReferential AvoidingResource-Intensive AvoidDataType UseEXISTSoverCOUNT(*)toVerifyData UseUNIONALLInsteadof UseIndexesforAggregateandSort AvoidLocalVariablesinaBatch BeCarefulNamingStored ReducingtheNumberofNetworkRound- ExecuteMultipleQueries UseSET ReducingtheTransaction ReduceLogging ReduceLock CHAPTER Blocking Understanding

NCONTENTS

學it技術來三通itDatabase Lock Lock Lock Lock Isolation Read Repeatable EffectofIndexeson EffectofaNonclustered EffectofaClustered EffectofIndexesontheSerializableIsolation CapturingBlocking CapturingBlockingInformationwith ProfilerTraceandtheBlockedProcessReport Blocking Optimizethe DecreasetheIsolation PartitiontheContended CoveringIndexonContended mendationstoReduce AutomationtoDetectandCollectBlocking CHAPTER Deadlock ChoosingtheDeadlock UsingErrorHandlingtoCatcha CollectingDeadlock yzingthe Avoiding AccessingResourcesintheSameChronological DecreasingtheNumberofResources MinimizingLock 學it技術來三通it

NCONTENTS CHAPTER Cursor Cursor Cursor Cursor Cursor CursorCost CostComparisononCursor CostComparisononCursor CostComparisononCursor DefaultResult yzingSQLServerOverheadwith yzingSQLServerOverheadwithT-SQL CHAPTER DatabaseWorkload WorkloadOptimization WorkloadOptimization Sample Capturingthe yzingthe IdentifyingtheCostliest DeterminingtheBaselineResourceUseoftheCostliest OverallResource DetailedResource yzingandOptimizingExternal yzingtheBatch-LevelOptionsUsedbythe yzingtheEffectivenessof yzingtheNeedfor yzingtheInternalBehavioroftheCostliest yzingtheQuery IdentifyingtheCostlyStepsinthe yzingtheEffectivenessoftheProcessing OptimizingtheCostliest ModifyinganExisting yzingtheApplicationofaJoin AvoidingtheClusteredIndexScan Modifyingthe

NCONTENTS

學it技術來三通ityzingtheEffectonDatabase tingThroughOptimization CHAPTER SQLServerOptimization Database BalancingUnder-and BenefitingfromEntity-Integrity Benefiting andIntegrity AdoptingIndex-DesignBest AvoidingtheUseofthesp_PrefixforProcedure MinimizingtheUseof Query UsetheCommandSETNOCOUNT ExplicitlyDefinetheOwnerofan AvoidNonsargableSearch AvoidArithmeticOperatorsontheWHEREClause AvoidOptimizer StayAwayfromNesting EnsureNoImplicitDataType MinimizeLogging AdoptBestPracticesforReusing AdoptBestPracticesforDatabase EliminateorReducetheOverheadofDatabase Configuration Affinity MemoryConfiguration CostThresholdfor MaxDegreeof OptimizeforAdHoc ernorCost FillFactor BlockedProcess DatabaseFile Database 學it技術來三通it

NCONTENTS Database KeeptheStatisticsUp-to- MaintainaMinimumAmountofIndex CycletheSQLErrorLog AvoidAutomaticDatabaseFunctionsSuchAUTO_CLOSEor MinimizetheOverheadofSQL Database IncrementalandTransactionLogBackup Backup Backup INDEX 學it學ititAbouttheNGRANTFRITCHEYworksforFMGlobal,anindustry-leadingengineeringandinsurance ,asaprincipalDBA.He’sdonedevelopmentoflarge-scaleapplicationsinlanguagessuchasInaddition,hehasworkedinfinanceandconsultingandforthreefaileddotcoms.HeistheauthorofDissectingSQLServerExecutionns(SimpleTalkPublishing,NSAJALDAMhasamaster’softechnologydegreeincomputersciencefromthenInsti-tuteofScience,Bangalore,andhasbeenworkingwith formorethan16years.Hehasdevelopedanextensivebackgroundindesigningdatabaseapplicationsandmanagingsoftwaredevelopment.Sajalalsopossessessignificantexperienceintroubleshoot-ingandoptimizingtheperformanceof-basedapplications,fromfront-endwebpagestoback-enddatabases.Hehasextensiveexperienceinworkingwithlarge,Fortune500customerstodesignscalabledatabasesolutionsandizetheperformanceoftheirdata-baseenvironments.學it學ititAbouttheTechnicalNJOSEPHSACKworksfor asadedicatedsupportengineerontheFieldEngineeringteam.Since1997,hehasbeendeveloandsupportingSQLServerenviron-mentsforsinthefinancialservices,ITconsulting,manufacturing,retail,andrealestateindustries.HeistheauthorofSQLServer2008Transact-SQLRecipes(Apress,2008),SQLServer2005T-SQLRecipes(Apress,2005),andSQLServer2000FastAnswersforDBAsandDevelopers(Glasshaus,2003).學it學ititHolycats!Writingabookisreallyhard.Fortuna y,there’sthisincrediblesupportteamthatopportunityandsupport;RichardDalPorto,thanksforkeeeverythingorganized;DouglasPundick,niceworkontheedits,andI’msorrytherewassomuchforyoutodo;KimWimpsettandKellyWinquist,samething.Mytechnicaleditor,well,“I’mnotworthy”isthebestthatcanbesaid,butmysincereappreciationgoestoJosephSackfortheabsoluyoutstandingworkhedidonthisbook.You’vealldoneamazingwork.Anythingthat’swrongwiththebooknowismyresponsibility,notyours.IhavetoalsoshowappreciationtoallthefolksoverattheSQLServerCentralSteveJonesandAndyWarrencreatedquiteacommunityoverthere.Steve,Gail,Jeff,Jack,Barry,Iworkforafantastic withgreatpeoplewhereI’vehadopportunitiestolearnandteamwhohavetaughtmesomuch:Peter,Ted,Basem,AJ,DaveM.,DaveH.,ChrisK.,ChrisP.,Yuting,Ray,Raj,andDet.IwanttothankthefolksatRedGateformakingterrifictoolsincludingSQLPrompt,whichIusedtowriteandformatalltheT-SQLcodeinthebook.Finally,beforesomeonestartsyingmusicandpushingmeoffthestage,Iwanttoshowappreciationtothefamily—mywifeandkids.Thisisthesecondbookyou’veallowedmetowrite.Thankyouforputtingupwithmesowell.Grant學it學ititPerformanceisfrequentlyoneofthelastthingsonanyone’smindswhenthey’reasystem.Unfortunay,thatmeansit esthebiggestproblemafterthatgoestoproduction.Youcan’tsimplyrelyongettingaphonecallthatlsyouthatproce-dureXondatabaseYthatrunsonserverZisrunningslow.Youhavetohaveamechanismincetofindthisinformationforyourself.Youalsocan’tworkoffthegeneralwordslow.Slowcomparedtowhat?Lastweek?Lastmonth?Thewayitranindevelopment?Andonceyou’veidentifiedsomethingasrunningslow,youneedtoidentifywhy.Doesitneedanindex?Doesithaveanindexthatitisn’tusing?IsittheCPU,thedisk,thememory,thenumberofusers,theamountofdata?Nowyou’veidentifiedwhatandwhy,butyouhavetodosomethingaboutit.How?Rewritethequery?ChangetheSDANAclause?Thequestionsthatwillcomeyourwaywhenyoustartperformancetuningareendless.Thisbookprovidesyouwiththetoolsyouneedtoanswerthosequestions.I’llshowyouhowtosetupmechanismsforcollectingperformancedataonyourserverfortheSQLServerinstancesanddatabaseslivingthere.I’llgooverthemoretacticalmethodsofcollectingdataonindividualT-SQLcalls.Alongtheway,I’llbediscussingindexstructure,choice,andmain-tenance;howbesttowriteyourT-SQLcode;andawholeslewofothertopics.Oneofmygoalswhenwritingthisbookwastodeliverallthesethingsusingexamplesthatresemblethetypesofqueriesyou’llseeintherealworld,atyourdesk.ThetoolsandmethodspresentedaremostlyavailablewithSQLServerStandardEdition,althoughsomeareavailableonlywithSQLServerEnterpriseEdition.Thesearecalledoutwheneveryoumightencounterthem.Themainpointistolearnhowtoanswerallthosequestionsthataregoingtobepre-sentedtoyou.Thisbookgivesyouthetoolstodothatandtoanswerthosequestionsinamethodicalmannerthateliminatesmuchoftheguessworkthatissocommoninperformanceoptimizationtoday.Performanceproblemsaren’tsomethingtobefeared.Withtherighttools,youcantackleperformanceproblemswithacalmnessandreliabilitythatwillearntherespectofyourpeersandyoursandthatwillcontributedirectlytotheirsuccess.WhoThisBookIsThisbookisforjustaboutanyoneresponsiblefortheperformanceofthesystem.Databaseadministrators,certainly,aretargetedbecausethey’reresponsibleforsettingupthesystems,creatingtheinfrastructure,andmonitoringitovertime.Developersaretoo,becausewhoelseisgoingtogenerateallthewell-formedandhighlyperformantT-SQLcode?Databasedevel-opers,morethananyone,arethetargetaudience,ifonlybecausethat’swhatIdoforwork.AnyonewhohasthecapabilitytowriteT-SQL,designtables,implementindexes,ormanipu-lateserversettingsontheSQLServersystemisgoingtoneedthisinformationtoonedegreeoranother.

NINTRODUCTION

學it技術來三通itHowThisBookIsThepurposeofthiswastouseasmany“real-looking”queriesaspossible.Todothis,Ineededa“real”database.Icouldhavecreatedoneanddeveryonetotrackdownthedownload.Instead,Ichosetousethesampledatabasecreatedby,called=`rajpqnaSkngo.,,4.ThisisavailablethroughCodePlex(dppl6++sss*_k`alhat*_ki+IOBP@>Lnk`O]ilhao).Isuggestkeeacopyoftherestorehandyandresettingyoursampledatabaseafteryouhavereadadifferentsetsofdataordifferentbehaviorofsomeofthequeriesthanthatlistedinthebook.Toadegree,thebookbuildsontheknowledgepresentedfrompreviouschapters.However,mostofthechapterspresentinformationuniquewithinthattopic,soitispossibleforyoutojumpinandoutofparticularchapters.YouwillstillreceivethemostbenefitbyasequentialreadofChapter1throughChapter16.performancetuning.You’llgetafirstglimpseatestablishingaperformancebaseline,identifyingbottlenecks,resolvingtheproblems,andfyingtheimprovements.U?>?ìiàó]o-T?ìi?*iàv?à?>?Vi?>?T???]??ì>àì?T???vvü?ì?????ì?à??}ì?isystemonwhichSQLServerruns.PerformanceMonitoranduser-definedfunctionsareshownasamechanismforcollectingdata.hood”andseewhatkindofqueriesarebeingrunonyoursystem.ItprovidesadetailedlookattheProfilerandtracetools.SeveralofthemostusefuldynamicmanagementviewsandfunctionsusedtomonitorqueriesarefirstidentifiedinthisU?>?ìià{]o?`iY?>?T???]?iY??>?????`iYi?>?`??`iY>àV??ìiVì?ài°ì`iv??i?differencesbetweenclusteredandnonclusteredindexes.Itshowswhichtypesofindexesworkbestwithdifferenttypesofquerying.BasicindexmaintenanceisalsoU?>?ìiàx]o>ì>L>?i?}??i/????}`????à]?V??ià?ì?i?Và???vìì???>ì>L>?iEngineTuningAdvisor.ThechaptergoesoverindetailhowtousetheDatabaseEngineTuningAdvisor;you’reintroducedtothevariousmechanismsforcallingthetoolandshownhowitworksunderrealloads.U?>?ìiàè]o????>à???????>?T???]?ì>?i???ì?iV?>???V?iàv?à?>?Vithekeylookup,whichisalsoknownasthebookmarklookup.Thischapterexploresvarioussolutionstothebookmarklookupoperation.statisticstomakedecisionsregardingtheexecutionofthequery.Maintainingstatis-tics,understandinghowthey’restored,learninghowtheywork,andlearninghowtheyaffectyourqueriesarealltopicswithinthischapter.U?>?ìiàn]oà>}?i?ì>ì????>?T???]????ü???ü??`iYi?và>}?i?ì??iàì??i°learnhowtoidentifywhenanindexisfragmented.You’llseewhathappenstoyourqueriesasindexesfragment.You’lllearnmechanismstoeliminateindexfragmentation.學it技術來三通it

NINTRODUCTION U?>?ìià?]oYiV?ì???*?>?>V?i?>?T???]??ài?i?ì?ì?i?iV?>?????ì?>ì-+usestostoreexecutionns.nreuseisanimportantconceptwithinSQLServer.You’lllearnhowtoidentifywhethernsarebeingreused.You’llgetvariousmecha-nismsforlookingatthecache.Thischapteralsointroducesnewdynamicmanagementviewsthatallowmoreaccesstothecachethaneverbefore.U?>?ìià￡?]o-ì?ài`*à?Vi`?ài,iV?????>ì???]?`????>T???ü>?`ü?i?-+will pilensthatwerestoredincache.You’lllearnhown pilescanhurtorhelptheperformanceofyoursystem.You’llpickupmechanismsforforcingapileandforpreventingU?>?ìià￡￡]o+?iàTi??}??>?T???]?ài?i>????üì?üà?ìiμ?ià?i?ì?>ì?iàv?à?withinyoursystem.Commonmistakesareexplored,andsolutionsareprovided.You’lllearnseveralbestpracticestoavoidcommonbottlenecks.U?>?ìià￡ó]o??V???}?>?T???]?ìi>V?i?ì?iLi?ìü>T?ì?àiV?}??aiü?i?sessionsonyourserverareincontentionforresources.You’lllearnhowtomonitorforblockingalongwithmethodsandtechniquestoavoidblockedsessions.U?>?ìià￡?]oi>`??V??>?T???]????ü???ü`i>`??V???VV?à??T??à?T?ìi?°getmethodsforidentifyingsessionsinvolvedwithdeadlocks.Thechapteralsopres-entsbestpracticesforavoidingdeadlocksorfixingyourcodeifdeadlocksarealreadyoccurring.U?>?ìià￡{]o?à??à??ì?>?T???]?`?>}à>??ì?i???iài?ìV??ì?ì?>ìV?à??à?toset-orientedT-SQLcode.However,whencursorsareunavoidable,youneedtounderstandhowtheywork,whattheydo,andhowbesttotunethemwithinyourenvironmentifeliminatingthemoutrightisnotanoption.U?>?ìià￡x]o>ì>L>?i7?à???>`"?ì???a>ì???]?`i????ìà>ìi???üì?ì>?imationpresentedinallthepreviouschaptersandputittoworkonarealdatabaseworkload.You’llidentifytheworst-performingproceduresandputthemthroughvarioustuningmethodstoarriveatbetterperformance.tersintoasetofchecklistsandbestpractices.ThegoalofthechapteristoenableyoutohaveaceforquicklyreviewingallyouhavelearnedfromtherestoftheDownloadingtheYoucandownloadthecodeexamplesusedinthisbookfromtheSourceCodesectionoftheApresswebsite(dppl6++sss*]lnaoo*_ki).MostofthecodeisstraightT-SQLstoredin*omhfiles,whichcanbeopenedandusedinanySQLServerT-SQLeditingtool.ThereisonePowerSscriptthatwillhavetoberunthroughaPowerScommandline.ContactingtheYoucancontacttheauthor,GrantFritchey,atcn]jpa``<ckkcha*_ki.Youcanvisithisblog學it學ititCHAPTE SQLQueryPerformanceQueryperformancetuningisanimportantpartoftoday’sdatabaseapplications.Oftenyoucanachievelargesavingsinbothtimeandmoneywithproperqueryperformancetuning.Hardwareperformanceisconstantlyimproving,whichcanleadtoanattitudesuggestingthatothermethodsofperformancetuningarenolongerimportant.UpgradestoSQLServer—especiallytotheoptimizer,whichhelpsdeterminehowaqueryisexecuted,andthequeryengine,whichexecutesthequery—leadtobetterperformanceallontheirown.Thebeautyofqueryperformancetuningisthat,inmanycases,asmallchangetoanindexoraSQLquerycanresultinafarmoreefficientapplicationataverylowcost.Thereare,however,manypitfallsfortheunwary.Asaresult,aprovenprocessisrequiredtoensurethatyoucorrectlyidentifyandresolveperformancebottlenecks.Towhetyourappe-titeforthetypesoftopicsessentialtohoningyourqueryoptimizationskills,hereisaquicklistofthequeryoptimizationaspectsIcoverinthisbook:U?>?Ta??}>μ?iàTiYiV?ì???U???`??}L????>à????????U?>?Ta??}>?`ài??????}và>}?i?ì>ì???U"?ì???a??}iYiV?ì?????>?U?????a??}L??V???}>?``i>`??V??U?>?Ta??}ì?iivviVì??i?i???vV?à??àU???T??}?iàv?à?>?Vi?ì????}?à?Vi??i?]ì????]>?`??ì???a>ì???ìiV???μ?i?optimizeSQLBeforejumstraightintothesetopics,let’sfirstexaminewhywegoaboutperfor-mancetuningthewaywedo.Inthischapter,Idiscussthebasicconceptsof1 tuningforaSQLServerdatabasesystem.Idetailthemainperformancebottlenecksandshowjusthowimportantitistodesignadatabase-friendlyapplication,whichistheconsumerofthedata,aswellashowtooptimizethedatabase.Specifically,Icoverthefollowingtopics:U*iàv?à?>?Vi??°?à?ViU/?i?iàv?à?>?ViU7?iàiì?v?V??ivv?àì???U/?iì??￡￡-+-ià?ià?iàv?à?>?ViThePerformance-TuningTheperformance-tuningprocessconsistsofidentifyingperformancebottlenecks,trouble-shootingtheircauses,applyingdifferentresolutions,andthenfyingperformanceimprovements.Itisnecessarytobealittlecreative,sincemostofthetimethereisnoonesilverbullettoimproveperformance.Thechallengeistonarrowdownthelistofpossiblecausesandevaluatetheeffectsofdifferentresolutions.Youcanevenundomodificationsasyoui tethroughthetuningprocess.TheCoreDuringthetuningprocess,youmustexaminevarioushardwareandsoftwarefactorsthatcanaffecttheperformanceofaSQLServer–basedapplication.Youshouldbeaskingyourselfthefollowinggeneralquestionsduringtheperformanceysis:U?>?T?ì?iàài???àVi???ìi????i>????V>ì???à?????}??ì?i>?iU?ì?i?>à`ü>ài??L?T?ìi?V>?>L?i?vü?ì??ì>?`??}ì?i?>Y????U?-+-ià?iàV??v?}?ài`?à??ià?T?U?ì?i`>ì>L>?iV???iVì???Liìüii?-+-ià?ià>?`ì?i`>ì>L>?i>????V>ì???U?i?ì?i`>ì>L>?i`i??}??????àìì?iv>?ìi?ì`>ì>àiìà?i?>?->?`??`?v?V>ì???>???`>ì>L?i`>ì>L>?i??U?ì?i??iàü?à???>`]V?????ì??}?v-+μ?ià?i?]??ì???ai`ì?ài`?Viì?i??>`-+-U7?>ì?à?Vi??i?>àiV>????}ì?i?T?ìi?ì????ü`?ü?>?àiv?iVìi`??ì?i?v?>à????ü>?ìU?i?ì?iü?à???>`?????àìì?i?>Y????Ifanyofthesefactorsisnotconfiguredproperly,thentheoverallsystemperformancemaysuffer.Let’sbrieflyexaminethesefactors.Havinganotherresource-intensiveapplicationonthesameservercanlimitthe>?>??>L?iì?-+-ià?ià°?i?>?>????V>ì???à?????}>?>?ià??ViV>?V?????i>}??`?vthesystemresourcesandlimittheresourcesavailabletoSQLServer.Forexample,running7??`?ü?/>??>?>}iàV??ì???????T??ì?i?ià?ià????ìàiV???i?`i`°7??`?ü?/>??學it技術來三通it學院就夠CHA ORMANCETUNING >?>}ià??>???>?>????V>ì???]p]ogicn*ata,whichrunsatahigherprioritythantheSQLServerprocess.Priorityistheweightgiventoaresourcethatpushestheprocessortogiveitgreaterpreferencewhenexecuting.Todeterminethepriorityofaprocess,followthesesteps:>??V?7??`?ü?/>??SelectView￠Select-i?iVìì?i>?i*à??à?ìTV?iV?ClicktheOKThesestepswilladdthe>]oaLneknepucolumntothelistofprocesses.T??ü???Li>L?iì?`iìià???iì?>ìì?i- ià?ià?à?Vi??-omhoanrn*ata)bydefaultruns?à?>??à??à?ìT]ü?iài>?ì?i7??`?ü?/>??>?>}ià?à?Vi??-p]ogicn*ata)runsatHighpriority.Therefore,toallowSQLServertoizetheuseofavailableresources,youshouldlookforallthenonessentialapplications/servicesrunningontheSQLServermachineandensurethattheyarenotactingasresourcehogs.ImproperlyconfiguringthehardwarecanpreventSQLServerfromgainingtheumbenefitfromtheavailableresources.Themainhardwareresourcestobeconsideredarepro-cessor,m