We have a client running a fairly small (2GB +/- ) database application. A number of users hit it internally, a number from the web, all through browser. Running Windows Server 2003 SP2 and SQL Server 2000 Enterprise Edition (8.00.760). From time to time users experience significant slowness in application response. During today's issue, the Application Event Viewer had a lot of 17052 Disk Information events. No information in the event but a search on the web indicates this is likely due to concurrent queries. That data indicates that MSDE and Personal Edition had an 8 concurrent query optimization limit that wasn't adjustable and the recommended solution was to upgrade to Standard or Enterprise. Well we're already at Enterprise and I'm attempting to find out how to determine if a limit is in place and, if so, how to increase it. Absent that, I'm looking for suggestions on how to alleviate this situation in the future if it is what is indeed causing the latency. In this case it is not unusual to have 10-20 users in data entry mode where they will be searching the database for accounts or individual record information for updates, additions, etc.
System has 4GB of RAM and plenty of disk space.
Transaction log was quite large (5GB which has been backed up and shrunk) but not sure how that would impact performance intermittently in any event.
Any thoughts or help welcome.