Hi Zahran,

 Removing cache is not recommended (even though you can remove the cached plan). Plan caching increases the performance by reusing the same execution plan (instead of creating one for each execution). 

Cache remove operation happens when the cache in memory become outdated.  If it is happening only once in a while, that is quite natural.  if it is happening all the time, you really need to check this procedure.

I hope this procedure is not happening after a big insert/delete operation to a table. If so, there is the cache become outdated when the statistics change considerably. 

Is it happening in the beginning of the procedure execution or in the middle?

If it is in the beginning, there could be a great chance that the stored procedure is created with different set options than in the execution context.

If it is in the middle of execution, it means that the stored procedure's execution plan become outdated in the middle of the procedure. Are you creating /altering any tables (including temporary ones) in the middle?

If you can post the code, things may be easy to identify.  Can you?

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!