SQL Server Execution Plan – Part 1 (Basics)

Hi geeks,

First of all I would like to make an apology for not posting any blogs for the last two months (was busy with my work) but it’s better late than never….

Today I am blogging about SQL Server Execution Plan but this topic is so huge that I have to divide this into multiple segments. Today I am posting the 1st one, hope you all like it.

Execution Plan
• In simple words, it is how SQL Server query optimizer is going to execute a query or already executed a query. A query can be executed in multiple ways but the trick is to find the optimal one in minimum possible time with maintaining the integrity of the database.
• SQL Server uses different means like statistics, index etc.to calculate the optimal plan. But it’s costly and time consuming to calculate the optimal plan every time a query is executed. So SQL Server saves the query plan into the Plan Cache area of the buffer pool and reuses it.
• Query plan or Execution plan serves as the primary mean of troubleshooting and investigating SQL Server Query Performance.

Execution Plan Types
• Estimated
§ As the name suggest it is an estimate of the query optimizer about how the query will be executed.
§ Generated using statistics.
§ Generated before actual execution of the query.
§ Some times can vary greatly with the actual plan due to outdated statistics (not desirable).
§ Can be viewed by selecting the query and using Ctrl + L key or clicking on Display Estimated Plans on SSMS.
• Actual
§ It is the Actual Plan used to execute the query.
§ Get stored in Plan Cache if the cost is high and reused.
§ Generated using the estimated plan if the estimated plan is used or gets generated during the run time (CPU intensive and costly).
§ Can be viewed only after the query gets executed using Ctrl + M key or clicking on Show Actual Plan in SSMS.
§ sys.dm_exec_query_plan DMF can be used to view the actual plan, if the plan is already cached in the Plan Cache.

Execution Plan Formats
• Graphical
§ Quick and easy to read but the detailed data for the plan is masked.
§ Both actual and estimated plan can be viewed in this format.
• Text
§ Harder to read but more information is available.
§ Can be viewed in three formats
*        SHOWPLAN_ALL: Overall a complete set of information.
*        SHOWPLAN_TEXT: Very limited set of data.
*        STATISTICS PROFILE: Similar to SHOWPLAN_ALL.
• XML
§ Contains the complete set of data available and viewed as XML format.
§ Can be viewed in two formats
*       SHOWPLAN_XML: Can be used to display estimated execution plan in XML format.
*       STATISTICS_XML: Can be used to display actual execution plan in XML format.

Because most of the time we DBA’s use graphical plan to interpret a query plan before going to demo a small introduction about the different components of the graphical plan.

• Each node in the tree structure is represented as an icon that specifies the logical and physical operator used to execute that part of the query or statement.
• Each node is related to a parent node. Child nodes of the same parents are drawn in the same column. However, all nodes in the same column do not necessarily have the same parent. Rules with arrowheads connect each node to its parent.
• Operators are shown as symbols related to a specific parent.
• Arrow width is proportional to the number of rows. Actual number of rows is used when it is available. If not, then the estimated number of rows is used.
• When the query contains multiple statements, multiple query execution plans are drawn.
• The parts of the tree structures are determined by the type of statement executed.
• For parallel queries, which involve multiple CPUs, the Properties for each node in the graphical execution plan displays information about the operating system threads used. To view the properties for a node, right-click the node, and then click Properties.

Fig 1: Displays a sample graphical plan

Tool Tips:
Each node displays ToolTip information when the cursor is pointed at it as described in the following table. Not all nodes in a graphical execution plan contain all ToolTips items described below

• Physical Operation:
§ Displays the physical operator used, such as Hash Join or Nested Loops. Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics or missing join predicates.

• Logical Operation:
§ Displays the logical operator that matches the physical operator, such as the Inner Join operator. The logical operator is listed after the physical operator at the top of the ToolTip.

• Estimated Row Size:
§ Displays the estimated row size.

• Estimated I/O Cost:
§ Displays the estimated cost of all I/O activity for the operation. This value should be as low as possible.

• Estimated CPU Cost:
§ Displays the estimated cost of all CPU activity for the operation.

• Estimated Operator Cost:
§ Displays the cost to the query optimizer for executing this operation. The cost of this operation as a percentage of the total cost of the query is displayed in parentheses. Because the query engine selects the most efficient operation to perform the query or execute the statement, this value should be as low as possible.

• Estimated Subtree Cost:
§ Displays the total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.

• Estimated Number of Rows:
§ Displays the number of rows produced by the operator

• Estimated Number of Execution:
§ Displays how many times the operator is executed.

• Actual Number of Rows:
§ Displays the actual no. of rows processed. Difference between Actual no. of rows and estimated no. of rows is called cardinal estimation. And should be as low as possible.

Fig 2: Displays a sample tool tips screen shot.

Phewww enough of theory, let’s have some fun with real demos…..:)

USE AdventureWorks2012;
GO
--Grpahical Query Plan
----------------------
--Estimated Execution Plan
--Select below query and press Ctrl + L key
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable Estimated Execution Plan (Ctrl + L)
 
--Now let’s execute the same query with actual query plan.
--Actual Execution Plan
--Select below query and press Ctrl + M to enable Actual Execution Plan andexecute the query (F5)
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable Actual Execution Plan Ctrl + M

Fig 3: Displays the estimated execution plan of the above query in graphical format.

--TEXT Query Plan
---------------------
--SHOWPLAN_ALL
--Enable showplan_all
SET SHOWPLAN_ALL ON
--Select below query and execute (F5)
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable showplan_all
SET SHOWPLAN_ALL OFF

Fig 4: Displays the actual execution result of above query in Text format.

--SHOWPLAN_TEXT
--Enable showplan_text
SET SHOWPLAN_TEXT ON
--Select below query and execute (F5)
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable showplan_text
SET SHOWPLAN_TEXT OFF

Fig 5: Displays the actual execution plan of the above query in text format.

--STATISTICS PROFILE
--Enable statistics profile
SET STATISTICS PROFILE ON
--Select below query and execute (F5)
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable statistics profile
SET STATISTICS PROFILE OFF

Fig 6: Displays the actual execution plan of the above query in text format.

--XML Plan
----------
--SHOWPLAN_XML
--Enable showplan_xml
SET SHOWPLAN_XML ON
--Select below query and execute (F5)
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable showplan_xml
SET SHOWPLAN_XML OFF

Fig 7: Displays the actual execution plan of the above query in XML format.

--STATISTICS XML
--Enable statistics_xml
SET STATISTICS XML ON
--Select below query and execute (F5)
SELECT FirstName,LastName FROM Person.Person
WHERE BusinessEntityID = 100
--Disable statistics_xml
SET STATISTICS XML OFF

Fig 8: Displays the actual execution plan of the above query in XML format.

Fig 9: Displays the contents of the above XML link.

That’s all folks for this post. Next part is coming soon. Hope you all enjoyed the blog. If you have any suggestion or questions you can post it in the comment section. Have a nice weekend 🙂

Regards

Debjeet Bhowmik

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook