We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second job, 15 minutes later, from a SQL Agent job running on a different server (as an emergency failover in case anything goes awry with the first job).
The procedure is defined WITH RECOMPILE.
The procedure normally executes in about 45 seconds. Last Wednesday, and again this morning (also Wednesday, coincidence?!?), the 2am procedure took 90 minutes to execute. While it was executing, the 2:15am job ran and that execution took the usual 45 seconds.
I have the execution plans from both situations. There are some table variable processes that should include estimated row counts in the neighborhood of 200K rows. The faulty plan reports these table variables with an estimated 130 billion rows. [Side note: I have already rewritten the code to use temp tables instead of table variables, based on this discussion and will be moving it to production in the near future]
Our monitoring software (Solar Winds DPA) reports excessive CXPACKET waits for the 2am execution. This seems to indicate issues with parallelism and is likely related to the table variables being used in the procedure.
There is still user activity on the server during this time, and some scheduled jobs, but nothing that I see that would affect this procedure or its execution plan. An index maintenance job is run at 2:30am.
I understand the poorly-performing execution plan is related to the temp tables, but why would this same procedure executed 15 minutes later have such a drastically different execution plan (and why does the 2am execution run fine the rest of the week?)