1 / 29

Understanding Graphical Execution Plans Level 200

Understanding Graphical Execution Plans Level 200. Ashwani Roy . Agenda. Query Processing lifecycle by Database Engine Elements in a Execution Plans Important Execution Plan Operators. What Happens when a Query is submitted. Query Parsing Query Optimization Query Execution .

nadine
Download Presentation

Understanding Graphical Execution Plans Level 200

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. Understanding Graphical Execution Plans Level 200 Ashwani Roy

  2. Agenda • Query Processing lifecycle by Database Engine • Elements in a Execution Plans • Important Execution Plan Operators

  3. What Happens when a Query is submitted • Query Parsing • Query Optimization • Query Execution

  4. Query Plan

  5. Operators in an Execution Plan • Logical and Physical Operators • Parallelism Physical Operators • Cursor Operators • Language Elements

  6. Columns in a Plan

  7. Cached Query Plans • If a Cached Plan exists then SQL Server will use this cached plan DEMO 01

  8. Important Operators in Execution Plans

  9. Index Seek • Reads B-tree entries to determine the data page • The Argument column contains the name of the nonclustered index being used • Prefered for highly selective queries

  10. Index Seek

  11. Index Scan • Horizontal traversal of the leaf level of the index from the first page to the last • Retrieves all rows from the nonclustered index • The Argument column contains the name of the nonclustered index being used

  12. Clustered Index Scan • The clustered index scan’s logical and physical operator scans the clustered index • The Argument column contains the name of the clustered index • If the table does not have Clustered Index the same Query will produce Table Scan

  13. DEMO 02

  14. Clustered Index Seek • Cluster index seek • Uses the seeking ability of indexes to retrieve rows • The Argument column contains the name of the clustered index being used • Seek() predicate contains the columns used for seeking

  15. Bookmark Lookups • Uses a bookmark to look up a row in a clustered index or table • The Argument column contains the bookmark label • Can be removed by covering columns • May have a performance improvement

  16. KEY LOOKUP A Key Lookup is a bookmark lookup on a table with a clustered index. Means that the optimizer cannot retrieve the rows in a single operation, and has to use a clustered key (or a row ID) to return the corresponding rows from a clustered index (or from the table itself). Performance can be improved by making Non-Clustered Index or Covering Index

  17. DEMO 03

  18. RID Lookup A type of bookmark lookup Occurs on a heap table (a table that doesn't have a clustered index) Uses a row identifier to find the rows to return.

  19. DEMO 04

  20. Nested Loop • The top input to the nested loop is the outer table • The bottom input to the nested loop is the inner table • For each outer row, searches for matching rows are in the inner input table • Effective if the outer input is very small and the inner input is preindexed and very large • Optimizer sometimes sorts the outer input to improve locality of the searches on the index over the inner input • Best when search exploits an index (indexes on join columns are used) • Low memory requirement

  21. Hash Join • The top input is build input, the smaller of the two inputs • The bottom input is probe input • The hash join first scans or computes the whole build input • Requires at least one equality clause in the join predicate • Good for ad-hoc queries

  22. Merge Join • Both inputs should be sorted on the merge column keys • An index on a correct set of columns is useful • A many-to-many merge join uses a temporary table to store rows • Very fast if the data that you want can be obtained presorted from existing B-tree indexes

  23. WHICH JOIN IS GOOD • NONE AND ALL • A Merge Join is an efficient way to join two tables,# • when the join columns are pre sorted • if the join columns are not pre sorted, the query optimizer has the option of • a) sorting the join columns first, then performing a Merge Join, or • b) performing a less efficient Hash Join. The query optimizer considers all the options and generally chooses the execution plan that uses the least resources.

  24. DEMO 05

  25. Stream Aggregation • The argument column of the plan output shows the list of columns of the GROUP BY or DISTINCT clause • The list of aggregate expressions will appear in the Defined Values column of the plan output • Best for smaller sets or sets already sorted • Input is sorted and output is ordered

  26. Hash Aggregation • Used with large sets • Aggregations are evaluated while building the hash • Input can be in random order; output is always in random order

  27. DEMO 06

  28. Rewinds and Rewinds

  29. Questions THANK YOU

More Related