Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use of statistics - Stored Proc v Ad hoc SQL Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 10:26 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:44 PM
Points: 286, Visits: 573
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
Posted Wednesday, August 8, 2012 2:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 5,018, Visits: 10,526
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


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1341722
Posted Thursday, August 9, 2012 12:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:44 PM
Points: 286, Visits: 573
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
Posted Thursday, August 9, 2012 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 5,018, Visits: 10,526
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1342447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse