Sudden degradation of insert into select performance

Question

We have a stored procedure running for almost 4 years now then suddenly it is being stuck on one of its step. In each step it changes the log status to know where it is. We have identified that the step where it is being stuck is under the ‘insert into [table 1] select from [table 2]. Before, this SP runs less than 30mins but now it spike into up to 20hours.

Answer 1

Is it safe to assume that this step includes a WHERE clause on the [table 2] query?

If so, the most likely cause is indexes.

If not, the stored proc may have been recently recompiled and the execution plan (usually built based on the very first execution after the recompile) is using costly queries. Examine your execution plan to confirm, or add WITH RECOMPILE to proc testing and see if performance changes significantly.