We've been using retool (on prem , now v3.33.3) for a while now (some years I suppose) and we have this constant problem where query performance dips to a point that no query ever finishes.
What all users just see (regardless of the page they are in) is the endless loading circle of doom on each component that is waiting for data from a query.
For context, all our data sources point to a single instance of MS SQL server, and when this problem occurs, there is no contention on the SQL server at all.
The only solution we have when we reach this point, isto restart the docker container retool-onpremise_db-connector_1. Once we do this, everything gets back to normal and queries fly...
This can happen once every 3 weeks or 3 times in 10 minutes.
What's the best way to debug it while it's happening? Or any logs that I can look at to try to find the source of the issues?
We've seen two similar issues in the past. Although it's hard to diagnose, we could check a couple of things:
What version of MS SQL server are we using? If we are using an old one, updating is always a good idea. Updates can include optimizations that improve query performance and resource management, which can help in environments with high load and complex queries.
What are the most heavily used apps, and what queries are we running on them? If many users are querying this db simultaneously, and these are expensive queries, we might be exhausting the pool of available connections. This is why restarting the Docker container temporarily fixes the issue, it closes the connections and gives the connection pool a fresh start.
When you are experiencing this performance issue, try running a query to a different resource. If this one works, we may be looking at a connection leak with your MS SQL server.
For the other issue we saw in the past, a customer told us that removing the comments on SQL query fixed it for them.
