Use of statistics - Stored Proc v Ad hoc SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Use of statistics - Stored Proc v Ad hoc SQL

Use of statistics - Stored Proc v Ad hoc SQL

Rate Topic

Display Mode

Topic Options

Author

Message

MissTippsInOz

Posted Tuesday, August 07, 2012 10:26 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:39 PM
Points: 286, Visits: 519

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

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?

Post #1341668

spaghettidba

Posted Wednesday, August 08, 2012 2:10 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

Looks like (bad) parameter sniffing in action.
Grant Fritchey wrote an excellent chapter of SQL Server MVP Deep Dives Vol. 2 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

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1341722

MissTippsInOz

Posted Thursday, August 09, 2012 12:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:39 PM
Points: 286, Visits: 519

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.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?

Post #1342391

spaghettidba

Posted Thursday, August 09, 2012 2:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1342447

« Prev Topic | Next Topic »

Permissions