Exploring Execution Plans, Learning to Read SQL Server Execution Plans

Getting started reading execution plans is very straight forward. The real issue is understanding the plans as they grow in size and complexity. This session will show you how to explore the nooks and crannies of an execution plan in order to more easily find the necessary information needed to make what the plan is telling you crystal clear. The information presented here will better empower you to traverse the execution plans you’ll see on your own servers. That knowledge will make it possible to more efficiently and accurately tune and troubleshoot your queries.

1. Exploring Execution Plans
Grant Fritchey

2. DevOps Advocate
Microsoft PostgreSQL MVP
AWS Community Builder
@gfritchey
[email protected]
scarydba.com
/in/gfritchey

3. Grant Fritchey | www.ScaryDBA.com
THE OPTIMIZER

4. Grant Fritchey | www.ScaryDBA.com
Where the Query Goes
QUERY
OPTIMIZER
QUERY
PROCESSOR
QUERY
WRITER
Q
u
e
r y
R
e
s
u
l
t
s
ExecutionPlan

5. Grant Fritchey | www.ScaryDBA.com
Relational Engine
QUERY

6. Grant Fritchey | www.ScaryDBA.com
QUERY
Relational
Engine
QUERY OPTIMIZER
Relational Engine

7. Grant Fritchey | www.ScaryDBA.com
QUERY
Relational
Engine
Query
Parsor
Syntax
CheckParse
Tree
QUERY OPTIMIZER
Relational Engine

8. Grant Fritchey | www.ScaryDBA.com
QUERY
Relational
Engine
Query
Parsor
Syntax
CheckParse
Tree
Algebrizer
Resolves
Objects
Query
Processor
Tree
QUERY OPTIMIZER
Relational Engine

9. Grant Fritchey | www.ScaryDBA.com
QUERY
Relational
Engine
Query
Parsor
Syntax
CheckParse
Tree
Algebrizer
Resolves
Objects
Query
Processor
Tree
Optimizer
Execution
Plan
QUERY OPTIMIZER
Relational Engine

10. Grant Fritchey | www.ScaryDBA.com
Relational Engine
QUERY
RESULT
Relational
Engine
Query
Parsor
Syntax
CheckParse
Tree
Algebrizer
Resolves
Objects
Query
Processor
Tree
Optimizer
Execution
Plan
Storage
Engine
QUERY OPTIMIZER

11. Grant Fritchey | www.ScaryDBA.com
What’s All This Mean?
SELECT ID FROM TableA WHERE ID = 42
Table A
SCAN
SEEK
OR

12. Grant Fritchey | www.ScaryDBA.com
What’s All This Mean?
SELECT a.ID, b.Name, c.Value
FROM TableA as a
JOIN TableB as b
On a.ID = B.ID
JOIN TableC as c
ON b.OtherID = c.OtherID
WHERE a.ID = 42
324 Possible Plans
LOOP HASH MERGE

13. Grant Fritchey | www.ScaryDBA.com
Flowchart
Plan
Look-up
Correctness
and Performance
Checks
Compile
New Plan
Execute
Query
Fetch Plan
From Cache
Recompile
Pass
Plan
Found
Plan
Not
Found

14. Grant Fritchey | www.ScaryDBA.com
Flowchart
Plan
Look-up
Correctness
and Performance
Checks
Compile
New Plan
Execute
Query
Fetch Plan
From Cache
Recompile
Pass
Plan
Found
Plan
Not
Found
EXECPT!
Query Store

15. Grant Fritchey | www.ScaryDBA.com
Types of Execution Plan
Estimated plan
and Actual plan
What really is the
difference?
Does the optimiser do a
quick job with an
estimated plan?
Does the optimiser do
less work for an
estimated plan?
No! None of those
Alternative names
Plan without runtime
information
Plan with runtime
information
But that’s longer to
write and say
Only real
difference is the
run-time
information
Actual rows affected
Actual executions
Data sizes
All costs are still
estimations
Session Code | All About Execution
Plans
15

16. Grant Fritchey | www.ScaryDBA.com
Optimization Levels
•Trivial
•Full
•Transactional Processing
•Quick Plan
•Full Optimization
Optimization
Level
•Timeout
•Good Enough Plan Found
•Memory Limit Exceeded
•Error!
Reason For
Early
Termination

17. Grant Fritchey | www.ScaryDBA.com
Where To Start?

18. Grant Fritchey | www.ScaryDBA.com
Where To Start?

19. Grant Fritchey | www.ScaryDBA.com
First Operator
Plan size
Compile time
Memory grant
Missing Indexes
Optimization level
Parameter
•Compiled value
•Runtime Value
Query hash
Reason for early termination
ANSI settings

20. Grant Fritchey | www.ScaryDBA.com
Right to Left or Left to Right?
A clue:
English
Another clue:
These things

21. Grant Fritchey | www.ScaryDBA.com
Left to Right or Right to Left
Answer: Both
Logical processing order:
•Represents how the optimizer sees the query
•Reading it from Left to Right
Physical processing order
•Represents the flow of data
•Follow the arrows/pipes from Right to Left
Both are necessary to understand certain plans

22. Grant Fritchey | www.ScaryDBA.com
What Else to Look For
Warnings
Most Costly Operations
Fat Pipes
Extra Operations
Scans
Estimated vs. Actual
Missing Indexes

23. Grant Fritchey | www.ScaryDBA.com
Where to Look
Tool tips
•Eh
Properties
•Follow the details

24. Grant Fritchey | www.ScaryDBA.com
DEMOS
24

25. Grant Fritchey | www.ScaryDBA.com
Questions?
How would you… ?
Why does… ?

26. 26

27. Grant Fritchey | www.ScaryDBA.com
DevOps Advocate
Microsoft PostgreSQL MVP
AWS Community Builder
@gfritchey
[email protected]
scarydba.com
/in/gfritchey