1 / 38

SQL Server Performance Tuning(I)

SQL Server Performance Tuning(I). -- SQL Server 2000. Haijun Yang AREC SQL Support Team mailto:hjyang@microsoft.com Feb, 2001. Agenda. Introduction to Optimizing Queries Index Strategies Query Plan Analysis. Overview. Introduction to the Query Optimizer

kiele
Download Presentation

SQL Server Performance Tuning(I)

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. SQL Server Performance Tuning(I) -- SQL Server 2000 Haijun Yang AREC SQL Support Team mailto:hjyang@microsoft.com Feb, 2001

  2. Agenda • Introduction to Optimizing Queries • Index Strategies • Query Plan Analysis

  3. Overview • Introduction to the Query Optimizer • Obtaining Query Plan Information • Indexing Fundamentals • Introduction to Statistics

  4. Function of the Query Optimizer • Determines the Most Efficient Query 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 • Creating column statistics • Produces a Query Plan • Uses Available Information

  5. How the Query Optimizer Uses 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. Transact-SQL Parsing Process Standardization Process Query Optimization Compilation Results Set Database Access Routines How the Query Optimizer Works

  7. 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

  8. Caching Query Plan • Storing a Query Plan in Memory • One copy for all serial executions • Another copy for all parallel executions • Using an Execution Context • An existing query plan is reused, if one exists • A new query plan is generated, if one does not exist • Recompiling Query Plans • Changes in database cause query plan to be inefficient or invalid

  9. Obtaining Query Plan Information • Querying the sysindexes Table • Viewing STATISTIC Statements Output • Viewing SHOWPLAN_ALL and SHOWPLAN_TEXT Output • Viewing Graphical Showplan

  10. Querying the sysindexes Table • Stores Table and Index Information • Type of index (indid) • Space used (dpages, reserved, and used) • Fill factor (OrigFillFactor) • Stores Statistics for Each Index

  11. 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 query plan • Details of the Execution Steps • Difference Between SHOWPLAN_TEXT and SHOWPLAN_ALL Output

  12. Viewing Graphical Showplan • Elements of Graphical Showplan • Reading Graphical Query Plan Output

  13. Elements of Graphical Showplan • 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

  14. Sequence of Steps Query Plan 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 non-clustered index. Filter Cost: 0% Member.fname Cost: 10% 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 Reading Graphical Query Plan Output

  15. Indexing Fundamentals • Understanding the Data • Limiting a Search • Determining Selectivity • Determining Density • Determining Distribution of Data

  16. High selectivity member_no last_name first_name Number of rows meeting criteriaTotal number of rows in table 100010000 1 Randall Joshua = 10% = 2 Flood Kathie . SELECT *FROM memberWHERE member_no > 8999 . . 10000 Anderson Bill Low selectivity member_no last_name first_name Number of rows meeting criteriaTotal number of rows in table 900010000 1 Randall Joshua = 90% = 2 Flood Kathie . SELECT *FROM memberWHERE member_no < 9001 . . 10000 Anderson Bill Determining Selectivity

  17. last_name first_name Randall Joshua . . . Randall Cynthia Randall Tristan . . . Ota Lani . . . Determining Density High Density SELECT *FROM memberWHERE last_name = ‘Randall’ Low Density SELECT *FROM memberWHERE last_name = ‘Ota’

  18. Standard Distribution of Values Number ofLast Names A - E F - J K - O P - U V - Z Last Name Even Distribution of Values Number ofLast Names A - B C - F G - K L - N O - Z Last Name Determining Distribution of Data

  19. Updating Statistics • Frequency of Updating Statistics • Automatically Updating Statistics • Manually Updating Statistics • If you created an index before any data was put into the table • If a table is truncated • If you added many rows to a table that contained minimal or no data, and you plan to immediately query against that table

  20. Viewing Statistics SQL Server Returns Information on: • The Time When the Statistics Were Last Updated • The Number of Rows Sampled to Produce the Histogram • Density Information • Average Key Length • Contents of the statsblob ColumnDBCC SHOW_STATISTICS

  21. Overview • Accessing Data • Using an Index to Cover a Query • Using Index Tuning Tools to Improve Query Performance • Indexing Strategies

  22. Accessing Data • Table Scans and Indexes • Index Architecture and Navigation • Using Row Identifiers to Access Data

  23. Table Scans Access Every Page Data Pages Data Pages … Indexes Use Key Values to Locate Data Index Pages Data Pages … Table Scans and Indexes

  24. Data Pages LeafLevel Index Architecture and Navigation Index Pages Clustered Index Nonclustered Index Non-LeafLevel Index Pages Non-LeafLevel Leaf Level(Key Value) Data Pages

  25. Using Row Identifiers to Access Data • Creation and Storage of a RID • Consists of the file number, page number, and slot number, which identify the exact location of a row • Is an internal value • RID Is Used to Retrieve Rows • Accessing the nonclustered index • Performing a Bookmark Lookup operation

  26. Using an Index to Cover a Query • Introduction to Indexes That Cover a Query • Locating Data by Using Indexes That Cover a Query • Identifying Whether an Index Can Be Used to Cover a Query • Determining Whether an Index Is Used to Cover a Query • Guidelines to Creating Indexes That Can Cover a Query

  27. Locating Data by Using Indexes That Cover a Query • Example of Single Page Navigation • Example of Partial Scan Navigation • Example of Full Scan Navigation

  28. Dunn Akhtar Chai … … … Ganio … Lang Jordan … … Smith Morgan … … Barr Con Dunn … … … Hall Hall … … Kim Martin … … Nash Smith … … Barr Fine Con … … … Hart … Martin Kim … … Smith Nay Akhtar Martin Akhtar … … Data Pages Cox Borm Fort … … … Jones … Martin Koch … … Smith Ota … Ganio Smith … … Buhl Dale Funk … … … Jones … Moris Koch … … Rudd Smith … … Martin … … Example of Single Page Navigation SELECT lastname, firstname FROM member WHERE lastname = 'Hall' Index Pages Non-Leaf Level Leaf Level(Key Value) …

  29. USE credit SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Funk' AND 'Lang' Index Pages Non-Leaf Level Leaf Level(Key Value) Dunn … Ganio … Jordan … Lang … Dunn … Hall … Kim … Martin … Fine … Hart … Kim … Martin … Fort … Jones … Koch … Martin … Chai Akhtar … … Smith Morgan … … Funk … Jones … Koch … Moris … Barr Con … … Smith Nash … … Barr Con … … Nay Smith Martin Akhtar Akhtar … … Data Pages Borm Cox … … Smith Ota Smith Ganio … … … Buhl Dale … … Smith Rudd Martin … … … … Example of Partial Scan Navigation

  30. USE credit SELECT lastname, firstname FROM member Index Pages Non-Leaf Level Leaf Level(Key Value) Akhtar … Chai … Dunn … Ganio … Jordan … Lang … Morgan … Smith … Barr … Con … Dunn … Hall … Kim … Martin … Nash … Smith … … Barr … Con … Fine … Hart … Kim … Martin … Nay … Smith … Borm … Cox … Fort … Jones … Koch … Martin … Ota … Smith … Buhl … Dale … Funk … Jones … Koch … Moris … Rudd … Smith … Akhtar Akhtar Martin Data Pages Ganio … Smith … … Martin Example of Full Scan Navigation

  31. Identifying Whether an Index Can Be Used to Cover a Query • All Data Can Be Found in the Index • First Column of a Composite Index Is Not Referenced in the WHERE Clause • A WHERE Clause Does Not Exist • There Is a Clustered Index on a Column Referenced in the WHERE Clause, and Selectivity Requires More I/O to Use the Clustered Index • Join Operations Exist

  32. Determining Whether an Index Is Used to Cover a Query • Observing the Query Plan Output • Displays the phrase “Scanning a nonclustered index entirely or only a range” • Comparing I/O • 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

  33. Guidelines to Creating Indexes That Can Cover a Query • Adding Columns to Indexes • Limiting Index Key Size • Maintaining Row-to-Key Size Ratio

  34. Demo: Analyzing How Queries Are Covered By Different Types of Indexes

  35. Using Index Tuning Tools to Improve Query Performance • Using the Index Tuning Wizard • Recommends or verifies optimal index configuration • Provides cost analysis reports • Recommends ways to tune the database • Specifies criteria when a workload is evaluated • Using the Index Analysis Tool • Recommends optimal set of indexes to support a given query or batch

  36. Access Method Page I/O Table scan 10,417 Clustered index on the charge_amt column 1042 Nonclustered index on the charge_amt column 100,273 Composite index on charge_amt, charge_nocolumns 273 Evaluating I/O for Queries That Access a Range of Data SELECT charge_noFROM chargeWHERE charge_amt BETWEEN 20 AND 30

  37. Demo : Analyzing Queries That Use the AND and OR Operators

  38. go YOU WANT TO WHERE DO ? TODAY Microsoft

More Related