DClayden
asked on
MSAccess Database slow between 2012 Hyper-V virtual machines.
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??
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??
Hamed Nasr
Check the Memory size allocated for the Virtual Machine.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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
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
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.
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.