Performance Issues (1 Viewer)

Kayleigh

Member
Local time
Today, 10:40
Joined
Sep 24, 2020
Messages
708
Hi all,

We are experiencing some serious performance issues with a client. They have been using a MS Access application + SQL Server backend. However recently it has run very slow for users, often experiencing delays, freezing and shutting down with no apparent reason.

Running the database locally is usually fine but the issues occur with users connecting over LAN.

There are usually 10-15 concurrent users. We are currently using SQL Express - is this an issue?

Can anyone advise what we can explore to try resolve this (network or software etc)?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 28, 2001
Messages
27,320
You say "recently" so this immediately calls for the question, "What changed? (And when?)" Before doing ANY serious diagnostics or testing of the computers or network devices, see if you can dredge up any system changes to account for this. If someone updated your app in some way with a new version, find out what it did. If someone is running a new query that you didn't have before, find out what it does. If you can tie this to a specific day that happens to be a Wednesday, look at the Windows Update History to see if there was a system patch that day (since Windows patches are released on Tuesday nights. No, ... not kidding.)

When you have a shutdown of your app but Windows itself DOESN'T shut down, you might find some useful information in the event logs. You would need to know the date and time of one of those shutdowns, but if you have that, the search in Event Manager is easy because logs are sorted by date & time and therefore are easy to navigate. Usually, if you have a network timeout, by default that is 30 seconds, so you should not have to look for event logs more than 30 seconds before the failure. Scanning event logs is a passive action that shouldn't disrupt system operations so you should be able to do this on the running system. Both the failing client (front-end) and the server event logs should be checked for events at or just before the time of the failure.

However, if event logs show nothing, you can try a few other things.

For instance, on a machine experiencing a problem, get to the CMD window and try to PING the SQL Server's computer to see if the problem is at the physical or end-to-end layers. Run the test when the app is not running and then run it again when you have one of those freezes. Use of PING might seem to tell you nothing, but it is a pretty good and yet really simple test to eliminate any hardware faults.

If you see order-of-magnitude variations in PING results, you have a network hardware problem. For an in-house LAN, a "normal" PING time should be less than 2 milliseconds even on an old, slow network, and you might see fractional milliseconds on a "hotter" network. But if you see double-digit PING times on a LAN theoretically wired end-to-end, you might have a problem. IF your PING detects a multi-hop situation, the individual hops should EACH be quick. But unless you have to traverse a "gateway" machine, you should not have any hops at all.

When you have these delays and freezing, ... First, do these freezes ever thaw spontanenously? Second, how long does the "freeze" last before the frozen process either shuts down or thaws? You have the shutdown, do you get an error message? If so what does it say?

Windows Task Manager can be a useful tool, particularly the "performance" and "process" tabs. First, on the app side of that connection, when there is a "freeze" does the performance tab show anything running at saturation level (i.e. 100% CPU or 100% disk or 100% network? I doubt you would ever see 100% memory but if you do, it would be significant.) You can run Task Manager on the server side too, but you would expect that to be more complex. Also, you can switch to the "processes" tab and look at the column headers. If you had a "performance" saturation, click in the "processes" column corresponding to what was saturated to see what process is consuming resources. If there is a saturation and you can tie it to a specific task, you have a place on which to concentrate.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:40
Joined
Feb 19, 2013
Messages
16,670
Need to know the basics if only to eliminate them as a possible cause

Does each user have their own copy of the FE? And not sharing one located on the server?

also is the FE a .accdb or .accde?

and when you say ‘connecting over LAN’ - is that a hardwired or wireless connection
 

tvanstiphout

Active member
Local time
Today, 02:40
Joined
Jan 22, 2016
Messages
253
Hi all,

We are experiencing some serious performance issues with a client. They have been using a MS Access application + SQL Server backend. However recently it has run very slow for users, often experiencing delays, freezing and shutting down with no apparent reason.

Running the database locally is usually fine but the issues occur with users connecting over LAN.

There are usually 10-15 concurrent users. We are currently using SQL Express - is this an issue?

Can anyone advise what we can explore to try resolve this (network or software etc)?
Is it this issue?
"SQL Server" ODBC Driver Slow in Monthly Channel (Fixed) (accessforever.org)
 

Kayleigh

Member
Local time
Today, 10:40
Joined
Sep 24, 2020
Messages
708
Need to know the basics if only to eliminate them as a possible cause

Does each user have their own copy of the FE? And not sharing one located on the server?

also is the FE a .accdb or .accde?

and when you say ‘connecting over LAN’ - is that a hardwired or wireless connection
Yes every user owns a local FE accdb file. They are connecting via cable connection.
 

Users who are viewing this thread

Top Bottom