SQL Historian tables locked by outstanding queries

We are using V7.9.13 and have multiple SQL databases we are using for historians. One of our database connections is routinely having to send records to the S&F local cache and it appears to be occurring due to SELECT queries remaining active for long periods of time (sometimes as long as 6-8 hours). Our apps have a lot of charts that can generate queries to the historian in question, so it has been very challenging to try to identify a cause for the "hung queries" but it is fairly obvious that these queries are what is preventing the forward throughput to the database tables.

I am hopeful that we may be able to find some suggestions as to how to troubleshoot what components are creating the queries, or potentially if there is a way to automate the detection and cancellation of queries that stay active for a certain amount of time.

I will add that the historian in reference is MASSIVE and this is the root of our issue. We are taking steps to minimize the size of this particular historian by splitting it into multiple separate DBs, rather than a single connection. So we know that we have a system changes that would greatly impact performance but the intent of this topic is to determine what options we may have to try to detect/prevent/cancel queries that appear to be going stale and remaining active.

The single most important technique is to constrain the allowed timespans on the chart user interface.

Consider expanding your database to use master-slave replication, and require all charts to work from the slave connection.