What could cause MS Access app as a front-end to SQL, periodically run slow?

Question

What could cause MS Access app as a front-end to SQL, periodically run slow?

Rod at work

SSC-Dedicated

Points: 33902
More actions
January 22, 2020 at 5:14 pm

#3717503

We've got an old Microsoft Access pharmacy application which acts as a front-end to a SQL Server 2016 database. Periodically the application will just start running slowly. We're working on a modern replacement app, but that won't be available for months. In the meantime, what can we do about this application suddenly running slowly?
Here's what I did. I got into SSMS and ran both sp_who and sp_who2, to find out what was hanging and to see if any session was blocking any other session. Nothing was hung and none of the sessions were blocking any other sessions.
Now I'm going to speculate and I could be entirely wrong and off base. I know that when SQL Server reboots, it replenishes a pool of new IDs for identity columns. The Pharmacy gets kinda bent out of shape when they see a "gap" in those numbers. (I think their practice is to print out a long list of labels, based upon those identity values, then apply those to bottles, containers, etc., anything that holds what the pharmacy houses. To have an interruption in the list of labels they've printed really torques them.) If that pool of identity values runs out, could that cause the application to run slow?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246715 More actions · Answer 1

Please point me to a link which talks about this "pool of new Ids for Identity columns", because I am unfamiliar with it.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

jasona.work SSC Guru Points: 50084 More actions · Answer 2

Phil Parkin wrote:

Please point me to a link which talks about this "pool of new Ids for Identity columns", because I am unfamiliar with it.

Phil, here you go: https://blog.sqlauthority.com/2017/03/24/sql-server-jump-identity-column-restart/

I ran into this myself when we migrated to SQL2012 with an app that the identity value meant something to the users and they were not happy with gaps in the sequence. So the developer and myself worked up an Agent job to run when SQL restarts to check the last identity value in the table, then run DBCC CHECKIDENT( table, RESEED, value)

Hacky, but it works. Ideally, a better solution would be to set up a sequence, but the dev at the time didn't have the time to do it.

Rod: Not sure if having the current batch of identity values "run out" would cause a slow down. I'm pretty sure at one time I found something on HOW SQL handles identity values in the cache and refreshing, but it was a while back and I'm not sure I could find it again.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Using a SEQUENCE won't necessarily solve the problem of loosing consumed IDENTITY values. If an INSERT transaction rolls back, or the service restarts:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15#general-remarks

Phil Parkin SSC Guru Points: 246715 More actions · Answer 4

Phil, here you go: https://blog.sqlauthority.com/2017/03/24/sql-server-jump-identity-column-restart/

Thank you very much. I knew about the Identity jump 'feature', but hadn't read deeply enough into the topic to know about the associated caching.

To the OP: have you considered that there may be are other things causing the issue, such as

Other tasks or applications running on the same server as SQL Server (or on the same host, if this is a virtual server)
Other tasks or apps running on the Access client machine(s)
Network bandwidth issues
SAN latency

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

To troubleshoot the performance problem, have you looked at SQL Server waits to get an idea of what resources could be a bottleneck?

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Also check out what's actually happening right when there is a problem querying using something like sp_WhoIsActive or sys.dm_exec_requests and sys.dm_exec_sessions

http://whoisactive.com/

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 6

Have you checked to see if a virus scan is running and maybe picking up on the Access files?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rod at work SSC-Dedicated Points: 33902 More actions · Answer 7

jasona.work wrote:

Phil Parkin wrote:
Please point me to a link which talks about this "pool of new Ids for Identity columns", because I am unfamiliar with it.
Phil, here you go: https://blog.sqlauthority.com/2017/03/24/sql-server-jump-identity-column-restart/
I ran into this myself when we migrated to SQL2012 with an app that the identity value meant something to the users and they were not happy with gaps in the sequence. So the developer and myself worked up an Agent job to run when SQL restarts to check the last identity value in the table, then run DBCC CHECKIDENT( table, RESEED, value)
Hacky, but it works. Ideally, a better solution would be to set up a sequence, but the dev at the time didn't have the time to do it.
Rod: Not sure if having the current batch of identity values "run out" would cause a slow down. I'm pretty sure at one time I found something on HOW SQL handles identity values in the cache and refreshing, but it was a while back and I'm not sure I could find it again.

I was not aware of the DBCC CHECKIDENT command, thank you. I'll pass that along to our real DBAs (I'm a developer in this large org, so haven't the necessary privileges to implement this.)

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 33902 More actions · Answer 8

Phil Parkin wrote:

Phil, here you go: https://blog.sqlauthority.com/2017/03/24/sql-server-jump-identity-column-restart/
Thank you very much. I knew about the Identity jump 'feature', but hadn't read deeply enough into the topic to know about the associated caching.
To the OP: have you considered that there may be are other things causing the issue, such as

<li style="list-style-type: none;">
Other tasks or applications running on the same server as SQL Server (or on the same host, if this is a virtual server)

<li style="list-style-type: none;">
Other tasks or apps running on the Access client machine(s)

<li style="list-style-type: none;">
Network bandwidth issues

<li style="list-style-type: none;">
SAN latency

Yeah, Phil, I knew that the whole running out of numbers in an identity pool, was nothing more than a shot in the dark. I was just drawing at straws.

Your list of other things to check I think is very good. There are about 8 databases on that server, of which I know of 3 and only have access to one of them. There very well might have been something else going on with one of the other applications. I got one of the official DBAs onto it. However about 2 hours after posting my original post the decision was made to reboot the server. This application I help support is too critical to let it languish too long. So we rebooted the server. Once it came up the users of the application that was running slow, reported that it was responding normally again. Congratulations all around, people slapping one another on the back. But I still have the feeling that all we've done is kicked the can down the road.

I suppose though, that the AV on the users' machines weren't involved in the difficulty, because I would think if it were the AV's, then those AVs would have continued to cause problems.

Networking issues might be the problem. But WOW, networking is not in my wheelhouse.

I don't like MS Access, so my nature inclination is to blame Access for any problems. Its really hard for me to ignore my prejudice against Access. It really may be Access. We're working on replacing that Access app with a more modern app, but it won't be ready for months.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 33902 More actions · Answer 9

I wasn't aware of SQL Waits. Thank you, Chris, I'll pass this along as well.

Kindest Regards, Rod Connect with me on LinkedIn.

as_1234 SSCrazy Points: 2849 More actions · Answer 10

I haven't used Access as a front end for SQL for a long time but I remember that it used to get slower over time. I had to make sure the Access application was closed every so often. We had people leaving themselves logged into their workstation and leaving the Access application open so it was never, or rarely, closing. As soon as it got closed regularly (I get remember the exact timescales) the speed problem went away. I never worked out why.

Rod at work SSC-Dedicated Points: 33902 More actions · Answer 11

as_1234 wrote:

I haven't used Access as a front end for SQL for a long time but I remember that it used to get slower over time. I had to make sure the Access application was closed every so often. We had people leaving themselves logged into their workstation and leaving the Access application open so it was never, or rarely, closing. As soon as it got closed regularly (I get remember the exact timescales) the speed problem went away. I never worked out why.

WOW! That's interesting. I suppose that could be happening in this case. I certainly can see how just starting logged in and in the app for days on end, could cause that. Then when we reboot the server, we tell them to get out of the app. So, in actual fact we have be inadvertently resolving the issue, then they get out of the app, waiting for the server to reboot.

Kindest Regards, Rod Connect with me on LinkedIn.

as_1234 SSCrazy Points: 2849 More actions · Answer 12

Thanks for the reply. One detail I forgot - if I remember correctly it didn't have to be the same user logged in. As long as there was always someone logged in then, at some point, the Access application would slow down. In case it's relevant the application was being run from a mapped network drive and the SQL Server was on the same local lan.

James Goodwin Hall of Fame Points: 3691 More actions · Answer 13

The "Track Name Changes" option has been known to slow down Access as well. You can turn it off under the Options Menu | Current Database.

tcroninlifepoint SSC Veteran Points: 235 More actions · Answer 14

Not seen any mention, but more than likely this may be a bad plan from executed sql. Run sp-whoisactive, or download Brent Ozar's blitzcache. Start there as soon as it gets slow, look for blocking etc. Also if all your stuff is on prem this setup may work, but using Citrix would also help alot.