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

  • Rod at work

    SSC-Dedicated

    Points: 33402

    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.

  • Phil Parkin

    SSC Guru

    Points: 244670

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jasona.work

    SSC-Forever

    Points: 49988

    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: 42146

    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: 244670

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Chris Harshman

    SSC-Forever

    Points: 42146

    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: 996869

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Rod at work

    SSC-Dedicated

    Points: 33402

    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: 33402

    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: 33402

    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.

  • as1981

    SSCrazy

    Points: 2746

    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: 33402

    as1981 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.

  • as1981

    SSCrazy

    Points: 2746

    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

    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: 227

    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 21 total)

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