Ignition and MSSQL Performance

Ignition
1

Ignition 2.5.5 on VM Server, (4) Xeon CPU
Windows Server Enterprise 2007, 32 bit, 4 GB
All tags from OPCDA - KepServerEX 5.5, about 5000 tags, about 2000 of these tags are Historized
3 MSSQL connections running .1 to 30 queries/sec
CPU usually <5%, Memory utilized in the high 3s.
IT swears there is no significant network loading and most of the time pings are 1-10ms
Typically 10-12 users running a screen with 900 tags with 1-5 second update times.

PROBLEM:
Occasionally the system seems to get stressed and there are holes in my historical data and trends, and tags will have data quality overlays.

No obvious issues in MSSQL studio activity monitor.

PerfMon has low average values but occasionally has huge Memory/Pages/Sec values and big hits on MSSQL/PageWrites/Sec. No MSSQL/LazyWrites/Sec observed.

QUESTION 1:
Will the Memory and Write issues go away when we upgrade to 64 bits and 8 GB?

I have a lot of MSSQL queries embedded into data tables and reports displayed in Igntion because Ignition integrates with SQL better than any other HMI/SCADA software out there. I thought that the queries would only run when the pages are displayed but after watching the data base connections I occasionally see queries from pages that are not open.

QUESTION 2:
Is there a way to see what queries are being executed? The database connections display is not persistent and seems to only show the query when it is delayed.

QUESTION 3:
How can I better condition when queries are executed? If a report or data table is not open, the query should not run. If a report is open then I need to update the query on a 1-5 second interval.

If I can master Ignition better, I have another factory that wants it bad.
Thanks
Ken P.

2

I am also running 374 transaction groups about half of them on 1 sec updates.

I am trying to learn where the system limits are, but average values for resource utilization are pretty low except for the previously mentioned paging and write peak values.

Thanks
Ken P.

3

Hi,

From what you describe, I'm not sure your problem has anything to do with the database. Sounds like that part's fine.

Let's start with this: overlays on the tags. This indicates bad quality. The graphs don't display data stored with bad quality, and this type of quality can't be influenced by the quality of the database connection. In other words, it only comes from the OPC server (or at least the opc part of the system).

Do you have any errors or messages in the console related to the OPC server? If you go into Kepware, do you have any errors/messages about the devices? The most common cause would be a loss of connection (or comm timeout) between kepware and the device. This would lead to "bad quality", reflected by the overlay, stored to the database, and ignored by the chart.

By "write issues" do you mean the history problem described in the post, or something else? If the history problem, as described, I think it's a "read" issue, not a "write" one. As for memory, what issue is there? Are you running out of memory? Adding more memory will only allow Ignition to use more memory... and in general, it's happy to use as much memory as you allow it to...

As you noted, the status page only shows you.... the queries being executed. But most of the time they run too quickly to see. There is a logger that you could turn on, if you go to Console>Levels and search for "Select", but I wouldn't recommend that for use besides very temporary troubleshooting.

And it won't. If you're seeing queries from windows that shouldn't be open, make sure you're using system.nav.swapWindow instead of "openWindow". A common mistake is to use openWindow, which will leave the previous window open in the background, causing queries to continue to run. In general, data bindings have a poll rate somewhere, so it might depend on exactly what type of data you're working with, but you can certainly get it to poll at a slower rate when open.

Hope this helps,