Solved

ASP / SQL Site Unreliable - Various Timeout Problems

Posted on 2003-11-05
45
551 Views
Last Modified: 2007-12-19
Dear Experts,

I have an ASP site that connects to a back end SQL Server 2000 database.  The part of the site that is giving me trouble is a multi-page user application.  In theory, a person will do the following:

Application Page 1 -> Application Page 2 -> Application Page 3 -> Processing Page (no output ) -> Welcome Page OR Rejection Page

In more detail:

1)  User goes to page 1 and enters information into form.
2)  User submits
3)  Javascript validation check
4)  On successful validation check, page 2 takes the post data from page 1 and does SQL insert statements to put data into database.
5)  Page 2 displays a form.
6)  User fills out information and submits
7)  Javascript validation check
8)  On successful validation, page 3 takes the post data from page 2 and does SQL insert/update.
9)  Page 3 displays a form
10)  User enters information and submits
11)  Javascript validation check.
12)  On successful validation, the processing page takes post data from page 3 and does SQL insert/update.
13)  Processing page automatically redirects to either welcome or reject, depending on data.

Unfortunately, this process is plagued by problems.  The two mains ones are:
============================
Active Server Pages error 'ASP 0113'
Script timed out
/pageX.asp (this can happen in multiple places, but the most common is 2->3)
The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.  (the timeout is set to a high value already)

There is also the lovely:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/pageY.asp, line ZZ  (line ZZ is a database query)

Sometimes people will see page 2 without having any data entered into the database at all.  

The ODBC problem is sometimes accompanied by the following symptoms:

1)  Microsoft Enterprise Manager freezes/crashes
2)  Queries in Microsoft Query Analyzer do not return results


There are also rarer problems like

"There is no website configured at this address"
=========================

There is no consistency to these errors.  They show up on different pages at different times.  Sometimes the site will run without a hitch for several days; other times there will be so many problems as to make it unusable.

I have consulted with the developers and my hosting provider regarding this problem.  The hosting provider blames the problems on bad coding, and the developers blame the problem on the server being too slow.

I am inclined to put the blame on my hosting provider, since I have been in touch with someone else on our shared server (there are dozens of other sites hosted from the same Windows 2000 Advanced Server machine) and it appears that our two sites experience problems at the same time.  Am I reasonable to fault the server rather than the code?

What should I do about this?  One solution is to move to a dedicated server where we can have more resources - but this is rather expensive.  Do you think the site could have been built in a more fault tolerant way?  I found this link http://www.marzie.com/devtools/asp/error500.asp, which looks useful for error handling, but I would rather not cause these 500 errors if possible (since it ruins the application process).  Any thoughts on how to be more fault tolerant?
 
 Thanks.



0
Comment
Question by:Igiwwa
  • 22
  • 10
  • 9
  • +2
45 Comments
 
LVL 4

Expert Comment

by:kblack15217
ID: 9689350
Two Quick Fixes that you can actually control

Server.ScriptTimeOut = 90   'Allows page to process for 90 minutes

objConn.ConnectionTimeOut = 10000 'allows connection to process database requests longer

Your code does not seem that database intensive or processing heavy, which means that your hosting provider probably has too many sites on its server
0
 
LVL 4

Assisted Solution

by:kblack15217
kblack15217 earned 20 total points
ID: 9689404
As for
The ODBC problem is sometimes accompanied by the following symptoms:

1)  Microsoft Enterprise Manager freezes/crashes
2)  Queries in Microsoft Query Analyzer do not return results

This is a direct result of poor management on the database server, and not the code.    In other words, EM is written just fine, but the database its connected too is overloaded or mismanaged.
0
 

Author Comment

by:Igiwwa
ID: 9689438
I currently have the ConnectionTimeOut at 300 and the ScriptTimeOut at as high as 6000 for some pages.  I'm pretty sure that is not the problem, because even the most resource intensive pages do not take anywhere close to that long under normal operation.  Besides, even if they do, the users are probably long gone.

Does the web server/database just start dropping connections when it is overburdened?  Do you think that might be happening?
0
 
LVL 4

Expert Comment

by:kblack15217
ID: 9689505
Its definately a possibility.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9689512
Maybe you're not closing some loops properly, could be a few things going wrong.
0
 

Author Comment

by:Igiwwa
ID: 9689602
Gary,

Do you think it is something minor in my code?  I feel like it is a larger problem because:

1)  It affects different pages
2)  The problem is not reproduceable (to my knowledge).
3)  It seems to be affecting other sites on the same server.



0
 
LVL 58

Expert Comment

by:Gary
ID: 9689626
If this is happen server wide with many sites then it could be someone elses site that's causing the problem.  It could also be a badly configured server or a badly spec'd server thats just not up to the job - maybe they've got 5000 sites on the same box!  If its only happening sporadically with no pattern to time or pages I would go with the server being bad and not up to the task of handling the current number of sites on it.  Which company are you using?
0
 
LVL 58

Expert Comment

by:Gary
ID: 9689638
Do the problems only occur when accessing pages that access the database?  
0
 

Author Comment

by:Igiwwa
ID: 9689692
We have the Platinum Plus plan on Hostway which includes (according to their site):

Dual Pentium III
RAID SCSI drive system
Microsoft® Windows2000® Advanced Server
Microsoft® 5.0 Internet Information Server

I will try to find out the exact specs.

I don't know how many sites are on the same box, but in Enterprise Manager I can see approximately 160 databases (max is 2 per customer) so I'd say there are at least 80 sites, probably 130-140 if I had to estimate.

To answer your second question:  There are times all the problems appear to be database related.  Pages that don't hit the DB come up with no problem, but the ones that go against the DB have errors.  
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 200 total points
ID: 9689763
If the SQL 2000 is being run of the same pc thats not good and may explain why hitting the db is when it seems to kill the server.  The price you're paying is not a lot at all and I suspect they have more than 130-140 sites on the server (n.b. for future reference any web host who offers unlimited hits/traffic/bandwidth is lying - should you start to use a lot of bandwidth they're likely to just kick you off without so much as a 'bye your leave')
0
 

Author Comment

by:Igiwwa
ID: 9689787
Oh, I don't know if the database / web server are on the same machine.  I will check into it.
0
 

Author Comment

by:Igiwwa
ID: 9690073
Different machines.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9690083
Not much else to offer if the host isn't prepared to spend 5 mins looking at their logs to see if anything is untowards
0
 

Author Comment

by:Igiwwa
ID: 9690121
I'm not sure exactly what you mean.  I can certainly ask my host anything - whether they actually do it is another matter.  Which logs should I ask about?

I have the SQL Server transaction log if that is what you are referring to.  I tried to look at it earlier, but I guess I need some sort of special viewer, since it was all random characters and whatnot.  How do I go about opening it in a viewable format?
0
 
LVL 58

Expert Comment

by:Gary
ID: 9690139
No, the web server logs everything that happens, like if a component crashes etc, if you can give them a time something happened they can check the logs and see what was happening at the time, maybe a site was using all the processor power, etc.
0
 

Author Comment

by:Igiwwa
ID: 9690560
I asked my hosting provider about the specs of the machine, and here is the data I got:

Total Users: 277

Role Member Server
Processors 1
Memory 1 Gb
Instance CPU0 Family Intel(R) Pentium(R) 4 CPU 2.40GHz Type x86 Family 15
Speed 2410 Mhz
Cache Speed -1 Mhz
External Clock 133 Mhz Interface ZIF Socket
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9692553
Hmm... did you say Hostway??... well.. I had an account there.. and i used to get the same kind of problems. They had great reviews and so many non profit sites rated them no:1 . Initially when i hosted the website there, everything seemed to be ok. But the strange thing that i noticed is that when ever i uploaded my connection.asp file which holds the database connection info with the wrong db server name, it seemed to pull the whole server down for a few minutes and automatically picks itself up. (Such a thing should never ever occur.. it should have displayed a normal error message like server could not be found) We still would have lived up with the site freezing up, but for the ridiculous pricing for additional storage space(1$ per 1Meg others offer 100megs for 5$). And the worst thing was that when ever such a thing happened, even the enterprise manager and the sharepoint teamsite freezes up. After a few minutes everything was back to normal.

Coming to your problem, make sure that you have closed off (set to noting) all your objects used within your code.

BTW.. I no longer have my app running on the server... so dont blame me for these issues any more... I have moved to a cheaper, faster and more reliable hosting provider.

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9692558
BTW.. dont you hate it when enterprise manager loads all the 100 odd databases??

Cheers!!
0
 

Author Comment

by:Igiwwa
ID: 9694087
yeah, opening enterprise manager is a big commitment for me
0
 

Author Comment

by:Igiwwa
ID: 9697454
to all the experts:

If you were in this situation, would you switch to a dedicated server in hopes of solving the problem?

(http://www.hostway.com/dedicatedserver/advantage/win100.htm)
0
 
LVL 58

Expert Comment

by:Gary
ID: 9697563
Before doing anything with a dedicated server, shop around, work out what you need, ask companies for proposals for what you need not what they offer.  Checkup on the company  - search google for reviews about them.
The plan you link to is probably way above what you need.
0
 

Author Comment

by:Igiwwa
ID: 9702024
I read somewhere that I should use the 'SET Nocount ON' with my sql to supress the return values from the insert/update/delete statements.  Do you think this is worth looking into?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:ap_sajith
ID: 9705687
>>If you were in this situation, would you switch to a dedicated server in hopes of solving the problem?

Dedicated servers are worth every penny you pay... How ever i would look into a different hosting provider. I can recomend a couple of good ones if you want.

BTW.. It is always a goo idea to use 'Set NoCount ON' In your SQL Stored Procedures...
Cheers!!
0
 

Expert Comment

by:adamkimber
ID: 9707427
I use asp-host.co.uk and have run some pretty intesive db stuff before now and it handles it fine!
0
 

Author Comment

by:Igiwwa
ID: 9718256
Ap_sajith,

I think it might be less headache to just stick with Hostway since we already have other sites hosted with them.  Even so, I am curious to your recommendations for other providers.

Thanks.

Igiwwa
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9720316
Well... I would suggest http://www.attenda.com if you are looking for dedicated hosting...and http://www.webhost4life.com for shared hosting...Attenda is a bit pricey as most of the dedicated hosting providers are... but their support has been excellent over the last 1.5 yrs. WEBHOST4LIFE... is cheap, super fast, hassle free and above all.. you have a super duper web based sql manager... you wouldnt believe how fast it churns out sql stuff for you... I was really impressed by webhost4life.. especially the fact that for such a low price.. the performance is toooooo good!!..


Cheers!!
0
 
LVL 58

Expert Comment

by:Gary
ID: 9720358
Outta the frying pan into the fire with webhost4life
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9720438
Gary, why do you say that?... Did you have any bad experience with them.. I moved my site to webhost4life a few months ago. So far its simple been awesome. Now that you have made the above comment, its giving me jitters....

Cheers!!
0
 
LVL 58

Expert Comment

by:Gary
ID: 9720505
Apart from offering Unlimited bandwidth and then in their ToS saying 'excessive use of data transfers leading to...' will result in your account being suspended.  And using 'testimonials' that can be found on numerous other sites whose nameservers are for their company and not webhost4life.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9721486
Yeah.. i did read that.. but i assume its just a precaution against sites sending spam etc.... But so far i feel that they have offered the best service for the money that we pay. Anyway, thanks for the word of caution from you I'll be careful :o)

Cheers!!
0
 

Author Comment

by:Igiwwa
ID: 9722481
An error occured at 11/11/2003 9:36:43 AM
BrowserType=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
Method=POST
Category=Microsoft OLE DB Provider for ODBC Drivers Number=(0x80040E31) Description=[Microsoft][ODBC SQL Server Driver]Timeout expired Filename=/page3.asp Line =129
===============================

If the script made it to line 129, it must have executed a SELECT statement.  It wasn't until it got to an UPDATE statement that it timed out.  Does this shed any light on the problem, or am I still shopping around for a dedicated?
0
 

Author Comment

by:Igiwwa
ID: 9725599
Well, Hostway wants $498/mo for a basic windows dedicated server w/ SQL Server, so I'm stuck trying to improve the code for the time being.

Regarding the SET NOCOUNT ON:  Will this provide any performance improvement if I am not using any stored procedures?  Does this stay on by default if I execute the command once?  Is this a global setting?

Thank you.
0
 

Author Comment

by:Igiwwa
ID: 9725628
Well, to answer part of my own question, it does not appear to be global.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9728334
>>Regarding the SET NOCOUNT ON:  Will this provide any performance improvement if I am not using any stored procedures?  

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.

As for the error that you are getting, Are you using a stored procedure to run the update query?

Cheers!!
0
 

Author Comment

by:Igiwwa
ID: 9743424
We are not using a stored procedure to run the query.

I talked to Hostway and I am now considering asking them to move the database to a different machine (from one shared to another shared in hopes of going to a less overcrowded db host) while leaving the web server unchanged.  Do you think this is a good idea?

I know I will have to change my connection string and Hostway said I would have to wait a few hours for change to propogate.  Are there any other issues I should be aware of?

0
 

Author Comment

by:Igiwwa
ID: 9790762
Hey.  I don't know if anyone is following this thread anymore, but I just realized that my database does not have any primary keys.  Is this a problem?

Thanks.  Sorry to have the question drag on for so long.
0
 
LVL 21

Accepted Solution

by:
ap_sajith earned 280 total points
ID: 9791018
Not having primary keys/ indexes in your table considerably slows data retrieval. So it could be a reson for your SQL slowing down.. Its always a good design practice to have primary keys and indexes on tables as this helps in data integrity and faster data retrieval. You need not necessarily have primary keys for all tables.. but try and use one of the colums as clustered index. The easiest way to achieve both primary key and indexing is to create a new column say, called ID that is numeric, autonumber and set it as primary key. This should help improve data access time.

Cheers!!

0
 

Author Comment

by:Igiwwa
ID: 9791034
I already have a user_id in most of my tables.   I guess I should set that to be the primary key.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9791104
yeah.. i think you should.. setting the primary key automatically indexes the table as well..

Cheers!!
0
 

Author Comment

by:Igiwwa
ID: 9791119
Thanks, I'll run it by the developers and then implement as soon as possible.

0
 

Author Comment

by:Igiwwa
ID: 9851307
Thanks for all your help.  I have added primary keys and indexes.  I also fixed a query that had to do 70,000 nvarchar -> int converts each time it was executed.  The problem has not gone away, but it is definitely better.  I think we'll eventually have to move to a dedicated for increased performance.

Thanks again.
0
 

Author Comment

by:Igiwwa
ID: 9975567
Dear GaryC123,

I'm not sure if you're still following this issue, but I had a question about a comment you made a few weeks ago.  You said "If the SQL 2000 is being run of the same pc thats not good".  Since then, I have talked to some other people who said putting SQL Server on the same *dedicated* machine as the web server is a good idea.  What do you think about this?  It seems to me that having it on the dedicated machine is better than sharing a db machine with 100+ other dbs.  Was your comment specifically targeted at shared servers, or do you think it is a bad thing no matter what?  Right now our options are either

1.  Shared Web / Shared DB
2.  Dedicated Web / Shared DB
3.  Dedicated Web / Dedicated DB

and I think the 3rd choice is best.

Hope to hear from you.

Thanks.
0
 

Author Comment

by:Igiwwa
ID: 9975572
oh, and I can toss some points your way for this, I just wanted to post it here because it directly references a prior comment in this thread.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9975760
The comment was in regard to a shared server scenario which was your case without rereading all the posts.  If you are going the dedicated server route then it would make sense to use the same server since you are likely to only have one or just a few sites - and it would be a bit silly paying for another server as well!  So if you are going the dedicated route then option 2 is for you.
0
 

Author Comment

by:Igiwwa
ID: 9994196
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now