SQL Query Performance Tuning_4215_Ch01_FINAL

SQL Quer y Perfor mance T uningP er for mance tuning is an impor tant par t of today’s database applications.V er y often,lar gesavings in both time and money can be achieved with proper per for mance tuning.The beautyof per for mance tuning is that,in many cases,a small change to an index or a SQL quer y canresult in a far mor e efficient application.Ther e ar e,ho w e ver,many pitfalls for the unwar y.As a result,a pr o v en pr ocess is require dto ensur e that y ou corr ectly identify and r esolve per for mance bottlenecks.To whet y our appetite for the types of topics essential to honing y our quer y optimizationskills,her e is a quick list of the quer y optimization aspects I will cover in this book:•I dentifying pr oblematic SQL quer ies•Analyzing a quer y execution plan•E v aluating the effectiveness of the curr ent indexes•A voiding bookmar k lookups•E v aluating the effectiveness of the curr ent statistics•Analyzing and r esolving fr agmentation•O ptimizing execution plan caching•Analyzing and avoiding stor ed pr ocedure recompilation•M inimizing blocking and deadlocks•E xamining the cost of using a specific cursor type•O ptimizing database connection cost•A pplying per for mance tuning pr ocesses,tools,and optimization techniques to optimizeSQL workload12B efor e jumping str aight in to these topics,let’s first examine why w e go about per for m-ance tuning the way w e do.I n this chapter,I discuss the basic concepts of per for mance tuning for a SQL S er v er database system.I detail the main per for mance bottlenecks and sho w justho w impor tant it is to design a database-fr iendly application,which is the consumer of thedata,as w ell as optimize the database.S pecifically,I cover the following topics:•The per for mance tuning pr ocess•P er for mance versus pr ice•The per for mance baseline•Wher e to focus effor ts in tuning•The top11SQL S er v er per for mance killersSo,without fur ther ado,let’s begin.The P erformance T uning ProcessThe per for mance tuning pr ocess consists of identifying per for mance bottlenecks,tr ouble-shooting their cause,applying differ ent resolutions,and then quantifying per for manceimpr o v ements.I t is necessar y to be a little cr eative,since most of the time ther e is no onesilver bullet to impr o v e per for mance.The challenge is to narr o w do wn the list of possiblereasons and ev aluate the effects of differ ent resolutions.Y ou may even undo modificationsas y ou iter ate thr ough the tuning pr ocess.Du r ing the tuning pr ocess, y ou must examine v a rious har dware and softwar e factors that can affect the per for mance of a SQL S er v er–based application.A few of the gener al questions y ou should be asking y ourself dur ing the per for mance analysis ar e as follo ws:•I s any other resource-intensive application r unning on the same ser ver?•I s the har dware subsystem capable of withstanding the maximum wor kload?•I s SQL S er v er configur ed pr operly?•I s the database connection betw een SQL S er v er and the database application efficient?•Does the database design suppor t the fastest data r etr iev al(and modification for anupdateable database)?•I s the user workload,consisting of SQL quer ies,optimiz ed to r educe the load on SQLSe r v er?•Does the workload suppor t the maximum concurr ency?If any of these factors is not configur ed pr operly,then the o v er all system per for mance may suffer.Let’s br iefly examine these factors.Having another resource-intensive application on the same ser ver can limit the resour ces av ailable to SQL S er v er.E v en an application r unning as a ser vice can consume a good par tof the system resour ces,limiting the resour ces av ailable to SQL S er v er. C onsider that evenr unning W indo ws T ask M anager continuously on the ser ver is not r ecommended.W indo ws3 Task M anager is also an application,taskmgr.exe,which r uns at a higher pr ior ity than the SQLSe rv er pr ocess. T o determine the pr ior ity of a pr ocess,follo w these steps:unch W indo ws T a sk M anager.2.S elect V iew S elect C o lumns.3.S elect the B ase P r ior ity check bo x.4.Click the OK button.These steps will add the B ase P r ior ity column to the list of pr ocesses.S ubsequently, y o uwill be able to determine that the SQL S er v er pr ocess(sqlservr.exe)b y default r uns atNo rm al pr ior ity,whereas the W indo ws T ask M anager pr ocess(taskmgr.exe)r uns at High pr i-or ity.Therefor e,to allo w SQL S er v er to maximiz e the use of av ailable resour ces, y ou shouldlook for all the nonessential applications/ser vices r unning on the SQL S er v er machine andensur e that they ar e not acting as resour ce hogs.I mproper configur ation of the har dware can pr event SQL S er v er fr om gaining the maximumbenefit fr om the av ailable resour ces.The main har dware resour ces to be consider ed ar e pr oces-sor,memor y,disk,and networ k.F or example,in a ser ver with mor e than 4GB of memor y,animproper memor y configur ation will pr event SQL S er v er fr om using the memor y bey ond 4GB.Fu r thermor e,if the capacity of a par ticular resour ce is small,then it can soon become a per for m-ance bottleneck for SQL S er v er.Chapter2covers these har dware bottlenecks in detail.Y ou should also look at the configur ation of SQL S er v er,since proper configur ation isessential for an optimiz ed application.Ther e is a long list of SQL S er v er configur ations thatdefine the gener ic behavior of a SQL S er v er installation.These configur ations can be view edand modified using a system stor ed pr ocedure,sp_configure.M any of these configur ationscan be managed inter actively thr ough SQL S er v er Enterpr ise M anager.S ince the SQL S er v er configur ations ar e applicable for the complete SQL S er v er installa-tion,a standard configur ation is gener ally pr eferr ed.The good news is that,gener ally, y o uneed not modify these configur ations;the default settings wor k best for most situations.I nfact,the gener al r ecommendation is to keep the SQL S er v er configur ations at the defaultv alues.I discuss the configur ation parameters in detail thr oughout the book.P oor connectivity betw een SQL S er v er and the database application can hur t applicationper for mance.One of the questions y ou should ask y ourself is―H o w good is the database con- nection?‖F or example,the quer y executed b y the application may be highly optimized,butthe database connection used to submit this quer y may add considerable o v er head to thequer y per for mance.B ased on the distr ibution of the application and the database,differ entnetwor k pr otocols should be used to r educe the networ k o v er head.A dditionally,the dataaccess lay er used to manage the database connectivity abo ve the networ k connection may notbe efficient.The data access lay er technology or the way the data access lay er is used b y the application may not be optimal.Chapter15covers the database connection aspects in detail.The design of the database should also be analyz ed while tr oubleshooting per for mance.This helps y ou understand not only the entity-r elationship model of the database,but alsowhy a quer y may be wr itten in a cer tain way.Although it may not always be possible to modifya database design because of wider implications on the database application,a good under-standing of the database design helps y ou to focus in the r ight direction and to understandthe impact of a resolution.4If the pr evious configur ations ar e quite nor mal or have to be the way they ar e,then the question becomes―I s the application slo w because of poorly designed or indexed quer ies?‖ The workload may contain resource-intensive or nonoptimized quer ies,which may hur t the per for mance of the quer ies and that of other quer ies in the workload.I cover index optimiza-tion in depth in Chapters3,4,5,and6.The next question at this stage should be―I s a quer yslo w because of its resour ce intensiveness or because of concurr ency issues with otherquer ies?‖Y ou can find in-depth infor mation on blocking analysis in Chapter12.The challenge is to find out which factor is causing the per for mance bottleneck.F or example,with slow-r unning SQL quer ies and high pr essure on the har dware resour ces, y o umay find that both poor database design and nonoptimized workload ar e to blame.I n sucha case, y ou must diagnose the symptoms fur ther and correlate the findings with possiblecauses.As per for mance tuning can be time-consuming and tiresome, y ou should ideally takea pr eventive appr oachb y designing the system for optimum per for mance fr om the outset.To strengthen the pr eventive appr oach,ever y lesson that y ou lear n dur ing the optimiza- tion of poor per for mance should be consider ed an optimization guideline when implement- ing new database applications.Ther e ar e also pr o v en best pr actices that y ou should consider while implementing database applications.I present these best pr actices in detail thr oughout the book,and Chapter18is dedicated to optimization best pr actices.P lease ensur e that y ou take the per for mance optimization techniques into consider ation at the early stages of y our database application development.Doing so will help y ou r oll outy our database pr ojects without big surpr ises later.U nfortunately,w e r a r ely live up to this ideal and often find database applications needing per for mance tuning.Therefor e,it is impor tant to understand not only ho w toimpr o v e the per for mance of a SQL S er v er–based application,but also ho w to diagnose thecauses of poor per for mance.An I terativ e P r ocessPe r for mance tuning is an iter ativ e pr ocess,where y ou identify major bottlenecks,attempt tor esolve them,measur e the impact of y our changes,and r etur n to the first step until per for m- ance is acceptable.While applying y our solutions, y ou should follo w the golden rule of making only one change at a time. Any change usually affects other parts of the system,so y ou mustr e-ev aluate the effect of each change on the per for mance of the o v er all system.As an example,the addition of an index may fix the per for mance of a specific quer y,but it could cause other quer ies to r un mor e slo wly,as explained in Chapter4. C onsequently,itis pr efer able to conduct per for mance analysis in a test envir onment,to shield users fr omy our diagnosis attempts and intermediate optimization steps.I n such a case,ev aluating onechange at a time also helps in pr ioritizing the implementation or der of the changes on theproduction ser ver,based on their r elative contr ibutions.Y ou can keep on chipping away at per for mance bottlenecks and impr o ving the system per for mance gr adually.I n itially, y ou will be able to r esolve big per for mance bottlenecks andachieve significant per for mance impr o v ements,but as y ou pr oceed thr ough the iterations,y our r eturns will gr adually diminish.Therefor e,to use y our time efficiently,it is worthwhile toquantify the per for mance objectives first(for example,an 80% r eduction in the time taken for a cer tain quer y,with no adverse effect anywhere else on the ser ver),and then wor k to war d them.5 The per for mance of a SQL S er v er application is highly dependent on the amount anddistr ibution of user activity(or workload)and data.B oth the amount and distr ibution of wor k-load and data change o v er time,and differ ing data can cause SQL S er v er to execute SQL quer iesdiffer ently.The per for mance resolution applicable for a cer tain workload and data may lose itseffect o v er a per iod of time.Therefor e,to ensur e an optimum system per for mance on a continu-ing basis, y ou will need to analyz e per for mance at r egular inter v als.P er f or mance tuning is anever-ending pr ocess,as sho wn in F igur e1-1.Figure1-1.Per formance tuning pr ocess6Y ou can see that the steps to optimize the costliest quer y make for a complex pr ocess , which also requires multiple iterations to tr oubleshoot the per for mance issues within the quer y and apply one change at a time . The steps involved in the optimization of the costliest quer y ar e sho wn in F igur e 1-2.sFigure 1-2. O ptimization of the costliest quer y7 As y ou can see fr om this pr ocess,ther e is quite a lot to do to ensur e that y ou corr ectlytune the per for mance of a given quer y.I t is impor tant to use a solid pr ocess like this in per-for mance tuning,to focus on the main identified issues.Ha ving said this,it also helps to tr y and keep a broader perspective about the pr oblem asa whole,since sometimes y ou may believe that y ou ar e tr ying to solve the correct per for mance bottleneck,when in r eality something else is causing the pr oblem.P erformance vs.PriceOne of the points I touched on earlier is that to gain incr easingly small per for mance incre-ments, y ou need to spend incr easingly lar ge amounts of time and money.Therefor e,to ensur ethe best r etur n on y our investment, y ou should be ver y objective while optimizing per for m-ance. Always consider the following two aspects:•What is the acceptable per for mance for y our application?•I s the investment wor th the per for mance gain?To der ive maximum efficiency, y ou must r ealistically estimate y our per for mance require-ments.Ther e ar e many best pr actices that y ou can follo w to impr o v e per formance—forexample, y ou can have y our database files on the most efficient disk subsystem.H o w e ver,befor e applying a best pr actice, y ou should consider ho w much y ou may gain fr om it andwhether the gain will be wor th the investment.S ometimes it is r eally difficult to estimate the per for mance gain without actually makingthe enhancement.A pr actical appr oach can be to increase a resour ce in incr ements andanalyz e the application’s scalability with the added resour ce.A scalable application will pr o-por tionately benefit fr om an incr emental increase of the resour ce,if the resour ce was tr ulycausing the scalability bottleneck.I f the results appear to be satisfactor y,then y ou can committo the full enhancement.E xper ience also plays a ver y impor tant r ole her e.―G ood Enough‖ T uningI nstead of tuning a system to the theor etical maximum per for mance,the goal should be totune until the system per for mance is―good enough.‖This is a commonly adopted per for m-ance tuning appr oach.The cost investment after such a point usually increases exponentiallyin compar ison to the per for mance gain.The80:20rule wor ks ver y well:b y investing 20% ofy our resour ces, y ou may get 80% of the possible per for mance enhancement,but for theremaining 20% possible per for mance gain, y ou may have to invest an additional 80% ofresour ces,as sho wn in F igur e1-3.I t is ther efor e impor tant to be r ealistic when setting y ourper for mance objectives.8Figure1-3.Per formance gain vs.inv estmentA business benefits not b y consider ing pur e per for mance,but b y consider ing pr ice per-for mance.H o w e ver,if the tar get is to find the scalability limit of y our application(for v a rious reasons,including mar keting the product against its competitors),then it may be worthwhile investing as much as y ou can.E v en in such cases,using a third-par ty str ess test lab may be a better investment decision.P erformance BaselineOne of the main objectives of per for mance analysis is to understand the underlying level ofsystem use or pr essure on differ ent har dware and softwar e subsystems.This kno wledge helps y ou in the following ways:•Allo ws y ou to analyz e resour ce bottlenecks.•E nables y ou to tr oubleshoot b y compar ing system utilization patterns with a pr e-established baseline.•Assists y ou in making accur ate estimates in capacity planning and scheduling har dwareupgrades.•Aids y ou in identifying low-utilization per iods,when the database administr ative activ-ities can be executed.•H elps y ou to estimate the natur e of possible har dware do wnsizing.This sounds inter-esting—why would a company do wnsiz e? W ell,in the past,some companies leasedver y high-end systems expecting str ong gr o wth,but due to poor gr o wth,they ar e no wfor ced to do wnsiz e their system setups.9 Therefor e,to better understand y our application’s resour ce requir ements, y ou shouldcr eate a baseline for y our application’s har dware and softwar e usage.A baseline ser v es as astatistic of y our system’s curr ent usage patter n and as a r efer ence with which to compar efuture statistics.B aseline analysis helps y ou to understand y our application’s behavior dur inga stable per iod,ho w har dware resour ces ar e used dur ing such per iods,and what the softwar echar acter istics ar e.W ith a baseline in place, y ou can•M easur e curr ent per for mance and express y our application’s per for mance goals.•C ompar e other har dware or softwar e combinations against the baseline.•M easur e ho w the workload and/or data changes o v er time.•E v aluate the peak and nonpeak usage patter n of the application.This infor mation canbe used to effectively distr ibute database administr ation activities,such as full data-base backup and database defr agmentation dur ing nonpeak hours.Y ou can use the S ystem M onitor tool(also r eferr ed to as P er for mance M onitor)to cr eate a baseline for SQL S er v er’s har dware and softwar e resour ce utilization.S imilarly, y ou may base-line the SQL S er v er workload using the SQL P r ofiler tool,which can help y ou to understandthe average resour ce utilization and execution time of SQL quer ies when conditions ar estable.Y ou will lear n in detail ho w to use these tools in Chapters2and3.Where to Focus EffortsWhen y ou tune a par ticular system,pay special attention to the application lay er(the data-base quer ies and stor ed pr ocedures executed b y V isual B asic/ADO or other wise that ar e usedto access the database).Y ou will usually find that y ou can positively affect per for mance in the application lay er far mor e than if y ou spend an equal amount of time figur ing out ho w to tunethe har dware,oper ating system,or SQL S er v er configur ation.Although a proper configur ationof har dware,oper ating system,and SQL S er v er is essential for the best per for mance of a data-base application,these fields have standar diz ed so much that y ou usually need to spend onlya limited amount of time configuring them pr operly for per for mance.A pplication designissues such as quer y design and indexing str ategies,on the other hand,ar e applicationdependent. C onsequently,ther e is usually mor e to optimize in the application lay er than inthe har dware,oper ating system,or SQL S er v er configur ation.Thus,for a unit of time spent ineach ar ea,wor k in the application lay er usually yields the maximum per for mance benefit,asillustr ated in F igur e1-4.10Figure1-4.Ti me spent vs.per formance gainIn my exper ience,the gr eatest impr o v ement in database application per for mance can be obtained b y looking first at the area of application design,including logical/physical database design,quer y design,and index design.Su re,if y ou concentrate on har dware configur ation and upgrades, y ou may obtain a satis- factor y per for mance gain.H o w e ver,a bad SQL quer y sent b y the application can consume all the har dware resour ces av ailable,no matter ho w much y ou have.Therefor e,a poor applica-tion design can make the har dware upgr ade requir ements ver y high,or even bey ond y ourlimits.I n the pr esence of a heavy SQL workload,concentr ating on har dware configur ationsand upgrades usually pr oduces a poor r etur n on investment.Y ou should analyz e the str ess cr eated b y an application on a SQL S er v er database at two levels:•H igh lev el:Analyz e ho w much str ess the database application is creating on individualhar dware resour ces and what the o v er all behavior of the SQL S er v er installation is.Thisinfor mation can help y ou in two ways.F irst,it helps y ou to identify the area to concen-tr ate on within a SQL S er v er application where ther e is poor per for mance.S econd,ithelps y ou to identify any lack of proper configur ation at the higher levels.Y ou can thendecide which har dware resour ce may be upgraded,if y ou ar e not able to tune the appli-cation,using the P er for mance M onitor tool,as explained in Chapter2.•Lo w lev el:I dentify the exact culpr its within the application—in other wor ds,the SQLquer ies that ar e creating most of the pr essure visible at the o v er all higher level.This canbe done using the SQL P r ofiler tool,as explained in Chapter3.11 SQL Ser ver P erformance KillersLet’s no w consider the major pr oblem ar eas that can degr ade SQL S er v er per for mance.By being awar e of the main per for mance killers in SQL S er ver in adv ance, y ou will be ableto focus y our tuning effor ts on the likely causes.Once y ou have optimiz ed the har dware,oper ating system,and SQL S er v er settings,the main per for mance killers in SQL S er v er ar e as follows,in a rough or der(with the worstappear ing first):•P oor indexing•I naccurate statistics•Ex cessive blocking and deadlocks•P oor quer y design•P oor database design•Ex cessive fr agmentation•N onr eusable execution plans•Fr equent recompilation of execution plans•I mproper use of cursors•I mproper configur ation of the database log•I neffective connection poolingLet’s take a quick look at each of these,befor e consider ing them in mor e depth in laterchapters.P oor I ndexingP oor indexing is usually one of the biggest per for mance killers in SQL S er v er.I n the absenceof proper indexing for a quer y,SQL S er v er has to r etr ieve and pr ocess much mor e data while executing the quer y.This causes high amounts of str ess on the disk,memor y,and CPU,incr easing the quer y execution time significantly.I ncr eased quer y execution time then leadsto ex cessive blocking and deadlocks in SQL S er v er.Y ou will lear n ho w to determine the index-ing str ategies and r esolve indexing pr oblems in Chapters4,5,and6.Gener ally,indexes ar e consider ed to be the r esponsibility of the database administr ator (DBA).H o w e ver,the DBA cannot define ho w to use the indexes,since the use of indexes is determined b y the database quer ies and stor ed pr ocedures wr itten b y the developers.Ther e-for e,defining the indexes should be the r esponsibility of the developers.I ndexes cr eatedwithout the kno wledge of the quer ies ser ve little purpose.12NOTE Because indexes crea ted without the knowledge of the queries serve little purpose,databasedevelopers ought to understand the indexes as much as the DBAs.I naccurate S tatisticsAs SQL S er v er r elies heavily on cost-based optimization,accur ate data-distribution statisticsar e extremely impor tant for the effective use of indexes.W ithout accur ate statistics,SQLSe rv er’s built-in quer y optimizer cannot accur ately estimate the number of ro ws affected b y a quer y.As the amount of data to be r etr ieved fr om a table is highly impor tant in deciding ho wto optimize the quer y execution,the quer y optimizer is much less effective if the data distr i-bution statistics ar e not maintained accur ately.Y ou will look at ho w to analyz e statistics inChapter7.Ex cessiv e B locking and D eadlocksB ecause SQL S er v er is fully A tomicity,C onsistency,I solation,D u r ability(A CID)compliant,thedatabase engine ensur es that modifications made b y concurr ent tr ansactions ar e pr operlyisolated fr om one another.B y default,a tr ansaction sees the data either in the state befor eanother concurr ent tr ansaction modified the data or after the other tr ansaction completed—it does not see an intermediate state.B ecause of this isolation,when multiple tr ansactions tr y to access a common resour ceconcurr ently in a noncompatible way,blocking occurs in the database.A deadlock,which isan outcome of blocking,aborts the victimiz ed database r equest that faced the deadlock.This requires that the database r equest be r esubmitted for successful execution.The executiontime of a quer y is adversely affected b y the amount of blocking and deadlock it faces.F or scalable per for mance of a multiuser database application,pr operly contr olling theisolation levels and tr ansaction scopes of the quer ies to minimize blocking and deadlock iscr itical;other wise,the execution time of the quer ies will increase significantly,even thoughthe har dware resour ces may be highly under utiliz ed.I will cover this pr oblem in depth inChapters12and13.P oor Q uer y D esignThe effectiveness of indexes depends entir ely on the way y ou wr ite SQL quer ies.R etr ievingex cessively lar ge numbers of ro ws fr om a table,or specifying a filter criterion that r eturns alarger result set fr om a table than is required,r enders the indexes ineffective. T o impr o v e per- for mance, y ou must ensur e that the SQL quer ies ar e wr itten to make the best use of new orexisting indexes.F a iling to wr ite cost-effective SQL quer ies may pr event SQL S er v er fr omchoosing proper indexes,which increases quer y execution time and database blocking.Chapter11covers ho w to wr ite effective quer ies.Q uer y design covers not only single quer ies,but also sets of quer ies often used to implement database functionalities such as a queue management among queue readers and wr iters.E v enwhen the per for mance of individual quer ies used in the design is fine,the o v er all per for mance of the database can be ver y poor.R esolving this kind of bottleneck requires a broad understanding13 of differ ent char acter istics of SQL S er v er,which can affect the per for mance of database func-tionalities.Y ou will see ho w to design effective database functionality using SQL quer ies inChapter17.P oor D atabase D esignA database should be adequately nor maliz ed to increase the per for mance of data r etr iev aland r educe blocking.F or example,if y ou have an undernor maliz ed database with customerand or der infor mation in the same table,then the customer infor mation will be r epeated in allthe or der ro ws of the customer.This r epetition of infor mation in ever y ro w will increase theI/O s requir ed to fetch all the orders placed b y a customer.A t the same time,a data wr iterwor king on a customer’s or der will reser v e all the ro ws that include the customer infor mationand thus block all other data wr iters/data readers tr ying to access the customer pr ofile.Over nor malization of a database is as bad as undernor malization.Over nor malizationincreases the number and complexity of joins requir ed to r etr ieve data. An o v er nor maliz eddatabase contains a lar ge number of tables with a ver y small number of columns.As a ve r ygener al rule of thumb, y ou may continue the nor malization pr ocess unless it causes lots ofquer ies to have four-way or gr eater joins.Having too many joins in a quer y may also be due to the fact that database entities havenot been partitioned ver y distinctly or the quer y is ser ving a ver y complex set of requir ementsthat could perhaps be better ser ved b y creating a new view or stor ed pr ocedure.D atabase design is a lar ge subject,and I will not cover it any fur ther in this book.H o w ever,if y ou want to r ead a book on database design,with an emphasis on intr oducing the subject,I r ecommend reading D ata Modeling for E v er yone by S haron Allen(A pr ess,2003).Ex cessiv e F r agmentationWhile analyzing data r etr iev al oper ations, y ou can usually assume that the data is or ganiz ed inan or derly way,as indicated b y the index used b y the data r etr iev al oper ation.H o w ever,if thepages containing the data ar e fr agmented in a nonor derly fashion,or if they contain a smallamount of data due to fr equent page splits,then the number of r ead oper ations requir ed b y thedata r etr iev al operation will be much higher than might other wise be required.The increase inthe number of r ead oper ations caused b y fr agmentation hur ts quer y per for mance.I n Chapter8,y ou will lear n ho w to analyz e and re mo ve fr agmentation.N onr eusable Ex ecution P lansTo execute a quer y in an efficient way,SQL S er v er’s quer y optimizer spends a fair amount ofCPU cy cles creating a cost-effective execution plan.The good news is that the plan is cachedin memor y,so y ou can reuse it once cr eated.H o w e ver,if the plan is designed so that y o ucannot plug v a r i able v alues into it,SQL S er v er creates a new execution plan ever y time thesame quer y is r esubmitted with differ ent v a r i able v alues.S o,for better per for mance,it isextremely impor tant to submit SQL quer ies in for ms that help SQL S er v er cache and reuse theexecution plans.Y ou will see in detail ho w to impr o v e the r eusability of execution plans inChapter9.。