Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Performance problems:  SQL Server 2000 + ODBC + MS Access 2002

Posted on 2004-08-13
13
Medium Priority
?
789 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:scooter126ca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 5

Expert Comment

by:hkamal
ID: 11794223
Have you checked if the problem lies at the Access side or the SQL Server side? I would guess the former since SQL is very efficient with small tables (eg 8000 rows). Is the file on a network drive? Is that server running out of open file handles or cache?
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
0
 
LVL 34

Expert Comment

by:arbert
ID: 11795251
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?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11795471
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:arbert
ID: 11795561
"would average 4000 roundtrips. "

Is that worse than 2000 one way trips?
0
 

Author Comment

by:scooter126ca
ID: 11796030
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

0
 
LVL 34

Expert Comment

by:arbert
ID: 11796155
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
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11796344
- 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.
0
 

Author Comment

by:scooter126ca
ID: 11810738
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?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11812198
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).
0
 

Author Comment

by:scooter126ca
ID: 11812872
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?
0
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 750 total points
ID: 11834013
No, the versions should be fine (although I would recommend downloading MDAC 2.8 and installing it on all machines).

Limiting SQL Server to 500MB should greatly improve performance. Beyond that, seriously consider moving SQL Server to a seperate machine.
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 750 total points
ID: 11838012
I agree--limit the memory, but like I said above, I still think 500meg is way too much if you only have 1gig of memory and you're running exchange on the same box.

"Beyond that, seriously consider moving SQL Server to a seperate machine."

I would also suggest the same thing, but I know the licensing of SBS2003 doesn't allow it.....
0
 

Author Comment

by:scooter126ca
ID: 11882510
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question