Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gavinharriss
Starting Member
17 Posts |
 Posted - 2010-03-21 : 04:35:06
|
| Hi there, I was wondering if any SQL gurus out there might know what's going on...I have a stored procedure that works lighting fast when called from SQL Server Management Studio, and works lighting fast when called from an ASP.NET... for a while.Every now and then the stored proc will decide it will always time-out when called from ASP.NET. I find that adding SET ARITHABORT ON resolves the issue, but only for a few weeks then the problem comes back. At which point I'll remove SET ARITHABORT ON and it'll be good again, for a few weeks. I have to repeat this process every few weeks adding the option / removing the option to keep the stored proc running from ASP.NET.Very frustrating as I can't see any obvious problem - seems to be an issue with SQL Server itself.Any help would be greatly appreciated.Cheers,Gavinwww.gavinharriss.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-21 : 12:58:23
|
| It's because you are getting a bad execution plan. Changing a setting is working because it is recompiling the stored procedure, which means you get a new execution plan once it executes. A bad execution plan could be present due to outdated statistics or differing input parameter values. SSMS gets a different execution plan since its settings are slightly different. How often are you running update statistics? Does the stored procedure have multiple queries in it?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
gavinharriss
Starting Member
17 Posts |
Posted - 2010-03-22 : 04:33:17
|
| Cheers Tara :)Figured the execution plan must be coming in to it somewhere but a little baffled why ok one day and then falls over the next.The stored proc does have 2 queries in it, but this is for paging results (before I discovered RANK). So I will revise this in to a single query if this is likely to help.I must admit - I don't refresh statistics as I thought this happened automatically. Perhaps I should set up a scheduled job to do this?www.gavinharriss.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-22 : 05:30:07
|
| You should definitely set up a job to update statistics...I usually set this up to run once week on tables that don't change all that much and daily in tables that have frequent insert/update/delete.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gavinharriss
Starting Member
17 Posts |
Posted - 2010-03-28 : 21:03:52
|
| I've adjusted my stored proc but found I still needed two parts - one to return the page of results, the other to count and return the total number of matches. I did manage to optimise the stored proc though using the RANK function for paging, so hopefully that will help!I've also scheduled a job to regularly update the table stats.I guess I'll find out if the tweaks worked in a week or two if we don't encounter any more problems.Thanks for all your help - much appreciated :)www.gavinharriss.com |
|
|
|
|
|
|
|
|
Bizarre Timeout Issues