Access on Terminal Server - CPU High Usage Problems

[NosNix]

A client has a W2K SP4 Server 2.4 GHz, 2 GB Ram where they run a management app, built years ago, with Ms Access 2003 (Office recently updated to SP3, MsJet and MDAC have also been updated recently on the server.

There is a growing problem, over quite some time, of Access giving poor performance, slow response and consuming disproportionate amounts of CPU%, to the point where today the CPU usage of only 6 msaccess.exe processes running was over 75%.
The memory usage runs at 35 MB/Access session.

The project code is not optimal, I have seen that there are many cases of non closed objects, a possible memory leak perhaps.
It is possible that the code references something inappropriate, not up to date.
Up to now the firm has always used one single FrontEnd for All Users rather than a distributed FE.
The current FE is rather bloated for the size of the app.
The original programmer should be visiting in the future but it seems clear that something is amiss.

Question: Any suggestions as to where the main problem might lie ?
Apart from being related to Access, using perfmon this is clear.

Thank you

[zmbd]

- Having one front end used by multiple people is certainly part of your problem and may very well be leading to the huge overhead and open objects etc... One very short term solution will be to distribute the front end to each client. You can handle this a variety of ways, I have used a small batch file as well as a more complex VBA solution; however, the easiest by far was to simply send an email out to my users and said, copy this file to your "my documents" and run it from there.

- Next issue may be the underlying table design if it was not well normalized ( > Database Normalization and Table Structures. ) to start with; however, you didn't provide that information.

- Unfortunately, reviewing your entire application is a tad outside of what we can do here, instead your original programmer should be able to help more with this than we really can with as little information as you've given in your post. HOWEVER, should you get stuck with something specific please post back (more than likely with a new thread)

Best of Luck!

[NosNix]

Thanks for your quick reply.
The question of distributing the FE is already being examined although this may not be the most critical part of the cause.
(Since this is a growing problem but has permitted the users to work up to now in an apparently acceptable fashion for most of the time and this for a number of years).
BTW although distributing the FE is a standard and recognized practice I have read somewhere that there is some controversy about the impact of single FEs on the Server compared to employing an FE/User on the Server.
Do you have any comments or notes on this ?

Regarding the DB Structure I have not as yet delved into it.
Up to now I have only commented on the lack of closure of certain variables and perhaps some orphaned objects and the size of the FE.

After the test I should have a better basis on which to work.
I was wondering however if you had any information on Access and TS, any particular useful tuning parameters and configs, Jet/DAO/MDAC/Registry etc.

Thank you

[zmbd]

BTW although distributing the FE is a standard and recognized practice I have read somewhere that there is some controversy about the impact of single FEs on the Server compared to employing an FE/User on the Server.
Do you have any comments or notes on this ?

Personally, the controversy is a "Red Herring."
That's like saying that everyone must open the web browser directly at the webserver because there will be some impact on the site - which many use ASP or something similar to deliver dynamic content, driven by a database.


I'll let microsoft speak for itself too: MS Access: To split or not to split an Access database


You have to tell me what "TS" means. Afraid I'm a Chemist and I think of "Total Solids," "Test Standard," "True Stoppered," or something else along those lines
As for tunning... normally not an issue; however, some general things: Performance Tips to Speed Up Your Access 2007 Database
and an older one: Ten Tips for Microsoft Office (Office XP)

[NosNix]

Hello,

I apologize for the delay in responding. I spent some time rereading your links, I had in fact read two of them some time ago. The Performance Tips is in fact interesting.
Some of the tips by Ms are in relation to net access, however if one has both FE and BE on the Terminal Server (TS), a part of the network activity is somewhat diminished.
This is by the way the case in hand.

Rebooting the Server often ease the problem for a while and some even schedule reboots to eliminate locks etc quite regularly. Tips regarding the Jet DAO ODBC etc. parameters would be useful. Regarding TS tuning, B Madden MVP has an interesting paper on this.

Thank you again for the follow-up.