SQL Service Pack with MS Access
-
"As you can see there are no service packs installed for SQL Server. If I add a service pack the CPU usage goes up to 100% and stays there for quite a bit of time. Memory is fine. We have approx. 200 employees and at any given time there are 500 -550 processes. (I run SELECT COUNT(spid) FROM sysprocesses WHERE dbid > 6). "
we had a problem like this a few weeks back - hit a certain number of access clients and *BANG* lock up city - for `quite a bit of time`
problem solved after upping the number of worker threads available on the sql server (and - after some time of wondering why it wasnt working - realising the number used wont changed till you restart the sql service)
martin
p.s.
for those interested, heres what we _think_ is happening
Access can hold a couple of different types of locks on connections to SQL server - a sleeping type and a runnable type if I remember from the top of my head. When I say connection type, im taking about the `Status` of a process as displayed in Enterprise Manager -> management -> current Activity -> Process Info so do excuse me using the terms interchangably.
Once we hit a certain number of access clients connecting to the server (we had a couple of hundred all logging in at the same time) the server just locked up - for around 15-20 minutes. All access clients ended up with ODBC time outs and even enterprise manager and query analyser couldnt get in. The server itself kept running fine, just the sql service playing games. All this was repeatable to get the same effect.
After about 15-20ish minutes things would start working again.
Now if I remember rightly its about every 15-20 minutes that sql server flushes out any old broken connections that have been dropped? Coincidence?
I wrote a little access app that blatted the sql server with as many connections as it possibly could to see if I could overload it - they were all sleeping connections - and it took them all fine. My current state of play is I think its the number of 'runnable' processes/connections that was causing the problem (maybe sql server cant do thread sharing with that type of connection? Ive not looked that up yet).
The high connection count seem to tie in with access reading records on an `as needed basis`, which isnt very clear so....
....for example, we have a couple of combo boxes in the access client with large recordsets as their sources. When our access form first loads, enterprise manager shows a couple of runnable connections in current activity/process info. If we open the combo boxes up, scroll to the end of them to make access populate them completely, suprise suprise a couple of runnable connections in enterprise manager drop!
So data seems to populate, in a combo box as an example, (to the point of the selected row in the combo box if the record set is above a certain size) as combo boxes are browsed for the first time - not when they are first loaded.
We managed to get our exact same number of clients as would crash the system all to load in and work by loading in a staggered manner - loading and using a few at a time, then a few more, etc. By the time the first few were loaded and were being used they will have dropped some of their connections as their recordsets fully populated, creating enough free processes that there were enough for the rest of the clients to get in.
If all that does hold true, it may be something extra for access developers to think about - how access holds data connections to your forms, prepopulation of data and how that might load the server. As I said earlier, upping the threads (and restarting the service) fixed the problem for us just fine.
Anyone know how to force access to pre-populate a combo box automatically on load without opening it and scrolling to the bottom by hand?
-
Martin, Thank you for your response! I never even thought of upping the processor worker threads.
There might be hope yet!
As to the question for the combo box... Is it a bound combo or unbound? I do know the best method for Access/SQL is to use unbound fields.
I cannot remember where I obtained the link to the following website (might have been from here)...but it had a couple of good hints and tips:
http://www.sql-server-performance.com/access.asp
Christi
-
Ill tell you this for free - im not moving my combo boxes from bound to unbound
good luck with the threads, id be interested to know if it works!
many thanks
martin
-
A few comments about this wide-ranging topic:
1) About http://www.sql-server-performance.com/access.asp This article is very old and has many very wrong statements about Access performance, and they are especially wrong as it pertains to ADPs, and versions after Ac2000. Note that none of the statements are supported. Also note that the author starts out with derogatory statements about Access, and appears to have a negative bias from the start. Don't believe any of it until you have tested it yourself. It's too difficult to go into detail here, but steer clear of advice from that article.
2) Martin's database uses ODBC, which generates far more connections than an ADP. An ADP usually re-uses 3 connections per User. ODBC is reported to generate one or more connections per form and per combo box. It would appear to be extremely wasteful of server resources. However, I have not verified this myself.
3) The main problem with Access is the horrible Access documentation from Microsoft, especially regarding ADPs. In particular, I have yet to see any MS documentation that even suggests adding timestamps to large or frequently updated tables.
My comment about bad developers was referring to novice programmers who whip up an Access application without knowing anything about databases or programming. It's amazing to me that these apps work most of the time.
4) SP4 has a new logging feature that may help you to identify your bottleneck. The article is very useful even if you don't use SP4. See http://support.microsoft.com/?kbid=897284
5) IMO, the comment about unbound controls being good for Access development is not correct in most cases. Bound controls are fine for most purposes. And, for the most part, Access has the best bound controls in the business. That especially includes combo boxes. But sometimes an unbound control fits the need better.
6) Martin, if you want to populate your combo boxes, try using the Dropdown method with painting turned off. I have not tried it, but it should work. FYI, Access sends the complete query to SQL Server on the first dropdown, but then "appears" to asynchronously populate the combo from the retrieved recordset data. Profiling shows that the query is only issued on the first dropdown. At least in my ADP projects.
7) Remi's notes about timestamps on Views were very interesting. However, MS documentation implies that Access 2002 and above can only edit data in the "most-many" table of a view (i.e. only in the Unique table, not in the lookup (foreign key) tables, etc.). Remi's note seems to show that this is wrong. It seems to me that the entries for the UniqueTable and the ResyncCommand for the view should be also be of critical importance here. But it seems you already have that under control.
-
7) Remi's notes about timestamps on Views were very interesting. However, MS documentation implies that Access 2002 and above can only edit data in the "most-many" table of a view (i.e. only in the Unique table, not in the lookup (foreign key) tables, etc.). Remi's note seems to show that this is wrong. It seems to me that the entries for the UniqueTable and the ResyncCommand for the view should be also be of critical importance here. But it seems you already have that under control.
I wouldn't take my word in this subject. I'm in no way an expert in that domain. I just did a single test (access 2k and sql server 2k) that seems to suggest it an work. Further test must be ran to be sure it's applicable in most/all cases.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply