This post is to discuss and rate the article Measuring Performance of Stored Procedures by Preethiviraj Kulasingham.[Poll]

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 ...

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, ...

The sample codes used by Preethiviraj Kulasingham for the session titled Performance Optimization of Stored Procedures at the 12th SS SLUG meeting.

Hi,I doubt that this is related to some hardware resources like memmory or IO operation.''According to tests I have done the time gap expands as data volume goes up'', this is not a issue. It should go up when the volume goes up because it needs to read more pages and more resources.

Hi,It depends on the way you have written the SP. This post may help you to find the problem.Use CURSOR whenever you have no other options for implementation. Most of the time, WHILE loop can be used instead CURSOR. If the above link does not help you on this, please let us know the content of the SP.