Database Connections unstable - Many slow running queries selecting data from history

I'm seeing a very large number of extremely long-running queries all requesting data from the tag history tables, presumably from Vision/Perspective charts.

Eventually, the database connection "crashes" and dies, and doesn't recover until I re-save the database connection. It only seems to have started since upgrading from 8.1.5 to 8.1.21. Previously I had set the max and idle connections to the default 8. But I've increased these out now to 50 and still having issues. The database (SQL Server) hasn't changed, and neither has the amount of data we're storing. Not sure what to look for? I'll look into testing the query speed in SSMS and analysing the table indexes. I don't have much experience here though


How would I see the parameters that were passed into these active queries?

This seems a little excessive... 84mins to insert a record into the SQL database

image

Unrelated. I just realised that labels are automatically added now based on your post text, to add things like Perspective and Vision labels. That's cool!

Here's the execution plan (I have no idea what I'm looking at):

And, in SSMS, the same kind of query as in my OP returns in ms

Here are other of the same queries coming in and leaving very quickly:

Why can't I kill the other dead queries? They've been "cancelling" for 10-20 mins

1 Like