This blog is a continuation to SQL Execution plan deep dive (01-Basics I) which explains what an execution plan is and different execution plan components.
How to view different execution plan details: Execution plan can be viewed in 3 different ways Text/ XML, and Graphical.
Text: SHOWPLAN_ALL, SHOWPLAN_TEXT can be used to see Expected execution plan in plain text for analysis.
XML: SHOWPLAN_XML and STATISTICS XML can be used to see the plan in XML Format and SSMS can be used to see the details in Graphical format. This can be used to see both estimated and actual execution plan.
Graphical: Graphical plans are easy to view and analyze. We can just see estimated execution plan and Actual Execution Plan. We will analyze the execution plans using the graphical method in this blog!
Sample Execution Plan 1:
- What we can understand from above execution plan:
While analyzing the data, data flow happens from left to right and logical flow happens from left to right. I will explain the difference between these two.
Data Flow (Right to Left):
- Non-Clustered index AK_Product_Name will be used to find Adjustable Race and returns the ProductID.
- Clustered Index PK_Product_ProductID will be used to fetch all columns related to key and returns the data
- Nested loop consolidates if there are multiple rows and pushes the data
Logical flow (Left to Right):
- Logical flow requests data from Select to Top and Top requests Nested Loop
- Nested Loop requests data from Non-Clustered index AK_Product_Name.
- Nested Loop requests Clustered Index PK_Product_ProductID to fetch all columns.
- Nested Loop repeats above 2 steps multiple times
- NOTE: NodeID represents the logical flow of the query
- What additional information we can find from execution plan components:
- When the mouse is scrolled over on any execution plan component, additional information will be visible which is very crucial to analyze the performance. This information is related to both Estimated and Actual plan.
- Physical and Logical operation: Physical and Logical operations will be mostly same and component name will also be same. This may change at times as Physical operation can be Hash Match and Logical operation can be Inner join or Outer join.
- Execution Mode: Execution Mode can either Row or Batch.
- Cost: I/O, CPU, Subtree, and Operator cost will be available in Estimated execution plan but this value will not be generated in the actual execution plan. This cost will be used to compare and choose the optimal execution plan.
- Number of Executions: Estimated and Actual number or executions are visible in Estimated and Actual execution plan. A significant difference between Estimated and Actual numbers indicates possible performance impact.
- Number of rows: We can validate Estimated and Actual number of rows similar to number of executions. Gaps between Estimated and Actual indicates the possible issue
- NodeID: This represents the numbering of the component considering the logical flow of the request.
- Rebinds and Rewinds: This indicates number of times the loop executions
Query Hints: Using Query hints is the wy to force Optimizer to create the plan in a specific way.
- Table hints: Hints to control table scans and use specific indexes.
- Hash /Order Group: Hash and Order groups can be used to alternatively used to find aggregate values.
- MERGE /HASH /CONCAT UNION: This is to hint how a UNION works
- LOOP /MERGE /HASH JOIN: All these methods are used for a table join (Example: Merge join require pre-sorting). We can change the way how a join should work.
- FAST n: This hints optimizer to optimize the execution plan to get first n rows in fasters way.
- FORCE ORDER: When multiple joins exists in SQL query Optimizer decides the order the jons to take place for optimal time but this hints to follow the order how the query is written.
- MAXDOP: This hint is used to control parallelism.
- OPTIMIZE FOR: This is an interesting hint which is applicable only after SQL 2005 engine. SQL Engine is smart enough to decide Execution plan based on the filter value being used but when a variable is passed it may not be able to fetch best execution plan. Using OPTIMIZE FOR we can provide SQL Server assumed parameter value and Plan will be created based on that.
- RECOMPILE: This option can be used to instruct optimizer to regenerate the execution plan every time it executes. This helps specially in parameterized query scenarios.
- ROBUST PLAN: This hint helps Optimizer to choose a plan which can work addressing any errors due to XML, Binary, and Text data types.
- KEEP PLAN: This hints optimizer to be more liberal while identifying the recompilation requirements. Frequency of recompiles can be reduced using this hint.
- MAXRECURSION: This is to limit the number of recursions in SQL Query while using CTE.
- USE PLAN: XML Parameter can be supplied to avoid optimizer creating the query.
What else I need to know about Execution plans:
- Execution plan can be saved in XLM format and shared across.
- Execution plan which is saved in XML can be opened in SSMS anytime for analysis.
- Execution plan can also be captured using SQL profiler (Events -> Performance -> ShowPlan XML)
- Each component of execution plan contains relative cost %. This is one of the pointers to look for while analyzing long running query.
- The thickness of arrow marks indicates the number of rows transferred in the execution plan. Thick lines are the places to look for.
- Cursers: When cursers are used in SQL Query estimated execution plan will show logical execution plan but actual execution plan shows one execution for each iteration.
I have read this book (http://download.red-gate.com/ebooks/SQL/sql-server-execution-plans.pdf) while writing this blog and this is very informative book!