Module 13 Optimizing Query Performance Overview 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 Limit Function 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 Plan How 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 processing How the Query Optimizer Works Transact SQL Transact SQL You are here Parsing ParsingProcess Process Standardization StandardizationProcess Process Useful format for optimization removes redundancy Query QueryOptimization Optimization Compilation Compilation Database DatabaseAccess AccessRoutines Routines Results Results Set Set 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 use Caching 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 recompile Setting 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 Plan Viewing STATISTICS Statements Output Statement Statement Output OutputSample Sample STATISTICS STATISTICS TIME TIME SQL SQL Server Server Execution Execution Times Times CPU time 0 ms elapsed CPU time 0 ms elapsed time time 22 ms ms STATISTICS STATISTICS PROFILE PROFILE Rows StmtId Rows Executes Executes StmtText StmtText StmtId 47 SELECT 16 47 11 SELECT FROM FROM charge charge 16 WHERE WHERE charge amt 1 charge amt 1 STATISTICS STATISTICSIO IO Table Table member member Scan Scan count count 1 1 logical logical reads reads 23 23 physical physical reads reads 0 0 read ahead 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 Operation Elements 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 Statement Reading Graphical Execution Plan Output Query Plan Sequence Sequence of of Steps Steps Member corp no Cost 9 SELECT Cost 0 Bookmark Lookup Cost 8 Hash Match Root Cost 28 Index Seek Scanning a particular range of rows from a nonclustered index Physical operation Logical operation Row count Estimated row sizes I O cost CPU cost Number of executes Cost Subtree cost Index Seek Index Seek 414 24 0 00706 0 000605 1 0 0 007675 6 0 00767 Argument OBJECT credit dbo member fname SEEK member firstname Rb AND member firstname T ORDERED Filter Cost 0 Member fname Cost 10 Using an Index to Cover a Query Covering a Query Resolving Queries without accessing the data pages Introduction to Indexes Locating Data by Using Indexes Identifying Whether an Index Can Be Used Determining Whether an Index Is Used Guidelines for Creating Indexes Introduction to Indexes That Cover a Query Indexes That Cover Queries Retrieve Data Quickly Only Nonclustered Indexes Cover Queries Indexes Must Contain All Columns Referenced in the Query No Data Page Access Is Required Indexed Views Can Pre Aggregate Data Locating Data by Using Indexes That Cover a Query Example of Single Page Navigation Example of Partial Scan Navigation Example of Full Scan Navigation Example of Single Page Navigation Index Pages Akhtar Lang Akhtar Ganio Sarah Eric Sarah Jon SELECT SELECT lastname lastname firstname firstname FROM member FROM member WHERE WHERE lastname lastname Hall Hall Non Leaf Level Lang Eric Leaf Level Key Value Akhtar Barr Barr Borm Buhl Sarah Data Pages Ganio Hall Hart Jones Jones Jon Don Sherri Amy Beverly Lang Martin Martin Martin Moris Eric Example of Partial Scan Navigation Index Pages Akhtar Jordan Akhtar Chai Dunn Ganio USE USE credit credit SELECT SELECT lastname lastname firstname firstname FROM member FROM member WHERE WHERE lastname lastname BETWEEN BETWEEN Funk Funk AND AND Lang Lang Jordan Lang Morgan Smith Non Leaf Level Leaf Level Key Value Akhtar Barr Barr Borm Buhl Data Pages Chai Con Con Cox Dale Dunn Dunn Fine Fort Funk Ganio Hall Hart Jones Jones Jordan Kim Kim Koch Koch Lang Martin Martin Martin Moris Morgan Nash Nay Ota Rudd Smith Smith Smith Smith Smith Example of Full Scan Navigation Index Pages USE USE credit credit SELECT SELECT lastname lastname firstname firstname FROM member FROM member Akhtar Martin Non Leaf Level Akhtar Ganio Martin Smith Leaf Level Key Value Akhtar Barr