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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 StoredProc is slow from ASP but not from QA

Author  Topic 

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-18 : 03:05:39
Hi ALL,
I've started a new thread at the suggestion of other users regarding an issue I'm having. 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?!?

The old thread is here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41100&whichpage=1
You can see how I'm calling the SP from ASP.

Thanks in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-18 : 08:02:50
What about your ASP code? If you're enumerating through the recordset using a While not EOF...MoveNext loop, or some other elaborate code, that could explain the delay in processing and why the query runs fine in QA.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-18 : 08:58:28
I agree with rob -- a lot depends on what you are doing in the ASP portion. how are you displaying this? Look into the GetRows() method of an ADO recordset to retrieve all rows quickly into an array, as well.

Hopefully you're not doing something expensive per row that you read, such as retrieving more data from the database (I've seen this before instead of Joins (!)), or perhaps certain conditions are bypassing your movenext() call or needlessly looping.

by the way -- even though you indicate taht in QA your SQL runs fine, that's might not be the most efficeint way to do a cross tab. You might have must better results with something like this:


select
a,b,c, WK1_X, WK1_Y, WK1_Z, WK2_X,WK2_Y,WK2_Z, .... etc ....
from
main
left outer join
(select val1,
max(case when weeknum = 1 then x1 else null end) as WK1_x,
max(case when weeknum = 1 then y1 else null end) as WK1_Y,
max(case when weeknum = 2 then x1 else null end) as WK2_X,
max(case when weeknum = 2 then y1 else null end) as WK2_Y,
max(case when weeknum = 3 then x1 else null end) as WK3_x,
max(case when weeknum = 3 then y1 else null end) as WK3_Y,
..etc...
from
table1
group by
val1) a
on
main.val1 = a.val1
left outer
(select val1,
max(case when weeknum = 1 then Z1 else null end) as WK1_Z,
max(case when weeknum = 2 then Z1 else null end) as WK2_Z,
max(case when weeknum = 3 then Z1 else null end) as WK3_Z,
...etc...
from
table2
group by
val1) b
on
a.val1 = b.val1


or use SUM() if you are aggregation numeric values.

- Jeff
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-18 : 12:40:59
Hi Rob and Jeff, thanks for the response.
The Microsoft SQL Server: OLE DB timeout occurs on the Execute from ASP of the initial (query the call to the SP), so its not an ASP timeout and the ASP code never gets to enumerate through the recordset. I also made a simple ASP page that only connects to the database, calls the SP and then ends, so that I could compare the execution time when called from ASP vs. Query Analyzer. Again, I got the OLE DB timeout.

Regarding your modified query, I can try something like that although I'll have to read up a bit as to what its doing (unfamiliar with using max() this way). Are you suggesting changing the query to avoid locking? When the problem is not occurring, the original SP is very fast, both in ASP and QA. When the problem does occur, I see locking of tempdb and the three tables (only when called from ASP) while the ASP page is grinding away trying to return from the execute call.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-10-18 : 14:14:50
"OLE DB timeout occurs on the Execute from ASP of the initial (query the call to the SP), so its not an ASP timeout and the ASP code never gets to enumerate through the recordset"

But maybe its getting blocked by another process iterating (slowly) through its results?

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-18 : 16:50:25
Well, the select only returns 40 rows and IIS is not running slow when the problem occurs as other scripts are snappy.

I'd like to put together some test ideas for when the problem is occurring. Since it seems that if I add an SP at the time of the problem doesn't allow me to see the problem anymore, this could be difficult. I will be able to attempt the DBCC freeprocache statement which should be interesting.

When it occurs again, I'm going to keep a close eye on the tempdb. As I mentioned earlier, tempdb is getting locked when the SP is called from ASP and that is why the SP is timing out. This is the biggest clue. I'm currently running the table variable version of the SP and haven't seen the problem yet. Maybe this affects recompilation and the SP cache?
Any suggestions for things to check when the problem next occurs will gladly be accepted!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 01:15:28
Perhaps we should have a look at the whole SProc source.

For example, are you doing

SELECT *
INTO #MyTempTable
FROM MyTable

or

CREATE #MyTempTable
(
...
)

INSERT INTO #MyTempTable
SELECT *
FROM MyTable

??

FWIW BoL says:
quote:

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.

Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.


Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-19 : 04:19:02
I'm doing
CREATE @MyTableVariable
(
...
)

INSERT INTO @MyTableVariable
select a,b,c,
(select ...
...
from main ...

select * from @MyTableVariable
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-19 : 06:03:23
Oops... that's:

declare @MyTableVariable table

etc.
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-10-19 : 06:27:22
Given your circumstances, it is very unlikely that the problem is at the Database end. Since both IIS and SQL are on the same machine, firewall and network issues can be ruled out. Check your connection string - how is your server/data source specified? Is it an IP address or DNS name? Try changing it to "localhost" or using the IP address if it is the machine name. I remember seeing an issue, when the ASP page couldn't even connect to the database unless we used the IP address. it's a long shot, but might be worth trying.

Another thing you could check is the version of the data access components. Download and install the latest MDAC from http://msdn.microsoft.com/data/mdac/default.aspx, it just might fix the problem.

OS
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-19 : 19:46:07
My test ASP page that just calls the SP was run on the local machine and a remote machine using a connection string specifying the IP address and same results.
How do I verify my current MDAC version?
Thanks for the input!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-10-20 : 00:44:01
Given dbConn as a database connection object:

sub kVersionADO()

Response.Write("<hr><h1>ADO Data Source Driver</h1>")
' Force output of parent page to this point - in case DUMP fails
Response.Flush
Response.Write("<table border=1>")
on error resume next
With dbConn
Response.Write("<tr><td>Version</td><td>" & .Version & "</td></tr>")
Response.Write("<tr><td>Timeout</td><td>" & .CommandTimeout & "</td></tr>")
Response.Write("<tr><td>Connection String</td><td>" & .ConnectionString & "</td></tr>")
Response.Write("<tr><td>Connection Timeout</td><td>" & .ConnectionTimeout & "</td></tr>")
Response.Write("<tr><td>Cursor Location</td><td>" & .CursorLocation & "</td></tr>")
Response.Write("<tr><td>Default Database</td><td>" & .DefaultDatabase & "</td></tr>")
Response.Write("<tr><td>Isolation Level</td><td>" & .IsolationLevel & "</td></tr>")
Response.Write("<tr><td>Mode</td><td>" & .Mode & "</td></tr>")
Response.Write("<tr><td>Provider</td><td>" & .Provider & "</td></tr>")
Response.Write("<tr><td>State</td><td>" & .State & "</td></tr>")
End With
on error goto 0
Response.Write("</table>")
' Response.Write rkADOCommandPropertyDump(dbConn)
' .Close
Response.Write("<H3>ADO Errors</H3>")
Response.Write("<table border=1>")
Response.Write("<tr><TH>Name</TH><TH>Value</TH></tr>")
For Each objProperty In dbConn.Errors
Response.Write("<tr><td>" & objProperty.name & "</td><td>" & objProperty.Value & "</td></tr>")
Next
Response.Write("</table><hr>")
end sub

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-24 : 07:20:07
Sorry for the duplicate post in two threads but I wanted to make sure all people who've graciously been helping get an update:

OK!!!
The problem happended AGAIN, this time with the table variable version of my SP. 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. This occurred a couple of hours after my optimization job ran. My optimization plan rebuilds indexes. Why would this issue occur?
Do I need to run DBCC FREEPROCACHE as part of my optimization plan?!?

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!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-10-24 : 10:30:21
I don't think it can be a recompilation, as it would run slow from both ASP and QA; also, it would say slow for a given "slow" query.

DBCC FREEPROCACHE is a bit of a sledgehammer solution!

If you want to try getting the SProc to recompile every time its called yopu could use

CREATE PROCEDURE MySProc
foo varchar(123)
WITH RECOMPILE
AS
...

but causing a recompile every execution is probably not ideal, performancewise.

Or you could scheduled

sp_recompile 'MySProc'

once in a while (daily?) which would force a recompile. You could try that the next time it slows down and see if that fixes it ...

I think your best bet would be to post the full source code for the SProc here and see if anyone can spot something untoward ...

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-24 : 14:29:00
I could post the SP source code, but then you'd likely need the database schema, and index info to fully understand the performance of the SP. I see if there is an easy way to show the detail.

It seems to me that Recompiling is MORE of a sledge hammer than clearing out the cache although I think that FREEPROCCACHE clears out the entire SP cache. I'll need to call a recompile on the SP when the problem happens next to see if that even fixes it. I've seen that some people have had similar problems and that recompiling didn't help but freeing the cache did and vice versa.
Maybe when I do the optimization job which rebuilds the indexes, the cached SP plan is no longer valid and must be freed?
Doesn't freeing the cache force a recompile anyway? If so, then called FREEPROCCACHE would cause ALL SP's to recompile the next time their called wouldn't it?

Can anyone shed some light on this? Thanks!
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-24 : 21:43:37
How are you rebuilding your indexes? Are you defragging or rebuilding all of them every night? If you are rebuilding, it should be updating the stats, which will cause a recompile the first time most of the stored procs run anyway. What service pack are you on for SQL Server?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-25 : 06:37:05
I'm rebuilding with an optimization job as part of the maintenance plan. On the optimization tab, I have Reorganize data and index pages selected with the option "Reorganize pages with the original amount of free space". I also have "Remove unused space from database files" checked. I'm also up-to-date with the latest service packs as well:
"Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) "

Any ideas why calling FREEPROCCACHE would help?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-10-25 : 07:55:04
I still think it would be worth seeing the SProc source - if that leads to also needing all the other DDL stuff we can ask for More! But someone might spot something in the SProc that is blocking, or similar.

The only thing I can think of that FREEPROCCACHE, rather than recompiling the SProc, might help with is if there are other query plans, e.g. from Adhoc SQL, that are [wrongly] interfering with the SProc - e.g. the SProc is actually fine, but something else is causing the trouble. Doesn't explain that usage of the SProc is fine from QA ...

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-11-04 : 06:19:47
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 a suggestion I got 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?!?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-04 : 06:38:10
You said earlier that
I can see locks on tempdb and the three tables in the query

Does that mean that it is being blocked or just that the locks are being held long enough for you to see them?
Try option (maxdop 1)

Are the variables the same datatype as the input parameters?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-11-04 : 06:59:48
The locks are there as long as I allow the query to run in QA which I assume means it is also blocked. What is "maxdop 1"?

I have an 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 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?!?
Go to Top of Page
    Next Page

- Advertisement -