This is running SQL Server 2012 SP2 Enterprise Edition.
I have a nightly ETL job that, for a specific SP, is taking way too long to run. During my investigation I found that it was a specific update as part of the SP that is estimating a single row in its execution plan, hence the bad plan.
A bit of background on this SP, it does some ETL and loads into a table then 5 different update statements run as part of the SP and update this table, it's one of these updates that's causing the problem.
More annoyingly, this SP does have parameters but they aren't used anywhere in the actual SP. I am waiting for dev to remove these and obviously it needs fixing elsewhere.
To try and solve I have put some rebuild statistics in the SP after the ETL part, as during investigation doing this then rerunning the offending update statement returned a good plan but when this runs as part of the nightly ETL it keeps returning the bad plan (SQL Sentry monitor is paying for itself here!!), even with recompile, I have also tried creating a new SP and calling that after the update stats instead of the updates text, wondering if it was some sort of scope issue, but to no avail.
The frustrating part is that if I execute this SP manually all is ok, it runs in less that 60 seconds. If I leave this to run as part of ETL it takes roughly 3 hours to run. I am at a loss as to why this is happening... I am wondering if this is something to do with the way the SP is called as the devs have dynamically called the SP by using Execute T-SQL and then manually building the SP name?