MS Access runs slow as a 2008 Terminal Server RemoteApp
I have upgraded our Windows 2000 Terminal Server to Windows Server 2008 with Terminal Server role added. In the 2000 environment, users logged into the server and on their 2000 desktop was an Access shortcut to a frontend file that was linked to the backend database.
The 2008 server is configured the same as the 2000 server as far as user accounts, profiles, home folders, etc. In the 2008 environment, I created a separate RemoteApp for each user, each had an argument pointing to the frontend file located in their home folder (on the 2008 server). I then saved their RemotApp as an RDP and copied it to their local desktop. This seems to be working fine except that certain functions are running extremely slow on the database. These function seem to be most prevetant when applying filters. Specifically (but not limited to), when a user clicks a drop-down box. Access will indicate "Not Responding" and could take minutes until the dropdown list is displayed. Since I still have access to the 2000 server, I compared the functionality on that and these functions are not a problem...all runs at an acceptable speed on the 2000 server.
The same latency problems occurs on the 2008 server when I log in as a user, via RDP, and run the database frontend file from the user's server desktop, bypassing the RemoteApp RDP on the local desktop.(the same way we ran the database on the 2000 server).
I am wondering if I installed Access incorrectly or configured the remote access incorrectly. The 2008 server is 64 bit and the Access program was installed in the Program Files (x86) folder.
I read (after the fact) that an option for installing applications on 2008 terminal server is to run the command line: "change user /install" prior to installing the application. I have also read that this command is executed automatically without the need to run the command line command. Could this be part of my problem?
It seems to me that the problem is not a database design issue as much as a 2008 connectivity issue.
Any help with this would be greatly appreciated.
Thanks!
The 2008 server is configured the same as the 2000 server as far as user accounts, profiles, home folders, etc. In the 2008 environment, I created a separate RemoteApp for each user, each had an argument pointing to the frontend file located in their home folder (on the 2008 server). I then saved their RemotApp as an RDP and copied it to their local desktop. This seems to be working fine except that certain functions are running extremely slow on the database. These function seem to be most prevetant when applying filters. Specifically (but not limited to), when a user clicks a drop-down box. Access will indicate "Not Responding" and could take minutes until the dropdown list is displayed. Since I still have access to the 2000 server, I compared the functionality on that and these functions are not a problem...all runs at an acceptable speed on the 2000 server.
The same latency problems occurs on the 2008 server when I log in as a user, via RDP, and run the database frontend file from the user's server desktop, bypassing the RemoteApp RDP on the local desktop.(the same way we ran the database on the 2000 server).
I am wondering if I installed Access incorrectly or configured the remote access incorrectly. The 2008 server is 64 bit and the Access program was installed in the Program Files (x86) folder.
I read (after the fact) that an option for installing applications on 2008 terminal server is to run the command line: "change user /install" prior to installing the application. I have also read that this command is executed automatically without the need to run the command line command. Could this be part of my problem?
It seems to me that the problem is not a database design issue as much as a 2008 connectivity issue.
Any help with this would be greatly appreciated.
Thanks!
Here's the link to the one thread I was thinking of:
https://www.experts-exchange.com/questions/25020557/Microsoft-Access-2007-slow-on-Server-2008-Terminal-Server.html
There have been others.
JimD.
https://www.experts-exchange.com/questions/25020557/Microsoft-Access-2007-slow-on-Server-2008-Terminal-Server.html
There have been others.
JimD.
BTW, I haven't worked with 2008 yet (have one client that's getting 2008 server this week), but in general, I would check that the setting for the server is for "Application" performance and not background as a start.
Also double check that your FE is in a compiled state and that the BE has recently been compacted.
That's all the simple stuff to check and quick, and all should be done anyway.
JimD.
Also double check that your FE is in a compiled state and that the BE has recently been compacted.
That's all the simple stuff to check and quick, and all should be done anyway.
JimD.
ASKER
Hi Jim,
Thanks for the input. I will check out the links (I might have already checked them out). This does seem like a hard nut to crack because of its uniqueness. I found these links also:
http://support.microsoft.com/kb/968991
http://www.mskbarticles.com/index.php?kb=974609
I figured it was not connectivity or the installation, but I had to put it out there. I am currently working on a virtual replication of the client's site to see if the various fixes will work, of course it is not happening fast enough for the client.
BTW, can you explain how to check if the server setting is for "Application"?
Thanks,
Bill
Thanks for the input. I will check out the links (I might have already checked them out). This does seem like a hard nut to crack because of its uniqueness. I found these links also:
http://support.microsoft.com/kb/968991
http://www.mskbarticles.com/index.php?kb=974609
I figured it was not connectivity or the installation, but I had to put it out there. I am currently working on a virtual replication of the client's site to see if the various fixes will work, of course it is not happening fast enough for the client.
BTW, can you explain how to check if the server setting is for "Application"?
Thanks,
Bill
ASKER
The Application server role has not been added. Do you think it needs to be added? From my research prior to setting up the 2008R2 server and using RemoteApps, Application server was not mentioned.
Bill,
Sorry. Doesn't look like that setting exists in the way I'm thinking. It used to be under the Computer properties and you would select either "Application Mode" (Foreground) or "Background Mode". The first being for running a TS server and/or many applications. The other was for running a file server, etc. This set basic tuning parameters in the OS.
Looks like a lot has changed. I really need to get up to speed. I did find this:
http://www.microsoft.com/whdc/system/sysperf/Perf_tun_srv.mspx
Which may be helpful (there is a section in there on tuning for Terminal services), but it is very basic and does not specifically address your question.
I'll poke around some more on this. Sorry I don't have anything more for you on this at the moment.
Jim.
Sorry. Doesn't look like that setting exists in the way I'm thinking. It used to be under the Computer properties and you would select either "Application Mode" (Foreground) or "Background Mode". The first being for running a TS server and/or many applications. The other was for running a file server, etc. This set basic tuning parameters in the OS.
Looks like a lot has changed. I really need to get up to speed. I did find this:
http://www.microsoft.com/whdc/system/sysperf/Perf_tun_srv.mspx
Which may be helpful (there is a section in there on tuning for Terminal services), but it is very basic and does not specifically address your question.
I'll poke around some more on this. Sorry I don't have anything more for you on this at the moment.
Jim.
ASKER
You are right, there are a lot of differences in 2008. I think it is all good though, it's just a matter of aquiring experience with it. I wish I could get paid for all the hours, day, weeks I spend doing R&D (I'm self employed).
I realized that by simply looking at the list of added "roles", I could easily see that Applicatin Server is not added.
I found a MS Hotfix, KB968991, thanks you your previous post to another EE post. My first task is to test this hot fix. It seems, at least at this point, that the issue is a performance (compatability) issue with copying an Access file from a x32 machine onto a x64 machine. The MS hot fix is supposed to address this.
I think if all else fails, I may simply create two new, blank databases on the current server (x64) and import all of the tables in to one of the new databases (backend) and the forms into the other (frontend). It seems that doing this would bypass the x32-to-x64 issue because the database would be actually created on the x64 machine.
I'll keep you updated, and thanks for your interest in this issue.
Bill
I realized that by simply looking at the list of added "roles", I could easily see that Applicatin Server is not added.
I found a MS Hotfix, KB968991, thanks you your previous post to another EE post. My first task is to test this hot fix. It seems, at least at this point, that the issue is a performance (compatability) issue with copying an Access file from a x32 machine onto a x64 machine. The MS hot fix is supposed to address this.
I think if all else fails, I may simply create two new, blank databases on the current server (x64) and import all of the tables in to one of the new databases (backend) and the forms into the other (frontend). It seems that doing this would bypass the x32-to-x64 issue because the database would be actually created on the x64 machine.
I'll keep you updated, and thanks for your interest in this issue.
Bill
ASKER
I still have not resolved this. I am in contact with Microsoft, but I don't have much hope. The server support sent me to Access support. Access support says it must be my code that makes the program run slow but I can run the same database on a WIndows Server 2008 Standard x32 and it works fine. Very frustrating.
If there is no more input in the next couple of days, I am going to close this. I will probably try another question at some point.
If there is no more input in the next couple of days, I am going to close this. I will probably try another question at some point.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<This was a very long and pinful process, but it is resolved, although not in the way I thought it would be.>>
Thanks for posting all that back.
<<This was a very long and pinful process, but it is resolved, although not in the way I thought it would be.>>
That seems to be the underlying theme; 64 bit is a problem for whatever reason. In some cases, it has been 32 bit ODBC drivers, in others, no one can put their finger on it. Looks like the 64 to 32 bit translation has some kinks. Not unexpected to be sure. Had more or less the same thing with Win 95 and 32 vs 16 bit.
<< They could not give me a good answer to the issue. >>
Probably won't be one for a while either :(
JimD.
Thanks for posting all that back.
<<This was a very long and pinful process, but it is resolved, although not in the way I thought it would be.>>
That seems to be the underlying theme; 64 bit is a problem for whatever reason. In some cases, it has been 32 bit ODBC drivers, in others, no one can put their finger on it. Looks like the 64 to 32 bit translation has some kinks. Not unexpected to be sure. Had more or less the same thing with Win 95 and 32 vs 16 bit.
<< They could not give me a good answer to the issue. >>
Probably won't be one for a while either :(
JimD.
ASKER
In my research, I found many posts regarding the ODBC diver on x64, and it seems that the default x64 ODBC driver is located in the folder C:\Windows\System32\odbcad
I never get over how Microsoft can come up with their new products, of which we, as consumers, have little choice to use, and yet the new product is not fully compatable with their own legacy products (even Access 2007). And in many cases, such as mine, there is no fix except to purchase another M$ product that will work. (just a little venting here...)
Thanks for your interest in my issue and your input.
Bill
I never get over how Microsoft can come up with their new products, of which we, as consumers, have little choice to use, and yet the new product is not fully compatable with their own legacy products (even Access 2007). And in many cases, such as mine, there is no fix except to purchase another M$ product that will work. (just a little venting here...)
Thanks for your interest in my issue and your input.
Bill
I have had the same problem. There seems to be 2 issues, 1) Problem with multi-core processors, 2) Access tables require indexing to run effectively in R2.
Microsoft have responded as follows following sending a dump to them.... which has fixed the hanging:
ACE (Jet) is internally using 2 variables:
+0x090 m_workingLimit : 0xe00 (3584 pages, 14.3MB)
+0x094 m_pagesInUse : 0xeb9 (3769 pages, 15.0MB)
When ACE (Jet) tries to allocate page, it runs into the following routine:
msjet40!System::AllocatePa
if (m_pagesInUse > m_workingLimit + 100)
Sleep(50);
This call to the Sleep method is here to flush memory, and try to garbage collect all data pages. In your dump, you can see that the m_pagesInUse (3769) is higher than the m_workingLimit (3584), so we’ll wait for a long time to flush all the data pages.
In multi-core computers, the ACE (Jet) allocate page by one core each time. Other cores are blocked in this time, hence Access need sleep more time in multi-core environment. We are really sorry for the inconvenience it may have caused to you.
To avoid the sleep (50) occurs, we can try to increase the MaxBufferSize to a bigger value. MaxBufferSize can take a value from 0 to 65536 pages, and the more data you retrieve, the higher MaxBufferSize should be. By default "MaxBufferSize" should be set to 0. The zero value means that the MaxBufferSize depends of the total amount of RAM. The formula to get MaxBufferSize is ((RAM in KB - 12288)/4 + 512 KB). However, for this formula the input RAM value is limited to 65536 KB (i.e. 64 MB), which means that MaxBufferSize is limited to 13824 KB (3456 pages).
Note: The unit for the MaxBufferSize is one page (4KB)
To fix the problem, let us perform the following steps.
1] click on "Start" and then click on "Run"
2] type "regedit" in the run box and hit enter, this opens up the registry editor
3] Browse to HKEY_LOCAL_MACHINE\SOFTWAR
4] Click on decimal
5] Change the value data to "16384" and click on "OK"
6] Close the registry editor
7] Open the file again and test the issue.
Microsoft have responded as follows following sending a dump to them.... which has fixed the hanging:
ACE (Jet) is internally using 2 variables:
+0x090 m_workingLimit : 0xe00 (3584 pages, 14.3MB)
+0x094 m_pagesInUse : 0xeb9 (3769 pages, 15.0MB)
When ACE (Jet) tries to allocate page, it runs into the following routine:
msjet40!System::AllocatePa
if (m_pagesInUse > m_workingLimit + 100)
Sleep(50);
This call to the Sleep method is here to flush memory, and try to garbage collect all data pages. In your dump, you can see that the m_pagesInUse (3769) is higher than the m_workingLimit (3584), so we’ll wait for a long time to flush all the data pages.
In multi-core computers, the ACE (Jet) allocate page by one core each time. Other cores are blocked in this time, hence Access need sleep more time in multi-core environment. We are really sorry for the inconvenience it may have caused to you.
To avoid the sleep (50) occurs, we can try to increase the MaxBufferSize to a bigger value. MaxBufferSize can take a value from 0 to 65536 pages, and the more data you retrieve, the higher MaxBufferSize should be. By default "MaxBufferSize" should be set to 0. The zero value means that the MaxBufferSize depends of the total amount of RAM. The formula to get MaxBufferSize is ((RAM in KB - 12288)/4 + 512 KB). However, for this formula the input RAM value is limited to 65536 KB (i.e. 64 MB), which means that MaxBufferSize is limited to 13824 KB (3456 pages).
Note: The unit for the MaxBufferSize is one page (4KB)
To fix the problem, let us perform the following steps.
1] click on "Start" and then click on "Run"
2] type "regedit" in the run box and hit enter, this opens up the registry editor
3] Browse to HKEY_LOCAL_MACHINE\SOFTWAR
4] Click on decimal
5] Change the value data to "16384" and click on "OK"
6] Close the registry editor
7] Open the file again and test the issue.
PrimeNZ,
Thanks for posting that....good info!
JimD.
Thanks for posting that....good info!
JimD.
ASKER
Thanks for the information. I will fire up the original R2 server and test your solution. This is a great help for the future.
Thanks again!
Bill
Thanks again!
Bill
ASKER
PrimeNZ: I tried the solution you posted, but I am using Access 2003 and there is no "Access Connectivity Engine\" key in the Registry. I only have Microsoft\Office\11\Access
Is there a comperable solution for Access 2003?
Thanks
Is there a comperable solution for Access 2003?
Thanks
I'm not sure as we haven't used Access 2003, but do a search in the registry (under HKEY_LOCAL_MACHINE) for "MaxBufferSize", and you should find a setting for Access 2003.
GREAT
we have found solution
With Server 2008 64 bits multicore and MsAccess 2003 , we have put maxbuffersize on Msjet 4.0 to the max and it's fine.
Many thanks to contributors
Patrick France
we have found solution
With Server 2008 64 bits multicore and MsAccess 2003 , we have put maxbuffersize on Msjet 4.0 to the max and it's fine.
Many thanks to contributors
Patrick France
Hello Patrick Germain,
I too have experienced intermittently poor/sluggish performance on very fast multi-core processors on a 64 bit Windows Server 2008 R2 machine running Access 2003. Your post above states that the solution is to change the max buffer size to "the max". Do you mean 65536 or the number referenced above (16384) or does the value vary by RAM size? I find that if I use a value above 30,000 then I also get "System Resources Exceeded" messages so I need to dial the MaxBufferSize down to around 26,000. I wish that I knew how to calculate the correct buffer size.
Also, can anyone say whether the problem goes away if the database has been upgraded to run under Access 2007 or Access 2010? Is it uniquely associated with Access 2003 only? Anyone?
Thank you!
Charlie
I too have experienced intermittently poor/sluggish performance on very fast multi-core processors on a 64 bit Windows Server 2008 R2 machine running Access 2003. Your post above states that the solution is to change the max buffer size to "the max". Do you mean 65536 or the number referenced above (16384) or does the value vary by RAM size? I find that if I use a value above 30,000 then I also get "System Resources Exceeded" messages so I need to dial the MaxBufferSize down to around 26,000. I wish that I knew how to calculate the correct buffer size.
Also, can anyone say whether the problem goes away if the database has been upgraded to run under Access 2007 or Access 2010? Is it uniquely associated with Access 2003 only? Anyone?
Thank you!
Charlie
Hello
I have not calculated buffersize - and I don't get message about system ressource
I did it 3 times
Sorry I have not yet migrated to Access 2010
Bye
Patrick
I have not calculated buffersize - and I don't get message about system ressource
I did it 3 times
Sorry I have not yet migrated to Access 2010
Bye
Patrick
Charlie,
<< Do you mean 65536 or the number referenced above (16384) or does the value vary by RAM size? >>
The max setting is 65536 (it's in pages). 4096 bytes to a page, so that's 16384kb of memory.
<<I find that if I use a value above 30,000 then I also get "System Resources Exceeded" messages so I need to dial the MaxBufferSize down to around 26,000. >>
You bumping into two different things there. As you found out, the "System Resources Exceeded" pertains to the the maxlocks per file setting.
Setting MaxBuffers higher is letting you bump into that. Set MaxBuffers to 65535 and MaxLocksPerFile to 100,000 and see if that flies.
Jim.
<< Do you mean 65536 or the number referenced above (16384) or does the value vary by RAM size? >>
The max setting is 65536 (it's in pages). 4096 bytes to a page, so that's 16384kb of memory.
<<I find that if I use a value above 30,000 then I also get "System Resources Exceeded" messages so I need to dial the MaxBufferSize down to around 26,000. >>
You bumping into two different things there. As you found out, the "System Resources Exceeded" pertains to the the maxlocks per file setting.
Setting MaxBuffers higher is letting you bump into that. Set MaxBuffers to 65535 and MaxLocksPerFile to 100,000 and see if that flies.
Jim.
Most likely not. If you used add/remove programs, that command is issued for you. That command is only used if your going to run a setup directly from the run box or command line. If you did not use add/remove programs, then you might want to go back and repeat the install.
But I still don't think that is the problem because were seeing this type of question (performance with 2008) pop up again and again and again on EE. One posted that there was a hotfix from Microsoft, but the author reported back that in his case, the hot fix did nothing.
But it does indeed seem to be some type of problem with 2008.
<<It seems to me that the problem is not a database design issue as much as a 2008 connectivity issue.>>
It's not connectivity either; remember with RDP only KVM is running over the wire, which is not a ton of data. The app is running local to the server. For the window to say "not responding", it means the server is getting hung up.
I'll find a link to some of those other threads in a minute.
JimD.