MSAccess Database slow between 2012 Hyper-V virtual machines.

DClayden
DClayden used Ask the Experts™
on
I currently have a problem with an MS Access front end / backend databae between two servers running on a Hyper-V 2012 core server.

The database opens quickly but browsing the data running queries eventually caused Access to crash "Not Responding".

I know neither of the servers are at fault as if I acces the same backend data on a physical server it works as expected and if I use a physical PC or Server to run the front end with the backend virtual this works also.

The problem only exists between 2 virtual Hyper-V servers. All other applications work fine on the virtual machines and network speeds are stable with data copy speeds around 2.6Gb/s.

It appears only network related when both source and destination network adapters are virtual.

Any thoughts??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
Check the Memory size allocated for the Virtual Machine.

Author

Commented:
There are no issues with memory - neither are using anywhere near the maximum memory usage. Also the problem on exists virtual to virtual machine. Virtual to Physical or Physical to Virtual works as expected.
Do you create and maintain a link to the backend database when the front end starts so the lock file doesn't get constantly created and deleted?

Here's our paper on the topic: http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

This is one of the recommendations from our Total Access Analyzer product which finds lots of other performance and design tips: http://www.fmsinc.com/MicrosoftAccess/Documentation/ErrorsList.html
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<Any thoughts??>>

 What version of Access?  Jet has a "bug" that exists when running on multi-core CPUs under a 64 bit OS.

 On older versions, you can try doing:

 ine.SetOption "MaxBuffers", 65535

 and see if that speeds it up.

  If A2010, make sure you apply this hot fix:

http://support.microsoft.com/kb/2726928

 When you set maxbuffers to 65535.

Jim.

Author

Commented:
The version of Access is 2007 running a a 32bit OS. What I have noticed is that if I set the MSAccess.exe process affinity to use 1 CPU it performs as expected.

I am not the develpoer of the database so unsure how its been created only support the OS and network around this.

Where would I apply the MaxBuffers?

Author

Commented:
I made the change in the registry as per this article. And it appears to be working correctly now. This would explain why when i set the process to only use one CPU it worked correctly.

Thanks JDettman!

http://osskb.host4kb.com/article/AA-01449/0/How-to-view-or-change-the-Jet-Access-2000-2002-2003-or-ACE-Access-2007-2010-MaxBufferSize.html
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Part of my comment got chopped.

You can do it with the registry (which is then system wide), or on a case by case basis in the app doing:

dbEngine.SetOption "MaxBuffers", 65535

at startup.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial