MSSQLSERVER is taking up too much memory

I have about 24 tags that I want to historized and be able to pull up on EasyChart's historical view when needed. These tags can be recorded every 500ms and saved to a local MSSQLSERVER installed on the same computer.

Currently, the computer has only 256GB of memory, and it maxes out in about 60-70 days of running continuously. Talking to others using the same types of server, they are able to record more data for years and years without requiring too much HD spaces. Am I doing something wrong?

I have the Tag group setup to record direct at 500ms and each tag that I want to show is also set to record periodically at 500ms. The settings in MSSQLSERVER is also standard (I am not sure what to modify).
Any suggestions you have would be much appreciated, thank you!


Are you running out of memory or hard drive space?

And you are logging each point every 500ms. No matter what. So twice a second per tag. And using discreet mode which does do any optimization.

Do you really need that data logger every 500 ms?
Is there a reason your Dead and is not auto?

1 Like

A float value is 4 bytes.
60 seconds * 60 min * 24 hours = 86400 seconds in a day

4 Bytes * 2 records per second * 24 tags = 192 bytes per second

192 bytes * 86400 seconds per day = 16,588,800 bytes per day

Hard disk GB are measured in 1000 Mb which are 1000 Kb which are 1000 Bytes each.

16,588,800 bytes = 16.5888 Mb per day of just the data.

16.5888 Mb * 70 Days = 1,161.216 Mb

I can't see 256Gb getting used even with the SQL table overheads.

I am running out of Hard drive space.
I am doing R&D for a new system so we needed as fine data as we can get. I used to do a data point every 10-20ms because we have some extremely time sensitive sequence that we wanted to see happening. Now that we resolved that, 500ms is a compromise for us.

The Discrete mode is just me trying to change settings to see what will help. no particular reason for it. I will put it back shortly. But according to another reply here, I don't think the 500ms data rate is unreasonable.

Thank you for the calculations, it confirms that I have some optimization to do. Do you have any suggestions on how to setup the SQL server? seems that it keeps generating a lot of side files

All correct, except that you also record:

  1. tagid -> int -> 4 bytes
  2. dataintegrity -> int -> 4 bytes
  3. t_stamp -> bigint -> 8 bytes

So that's an extra 16 bytes per record.

20 bytes * 2 records per second * 24 tags = 960 bytes per second.

960 bytes * 86400 seconds per day = 82,944,000 bytes per day = 82.944 Mb per day

82.944 Mb * 70 days = 5,806.08 Mb

That plus overhead for SQL tables, and other required tables for the historian, plus whatever else is on the system like say Windows and all of it's bloat, and I can see that 256GB being chewed up.

I would look into recording 'on change' and setting an appropriate deadband.

What other software is installed on the system with the DB?

1 Like

the computer is a Dell Rugged computer so they have some bloatware from Dell, I uninstalled most of it but they seem pretty light. Other than that, just the MSSQLSERVER and Ignition as an HMI.

I have One Drive on it and MS office suites to open word and excel document. The One Drive is set to file on demand so it doesn't download all the files in it.

I do have another observation though. I am downloading the logs from the "Save" button in Easy Chart and it is giving me 8-9 lines of data per second, is this relates to how the data is being collected? like Ignition is taking separate data line for each tag instead of 1 set of data per the time specified?

Yes, that's how the historian works. If you want to record unconditionally a bunch of tags, the SQL Bridge's wide tables are more space-efficient, and keep the samples together in database rows.

Some related reading:

(Follow the nested links.)

5 Likes

So you're running the gateway and MSSQL on the same machine, or MSSQL and the Client, or worst of all, all three?

I am running the MSSQL, the gateway, Vision Client Launcher, and Designer Launcher on the same computer.

Eventually I just want the computer to run the HMI and collect data into the MSSQL. This seems doable so far aside from the Server taking up all the HDD spaces.

Are you using SQL Express? I believe that has a 10GB limit

It is not far aside, particularly depending on how you have the server and your gateway configured. The JVM will claim memory up to whatever limit you have set (separate allocations for the Designer, Cleint, and Gateway), and once it claims it, it is not released back to the OS. This means that the DB has less RAM to work with (impacting performance) and the DB (if necessary) will start paging memory which takes up additional HDD storage (up to whatever limit is set for the paging file).

Not to mention that the installations of all of those different applications take up HDD storage. The gateway and client save their wrapper logs locally which take up HDD storage.

This is very much related to how much HDD storage is available for SQL to use.

For all but the smallest of setups this is a configuration which is doomed to fall over.

At 83Mb per day 10Gb would last approximately 120 days, so I'm not sure it matters for the stated problem. Definitely an issue if the data needs to be maintained for any extended period though.

1 Like

I'm confused as well. I think he's talking about drive storage rather than memory/RAM since he mentions it only has 256GB and that it's filling up.

Thank you for the information, I understand the concept of it but I couldn't find any information on how to set up the wide table and set up the tags to record unconditionally at a fixed time interval. Is there a training video on this?

I think everyone's numbers are wrong. If you use the Igniton Tag Historian calculator, with 24 tags at 7200 records per hour, the calculator shows 16.48MB/hr, 395.51MB/day, and 2,768.55MB/week.

Apologies as I am extremely new at this and is trying to learn it as I go. So far, I don't have issues with RAM, the computer has 32GB of RAM and the most I have seen is about 50% usage.

I have limited the Pagefile size in the server setting and maybe that is why the RAM is not being used up completely. The only issue I keep running into is every month or 2, the Server takes up all the HDD on the computer. Right now, I completely removed the old Database and started fresh, and it is using about 2GB of HDD each day.

This link has some sql that can tell you how much space your tables are taking up: