Classic ASP - troubleshoot poor performance

cuadmin
cuadmin used Ask the Experts™
on
I have a classic ASP application running on a Windows 2008 R2 server, with a Microsoft Access backend.  This is the only application running on the server.  
Periodically, especially during busy times, the application will crawl to a halt.  I have added more system resources to the server, but this is not resolved the issue.  I have gone through the logs, including the failed requests, but it still unsure how to rectify.  
Any thoughts on how to troubleshoot?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
There are a lot of possibilities.  

Is this a shared server like godaddy or some other shared service where you are paying less than $30 per month?  Or a VPS where you may be paying in the $70 to $150 range,  a true dedicated server from a hosting company where you are paying over $200 each month or are you hosting a server at your home or office?

What is the type of server and memory?

If you create a plain hello world .html email and load that when you see your app running slow, does that load slow?  If not, the issue is your database.

How many users do you have?  Per month?  Per day? Page views  per hour during peak times? page views per second during peak times?

When the app runs slow on a page that uses the dasabase, are there other pages that also runs slow that use the database?

Taking out any private info like passwords, what is the code and any database query that is running slow?

How many rows of data are in each of the top 5 largest tables? or the tables/views being accessed in the pages that are running slow?

Are you allowing  users to upload files or images?

Author

Commented:
Scott,

Thanks for the reply. It’s a VM, hosted and supported by internal staff. Two 2.4 Ghz Xeon processors, 8 GB RAM on a 64-bit OS.

I have application transaction log that capture a variety of records – it’s anywhere from 1000-1500 on an average day to nearly 3000 on a busy day. (The log captures logins, logouts and saved records)
When it’s busy, or really slow, the login page will still load. It appears that when you run a query, during peak time, it kills the server or caused a long lag. Is there a way I can prove that it’s the database that’s killing the app?

I am running the performance monitor and, sometimes, I’ll see the ‘requests failed’ start to increase. Also, I am logging the FailedReqLogFiles – but ensure of what to make of these.

The Access DB is roughly 300 Mb. Would compacting a repairing the DB regularly help? I have one table that is about 70K records, another that is 66K, one that is around 10K and another that is around 4 K. Roughly 100 tables in all.

I am not allowing users to upload images.
Is there any other logging I should be doing? If it’s obvious that it’s the MS Access DB that’s causing the issue, what are my options? Could I move to MS Sql Express? Something else?

Thanks for your help! 
Systems Engineer
Commented:
I would say that the AccessDb could be an issue.  The main issue is how you are handling the connections to Access.  Under stress/heavy load, an AccessDb has a concurrent connection limit.

Reference link:  http://sql-troubles.blogspot.de/2010/10/limitations-of-ms-access-database.html

This is what I could recommend:
1. make sure http logging is enabled, all fields
2. take a look in the logs to see what requests are incoming when you experience your described issue
2a.  look for pages with long "time-taken" entries or 500 level errors
3. review the process of connecting to the AccessDb.

Many years ago, I developed a Classic ASP based content manager with Access as the backend.. [SQL Express wasn't out yet].  I ran into a similar issue.  Under stress, the site would slow down and request would either timeout or take a long time to be serviced.  It turned out to be the process of opening, querying and closing the db connection.  I had to limit (as much as possible) the time the db was called upon and the connection was left open.  I eventually switched to opening the db, running the query, storing the respond in a variable and immediately closing the connection.

Dan

Author

Commented:
Thanks for the advice - planning on moving the DB to MS SQL. Thanks guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial