scooter126ca
asked on
Performance problems: SQL Server 2000 + ODBC + MS Access 2002
Hi all,
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.
Any ideas?
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.
Any ideas?
ya, this sounds like more of an Access/Network problem than SQL Server problem. However, an Access front-end against SQL Server isn't very efficient. Run sp_WHO and sp_lock in query analyzer and see if you see any locking/blocking on the sql server.
" Is the file on a network drive? Is that server running out of open file handles or cache?" Since you said you copied the Access frontend out to each workstation without any change, I'm assuming the server that has the access file isn't the problem. You don't have the access database shared off the sql server fight?
" Is the file on a network drive? Is that server running out of open file handles or cache?" Since you said you copied the Access frontend out to each workstation without any change, I'm assuming the server that has the access file isn't the problem. You don't have the access database shared off the sql server fight?
It can also be helpful to run SQL Profiler and have a look at what actually gets sent to the server while it is slow. For example, the Find function in Access will pull records one-by-one, so one search would average 4000 roundtrips.
"would average 4000 roundtrips. "
Is that worse than 2000 one way trips?
Is that worse than 2000 one way trips?
ASKER
Thanks for the answers so far. Some clarifications and questions:
Clarifications:
- The MS Access file is on a network drive. It's on the same server as SQL Server. It's a Small Business Server 2003.
- One thing I forgot to mention: Six of the eight users are using Windows XP Pro and two are on Windows 2K . The problem seems aggravated when the Windows 2K users are on. Could it be an O/S version problem? Would it be something so simple as to put everyone on Window XP?
Questions:
- How can I see if the server is running out of (a) file handles or (b) cache?
- We use the Find function here and there. Is there any way around the recordset "Find" function in Access to avoid all those network round trips?
- I've tried using SQL Profiler and all I get is a bunch of cryptic entries that I can't decypher. Is there a good introduction to configuring and using this beast somewhere?
To Do...
- In the meantime, I'll try sp_WHO and sp_LOCK
Clarifications:
- The MS Access file is on a network drive. It's on the same server as SQL Server. It's a Small Business Server 2003.
- One thing I forgot to mention: Six of the eight users are using Windows XP Pro and two are on Windows 2K . The problem seems aggravated when the Windows 2K users are on. Could it be an O/S version problem? Would it be something so simple as to put everyone on Window XP?
Questions:
- How can I see if the server is running out of (a) file handles or (b) cache?
- We use the Find function here and there. Is there any way around the recordset "Find" function in Access to avoid all those network round trips?
- I've tried using SQL Profiler and all I get is a bunch of cryptic entries that I can't decypher. Is there a good introduction to configuring and using this beast somewhere?
To Do...
- In the meantime, I'll try sp_WHO and sp_LOCK
ahhh, that makes all kinds of difference...SBS2003--are you using all the features? Exchange, AD, etc???
Do all your users have the same version of ODBC/MDAC?
"We use the Find function here and there. Is there any way around the recordset "Find" function in Access to avoid all those network round trips?"
Use stored procs--or dump access...
Using Profiler:
http://www.winnetmag.com/SQLServer/Article/ArticleID/5440/5440.html
Do all your users have the same version of ODBC/MDAC?
"We use the Find function here and there. Is there any way around the recordset "Find" function in Access to avoid all those network round trips?"
Use stored procs--or dump access...
Using Profiler:
http://www.winnetmag.com/SQLServer/Article/ArticleID/5440/5440.html
- How much memory does the server have?
- Have you tried limiting SQL Server's memory to about half of the server's physical RAM? (Enterprise Manager, right-click on the server, properties).
> Is there any way around the recordset "Find" function in Access to avoid all those network round trips? <
Yes. Create a search + search results form, create a stored procedure that does the searching, and tie it all together. Let us know if you need more help with that.
- Have you tried limiting SQL Server's memory to about half of the server's physical RAM? (Enterprise Manager, right-click on the server, properties).
> Is there any way around the recordset "Find" function in Access to avoid all those network round trips? <
Yes. Create a search + search results form, create a stored procedure that does the searching, and tie it all together. Let us know if you need more help with that.
ASKER
Thanks for the latest responses.
Some answers:
> How much memory does the server have? <
The server has 1GB of memory. It's running Exchange and store.exe currently takes 384MB. The page file usage is 1.58GB. There's currently only 35MB of available physical memory left.
> Have you tried limiting SQL Server's memory to about half of the server's physical RAM? (Enterprise Manager, right-click on the server, properties). <
I haven't tried limiting the memory for SQL server. Should I set it to 500MB? Do you think this will speed things up? If so, why?
> ahhh, that makes all kinds of difference...SBS2003--are you using all the features? Exchange, AD, etc???<
I'm not the sysadmin on this box but Exchange is certainly being used (since store.exe takes up so much memory). What's AD?
Some answers:
> How much memory does the server have? <
The server has 1GB of memory. It's running Exchange and store.exe currently takes 384MB. The page file usage is 1.58GB. There's currently only 35MB of available physical memory left.
> Have you tried limiting SQL Server's memory to about half of the server's physical RAM? (Enterprise Manager, right-click on the server, properties). <
I haven't tried limiting the memory for SQL server. Should I set it to 500MB? Do you think this will speed things up? If so, why?
> ahhh, that makes all kinds of difference...SBS2003--are you using all the features? Exchange, AD, etc???<
I'm not the sysadmin on this box but Exchange is certainly being used (since store.exe takes up so much memory). What's AD?
I would limit it to less than 500meg! Exchange is taking almost half of your memory now! You aren't leaving enough memory for the operating system and windows is going to start paging.
Since you're using Exchange, you're using AD (active directory).
Since you're using Exchange, you're using AD (active directory).
ASKER
Additional information. Here are the computers and their versions of SQL Server ODBC:
COMPUTER (OS VERSION) = SQL SERVER ODBC VERSION
--------------------------
Isabelle01 (XP Pro) = 2000.81.9042.00
TC01 (XP Pro) = 2000.81.9042.00
p4-1500 (W2K) = 3.70.11.46
P420000 (XP Pro) = 2000.81.9042.00
42400-2 (XP Pro) = 2000.81.9041.40
melop4 (W2K) = 3.70.11.46
P42400 (XP Pro) = 2000.81.9042.00
JAS (XP Pro) = 2000.81.9041.40
Could these different O/Ss and/or driver versions cause these problems? Anybody have experience with this? If so, how would I upgrade all the drivers?
COMPUTER (OS VERSION) = SQL SERVER ODBC VERSION
--------------------------
Isabelle01 (XP Pro) = 2000.81.9042.00
TC01 (XP Pro) = 2000.81.9042.00
p4-1500 (W2K) = 3.70.11.46
P420000 (XP Pro) = 2000.81.9042.00
42400-2 (XP Pro) = 2000.81.9041.40
melop4 (W2K) = 3.70.11.46
P42400 (XP Pro) = 2000.81.9042.00
JAS (XP Pro) = 2000.81.9041.40
Could these different O/Ss and/or driver versions cause these problems? Anybody have experience with this? If so, how would I upgrade all the drivers?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help.
The employees weren't really keeping track of what they were doing with the DB when the problems occurred. I've since noticed that some of them were creating their own queries without consulting me and probably made some multi-table queries with sorts and filters on columns for which we had not prepared indexes. This could explain sudden extreme slowdowns.
We've set the SQL Server priority to "boosted" and things seem stable now.
The employees weren't really keeping track of what they were doing with the DB when the problems occurred. I've since noticed that some of them were creating their own queries without consulting me and probably made some multi-table queries with sorts and filters on columns for which we had not prepared indexes. This could explain sudden extreme slowdowns.
We've set the SQL Server priority to "boosted" and things seem stable now.
Can you simulate what the users are doing from native SQL Server sessions? That would reveal if the problem indeed lay with your SQL tables