Solved

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

Posted on 2004-08-13
13
744 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 250 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 250 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now