Access front makes Sql server backend suspended (1 Viewer)

[kasmax]

Hi everyone

I have developed an application that has MS Access frontend linked to sql server backend database.

Recently the sql server admin informed me that my frontend application is causing the sql server to suspend few times and he had to restart the server each time. he sent me the log showing suspended along with the username that I use.

I have the timeout in access options to 30.

how I understand that the frontend runs a query or a code in order to pull data from sql server db, but when its taking too long to fetch the results it suspends the sql server database.

I thought sql server would have some safety to handle this.

So can some of the experts here please give me some suggestions on
what is the actual issue,
what would be causing it
what to do to deal with it.
and what are the best practice in vba to avoid such problem.

Many thanks for all your help!

 

[CJ_London]

need more information - is it a specific query? what is the sql? how are you running it (access query? passthrough query?) how do you connect to sql server? How big is the expected returned dataset?

things that can cause a query to be slow include the query referencing non sql server tables, using UDF's or VBA functions and within sql server sorting/filtering on non indexed fields or using an initial * on indexed fields. Also consider the network connection speed and whether you are connecting wirelessly.

 

[Minty]

In addition to CJ's excellent advice , lf the query is a complex one then consider moving it to the back end as view, if it's a complex update or insert consider moving that to the backend as stored procedure.

Let the SQL server take the load, and redesign to move as little data as you can over the network.

 

[kasmax]

Thanks CJ_london and Mint for your reply


Its not just a specific query. infect there are multiple queries and they are sql queries and some in vba.
Connection to the server is via ODBC
The expected return is usually in thousands and sometimes in tens of thousands.


Just to explain further, the sql server database that frontend connects to is state wide database and its readonly to fetch transactions.
there are many fields in the sql server database which are not index and text thats is filtered in my query criteria. but i have no control over the design of the database.


but my question is not about the slow performence at this time, i can look into this bit. but my question is that if the query takes so long to fetch then why does it hangs the server.


and i would also like to point out that there are more than one application i have linking to two different sql server db using many queries. not often but only sometimes does that happens the frontend apps will hang the sql server.


Also since this sqlserver dbs are not owned by us and we have given only read only permissions therefore we cant create stored procedures on the server.


yes we do often have slow network issues.


but despite the complex or slow runing queries, and slow network what can possibly be done to avoid the issue of suspending server.


like i have set the time out so shouldnt the query times out after that period. which it does often when a query is fired it sometimes cant fetch and meg odbc time out. so why sometimes it fails to time out and keep runing until suspends sql server db.


for example when run a routine via vba to open connection and query sql database, does it help if we close connection after the routine is executed? does it helps in this situation although i think suspending server means it doesnt comes to the point where execution is completed.


so basically i am trying to find what things can we do to stop the server from being hanged.

 

[Minty]

If you have read only access, then I suspect something else is at play here. A read only query shouldn't normally cause a well set up SQL server to hang.
Change one of your queries that is troublesome to return a snapshot recordset.

Generally you should actually try and maintain a permanent connection to the back end to try and improve performance, Access really doesn't like playing over a WAN.

The other option maybe to import the sub datasets you need to work with to local tables and then perform the more complex queries locally. This would take the network issues out of the equation.

 

[isladogs]

In addition to the excellent advice already given....
Pulling 100k records shouldn't be an issue but how large is the original dataset you are searching?
Are the fields used in the query filter indexed?
If you can't alter the table designs, can you ask for this to be done?
Are you retrieving all fields or just those you need?

SQL Server (like Access) uses a query execution plan. You can use this to investigate ways of streamlining the process

 

[CJ_London]

my question is that if the query takes so long to fetch then why does it hangs the server
…
…
what can possibly be done to avoid the issue of suspending server

your sql server admin needs to answer those. It is not a problem I have experienced so cannot advise.

 

[Frothingslosh]

your sql server admin needs to answer those.

Yep, what he said. Your DBA has tools available to tell him what the process is waiting for, what is blocking it (if anything is), etc. Him telling you 'your application is suspending and locking the server' is much like a doctor telling you 'Yep, you're sick.'.

If you know which queries this is happening to, if you want, you can copy them here and we can take a look at them.

 

[kasmax]

Thanks for all your advises.
well as I mentioned earlier its a state wide database and there fore roughly I would be search among over 20 million records.
well fields are not indexed. I know its a huge problem but this is how its setup. basically these are the views they have created for user to have read only access.

I have been dealing with them foir long time. they are so high above the level so they wont accept any requests for any changes so its very hard to ask them to index fields or any such request.

also as I mentioned I have developed quite few apps that connects to these sql servers and we often get odbc error. but its separate issue. sometimes its slow network or too many users connected at peak hours, so do need to look at how can I optimise my queries. I tried pass queries before but it didn't seemed to make any difference.

there is not just one query for the example.

but I am recently been informed of this new issue for server been suspended and I'm not sure how to address it

 

[Galaxiom]

... they wont accept any requests for any changes so its very hard to ask them to index fields or any such request.

Unfortunately the performance will remain hideous without indexes.

 

[sonic8]

... he had to restart the server each time. he sent me the log showing suspended along with the username that I use.

That is ridiculous! It's not the server that is suspended, but only a single (or multiple) sessions. The admin could just kill the suspended session, or even better the session that is causing the issue, which is most likely not the one being suspended. sp_lock (or similar tools) will reveal the culprit. - The admin should know that if he has reasonable knowledge of SQL-Server.

what is the actual issue,

A session/query gets suspended, when it is waiting for a resource that is currently not available. Most likely because the resource is locked by another session.

what would be causing it

Another query (b)locking a certain resource. With an Access frontend the cause is most likely a query bound to a form, combobox or listbox that is returning more results than Access fetches by default. Unless the user scrolls to the end of the recordset, Access is then just doing nothing while SQL-Server is waiting for Access to fetch all the records before it can release the lock on the underlying tables.

what to do to deal with it.

Approaches to overcome the problem:
- Redesign the query to fetch less records
- Scroll to the end of the recordset of the form/combobox to force Access to fetch all records.
- If the results are readonly, use a pass through query with the WITH (NOLOCK) hint to prevent the query from locking the records.

 

[Frothingslosh]

Approaches to overcome the problem:
- Redesign the query to fetch less records
- Scroll to the end of the recordset of the form/combobox to force Access to fetch all records.
- If the results are readonly, use a pass through query with the WITH (NOLOCK) hint to prevent the query from locking the records.

-Offer sacrifices to the Ruinous Powers in hopes that they encourage the state to hire a qualified DBA.

 

[Isaac]

The fact that your access to the server is set up to only allow Select access, does not mean that your queries will not hang indefinitely if poorly written. I suggest adding something to avoid locking a table, such as "Set Transaction Isolation Level Read Uncommitted" at the beginning of the query...

 

[sonic8]

such as "Set Transaction Isolation Level Read Uncommitted" at the beginning of the query...

I strongly discourage implementing this suggestion.
1.) You usually want explicitly to read only committed data and not some half baked stuff that might disappear again any instant.
[I just deleted multiple other arguments against this, they became too tedious to explain. - Just this one is enough not to use it.]

 

[Isaac]

This is the same as your with(nolock) suggestion, except updated for modern versions.
It is the current standard for not locking a table, but obviously, you have to know something about data refresh cycles and timing. A reasonably set up sql server environment and batch updates would not be allowing ghost records in the first place - it is quite normal to use this setting and in fact, the recommended standard compared to adding table locks after each table. We can agree to disagree. It's been recommended to me to use this standard in the working environment of one of the largest banks in the world.

 

[sonic8]

This is the same as your with(nolock) suggestion,

You are right. Thank you very much for this comment. That's what makes participation in this forum so valuable. You'll learn so much stuff you never expected to learn.

I was slightly shocked that WITH (NOLOCK) also has the effect that uncommitted changes might end up in the query. After thinking about that (and reading the documentation) it became clear to me that it has to be this way with the implementation of transaction isolation in SQL Server. So, I would also rather discourage to use my suggestion with WITH (NOLOCK).

except updated for modern versions.

This has nothing to do with modern or old. WITH (NOLOCK) is a query optimizer hint and applies only to the single query it is used in. SET TRANSACTION ISOLATION LEVEL changes the isolation level of the session it is executed in and then affects all queries executed within that session. Neither is better than the other. They are different approaches for different purposes. With most Access frontends to SQL Server, changing sessions properties is not feasible. Query hints in linked views are much more practical but also not always possible.

It is the current standard for not locking a table, but obviously, you have to know something about data refresh cycles and timing. A reasonably set up sql server environment and batch updates would not be allowing ghost records in the first place - it is quite normal to use this setting and in fact, the recommended standard compared to adding table locks after each table.

The mechanism to disallow ghost records is transaction isolation!

Also, keep in mind that the original question in this thread was about one single query. So, the requirement that query hints need to be applied to each query is not a disadvantage in this context. Beyond the context of the question, one should choose what is more appropriate for the scenario. However, in general, I keep my concern about disabling transaction isolation.

 

[Isaac]

This has nothing to do with modern or old

Yes, it does, because the option only became available on sql server 2014 or 2016...I forget which. Thus, read uncommitted is a more modern version of saying the same as with(nolock).

Points taken on the rest. If you know what you're doing, and you know you're querying a table that won't be in the middle of complex transaction-based updates, you can use this (just like you can also use with(nolock), it's just more outdated) to avoid the DBMS acquiring locks which may cause other problems with other processes. If you know the context of the table being queried, of course, and know you won't fall afoul of the-middle-of-an-update process. Which is often the case.

 

[Isaac]

actually it may even have been available now since 2008!

 

[sonic8]

Yes, it does, because the option only became available on sql server 2014 or 2016...I forget which.

The first time I definitely used it was in SQL Server 2000. ;-)
You can download the old SQL 2000 documentation and look it up.
I think, it was already in SQL Server 6.5 (the oldest version I ever worked with), but I'm not sure and can't find that old documentation right away.

 

[Isaac]

I stand corrected then. Maybe I was thinking of the nolock option without With being deprecated. My bad.