We are having major problems with an ASP application that has a SQL Server 2000 backend. We have started monitoring how long each request to the database is taking, and the results are quite staggering. When I run a reasonably simple query in Enterprise Manager (just something like SELECT * FROM vw_someview) it takes no time at all, perhaps just a split second. However, we can see from our logs that when the same query is being requested via the front end it's taking anything from a few seconds to 90 seconds. Some of the recordsets being returned are actually empty and still taking this long to come back!
This is a reasonably complex system with lots of different things going on at once, and anything from 150 to 350 users on at any one time, so it's very difficult to know what's happening here.
We're suffering serious performance issues with this system and we're trying desperately to work out what's causing them and what we can do to speed things up.
So, I think I have two questions:
- Does anyone have any ideas as to why a query would take much longer to run when called from an ASP page rather than directly in the database.
- Are there any methods I can use to try and monitor what's happening in the database at any one point? Or what can I do to try and enhance the performance of the database?
Any suggestions will be very gratefully received!
Many thanks in advance.