# Ignition and MSSQL Performance

**URL:** <https://forum.inductiveautomation.com/t/ignition-and-mssql-performance/5806>\
**Category:** Ignition\
**Created:** [February 5, 2013, 7:05pm UTC](https://forum.inductiveautomation.com/t/ignition-and-mssql-performance/5806 "2013-02-05T19:05:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kpollock](https://avatars.discourse-cdn.com/v4/letter/k/df788c/32.png) [@kpollock](https://forum.inductiveautomation.com/u/kpollock)\
**Post date:** [February 5, 2013, 7:05pm UTC](https://forum.inductiveautomation.com/t/ignition-and-mssql-performance/5806/1 "2013-02-05T19:05:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kpollock](https://avatars.discourse-cdn.com/v4/letter/k/df788c/32.png) [@kpollock](https://forum.inductiveautomation.com/u/kpollock)\
**Post date:** [February 5, 2013, 7:14pm UTC](https://forum.inductiveautomation.com/t/ignition-and-mssql-performance/5806/2 "2013-02-05T19:14:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [February 5, 2013, 11:59pm UTC](https://forum.inductiveautomation.com/t/ignition-and-mssql-performance/5806/3 "2013-02-05T23:59:19Z")

</div>

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.

> [@](#):
>
> Will the Memory and Write issues go away when we upgrade to 64 bits and 8 GB?

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...

> [@](#):
>
> Is there a way to see what queries are being executed?

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.

> [@](#):
>
> If a report or data table is not open, the query should not run

And it won't. If you're seeing queries from windows that shouldn't be open, make sure you're using [system.nav.swapWindow](http://inductiveautomation.com/support/usermanuals/ignition/system_nav_swapwindow.htm) 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,