[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3796
  • Last Modified:

Access database slow after move to Server 2008 R2

We have an access database around 50MB in size, which worked fine when hosted on Server 2003.  We moved it to SBS2011 (Server 2008 R2) and now it's fine for a single user, but as soon as anyone else uses it as the same time, it becomes slow, taking at least 5 seconds to open a simply record which previously happened almost instantaneously.

As a test the data was moved to a Server 2003 virtual machine, and the problem was resolved.  However we would like to host the data on the SBS2011 box natively, which I would expect to respond more  quickly, especially from Windows 7 clients (it certainly is very fast for regular file access).

How can we improve the performance of the database while hosting it on Server 2008 R2 (SBS2011)?  The database has been compacted already.

Thanks in advance.
0
David Haycox
Asked:
David Haycox
  • 4
  • 3
4 Solutions
 
TobiasCommented:
Dear,

What you could do is :

1) Separate the front-end and the table, and copy for example at logon the lastest version from the server.

2) Re-linked all the tables were to be the UNC path and NOT a path relative to the mapped drive (ie \\server\share\database.mdb and NOT T:\database.mdb)

Regards
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Is this 64 bit and a multi-core machine?  If so, try adding the following to the application at startup:

DBEngine.SetOption dbMaxBufferSize, 65536    

 and see if it resolves the issue.

 There have been a number of issues with 2008 and Access.  One is on a multi-core machine, JET gets stuck in a loop when doing garbage collection on it's cache.  The above setting avoids most of that.

 Also early on, there were issues with SMB 2.0 in a mixed environment.  Some have found that turning it off and going back to SMB 1.0 helped.  

 But try the maxbuffer setting first as it's easy to do.

Jim.
0
 
David HaycoxAuthor Commented:
It is indeed a 64-bit multi-core machine.  We've just switched to UNC paths and are awaiting feedback from the users.

We'll try the buffer setting shortly.

There are still two XP clients, the rest are Windows 7 though.  If it needs be done then we can switch off SMB 2.0 but of course this will reduce performance for regular file shares so I'd prefer to avoid it if possible.
0
Independent Software Vendors: 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!

 
David HaycoxAuthor Commented:
MadShiva: the front-end and the table were already separate.  We've had the tables re-linked to a UNC path, but that hasn't made a difference.

JDettman: We just tried the MaxBuffer setting but it made no difference.  I'm about to disabled SMB 2.0.

The users report that the first person to log on seems to run with decent performance, at least to start off with.  Later on it can switch around, but there's usually one person working ok with the rest looking at the blue circle of boredom.  Could this be something to do with the way Server 2008 deals with locks on the files?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The users report that the first person to log on seems to run with decent performance, at least to start off with.  Later on it can switch around, but there's usually one person working ok with the rest looking at the blue circle of boredom.  Could this be something to do with the way Server 2008 deals with locks on the files? >>

 You also in addition to testing SMB2 off want to try OPLOCKs off.  Oplocks allows client side caching of files, which is something JET doesn't need as it maintains it's own cache.

 What happens is when someone us using a file and has an exclusve Oplock, it first needs to be broken before other users are allowed in the file.

 If your app is the type of app that connects and disconnects from the backend, that could be the cause of the problem as it comes into play only when you connect to a DB.  Here's more on it:

Configuring opportunistic locking in Windows
http://support.microsoft.com/kb/296264

 and I just learned something as well; it's on no matter what with SMB2, which may explain why some in the past got good results swtiching it off.

Jim.
0
 
David HaycoxAuthor Commented:
Found a solution, but only for Windows 7 clients.

It looks like the registry keys listed in the “Turn off the sharing violation notification delay” section of this article had been added, so I removed them:
http://support.microsoft.com/kb/889588

What we were seeing is all users having multiple (around 15-20) locks on each database file; now only the XP users show this many locks, the Windows 7 users show zero.

It’s possible that other changes may have been made to the XP machines which are causing the problem with file locks – we could try this by using a clean build of XP as a test, but simply moving to Windows 7 fixes it also.

Also it looks like an attempt had been made to disable SMB 2.0, but I doubt this had worked as SMB 2.0 was almost certainly working the entire time (users noticed the difference in performance immediately when the shared files were moved from the Server 2003 VM to SBS2011). I removed the registry key in any case, so I have not tried disabling SMB 2.0 - but if possible it's much better to keep it, so that's what we've done.

Thanks for the advice, it was definitely on the right track.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
That setting deals with OPLOCKS.  If you can't get them off because of SMB 2.0. modify the application to open a connection to the back end and keep it open for the life of the app.

This can be as simple as opening a recordset on a table in the BE, a from bound to a table, etc.

Jim.
0
 
David HaycoxAuthor Commented:
Good advice from experts, pointed in the right direction but ultimately solved it myself.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now