April 1, 2004 at 5:16 am
I had a strange issue to handle today. One of our web applications was getting a SQL Server timeout error on a query. However, when I connected to the database server under my account and ran the same query, the results were displayed in 3 seconds.
Recycling IIS did not fix the problem, but recycling SQL Server did! The query now runs in under 5 seconds from the web application, too.
So, can anyone suggest where I could start looking to find out why, before SQL Server was recycled, that the query running under the web application's account was timing out, while the same query run under my account worked fine? I didn't think SQL Server stored execution plans by user account. (?)
Note that before SQL Sever was recycled, it had been running for months and had built up a sizeable cache in RAM (~1.4 GB). Normally, I could understand that maybe the proc's execution plan got messed up, but this doesn't explain why I was able to run it without problems under my account.
Any help would be greatly appreciated! Thanks!
- Windows 2003 Server, IIS 6, Dual P-III 1.26GHz, 2GB RAM
- SQL Server 2000 (SP 3a)
Andre, MCDBA 2000
April 2, 2004 at 12:34 am
I have encountered something similar before, and spent some time trying to get to the bottom of the problem, using SQL Profiler, and watching the query run by the user, then repeating the query from Query Analyzer. The only explanation I could come up with was:-
SQL Server stores query plans according to the 'SET' options in force at execution time (e.g. ANSI_NULLS). The users may come in with different 'SET' options than you would when running the query via Query Analyzer, and therefore SQL Server will store more than one plan for the same query.
It's possible that one query plan will have decided not to use an index (because at the time is was cached, the WHERE clause had a parameter that made using the index pointless e.g. SURNAME='SMITH'), whereas the other plan will have used an index (e.g. SURNAME='WILKINSON-JONES'). Once the plans are cached, they will get re-used, even though the parameters are different.
Rather than re-cycling SQL Server, I did a DBCC FREEPROCCACHE to force SQL Server to generate a new plan, which had the desired affect, and the queries started running quickly. Using DBCC FREEPROCCACHE, unfortunately drops all cached queries, so I also tried a slightly less drastic method of doing an UPDATE STATISTICS on one of the tables used by the queries. Again this had the desired affect.
Of course, I may be talking complete !%&%$"* in my assessment of the problem, but the action taken to solve it worked!
April 2, 2004 at 4:47 am
In times like that you should use Profiler to watch what happens with the cache and the plan for the IIS calls and your own. I the logic is wrapped in an SP for them may be a condition early on caused an improper plan and the later conditions are suffering. To rememdy you add the WITH RECOMPILE or figure out another way to break down the SP to optimize for each case. But without knowing what was going on behind the scenes these are suggestions and guesses.
April 2, 2004 at 10:28 am
Suggestions above are good. Be sure all connection options are the same. I'd also be wary of dbcc freeprocache as it wipes all query plans out and could cause performance issues. Not that you shouldn't do it, but be aware of the risks.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 5, 2004 at 2:06 pm
Thanks for all the replies and suggestions! This gives me some place to start if and when it happens again.
Cheers,
Andre
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply