Hi All,

For my company, I am revisiting one of the stored procedures that has already been written by a third party vendor. Very recently there was an issue where this procedure took unusually long to complete(due to enormous data volume). The sp_who option showed that the insert operation in one of the temporary tables was the time consuming bit. Creating an index did help to a certain extent but in general, I would like to know:

  • If the temporary tables can all be replaced by permanent tables( there are quite a lot of temp tables used by this proc) where possible to have better control for monitoring. What are its pros and cons.
  • If there is an effective logging mechanism to aid us in the debugging process.
  • If there is a way to have restart capability to resume from the point where the procedure had previously failed (For e.g. if 100 records are processed successfully and then a failure occurs, the restart should continue processing from the 101st record onwards)

Any help would be greatly appreciated.

Many thanks