Performance problems: SQL Server 2000 + ODBC + MS Access 2002
Posted on 2004-08-13
I've got an SQL Server database with a dozen tables and a maximum of 8000 records in each table. The front end is MS Access 2002 with all tables linked to the server via ODBC. There are up to 8 users.
We've noticed that the database becomes EXTREMELY slow when three or more people start using the database. For instance, opening a form which takes about 1 second when you're alone takes 45 seconds when there are several people on the database. While we're waiting, the bottom of the form shows "Calculating...".
I've looked at the server performance monitor when we're having the performance problems and the CPU, Pages/Sec and Average disk queue length are all around 5% with very brief peaks of 100%--so the server is NOT very busy.
The front-end MS Access file is a shared file on the server. I've tried copying the file 8 times with different names so that everyone is using their own copy of MS Access--there's NO increase in performance. I've also tried converting to Access 2000 without any performance gain.