Use of statistics - Stored Proc v Ad hoc SQL

Question

Use of statistics - Stored Proc v Ad hoc SQL

MissTippsInOz

Hall of Fame

Points: 3160
More actions
August 7, 2012 at 10:26 pm

#260449

I had a curious situation recently where a stored procedure ran for 48 hours without completing. Conversely, running the procedure body as adhoc SQL takes a matter of seconds. Clearing the procedure cache makes no difference to the execution of the stored procedure but a stats update on the tables involved allows it to complete, although still taking a matter of minutes in comparison with the adhoc SQL.
The symptoms led me down the path of bad plan/parameter sniffing/bad stats and, certainly, as I mentioned the stats update makes a fundamental difference to the stored procedure, but it doesn't make it great. So why does the proc need the stats update but the adhoc SQL doesn't?
I haven't posted specific details of the query/tables etc because it's more a generic question of 'what am I missing?' but happy to provide more detail if required.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Looks like (bad) parameter sniffing in action.

Grant Fritchey wrote an excellent chapter of SQL Server MVP Deep Dives Vol. 2[/url] all about parameter sniffing and I strongly suggest buying this book. It's a conference in a book. Worth every penny.

Without seeing your code I can't get into details, but looks like the optimizer takes a different path when executing the statement ad-hoc and in a stored procedure. This is something that can definitely happen when bad parameter sniffing kicks in. As to why updating statistics affects the two plans in a different way, I would separate the two issues:

1) statistics are outdated

2) bad parameter sniffing occurs

The parameter sniffing issue is not caused by outdated statistics: it gets worse with stale statistics, but doesn't go away completely when stats are up to date.

However, the fix is easy for both issues:

For the first one, update your stats.

For the second one, you could use local variables or the OPTIMIZE FOR hint.

Hope this helps

Gianluca

-- Gianluca Sartori

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 2

Thanks Gianluca

The reason I was veering away from parameter sniffing is that this problem occurs even when the procedure cache is clear. I had considered the OPTIMIZE FOR query hint, but ideally I would like it to be OPTIMIZE FOR UNKNOWN which, unfortunately only appears in SQL 2008 (this is a 2005 instance).

Thanks again anyway, good to know my thinking was along common lines.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Have you tried using variables instead of parameters?

Another option would be OPTION RECOMPILE on the statement or WITH RECOMPILE on the procedure. I don't remember exactly when it was fixed, but there was a bug in the OPTION RECOMPILE hint that prevented the hint from working. It should have been fixed in SP2 IIRC. You could give it a try.

-- Gianluca Sartori