[AccessD] CPU and locks

artful at rogers.com artful at rogers.com
Thu Aug 31 16:10:04 CDT 2006


What I have to offer may or may not be useful. Mostly it is a counter-example. I had about 70 users all hitting a TS box with 2GB of RAM, and the SQL db lived on another box. Users in several cities across North America. Never ONCE a performance problem. In fact, the sickening thing from my point of view: it was quicker to hit that remote box than to hit my local instance of exactly the same DB. We never experienced any collisions or deadlocks (polishing my nails for clever coding ), and we never suffered a performance issue or a deadlock. It simply did not happen, even once in about 4 years. I almost wish that it HAD happened so I would have a problem to investigate, but it never did. 

I ran the resource allocation stuff but it never presented a problem. Using TS, the initial user grabs a chunk of memory and everyone else uses re-entrant code, so the additional mem-grabs were tiny in comparison. Two GB sufficed to serve a potential 70 users (in reality, only maybe 40 were hitting the system at once, given time-zone variations etc.), but the point is that I did not have to throw heavy hardware at this problem, and everything worked beautifully. Admittedly, when 50 users were aboard, the CPU usage went way up, but that is only to be expected. More important, nobody whined about the performance delays.

(Other facts of potential importance: it was an Access ADP app whose every object was driven by a sproc or udf; nothing was dynamic SQL. The server was not awesome, merely a P4 with 2GB of RAM. The OS was w2K TS with appropriate patches. The connections were all quick.We didn't bother with Citrix since TS gave us everything we needed.)

Arthur

----- Original Message ----
From: Steve Capistrant <scapistrant at symphonyinfo.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Thursday, August 31, 2006 4:40:47 PM
Subject: [AccessD] CPU and locks

Dear List,
 
We have an Access 2000 app that has been running for years at a customer's site, provided to 100 users via Citrix profiles.  In daily practice, there are usually 5 to 15 people logged in at any given time.
 
The techies running the server are belly-aching about resource utilization.  They note that CPU utlization per user zips up to 25% or 30% of server capacity when they perform routine actions (like applying filter rules to search on a form).  It drops down to zero after a second or two.   But they say "do the math" -- just 4 users doing stuff is going to completely max out our servers.  
 
We (our development team) are scratching our heads.  This is normal for Access.  It grabs as much resources as it can get to perform data access requests.  And I've never heard of a server blowing up from too many simultaneous requests.  Windows is designed to handle process threading reasonably well, putting things in  order, assigning priorities, reserving some CPU for critical processes, etc.
 
Nothing's really changed over the years.  Is it just their awareness that has changed?  Are these techies just freaking out because they have a new Performance Monitoring tool?  
 
The other observation they make that might cause overuse of CPU is something new to me:  When you look at 
START > Control Panel > Adminstrative Tools > Computer Management > Shared Folders > Open Files....
 
....they note that most of the MS Access item in this view are showing large numbers in the "# of Locks" column.  Ranging from 15 to 42 each.  This is something I can't explain.  It's a standard FE/BE deployment, so an ldb gets generated at both the FE and BE levels.  But what is this huge number of locks?  Stranded ldbs?  Some calculus involving the number of simultaneous users?  I told them I'd explore what this meant..
 
Thank you !
 
Steve Capistrant
Symphony Information Services
scapistrant at symphonyinfo.com
www.symphonyinfo.com
Main: 763-391-7400 ext 801
Toll Free: 888-357-1373 ext 801
Direct:  612-237-0075

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com







More information about the AccessD mailing list