January 22, 2020 at 5:14 pm
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.
January 22, 2020 at 6:49 pm
Please point me to a link which talks about this "pool of new Ids for Identity columns", because I am unfamiliar with it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 22, 2020 at 7:12 pm
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.
January 22, 2020 at 7:46 pm
Using a SEQUENCE won't necessarily solve the problem of loosing consumed IDENTITY values. If an INSERT transaction rolls back, or the service restarts:
January 22, 2020 at 8:28 pm
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
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 22, 2020 at 10:05 pm
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
January 23, 2020 at 3:39 am
Have you checked to see if a virus scan is running and maybe picking up on the Access files?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2020 at 4:44 pm
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.
January 23, 2020 at 4:56 pm
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.
January 23, 2020 at 9:49 pm
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.
January 24, 2020 at 3:28 pm
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.
January 24, 2020 at 3:42 pm
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.
January 28, 2020 at 7:00 pm
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.
January 29, 2020 at 2:25 pm
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply