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!