EXECUTION PLANS AND THE MEMORY

Why stored procedures takes the same amount of time after some hours as it takes at
the first go.

It should take less time than the time taken by SQL SERVER to
execute it for the first time. The answer is this is depends on the
age. This has been taken care of LAZY WRITER.

1.It is too costly for the SQL SERVER to generate execution plan
every time it executes a query. Hence SQL SERVER will keeps the same in
the memory and reuse the same when ever possible.They are stored in
part of the SQL SERVER memory called plan cache.

2.When a query is given to the SQL SERVER , an estimated execution
plan is created by the query optimizer.Then the optimizer compares both
the plan estimated as well as the plan from the plan cache.If both are
same then the optimizer will reuse the existing plan because it is
already been used by the query engine.This will reduce overheads of
creating actual execution plans.

3.Execution plans are not saved in memory forever.

4.SQL SERVER removes the execution plan if one of the following criteria is met.
a) If the SQL SERVER wants to use the memory which is used by our execution plan.
b) The age of our plan is 0.Age formula that multiplies the estimated
cost of the plan by the number of times it has been used .The
lazywriter 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.
c) If the plan is not used by the existing connections.

5.To clear the procedure cache the query used is : DBCC FREEPROCCACHE

6.Following actions can lead to recompilation of an execution plan:
a)If you change the index which is used by the query.
b)Chaging the structure of the table.
c)Dropping the index used by the query.
e)Calling the function sp_recompile explicitly or it has been written inside the stored procedure.
f)Due to large number of deletes or inserts.

7.One can easily get the information about execution plans on our
system.The tables used for the same are sys.dm_exec_cached_plans cp ,
sys.dm_exec_sql_text , sys.dm_exec_query_plan

Pawan Kumar
Pawankkmr@hotmail.com