ASP / SQL Site Unreliable - Various Timeout Problems

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, 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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
IgiwwaAuthor Commented:
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?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Its definately a possibility.
Maybe you're not closing some loops properly, could be a few things going wrong.
IgiwwaAuthor Commented:

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.

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?
Do the problems only occur when accessing pages that access the database?  
IgiwwaAuthor Commented:
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.  
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')
IgiwwaAuthor Commented:
Oh, I don't know if the database / web server are on the same machine.  I will check into it.
IgiwwaAuthor Commented:
Different machines.
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
IgiwwaAuthor Commented:
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?
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.
IgiwwaAuthor Commented:
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
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.

BTW.. dont you hate it when enterprise manager loads all the 100 odd databases??

IgiwwaAuthor Commented:
yeah, opening enterprise manager is a big commitment for me
IgiwwaAuthor Commented:
to all the experts:

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

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.
IgiwwaAuthor Commented:
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?
>>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...
I use and have run some pretty intesive db stuff before now and it handles it fine!
IgiwwaAuthor Commented:

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.


Well... I would suggest if you are looking for dedicated hosting...and 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!!..

Outta the frying pan into the fire with webhost4life
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....

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.
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)

IgiwwaAuthor Commented:
An error occured at 11/11/2003 9:36:43 AM
BrowserType=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
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?
IgiwwaAuthor Commented:
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.
IgiwwaAuthor Commented:
Well, to answer part of my own question, it does not appear to be global.
>>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?

IgiwwaAuthor Commented:
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?

IgiwwaAuthor Commented:
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.
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IgiwwaAuthor Commented:
I already have a user_id in most of my tables.   I guess I should set that to be the primary key.
yeah.. i think you should.. setting the primary key automatically indexes the table as well..

IgiwwaAuthor Commented:
Thanks, I'll run it by the developers and then implement as soon as possible.

IgiwwaAuthor Commented:
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.
IgiwwaAuthor Commented:
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.

IgiwwaAuthor Commented:
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.