Oct 28, 2013

Difference between actual execution plan and estimated execution plan in sql server


Difference between actual execution plan and estimated execution plan in SQL server

In SQL server management studio we can generate two types of the execution plan of any queries:

1. Estimated execution plan
2. Actual execution plan

There are many different ways to view estimated and actual execution plan:

A. By using key shortcut

Estimated execution plan: Ctrl + L
Estimated execution plan: Ctrl + M

B. By using SSMS (Using Icon)


C. By using SSMS (Using Query menu)


D.  By using SSMS:

We can also get these options if we will right click on any query page

Note: To get the actual plan, first enable the actual execution plan by using any above methods then execute the query. You will get a new tab "Execution Plan" in the query result window.

Why there are two types of the execution plan?

We can get the execution plan of a query without executing the query and also with executing the query. There are many scenarios in which we cannot execute the query while we need to view the execution plan to analyze the query performance. Following are may be the reason in which we cannot execute the query:

1. Any INSERT, UPDATE, DELETES or MERGES statement in a live server which can modify the tables.
2. A SELECT statement which takes too much time to execute the query.
3. A complex stored procedures in which we are not sure that execution will affect the application or not etc. 

Due to this SQL server has introduced two types of the execution plan:

1. Not executing the query: It is called estimated execution plan. As the name suggests SQL server query is guessing or estimating that query may follow this execution plan when SQL server will execute the query.

2. Executing query: It is called actual execution plan. As the name suggests it is actual execution plan, which SQL server has followed to execute this query.

Some important differences: Estimated Execution Plan Vs Actual Execution Plan

1. Estimated execution plan can generate the plan for language elements while not by the actual execution plan. Examples of SQL server language elements are:

a. Assignments
b. Type conversion
c. Declaration
d. If statement
e. While loop
f. A Select statement without from a table etc.

For example:

--Declaration
DECLARE @Var AS INTEGER

--Assignments
SET @Var = 10

--If statement
IF @Var > 5 BEGIN

    --Select statement without from a table
    SELECT @Var
   
END ELSE BEGIN

    --Select statement without from a table
    SELECT 0
   
END

--While loop
WHILE @Var > 10 BEGIN

    --Assignments
    SET @Var = @Var - 1
   
END

If we will generate the estimated execution plan:
 

It is generated the estimated execution plan for each language elements while SQL server will not generate any actual execution plan of the language elements.

2. In case of estimated execution plan it doesn't evaluate the language element while actual execution plan evaluates the language elements and on the basis of this, it generates the execution plan. For example:

DECLARE @Var AS INTEGER = 10

IF @Var > 5 BEGIN
    SELECT * FROM tblEmployee
WHERE ntEmployeeID = @Var
END ELSE BEGIN
    SELECT TOP(1) * FROM tblEmployee
WHERE ntEmployeeID < @Var
END

Its estimated execution plan:


Instead of validating the IF condition estimated execution plan has generated for both IF block as well as ELSE block.

Its actual execution plan:


Actual execution plan has first evaluated the IF condition and generated the execution plan of only that block which satisfies the IF condition.

Another example:

DECLARE @Var AS INTEGER = 3

WHILE @Var > 0 BEGIN
    SELECT * FROM tblEmployee WHERE ntEmployeeID = @Var
    SET @Var = @Var - 1
END

Its estimated execution plan:

 

In the above query WHILE loop will iterate three times but estimated execution plan doesn't evaluate it.

Its actual execution plan:

 

3. Estimated execution plan doesn't execute the query. If our queries creates any table and then we referenced that table in another query it will not able to generate the estimated execution plan. For example:

CREATE TABLE tblStudent(
    ntStudentID BIGINT PRIMARY KEY,
    vcName VARCHAR(100)
)

SELECT * FROM tblStudent

If we will try to generate the estimated execution plan we will get an error message: Invalid object name 'tblStudent'.

4. Sometimes both estimated and actual execution plan may generate the wrong execution plan due to not updated statistics. For example, I am creating a table and inserting some records into it:

--Creating table
CREATE TABLE tblEmployee(
    ntEmployeeID BIGINT PRIMARY KEY IDENTITY,
    vcName VARCHAR(100),
    ntAge INT
)

--Inserting some records into it
INSERT tblEmployee
SELECT
    LEFT([Text],100),
    severity 
FROM sys.messages

--Creating non-clustered index
CREATE NONCLUSTERED INDEX NCI_Age ON tblEmployee(ntAge)
   
Now I am deleting all records where ntAge is 21

DELETE FROM tblEmployee WHERE ntAge = 21

It will delete 671 records from the table.

Now I am generating the execution plan for this query:

SELECT vcName FROM tblEmployee WHERE ntAge = 21

We will get the same estimated and actual execution plan:

 

SQL Server query optimizer has generated the above execution plan assuming that table has 671 records where ntAge is 21 while we have already deleted those records. So now we are going to update all the statistics in the table tblEmployee:

UPDATE STATISTICS tblEmployee

Now if we will check the estimated or actual execution plan we will find query optimizer has changed its execution plan:


Sometimes we may get different estimated and actual execution plan due to not updated statistics.

Indexes best practices in SQL server: Where to start creating indexes?
SQL server query optimization tips: Tuning best practices with examples
Fastest or most efficient way to insert data in SQL server
Index in SQL server with examples
What is clustered index in SQL server

No comments:

Post a Comment