timeout from web page but not from query analyzer? 1

[jrenae]

Hello,

Periodically we have a web page timeout when running a query, but when we run the same query from query analyzer the query comes back in a few seconds. (I might add that query analyzer will take a very long time to finish sometimes, which just means that our database is crunching too much at the time). I thought maybe the web page is somehow hanging onto to a bad query plan so I did sp_recompile on the stored proc but that didn't help.

fyi, our web servers are load balanced (I think we have about 5).

any ideas?

Thanks in advance

 

[TysonLPrice]

Not to be facetious but it sounds like you answered your own question when you posted "I might add that query analyzer will take a very long time to finish sometimes, which just means that our database is crunching too much at the time".

What I do in situations like that, I'm not a DBA, is to work with the DBAs. I run the application and they monitor the database activity. Often they will see what's up.

There's also a tool called SQL profiler that may help you.

http://www.developer.com/db/article.php/3482216/Introduction-to-SQL-Profiler.htm

 

[jrenae]

Thanks. But the issue is that the query will go fast in query analyzer but slow from the web page. Like the web server is hanging onto an old cached execution plan or something.

Why would the same query finish in a few seconds from query analyzer, but hang from the web page?

 

[gmmastros]

I assume this stored procedure has parameters that you pass to it (because most of them do). When you test the performance in Query Analyzer, are you testing the procedure with the same parameters that are used on the web page?

Basically... I think you may have a [google]parameter sniffing[/google] issue here. The idea is.... SQL will generate an execution plan for the procedure based on the parameter values. Depending on data, it may generate an execution plan that is optimal for certain parameter values, but NOT optimal for others. A lot of this has to do with statistics and such.

Anyway... let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jrenae]

Thank you, George. I remember an article we came across a while back about the parameter sniffing. So today I implemented the change to the stored procs involved and did see a huge improvement.

Time will tell if this will be a permanent solution.

Thanks again.