三通it學(xué)院-performance tuning distilled技術(shù)來就夠了

學(xué)it技術(shù)來三通itSQLServer2008QueryPerformanceTuningGrantFritcheyandSajal學(xué)it技術(shù)來三通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學(xué)it學(xué)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 學(xué)it學(xué)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

學(xué)it技術(shù)來三通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 學(xué)it技術(shù)來三通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

學(xué)it技術(shù)來三通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 學(xué)it技術(shù)來三通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

學(xué)it技術(shù)來三通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 學(xué)it技術(shù)來三通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

學(xué)it技術(shù)來三通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 學(xué)it技術(shù)來三通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

學(xué)it技術(shù)來三通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 學(xué)it技術(shù)來三通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

學(xué)it技術(shù)來三通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 學(xué)it技術(shù)來三通it

NCONTENTS Database KeeptheStatisticsUp-to- MaintainaMinimumAmountofIndex CycletheSQLErrorLog AvoidAutomaticDatabaseFunctionsSuchAUTO_CLOSEor MinimizetheOverheadofSQL Database IncrementalandTransactionLogBackup Backup Backup INDEX 學(xué)it學(xué)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.學(xué)it學(xué)ititAbouttheTechnicalNJOSEPHSACKworksfor asadedicatedsupportengineerontheFieldEngineeringteam.Since1997,hehasbeendeveloandsupportingSQLServerenviron-mentsforsinthefinancialservices,ITconsulting,manufacturing,retail,andrealestateindustries.HeistheauthorofSQLServer2008Transact-SQLRecipes(Apress,2008),SQLServer2005T-SQLRecipes(Apress,2005),andSQLServer2000FastAnswersforDBAsandDevelopers(Glasshaus,2003).學(xué)it學(xué)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學(xué)it學(xué)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

學(xué)it技術(shù)來三通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.學(xué)it技術(shù)來三通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學(xué)it學(xué)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??`?ü?/>??學(xué)it技術(shù)來三通it學(xué)院就夠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