1 / 34

Module 12: Optimizing Query Performance

Module 12: Optimizing Query Performance. Overview. Introducing the Query Optimizer Tuning Performance Using SQL Utilities Using an Index to Cover a Query Overriding the Query Optimizer Understanding Indexing Strategies and Guidelines. Lesson: Introducing the Query Optimizer.

flo
Download Presentation

Module 12: Optimizing Query Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 12:Optimizing QueryPerformance

  2. Overview • Introducing the Query Optimizer • Tuning Performance Using SQL Utilities • Using an Index to Cover a Query • Overriding the Query Optimizer • Understanding Indexing Strategies and Guidelines

  3. Lesson: Introducing the Query Optimizer • Function of the Query Optimizer • Cost-Based Optimization • The Query Optimization Process • Phases of Query Optimization • Execution Plan Cache Management • The Query Governor

  4. Function of the Query Optimizer • Determines the Most Efficient Execution Plan • Determines whether indexes exist and evaluatingtheir usefulness • Determines which indexes or columns can be used • Determines how to process joins • Uses cost-based evaluation of alternatives • Creates column statistics • Uses Additional Information • Produces an Execution Plan

  5. Cost-Based Optimization • Limits the Number of Optimization Plans • 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

  6. The Query Optimization Process Transact-SQL Parsing Process Standardization Process Query Optimization Compilation Results Set Data Access Routines

  7. Phases of Query Optimization • 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

  8. Execution Plan Cache Management • Memory Storage • One copy for all serial executions • Another copy for all parallel executions • Execution Context • An existing execution plan is reused, if one exists • A new execution plan is generated, if one does not exist • Recompilation • Changes in database cause execution plan to be inefficient or invalid

  9. The Query Governor • How Do You Prevent Long-Running Queries? • Use the query governor to specify an upper limit • Connection Limits • Use the sp_configure stored procedure • Execute the SET QUERY_GOVERNOR_COST_LIMIT statement • Specify 0 to turn off the query governor sp_configure query governor cost limit value SET QUERY_GOVERNOR_COST_LIMIT value

  10. Lesson: Tuning Performance Using SQL Utilities • The STATISTICS Statement • The SHOWPLAN_ALL and SHOWPLAN_TEXT Statements • The Graphical Execution Plan • Analysis of Graphical Execution Plan Output • Potential Problem Areas in Execution Plans

  11. The STATISTICS Statement

  12. The SHOWPLAN_ALL and SHOWPLAN_TEXT Statements • Structure of the SHOWPLAN Statement Output • Returns information as a set of rows • Forms a hierarchical tree • Represents steps taken • Shows estimated values of how a query was optimized • Details of the Execution Steps • SHOWPLAN_ALL Returns Additional Information

  13. The Graphical Execution Plan • Steps Are Units of Work to Process a Query • Sequence of Steps Is the Order in Which the StepsAre Processed • Logical Operators Describe Relational Algebraic Operation Used to Process a Statement • Physical Operators Describe Physical Implementation Algorithm Used to Process a Statement

  14. Analysis of Graphical Execution Plan Output Query Plan Sequence of Steps Member.corp_no Cost 9% Hash Match Root… Cost 28% Bookmark Lookup Cost: 8% SELECT Cost: 0% Member.fname Cost: 10% Filter Cost: 0%

  15. Potential Problem Areas in Execution Plans

  16. Lesson: Using an Index to Cover a Query • Introduction to Indexes That Cover a Query • Single-Page Navigation • Partial-Scan Navigation • Full-Scan Navigation • Factors Affecting an Index’s Ability to Cover a Query • Methods to Verify That an Index Is Used to Cover a Query • Guidelines for Creating Indexes That Cover a Query

  17. Introduction to Indexes That Cover a Query • 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 • Indexes That Cover Queries Retrieve Data Quickly

  18. Lang Akers Akers Kim Eric Kim … … Lang Eric … … … Ganio … … Jon … … … … … … … Akers Kim Ganio Jon Barr … Hall Hall Don Don Barreto … Hartono Tommy Borba … Jones Brannon Lang Eric Budai … Jones David Martin … Martin … Martin … Morris … Single-Page Navigation SELECT lastname, firstname FROM member WHERE lastname = 'Hall' Index Pages Non-Leaf Level Leaf Level (Key Value) … … … Data Pages

  19. Akers … Akers Jordan … … Jordan … Chai Lang … … … … Moyer Dubois … … Smith Ganio … … Dubois … Ganio … Jordan … Lang … Duffy … Hall … Kim … Martin … Fine … Hartono … Kim … Martin … Fort … Jones … Koch … Martin … Akers Chai … … Moyer Smith … … Funk … Jones … Koch … Morris … Barr Con … … Smith Nash … … Cox Barreto … … Smith Nay … … Borba Cox … … Owen Smith … … Budai Danner … … Smith Russo … … Partial-Scan Navigation USE credit SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Funk' AND 'Lang' Index Pages Non-Leaf Level Leaf Level (Key Value) Data Pages

  20. Akers … Chai … Dubois … Ganio … Jordan … Lang … Moyer … Smith … Barr … Con … Duffy … Hall … Kim … Martin … Nash … Smith … Barreto … Cox … Fine … Hartono … Kim … Martin … Nay … Smith … Borba … Cox … Fort … Jones … Koch … Martin … Owen … Smith … Budai … Danner … Funk … Jones … Koch … Morris … Russo … Smith … Martin Akers Akers Smith … Ganio Martin … … Full-Scan Navigation USE credit SELECT lastname, firstname FROM member Index Pages Non-Leaf Level Leaf Level (Key Value) … Data Pages

  21. Factors Affecting an Index’s Ability to Cover a Query • All Necessary Data Must Be in the Index • A Composite Index Is Useful Even if the First Column Is Not Referenced • A WHERE Clause Is Not Necessary • A Nonclustered Index Can Be Used if It Requires Less I/O Than a Clustered Index Containing a Column Referenced in the WHERE Clause • Indexes Can Be Joined to Cover a Query

  22. Methods to Verify That an Index Is Used to Cover a Query • Execution Plan Output • Displays the phrase “Scanning a non-clustered index entirely or only a range” • No Bookmark Lookup operator • I/O Comparison • Nonclustered index • Total number of levels in the non–leaf level • Total number of pages that make up the leaf level • Total number of rows per leaf-level page • Total number of rows per data page • Total number of pages that make up the table

  23. Guidelines for Creating Indexes That Cover a Query • Add Columns to Indexes • Minimize Index Key Size • Maintain Row-to-Key Size Ratio

  24. Lesson: Overriding the Query Optimizer • Hints and SET FORCEPLAN Statement • Guidelines for Maintaining Overridden Queries • Alternatives to Overriding the Query Optimizer

  25. Hints and SET FORCEPLAN Statement • Table Hints • Join Hints • Query Hints • SET FORCEPLAN Statement

  26. Guidelines for Maintaining Overridden Queries • Verify That Performance Improves • Document Reasons for Using Optimizer Hints • Retest Queries Regularly

  27. Alternatives to Overriding the Query Optimizer • Limit Optimizer Hints • Explore Other Alternatives Before Overriding the Query Optimizer by: • Updating statistics • Recompiling stored procedures • Reviewing the queries or search arguments • Evaluating the possibility of building different indexes

  28. Lesson: Understanding Indexing Strategies and Guidelines • I/O Consideration and Evaluation • Indexing Strategy for Multiple Queries • Guidelines for Creating Indexes • Best Practices

  29. I/O Consideration and Evaluation SELECT charge_noFROM chargeWHERE charge_amt BETWEEN 20 AND 30

  30. Indexing Strategy for Multiple Queries Example 1 USE credit SELECT charge_no, charge_dt, charge_amt FROM charge WHERE statement_no = 19000 AND member_no = 3852 Example 2 USE credit SELECT member_no, charge_no, charge_amt FROM charge WHERE charge_dt BETWEEN '07/30/1999' AND '07/31/1999' AND member_no = 9331

  31. Guidelines for Creating Indexes • Determine the Priorities of All of the Queries • Determine the Selectivity for Each Portion of the WHERE Clause of Each Query • Determine Whether to Create an Index • Identify the Columns That Should Be Indexed • Determine the Best Column Order of Composite Indexes • Determine What Other Indexes Are Necessary • Test the Performance of the Queries

  32. Best Practices Use the Query Governor to Prevent Long-Running Queries from Consuming System Resources ü Have a Thorough Understanding of the Data and How Queries Gain Access to Data ü Create Indexes That Cover the Most Frequently Used Queries ü Establish Indexing Strategies for Individual and Multiple Queries ü Avoid Overriding the Query Optimizer ü

  33. Lab A: Optimizing Query Performance • Exercise 1: Using the Graphical Execution Plan to Determine How a Query Is Resolved • Exercise 2: Comparing I/O for Queries That Are Covered or Not Coveredby Indexes • Exercise 3: Comparing I/O for Queries That Retrieve a Range of Data • If Time Permits:Using Optimizer Hints to Force the Use of an Index or Join

  34. Course Evaluation

More Related