Understanding of Execution Plan – II [Reuse of the Execution Plan]

 Introduction

We promise to our readers that, we are trying to publish a series of article to make the execution plan easier to understand and how to increase the performance of a query by understanding the execution plan. For this we starts our Article named “Understanding of Execution Plan [What happened When SQL statement Execute]”.

If you don’t read it yet or have any doubt please follow the link.
http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html

In this article we are trying to discuss about the Reuse of the Execution Plan

 

Why the Reuse of Execution Plan needs

The MS SQL Server takes time to generate the Execution plan depends on the Query that we submit. It can take millisecond, seconds and even minutes also depend on the complexity of the Query submitted by us.

So the MS SQL Server creates the plan and kept in a memory location called Plan Cache or from MS SQL 2005 it is called Procedure Cache, so that SQL Server kept and reuses the Plan whenever possible in order to reduce the overhead.

 

How it Works

Whenever we submit a query to MS SQL server the SQL Server Algebrizer process creates a hash, like a coded signature, of the query. The hash is a unique identifier; its nickname is the query finger­print.

 

 

If a query exists in the cache that matches the query coming into the engine, the entire cost of the optimization process is skipped and the execution plan in the plan cache is reused.

 

How to Re-Use the Cache

So it is the best practice to write the Query in such way that SQL Server can reuse he plan. To ensure this reuse, it's best to use either stored procedures or parameterized queries. Parameterized queries are queries where the variables within the query are identified with parameters, similar to a stored procedure, and these parameters are fed values, again, similar to a stored procedure.

If, instead, variables are hard coded, then the smallest change to the string that defines the query can cause a cache miss, meaning that MS SQL Server did not find a plan in the cache (even though, with parameterization, there may have existed a perfectly suitable one) and so the optimization process is fired and a new plan created.

When the Cache Plan Removed

MS SQL server never put the plan in Cache forever. They are slowly aged out of the system using an "AGE" formula that multiplies the estimated cost of the plan by the number of times it has been used.

For an example, is a plan with an estimated cost of 10 that has been referenced 5 times has an AGE value of 50.

The lazywriter process, an internal process that works to free all types of cache (including the plan cache), periodically scans the objects in the cache and decreases this value by one each time.  

 

In this following situation the Execution Plan is removed from Memory

1.    When more memory is required by the System

2.    The AGE of the Plan is reached to Zero

3.    When the plan isn't currently being referenced by an existing connection.

Re-Compiling a Execution Plan

The Re-Compiling of an execution plan may be very Expensive Operation and we must remember it. Re-Compilation done in this situation mentioned bellow.

1.    Changing the structure of a Table that is referred by the Query.

2.    Changing the Schema of a Table hat is referred by the Query.

3.    Changing / Dropping the Index of a Table that is used by Query.

4.    Using sp_recompile

5.    for tables with triggers, significant growth of the inserted or deleted tables

6.       Mixing DDL and DML within a single query, often called a deferred compile

7.    Changing the SET options within the execution of the query

Clearing the Plan from Plan Cache

Sometimes we need to clear the Cache. Suppose we have a bad execution plan in our cache the performance of the Query is so slow and we are unable t improve it due to Bad plan in cache and the plan is not automatically removed.

To remove the Plan from Cache we us the following command.

DBCC FREEPROCCACHE

   

WARNING: Clearing the cache in a production environment

Running it in production environment will clear the cache for all databases on the server. That can result in a significant performance hit because SQL Server must then recreate every single plan stored in the plan cache, as if the plans were never there and the queries were being run for the first time ever.

Hope you like it.

Posted by: JOYDEEP DAS