Understanding Graphical Execution Plans Level 200

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