Module 13: Optimizing Query PerformanceOverviewIntroduction to the Query OptimizerFunction of the Query OptimizerHow the Query Optimizer Uses Cost-Based OptimizationHow the Query Optimizer WorksQuery Optimization PhasesCaching the Execution PlanSetting a Cost LimitObtaining Execution Plan InformationViewing STATISTICS Statements OutputViewing SHOWPLAN_ALL and SHOWPLAN_TEXT OutputGraphically Viewing the Execution PlanElements of the Graphical Execution PlanReading Graphical Execution Plan OutputUsing an Index to Cover a QueryIntroduction to Indexes That Cover a QueryLocating Data by Using Indexes That Cover a QueryExample of Single Page NavigationExample of Partial Scan NavigationExample of Full Scan NavigationIdentifying Whether an Index Can Be Used to Cover a QueryDetermining Whether an Index Is Used to Cover a QueryGuidelines for Creating Indexes That Cover a QueryIndexing StrategiesEvaluating I/O for Queries That Access a Range of DataIndexing for Multiple QueriesGuidelines for Creating IndexesOverriding the Query OptimizerDetermining When to Override the Query OptimizerUsing Hints and SET FORCEPLAN StatementConfirming Query Performance After Overriding the Query OptimizerRecommended PracticesReviewModule 13: Optimizing Query PerformanceOverviewIntroduction to the Query OptimizerObtaining Execution Plan InformationUsing an Index to Cover a QueryIndexing StrategiesOverriding the Query Optimizer Introduction to the Query OptimizerFunctionHow It Uses Cost-Based OptimizationHow It WorksPhasesCaching the Execution PlanSetting a Cost LimitFunction of the Query OptimizerDetermines the Most Efficient Execution PlanDetermining whether indexes exist and evaluating their usefulnessDetermining which indexes or columns can be usedDetermining how to process joinsUsing cost-based evaluation of alternativesCreating column statistics Uses Additional InformationProduces an Execution PlanHow the Query Optimizer Uses Cost-Based OptimizationLimits the Number of Optimization Plans to Optimize in Reasonable Amount of TimeCost is estimated in terms of I/O and CPU costDetermines Query Processing Time Use of physical operators and sequence of operationsUse of parallel and serial processingHow the Query Optimizer WorksParsing ProcessParsing ProcessStandardization ProcessStandardization ProcessQuery OptimizationQuery OptimizationCompilationCompilationDatabase Access RoutinesDatabase Access RoutinesTransact-SQLTransact-SQLResultsSetResultsSetYou are here!Useful format for optimization (removes redundancy)Query Optimization PhasesQuery AnalysisIdentifies the search and join criteria of the query Index SelectionDetermines whether an index or indexes existAssesses the usefulness of the index or indexes Join SelectionEvaluates which join strategy to useCaching the Execution PlanStoring a Execution Plan in Memory One copy for all serial executionsAnother copy for all parallel executionsUsing an Execution ContextAn existing execution plan is reused, if one existsA new execution plan is generated, if one does not existRecompiling Execution PlansChanges can cause execution plan to be inefficient or invalid• For example, a large number of new rows added• ALTER TABLE/VIEW• UPDATE STATISTICS• Dropping an INDEX that is used• Explicit sp_recompileSetting a Cost LimitSpecifying an Upper Limit (based on Estimated Costs)Use the query governor to prevent long-running queries from executing and consuming system resources• Effectively controls run-away queriesSpecifying Connection LimitsUse the sp_configure stored procedureExecute the SET QUERY_GOVERNOR_COST_LIMIT statement Specify 0 to turn off the query governor Obtaining Execution Plan InformationViewing STATISTICS Statements OutputViewing SHOWPLAN_ALL and SHOWPLAN_TEXT OutputGraphically Viewing the Execution PlanViewing STATISTICS Statements OutputStatementStatementStatementStatementOutput SampleOutput SampleOutput SampleOutput SampleSTATISTICSTIMESTATISTICSTIMESTATISTICSPROFILESTATISTICSPROFILESTATISTICS IOSTATISTICS IOSQL Server Execution Times:CPU time = 0 ms, elapsed time = 2 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 2 ms.Rows Executes StmtText StmtId…-----------------------------------------------47 1 SELECT * FROM [charge] 16 WHERE (([charge_amt]>=@1) . . .Rows Executes StmtText StmtId…-----------------------------------------------47 1 SELECT * FROM [charge] 16 WHERE (([charge_amt]>=@1) . . .Table 'member'. Scan count 1,logical reads 23, physical reads 0, read-ahead reads 0.Table 'member'. Scan count 1,logical reads 23, physical reads 0, read-ahead reads 0.Viewing SHOWPLAN_ALL and SHOWPLAN_TEXT OutputStructure of the SHOWPLAN Statement Output Returns information as a set of rowsForms a hierarchical treeRepresents steps taken by the query optimizerShows estimated values of how a query was optimized, not the actual execution planDetails of the Execution Steps Explore:What is the difference Between SHOWPLAN_TEXT and SHOWPLAN_ALL Output Graphically Viewing the Execution PlanElements of the Graphical Execution Plan Reading the Graphical Execution Plan OutputUsing the Bookmark Lookup OperationElements of the Graphical Execution PlanSteps Are Units of Work to Process a Query Sequence of Steps Is the Order in Which the Steps Are Processed Logical Operators Describe Relational Algebraic Operation Used to Process a StatementPhysical Operators Describe Physical Implementation Algorithm Used to Process a StatementReading Graphical Execution Plan OutputQuery PlanSELECTCost: 0%Bookmark LookupCost: 8%Hash Match Root…Cost 28%Member.corp_noCost 9%Member.fnameCost: 10%FilterCost: 0%Sequence of StepsSequence of StepsSequence of StepsSequence of StepsIndex SeekScanning a particular range of rows from a non-clustered index.Physical operation:Logical operation:Row count:Estimated row sizes:I/O cost:CPU cost:Number of executes:Cost:Subtree cost:Index SeekIndex Seek 414240.007060.0006051.00.007675(6%)0.00767Argument:OBJECT: ([credit].[dbo].[member].[fname]), SEEK: ([member],[firstname] >=‘Rb’ AND [member],[firstname] <‘T’) ORDERED Using an Index to Cover a QueryCovering a Query: Resolving Queries without accessing the data