One of my stored procedure takes long time to execute. When I monitor the events in SQL Profiler, I noticed sp:CacheRemove event takes long time to process. Is it recommended to clear the cache manually? If yes, how can I do it?

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!

Hi Preethiviraj

Thanks for your useful information. Yes, my procedure does around 10 million records delete and insert operation. But I don't create/alter any tables (including temporary table) in my procedure. It doesn't happen all the time. Usually it takes 30 minutes to complete. But some times it takes nearly three hours.

Ok.  I believe the number is the main reason for the Remove Cache operation.  In fact when this number of deletes inserts happen, the statistics change drastically, and SQL Server will recompile the stored procedure always.

If the statistics changes, It is good to include WITH RECOMPILE option in the stored procedure header so that it will thorw away the cache after the execution and recompile again.

If your delete/insert besically brings the table back to the same position, (even though you delete a lot, you insert the same amount within the procedure) you can also use the plan hint to use the existing plan.

Watch out for the execution plans and if they are finally the same, you can go for this fixed plan.  (This is a big topic and should be used with care.  If you force SQL Server to use a less optimal plan, it may affect the server performance)

refer books online for query plan

Hope this helps,

 

 

---

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