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
 Calling SP from ASP versus Query Analyzer

Author  Topic 

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-11 : 03:31:11
Hi Experts!

Here is my situation:

Earlier today, I had an issue where a particular stored procedure was timing out when called from my ASP pages. The SP has 4 input params and is a basically a large select statement using those params to return a rowset. I attempted to call the SAME stored procedure from query analyzer using the same params and it is BLAZING fast. My connection string through ASP looks like this:

ConnStr = "Provider=SQLOLEDB;Network=DBMSSOCN;Data Source=localhost;Initial Catalog=mydata;User ID=uid;Password=mypassword"

To call the SP from ASP I do the following:

Set DB = Server.CreateObject("ADODB.Connection")

DB.Open(ConnStr)

Set RS=DB.Execute("exec dbo.spMySP 1,2,3,4")

This has worked fine for weeks. My database has been growing and then at some point today, this started timing out but as I mentioned, calling "exec dbo.spMySP 1,2,3,4" from query analyzer works and is FAST!. In profiler, I notice that the next log entry after each call to the SP is followed by "Audit Logout" which has a duration of 30,000+, about the same as the SP itself. As a matter of fact, I noticed quite a few "Audit Logout" entries with a VERY large druation, not all associated with this SP, one with 624,266. Is this an indication of what this problem might be?

Any ideas would be greatly appreciated!

Thanks!

tuenty
Constraint Violating Yak Guru

278 Posts
Posted - 2004-10-11 : 10:45:27
I really don't know about the Audit Logout but I think you can avoid the timeOut in the ASP by setting the connection or command timeout to 0 or to a larger number than what you currently have

from BOL
Using Audit Logs
SQL Profiler system stored procedures support file rollover. The maximum file size for the audit log is fixed at 200 megabytes (MB). When the audit log file reaches 200 MB, a new file will be created and the old file handle will be closed. If the directory fills up (for example, if the disk quota for the user of the service account has filled up or the disk is full), then the instance of Microsoft® SQL Server™ is stopped. The system administrator needs to either free up disk space for the audit log before restarting the instance of SQL Server or restart the instance of SQL Server (if auditing is not configured to start automatically).

Use file rollover to prevent the audit trace from failing because the audit log filled up. However, SQL Server will not shut down unless the user specifically requested this feature when they created the trace. An audit failure produces an entry in the Microsoft Windows® event log and the SQL Server error log.

It is strongly recommended that during SQL Server Setup you create a new directory to contain your audit files. \mssql\audit is the suggested path. If you are running SQL Server on a named instance, the suggested path is MSSQL$Instance\audit.

Search in the BOL for AuditLevel Property

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-11 : 12:52:28
Thanks for the reply! I'm actually not suspicious of Audit Logout anymore as I now know that its duration is cumulative and problaby just includes the long duration of the SP in question. I guess the really issue has to do with why does this SP take so long to run when called by ASP vs. Query Analyzer.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-12 : 03:50:35
Yeah, looks like it doesn't it.

No chance anything is locking/blocking your SProc when run from ASP? Does the problem happen, from ASP, even if the system is "idle"?

Can you put some code in ASP just before and after the SProc call to check that it is indeed the call to SQL that is slow, rather than something that happens in ASP a bit later on? (e.g. store the time before the SQL call, and again after, to calculate elapsed time)

I can't think of anything else that would make an SProc call from ASP slower than QA (Note that, by default, QA has no timeout, but as you say execution is quick in QA)

Are you running QA on the same machine as the ASP is running? Maybe there is some "delay" communicating from the ASP machine to the SQL box (compared to your machine's QA communicating to SQL box) ... just looking to see if there is any difference between the ASP setup and the QA one, you don;t want to be testing a "hybrid" solution!

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-12 : 11:59:25
Hi Kristen,

Thanks for the reply.

The IIS machine and the SQL Server are the one and the same, so ASP is talking directly to SQL Server. QA is running on a remote box.
The error I get is an OLE DB SQL Timeout in ASP which is a strong indication of an SQL SP timeout rather than another issue. I believe the default timeout is 30 second for OLE DB and the call takes about that long before I get the timeout error in ASP. From QA, the call took at most 1 second.

Any other ideas?

Thanks for your input!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-12 : 14:45:05
I still think it would be worth putting some logging statements around the actual call to SQL just to be sure that it is NOT returning.

You could also try running QA on the server - and using the same UserID/password as ASP - anything to reduce the chance that a simple difference is causing the problem (can't see how any of that would cause a timeout, but as an old hand I've had similar thoughts before and been wrong!)

I presume there are loads of other SProcs that are being called from ASP, which work just fine?

Its a blinking mystery, that's for sure ...

... no chance that the server is being hacked is there? Teh standard portscan attacks for SQL port 1433 which then try to login as SA/ADMIN/A.N.Other will cripple most servers in short order. Easiest way to check for that is to turn ON logging of failed logons - Enterprise Manager - RightClick the Server : Properties : Security and then select "Audit level = Failure" - then look in either Event Log or SQL Log after a little while; its not a bad Come-In_Handy setting to ahve on the server anyway.

The CPU will be running at 100% utilisation when the attacks happen too ...

But it doesn't explain why QA gives "Instant" from remote machine and ASP Query on Server runs like a dog ...

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-12 : 20:37:35
The server could be getting hacked. I have a lot of failed logins for the following accounts:
sa
master
user
etc....

I was getting 100% CPU utilization at the time of the problem. But other SPs WERE working OK when being called via ASP.
And hacking still doesn't explain the performance issue I've seen between ASP and QA SP calls.

Hmmm... Is it possible that DNS could have something to do with this?
QA makes a connection and keeps it open, right? My connetion string that ASP uses calls out 'localhost'. If DNS is having problems, could this be an issue? Since IIS and SQL are on the same server box, is there a better way to do the connection?

Thanks!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-13 : 01:02:02
Easiest way (short of calling out the boys from the Firewall Department) is to change the PORT that SQL is running on - boost it to a number over 10,000, most port scanners don't bother to go up that high.

On the server use "Server Network Utility" to change the port, and put the port number in the connection string - can't exactly remember the syntax, but I think its "MyServerName:10000"

And you'll need to change the port that your local utils are set to connect to ("Client Network Utility" on your PC, put an Alias in there for your server and connect to that named-alias server from QA/EM)

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-13 : 20:51:06
OK, more info!

This seems to be a LOCKING problem. When this stored procedure is called from ASP, I see a TON of locks on the tempdb and on the 3 tables that it does selects from. When I do the same call from query analyzer which is a different process, I do NOT see any locks or at least they happen so fast and are gone that I can not see them in EM. I've save to text files the export of the lists for locks/spid, locks/objects etc.

When I stop SQL Server from the manager and then Restart it, the problem goes away. The stored procedure is blazing fast again like it is from QA. I know that everytime you start SQL Server, the tempdb is deleted and recreated.

What would be causing this type of problem?

Thanks in advance!
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-10-13 : 21:49:01
I'm going to ask a really stupid question. :)

Have you ran UPDATE STATISTICS on the tables involved in the procedures? Also, do you have proper indexes? Have you reindexed the indexes lately?

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-13 : 23:49:32
Hi Derrick!
What's a stupid question?!? :)

Well, I've run a maintenance plan on this DB with the optimization job selected. This drops the indexes and rebuilds and regenerates stats. I've done this recently. The problem does seem to take time to occur (a day or two) and goes away immediately when I restart SQL Server. Would these affect tempdb usage? Why only from ASP and not QA? QA is using a different process but should they be affected in the same way? Why the difference with the locks?

Regarding the indexes, I used index tuning wizard to arrive at many of my indexes. I may have a number of unused/wasteful indexes on the three tables involved. I was afraid to remove any though because I'm in production with lots of activity, so I didn't want to break something that wasn't broken. Unfortunately, now it IS broken!

By the way, I have all of the latest service packs on SQL Server 2000.

Any ideas or suggestions?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-14 : 02:58:10
We've had a problem like this before. It has tended to be after some mucking about in development only, and we've attributed it to IIS not closing an object, or somesuch, (particularly if IIS terminates an ASP page due to error) and thus not releasing the DB connection, or at least the Connection Pool not getting a clear idea of what has gone on.

Sopt/Start IIS always clears it for us (which kinda absolves the DB of any blame!)

This is a production server, right? No mucking-about development going on using that machine, right? <Titter/>

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-14 : 04:30:59
No Delevopment. I did a DNS restart and an IIS restart to clear them of blame and it did. The problem was definitely in SQL Server with tables including tempdb locking.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-14 : 05:11:48
"No Delevopment"

Shucks! It was a cheap shot and I was hoping to find the target ;-)

Hmmm ...

Has your SProc got some sort of permanent-temporary table - ##MyTempTable style? Or some other resource in TempDB that is causing the locking?

Might be worth having a look in TempDB to see what objects there are in there

Something along the lines of:

SELECT TOP 100 *
FROM tempdb.dbo.sysobjects
WHERE xtype <> 'S'

but I can't see why your QA wouldn't suffer from the same issue. Perhaps your settings for ADO are causing it to use some sort of cursor that is clogging up TempDB? I reckon its gotta be something specific to the environment that IIS/ADO is doing that is different to QA.

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-14 : 05:39:42
My SP is simple. I looks kinda like this:

CREATE Procedure spUpdateWinsByPool
@userid int
As
set nocount on
select a,b,c,
(select x1 from table1 where val1=main.val1),
(select y1 from table1, where val1=main.val1),
(select z1 from table2 where val1=main.val1),
(select x2 from table1 where val1=main.val1),
(select y2 from table1, where val1=main.val1),
(select z2 from table2 where val1=main.val1),
.
.
.
(select x21 from table1 where val1=main.val1),
(select y21 from table1, where val1=main.val1),
(select z21 from table2 where val1=main.val1)
from table3 where val1=@userid
GO

Basically its a large select from 3 tables where the data is formatted into the desired row set result.
Does this use tempdb even though I'm not explicitly creating a tempdb and inserting data? The seems to be locking the tables after a while and there appear to be locks on tempdb.

What's wrong here?

I'm returning this to an implicitly declare record set in ASP from where the SP was called.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-14 : 06:36:11
I suppose its possible that each of the "select X from tableN where val1=main.val1" is creating a temporary table in TempDB, and that's overloading something - but I don't understand why QA gets a different scenario. Maybe teh addition of multiple concurrent requests from ASP are causing blocking on the TempDB stuff

Do you have to code it this way, or could you use a JOIN?

select a,b,c,
table1.x1,
table1.y1,
...
from table3
LEFT OUTER JOIN table1
ON val1=main.val1
where val1=@userid

("main" needs aliasing/JOINing somewhere along the way)

'coz if you can then this will be a much lighter load on SQL

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-14 : 19:11:18
I'm sorry, table3 should actually be main in my example.

I'm not familiar with using JOINS. I need to read up. From my example with the correction, how would I write that?

Thanks in advance!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-15 : 00:40:45
So jsut to make sure I've got the right end of the stick.

For a row in MAIN there will be one row in TABLE1 and one row in TABLE2? (To be on the safe side I wil use an OUTER JOIN, so that will allow ZERO or ONE matching row in each of those two tables. If there MUST be exactly one row in each then change that to an INNER JOIN. If there are multiple matching rows in TABLE1 and/or TABLE2 you will get a row returned for each matching combination)

SELECT a,b,c,
       table1.x1,
       table1.y1,
       table2.z1,
       table2.z2
...
FROM  main
      LEFT OUTER JOIN table1
         ON val1 = main.val1
      LEFT OUTER JOIN table2
         ON val2 = main.val2
WHERE val1=@userid

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-16 : 17:39:03
OK People! I've got some interesting info! The problem is happening right now as I type this. When the SP with the large SELECT I've described is called, I'm getting the SQL OLE DB timeout when called from ASP but NO timeout when called from Query Analyzer and the call returns immediately with the data.
I tried calling the SP from ASP using the Command object instead, calling it as a stored procedure with parameters and no luck.
During the call from ASP, the CPU usage jumps to 50+% for the duration until the timeout.
I then made an identical SP but rather than returning the data directly from the SELECT, I instead create a table variable, INSERT into it with the EXACT SAME select statement and then do a SELECT on the table variable to return the results. NO PROBLEM!!
The query remained as fast in ASP as it does from Query Analyzer. I could alternate between the two calls by changing my ASP page and the original SP times out but the new SP does not.
So I got all excited that I was onto something but then, I then thought that maybe I should test to see that this does not have to do with simply creating a new SP, so I created another new SP with the EXACT same content as the troublesome SP but changed the name by appending "_test" to the name. I modified my ASP to call the new SP and IT DID NOT FAIL EITHER! I could then alternate between the two SPs with the SAME EXACT contect and one would fail while the other would not!
What could cause this?!? I checked Perfmon and didn't notice any big jumps in any counters between calling one versus calling the other!
Any ideas?!?
Thanks in advance!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-17 : 01:05:39
Recompilation of the SProc? When's the last time you would have re-created the original SProc? Maybe its using a now-stale query plan?

Kristen
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts
Posted - 2004-10-17 : 03:15:41
I guess that's possible, but why would it only be slow when called from ASP?
So identical stored procedures containing identical queries returning 40 recordsets from some large tables are performing completely differently from ASP (one times out OLE DB) and from Query Analyzer they perform equally fast (under a second)
Would recompilation have something to do with this? As I mentioned, in PerfMon, I don't see the recompilation count jump up when calling one from ASP vs. the other.
I'm open to any possiblity. What about hard disk trouble?
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site