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

Ignition
, ,
1

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

image
image1224×1557 191 KB

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
image1190×57 4.38 KB

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!

2

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

image
image1337×1050 41.8 KB

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
3

Hey, did you ever get a resolution to this? Trying to troubleshoot basically the exact same thing.