What is an execution and how to get execution plan of a query?

Problem: What is an execution and how to get execution plan of a query?

Solution: Query is generally a block of declarative code executed against a database. When the execution happens SQL Server processes the query by the database engine. The engine while processing the query uses query optimizer (query processor) translates the request to logical algorithm of the data flow called as execution plan.

The Execution Plan options graphically display the data retrieval methods chosen by the SQL Server query optimizer. The graphical execution plan uses icons to represent the execution of specific statements and queries in SQL Server rather than the tabular representation produced by the Transact-SQL SET statement options SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT, or the XML for representation produced by SET SHOWPLAN_XML. The graphical display is very useful for understanding the performance characteristics of a query. SQL Server Management Studio shows which statistics are missing, thereby forcing the query optimizer to make estimates about predicate selectivity, and then permits those missing statistics to be easily created.

There are two types of execution plans one is estimated and the other is actual.

• Estimated execution plan is based of query processor analysis by parsing the script and this can be generated without execution the script.

You can see the below screen shot to select the feature in SSMS while running a query press(CTR+L)

• Actual execution plan is based on the execution of the script and the processed algorithm to fetch the query work.

You can see the below screen shot to select the feature in SSMS while running a query press(CTR+M)

These following set options can enable the execution plan to show.

SET SHOWPLAN_ALL (Transact-SQL) -Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

SET SHOWPLAN_TEXT (Transact-SQL) -Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.

SET SHOWPLAN_XML (Transact-SQL) -Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document

Syntax:

SET SHOWPLAN_ALL { ON | OFF }

SET SHOWPLAN_TEXT { ON | OFF }

SET SHOWPLAN_XML { ON | OFF }

Example:

In this example, I would like to show we can see an execution plan of a query. I would like to write a SQL statement and query employees table, let’s see how the query processor will translate this request into a logical flow.

USE DB;

GO

SET SHOWPLAN_XML ON;

GO

-- Sample query.

SELECT  [EmployeeID]

      ,[Name]

      ,[PhoneNumber]

  FROM [dbo].[Employees]

  WHERE EmployeeID =100136;

GO

SET SHOWPLAN_XML OFF;

OUTPUT:

After clicking at the xml the plan will be shown as below.