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
Query Plan
Operators in an Execution Plan
• Logical and Physical Operators
• Parallelism Physical Operators
• Cursor Operators
• Language Elements
Columns in a Plan
Rows
EstimateIO
Executes
EstimateCPU
StmtId
AvgRowSize
NodeId
TotalSubtreeCost
Parent
OutputList
PhysicalOp
Warnings
LogicalOp
Type
Argument
Parallel
DefinedValues
EstimateExecutions
EstimateRows
Cached Query Plans
• If a Cached Plan exists then SQL Server will use
this cached plan
DEMO 01
Important Operators in Execution Plans
Select
(Result)
Sort
Clustered
Index Seek
Clustered
Index Scan
Non-clustered
Index Scan
Non-clustered Table Scan
Index Seek
RID Lookup
Key Lookup
Hash Match
Nested
Loops
Merge Join
Top
Compute
Scalar
Constant
Scan
Filter
Lazy Spool
Spool
Eager Spool
Stream
Aggregate
Distribute
Streams
Repartition
Streams
Gather
Streams
Bitmap
Split
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
Index Seek
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
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
DEMO 02
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
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
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
DEMO 03
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.
DEMO 04
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
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
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
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.
DEMO 05
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
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
DEMO 06
Rewinds and Rewinds
Questions
THANK YOU
Descargar

Performance tuning