October 18, 2004 at 1:22 am
Hi ALL,
I've started a new thread regarding an issue I'm having as I have much more info about the problem now. I'll try to summarize here:
System Info: Win2K Server running both IIS and SQL Server 2000, 3.0GHz Hyperthreading P4, 1.5GB RAM, 80GB HD, two partitions, database files on 2nd partition.
I have a stored procedure that has worked well for some time and then started getting OLE DB timeouts in the ASP page that displays the returned data. This started happening after heavy activity on the web server. In ASP, I call the SP with the Connection object. It has 4 parameters and returns a resulting recordset of no more than 40 rows. Its basically a large select that looks like this:
select a,b,c,
(select x1 from table1 where val1=main.val1 and weeknum=1),
(select y1 from table1, where val1=main.val1 and weeknum=1),
(select z1 from table2, where val1=main.val1 and weeknum=1),
(select x1 from table1 where val1=main.val1 and weeknum=2),
(select y1 from table1, where val1=main.val1 and weeknum=2),
(select z1 from table2, where val1=main.val1 and weeknum=2),
...
(select x1 from table1 where val1=main.val1 and weeknum=21),
(select y1 from table1, where val1=main.val1 and weeknum=21),
(select z1 from table2, where val1=main.val1 and weeknum=21)
from main where val1=@userid and weeknum=@week
I run an optimization job once per week. The ASP page usually draws in under a second. When I get the OLE DB timeouts in ASP, I can then go to Query Analyzer and call the same SP with the same paramters and the correct recordset is returned in under a second. I created an ASP page on my local server that connects to the same SQL Server on the webserver box and calls the same SP the same exact way and it also times out. I changed the code to call the SP with the Command Object and appended the parameters appropriately and it still times out. I restart IIS and DNS and still no luck. Looking in Enterprise Manager, I can see locks on tempdb and the three tables in the query when calling the SP from ASP but not when called from Query Analyzer. If I restart SQL Server the problem goes away. In ASP, I'm closing all connections and recordsets.
When the problem was occurring, I tried to create a new SP that instead does an insert with the select into a declared table variable and then returns the table variable rows with a select. This worked! I got excited that maybe the new SP put less of a strain on tempdb or something like that and that may have helped resolve the issue. But then I decided I should try an experiment to create another SP with the EXACT code of the troublesome SP and only change the SP name. I did this and this ALSO worked! So calling the new SP with the exact same code from ASP would work, but calling the old one would not!! I could alternate between the two and see the failure with the old one but not the new!
So the suggestion came up to try to call dbcc dropcleanbuffers and dbcc freeproccache when the problem occurs. This sounds like a good suggestion except for the fact that if this was really a query plan cache issue, wouldn't the query also be slow from Query Analyzer as well?
In PerfMon, I don't see a huge jump in any counter numbers when the problem occurs. If you have suggestions on specific counts I can put them in.
Any ideas?? Maybe a hardware issue?!?
Thanks in advance
October 18, 2004 at 1:49 am
You could try running sp_recompile on your procedure.
Also you have
(select x1 from table1 where val1=main.val1 and weeknum=1),
(select y1 from table1, where val1=main.val1 and weeknum=1),
If this is literally what you have then why not put in
(select x1,y1 from table1 where val1=main.val1 and weeknum=1),
and save yourself a select per week i.e. 20 SELECTs fewer.
What is the relationship between table1 and table2? Can you role up the selection of the three queries per week into the one?
October 18, 2004 at 5:16 am
Hi David,
It's my understanding that a select in ()'s can not return more than one value.
table1 and table2 can be joined on weeknum and val1 also.
How can I do what you suggest if queries in ()'s can only return one value?
I'd definitely try it if there is a way!
Thanks...
October 18, 2004 at 7:11 am
You can move these selects to the FROM clause.
select a, b, c, d
from main, (select c, d, from mytable) b
October 18, 2004 at 4:45 pm
We've had a very similar situation where QA produced a fast response while intranet site "SOMETIMES" was timing out while still holding shared locks on the tables involved. sp_recompile did not do anything unless you use it against at least one of the tables involved. Calling the sp WITH RECOMPILE or re-creating it WITH RECOMPILE temporarily fixed the problem. While we got some breathing time we went back to the drawing board (QA) and discovered that a "bad" plan gets created even from there, also "SOMETIMES". The execution reverses to table/index scan and everything comes to a full stop. Of course the proc was a drag, horrible logic, 8 pages of IF's/ELSE's and WHERE clause was convoluted with LIKE's and dislikes. After re-writing it (which shrunk it to 1.5 page) it never happened again. I believe the frequency of execution was also a big contributor (about 500/sec).
October 19, 2004 at 12:01 am
Butch
October 19, 2004 at 2:39 am
Hi Robert,
This sounds just like what I'm experiencing. My query is really not all that ugly as you can see and it gets called probably several times per sec, not hundreds. When you say that calling it WITH RECOMPILE temporarily fixed the problem don't you mean that it ACTUALLY fixed the problem? If the query plan goes stale due to data changing, might this not be normal?
I'm currently running the same SP but instead, my select is inserted into a table variable in the SP and then the returned select is done from the table variable. I did not experience the failure yet but haven't had much time running with it yet either. I should know if this makes things better by next weekend.
Hey Butch, I totally agree with your point. Do you put just the data and transaction log files and the 2nd physical drive or the installation of SQL Server as well?
Thanks for the great input!
October 19, 2004 at 7:06 am
Try this:
select
a,b,c,x01,y01,z01,x02,y02,z02,...,x21,y21,z21
from main,
(
select
max(case when weeknum=1 then x1 else null end) as x01,
max(case when weeknum=1 then y1 else null end) as y01,
max(case when weeknum=2 then x1 else null end) as x02,
max(case when weeknum=2 then y1 else null end) as y02,
...
max(case when weeknum=21 then x1 else null end) as x21,
max(case when weeknum=21 then y1 else null end) as y21
from table1 where val1=@userid and weeknum between 1 and 21
) t1,
(
select
max(case when weeknum=1 then z1 else null end) as z01,
max(case when weeknum=2 then z1 else null end) as z02,
...
max(case when weeknum=21 then z1 else null end) as z21
from table2 where val1=@userid and weeknum between 1 and 21
) t2
where val1=@userid and weeknum=@week
October 19, 2004 at 10:27 am
Here is my 2cents
Often it wasn't just the query inside of the proc that causing that problem but problem with connection pooling between SQL server and asp(IIS). Have you tried to drop and recreate the store proc to clear the connection to see if all are good? If that happens often, you might have to change your asp code to close the connection after you call the proc and create a new connection to call that proc. That will solve the problem but not in good way though. So, if you don't do update stats and recomplie often (like daily), I'd recommend to run it daily and make sure all good.
Try the alter proc first and then try the drop and create the proc first to see if all works.
October 19, 2004 at 11:27 am
DBCC statements should have taken care of clearing out the old plan from the cache.
As per placement, of course you want to put DATA device on a separate RAID-5 array unless you have a SAN. With local drives for IO subsystem trx logs can be very happy sitting on RAID-1.
Back to RECOMPILE, - it helps when execution plan in cache would yield a poor performance because the parameter passed produces a totally different logic processing path. And most importantly you know it in advance by testing the proc with different parameters passed.
October 21, 2004 at 9:15 am
You can try to give index hint to the queried tables explicitly. In this scenario, your are not trusting sql optimizer.
Give no lock hint as well if the chance of dirty read is very low.
October 21, 2004 at 9:32 pm
Larry,
If it was MY call it would be FOUR drives, system, SQL, Databases and Log Files.
Right now we have System on the first drive, SQL, the databases and logs on the second drive.
Butch
October 24, 2004 at 5:37 am
OK!!!
The problem happended AGAIN, this time with the table variable version of my SP (that piece of info is probably irrelevant). I could call the SP from query anaylzer and it was FAST. When I tried to call if from my ASP page, it would timeout.
I called this from QA:
DBCC FREEPROCACHE
I could then call the SP again from ASP without the timeout. It occurred just a couple of hours after my optimization job ran. My optimization job rebuilds indexes. Why would this issue occur?
Do I need to run DBCC FREEPROCACHE as part of my optimization job?!?
Only this one stored procedure seems to have the problem.
Does DBCC FREEPROCACHE free all of the SP cached plans?
Any light shed on this issue would be VERY helpful.
Thanks!
November 4, 2004 at 4:34 am
NEW INFO!
OK, The problem hasn't been happening much lately but I'm now copying this entire database to a new SQL Server and it again sprung its ugly head. This time I saw a couple of different symptoms. Sometimes the Stored Proc would timeout when called from ASP but NOT from query analyzer and sometimes it would timeout from both. This time, I could not get it to clear up by forcing a recompile of the stored proc or by rebuilding indexes or by calling FREEPROCCACHE. When the problem occurs, there is be lots of locking going on in tempdb and the three tables involved in the query.
I then tried the suggestion to copy the input parameters to local variables and use the local variables throughout the stored proc instead of the passed in variables and this WORKED!
My guess is that this is an indication that the query plan was getting confused in some way by the use of the input parameters. Any ideas about this?!?
November 4, 2004 at 5:08 am
One extra piece of info I just discovered:
In my large query statement, I have a in the where clause "WHERE rank>(((@pg-1)*@items_per_page)+1) and rank<=(@pg*@items_per_page)"
@pg and @items_per_page are passed in parameters. If calculate these values into local variables @min and @max-2 and make the where clase "WHERE rank>@min AND rank<=@max", this by itself seems to make the problem go away. Does using input parameters like this screw up the query plan?!?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply