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)