1

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?

  • When you update stats, they invalidate the plans and hence you are getting a good plan after you update the stats and run the SP. Suggest you to alter SP and put OPTION (RECOMPILE) or once you are dont with your data load (ETL), then you can update the stats and run the SP. – Kin Shah May 16 '15 at 12:14
  • Yes, that is what i am attempting to do, so i have some UPDATE STATISTICS statements inbetween the ETL and the UPDATE statements... also i have WITH RECOMPILE, i take is there is no difference between this and option (recompile)? – user744284 May 16 '15 at 12:23
  • 2
    Yes, there is a difference, highly recommend OPTION (RECOMPILE) over WITH RECOMPILE. See this post by Paul White: sqlperformance.com/2013/08/t-sql-queries/… – Aaron Bertrand May 16 '15 at 12:27
  • @user744284 readup the link that Aaron mentioned. OPTION (RECOMPILE) is at individual statement level as opposed to WITH RECOMPILE will generate a new plan for entire SP which is very expensive. – Kin Shah May 16 '15 at 12:35
  • who am i kidding :P of course there is a difference, they wouldnt change the statement for no reason! Thanks both, will try recompiling the specific statement in the batch, after the update stats have run. Will update tomorrow after the nightly ETL. – user744284 May 16 '15 at 12:36
0

If there is a particular plan you want it to use, regardless of the statistics, then try using a plan guide for that query. It seems pointless to try to persuade the optimiser when there's a known behaviour you want to use. It's better to update your stats AFTER your ETL, not just to persuade your ETL to run well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.