Classic ASP - troubleshoot poor performance

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?
cuadminAsked:
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.

Scott FellDeveloper & EE ModeratorCommented:
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?
cuadminAuthor 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! 
Dan McFaddenSystems EngineerCommented:
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

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
cuadminAuthor Commented:
Thanks for the advice - planning on moving the DB to MS SQL. Thanks guys!
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
ASP

From novice to tech pro — start learning today.