Execution plan explaination

Execution plan :

-----------------------------

There are two type of execution plan

1. Estimated execution plan :

a. Created without even running the query.

b. Uses statistics of table and indexes to find the detail.

c. Good for long running query tuning.

2. Actual execution plan :

a. It creates execution plan when actual query runs.

b. It displays result after query completes the execution so wait for large data query.

c. It gives exact plan of query with calculated on actual data and indexes.

d. If statistics are out of date result of estimated and actual could be different.

assuming you're not sysadmin, dbcreator or db_owner, you'll need to be granted the ShowPlan permission within the database being tested.

GRANT SHOWPLAN TO [username]

Execution plan uses physical joins for any logical joins (inner, left, right and outer) used by user in a query.

These physical joins are: HASH Join, MERGE Join and NESTED join.

Execution plans internally uses above joins according to the size of tables, indexes used in joined tables and Sorted order on the join column. The query optimizer will be smart and always try to pick up the most optimal physical joins. As we know SQL optimizer creates a plan cost based and depends upon the query cost it will choose the best join.

HASH JOIN: Uses each row from top input to build a hash table, and the each row from below input to probe into the hash table and outputting all matching rows. It creates hash table in available memory or temp database.

MERGE JOIN: Match rows from two suitably sorted input tables exploiting the sort order. Merge join only works with unique clustered index.

NESTED JOIN: For each row in top(outer) input, scan the bottom(inner) input, and output the matching row.

For below conditions with unique clustered index:

Table1 (BIG) and Table2 (BIG)

1. If the both or eitherof table has no index optimizer will use hash join internally.

2. If both the big table has unique clustered index optimizer will use merge join internally.

Table1 (Medium) and Table2 (Medium)

1. If the both the table has no index optimizer will use hash join internally.

2. If the both or eitherof table has unique index optimizer will use merge join internally.

Table1 (small) and Table2 (small)

1. If the both the table has no index optimizer will use hash join internally.

2. If the both or eitherof table has unique index optimizer will use nested join internally.

Table1 (Big) and Table2 (small/medium)

1. If the both the table has no index optimizer will use hash join internally.

2. If the both or eitherof table has unique index optimizer will use nested join internally.

Please note if clustered index is used instead of unique clustered index all joins will use only hash except nested join for small tables.if the index created without unique keyword then SQL will not know its unique (data) so it will create GUID of 4-byte integer for joins.

If no other match suits to optimizer it uses hash match using hash to retrieve output.

Execution plan also displays the type of scan or seek used by table.

They are : Table scan , Index scan and index seek.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

Table scan means iterate over all table rows.

Index scan means iterate over all index items, when item index meets search condition, table row is retrived through index.

table scan/index scan/index seek possibilities:--

A table with no index will do table scan.·

A table with only clustered index will do index scan when no search criteria used.·

A table with only clustered index will do index seek when clustered index column used in search criteria.·

A table with only clustered index will do index scan when nonindexed column used in search criteria.· A table with only non clustered index will do table scan when non indexed column used in search criteria.·

A table with only non clustered index will do index scan/index seek depending on volume of table when indexed column used in search criteria.·

A table with clustered and non clustered index will do index scan or seek depending on the search column and volume of table in where clause.

Execution plan cache :

It is expensive for the Server to generate execution plans for each query so SQL Server will keep and reuse plans wherever possible. As they are created, plans are stored in a section of memory called the plan cache) and can be checked using DMV sys.dm_exec_cached_plansEach unique query gets an Execution Plan

–Performed by the Query Optimizer

–Identical queries gets the same Execution Plan

–Problematic when used with dynamic T-SQL

Creation takes some time

Stored in the Plan Cache

When a query is submitted to the server, an estimated execution plan is created by the optimizer. Once that plan is created, and before it gets passed to the storage engine, the optimizer compares this estimated plan to actual execution plans that already exist in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it's already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries called thousands of times in a minute.

Each plan is stored once, unless the cost of the plan lets the optimizer know that a parallel execution might result in better performance. If the optimizer sees parallelism as an option, then a second plan is created and stored with a different set of operations to support parallelism. In this instance, one query gets two plans.

Execution plans are not kept in memory forever. A plan with a cost of 10 that has been referenced 5 times has an "age" value of 50. The lazy writer process, an internal process that works to free all types of cache (including plan cache ), periodically scans the objects in the cache and decreases this value by one each time.

If the following criteria are met, the plan is removed from memory:

More memory is required by the system

The "age" of the plan has reached zero

The plan isn't currently being referenced by an existing connection

The plan also modifies or recompiles (may be expensive operation) in following event:

· Change in schema or structure of table used in query.

· Changing a index or updating statistics used by query.

· Using sp_recompile function.

To clear the cache use below command:

DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE

To see the object referenced and query used by optimizaor plan we can use below query.

SELECT [cp].[refcounts]

, [cp].[usecounts]

, [cp].[objtype]

, [st].[dbid]

, [st].[objectid]

, [st].[text]

, [qp].[query_plan]

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st

CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

Above columns in query defines :

· Refcounts : Number of objects referecing this plan

· Usecounts : Usage count

· Objtype : Proc, Ad hoc, View

· Plan_handle : Identifier for this plan in memory (You can use the XML directly or open it as a graphical execution plan.)

Different ways of getting execution plan:

To get the graphical execution plan we can use SSMS icon for estimated and actual execution plan.

To get text execution plan :

To activate the text version of the estimated text execution plan, simply issue the following command at the start of the query:

SET SHOWPLAN_ALL ON/OFF;

In order to activate and deactivate the text version of the Actual execution plan, use:

SET STATISTICS PROFILE ON/OFF;

To get the XML format execution plan:

In order to activate and deactivate the XML version of the Estimated execution plan, use:

SET SHOWPLAN_XML ON/OFF;

For the XML version of the Actual plan, use:

SET STATISTICS XML ON/OFF;

Automating Plan Capture Using SQL Server Profiler

A production system may be subject to tens or hundreds of sessions executing tens or hundreds or queries, each with varying parameter sets and varying plans. In this situation we need a way to automate plan capture so that we can collect a large number of plans simultaneously. In SQL Server 2005 you can use Profiler to capture XML execution plans, as the queries are executing. You can then examine the collected plans, looking for the queries with the highest costs, or simply searching the plans to find, for example, Table Scan operations that you'd like to eliminate.

Showplan All, Showplan Statistics Profile, Showplan XML, Showplan XML Statistics Profile are few events of profilers for execution plan.

Capturing all of the execution plans, using Showplan XML or Showplan XML Statistics Profile, inherently places a sizeable load on the server. These are not lightweight event capture scenarios. Even the use of the less frequent Showplan XML for Query Compile will cause a small performance hit. Use due diligence when running traces of this type against any production machine.