What is Execution plan? in sql server 2005

Some of the times your proc is running slow. Because you are not looked into the execution plan.Here i explain how the execution plan is created.

Execution plan: Result of an query optimizer(that calculate the most efficient way to implement the T-sql query you submitted).By looking into this u can find out what is the problem in your query, if there is any problem.

What happens when a query is submitted?

The Query split into two stages

1) Processes that occur in relational engine.

2) Process that occur in Storage Engine.

Relational Engine

Relational engine parse the query and processed by query optimizer and produce an Execution plan.

Storage Engine

The result produce from the execution plan is sent(is a binary data) to storage engine, where data are stored and do operations like (locking, indexing transactions etc occur).

Execution plan

Execution plan is happens in relational Engine.

The following steps are happened in relational engine to produce the Execution plan.

a) Query parsing

Process: Checks the syntax

Output: Parse tree or query tree or process tree

Note: the parse tree contain the logical steps to the necessary operations for query.

If T-Sql is not a DML. So there are no opportunities to improve the performance. if T-sql is a DML So the parse tree is passing through the algebrizer.

DML: which helps developers to retrieve, insert, delete and update data in database.

Algebrizer: (process in query parsing)

Process: Resolves various name object, tables and columns.

Note: identify the each column fields (e.g.: varchar (200), int etc).It also determines the location of aggregate (group by and max etc) in the query and the process is called aggregate binding.

Algebrizer output: binary tree called query processor tree, which passed on to query optimizer.

b) Query optimizer

Which is an essential process model and says which relational engine works. Based on query processor tree and relational engine it produces the execution plan.

Cost based plan

This steps analyze which joins are used. Which calculate the cost of the execution plan, in terms of CPU.

In this step the optimizer plans which is the best plan to reduce the execution time.based on the cpu and i/o it produces the new query.

Trivial plan

If you submit a very simple query – for example, a single table with no indexes and with no aggregates or calculations within the query – then rather than spend time trying to calculate the absolute optimal plan, the optimizer will simply apply a single, trivial plan to these types of queries

Non Trivial plan

If it is a non trivial plan it performs cost based calculation. For this looks statistics maintained by sql.

Statistics: collection of data columns and indexers in data base and data distribution among them. The information for statistic is maintained by a histogram.

Histogram: tabulation of occurrence of particular values.

Plan cache: once optimizer creates execution plan this plan stored in a memory known as plan cache. if the identical plan is not there in the cache it goes to generate potential plan.

Query Execution

Once the execution plan is created it goes to storage engine and execute according to the plan.

Estimated and actual Execution plan

Two types of execution plans are there

Estimated plan: output from the query optimizer.

Actual plan: Actual query is execution.

Reuse of execution plan

The execution plan is stored in plan cache. The plan won’t store forever .as they aged out based on the age formula, it calculates the number of times this has been used. Lazywirter process(as garbage collector in .net) internally checks the all types of cache periodically scans the objects in the cache.

Following criteria removes the memory from the cache .

· More memory required by the system.

· The “age” plan reaches zero

· Plan is not being referred by the existing collection.

Plan can cause recompiled in the following ways

• Changing the structure or schema of a table referred by the query.
• Changing the index used by the query.
• Dropping index used by the query.
• Updating statistics used by the query.
• Calling sp_recompile
• Mixing DDL and DML .

Note: DDL (data definition language) descries the records, fields and sets makeup by the user. E.g.: create table alter table etc.