Fixing Factory PMI Easy Chart Multi-Client Memory Load

Where does the chart request actually stop?

Follow the packet. An Easy Chart pen does not stream live values from the PLC — it issues a historical query. The path is fixed and every hop has a cost:

1. The client JVM (javaw.exe) builds a SELECT for each pen, bounded by the chart's date range.
2. The query travels over TCP to the PMI/gateway server process.
3. The server hands it to SQL Server Express 2005, in this case through a view that reshapes the waste variable.
4. SQL Server resolves the view, reads rows out of the historical table, sorts and returns them.
5. The result set is serialized back to the client, where it is held in the chart's dataset until the pen is refreshed.

Memory that grows per connected client can be created at hop 1 (client heap), hop 4 (database working set), or hop 5 (result set size). The five machines on the Ethernet segment and the PLC scan rate are not in the path at all once the data is logged, so layer-one and switch-level diagnostics buy nothing here. The failure is above the wire.

Which symptom points at which hop?

Observation	Hop	Mechanism	Test that decides it
Each connection spawns an 80 MB javaw.exe on the server	1	The client runtime is being launched on the server console, not on a remote workstation	Check where the process appears: javaw.exe belongs to the client, not the server service
Easy Chart pens redraw slowly, server RAM spikes during redraw	4	Unindexed time column forces a full table scan and a sort per pen, per client	Easy Chart > Run Diagnostics; look for "The time column 't_stamp' is not indexed"
Memory scales with the chart's date range, not with client count	5	Result set size — one row per second per tag over a wide window	Halve the date range and re-measure the client heap
Slowness appears only when the pen uses the reshaped waste variable	3	View definition prevents index use or materializes an intermediate set	Query the base table directly with the same range and compare duration
All clients drop with an expiry message inside two hours	—	Demo runtime is a combined budget, not per-client	Count connected clients × elapsed minutes against 120

Is javaw.exe the server or the client?

It is the client. Every javaw.exe that appeared when a connection was made is a Java client runtime, and ~80 MB is a normal resident size for one. Launching five of them on the server itself means the 2 GB Windows XP box is carrying roughly 400 MB of client heap plus the server JVM plus SQL Server Express — before any query runs.

That test topology invalidates the measurement. In production there is typically one client process per operator machine, so the server never holds client heap at all. Move the five clients to five separate workstations on the same Ethernet segment and re-measure; the server-side curve you are trying to characterize only becomes visible once the client heap is off the box.

The second constraint on that box is the database edition. SQL Server 2005 Express limits how much memory it will use for its buffer pool and runs on a single CPU. When a query cannot use an index, it does not fail — it reads the table, spills the sort, and pushes the whole working set through that capped pool. Five clients issuing the same scan concurrently is what takes the machine to 100%.

Which fix do you apply first?

Approach	What it removes	Effort	Risk	Effect on 5 concurrent clients
Index t_stamp on the historical tables	Full table scan and sort per pen	Minutes, online-capable on small tables	Low — small write-side cost, extra storage	Query time and server memory drop by orders of magnitude; scales with client count
Move clients off the server	~80 MB of client heap per session on the server	Deployment change only	None	Frees ~320 MB immediately, but slow queries remain slow
Reduce the logged date range per chart	Result-set size at hops 4 and 5	Operator-facing change	Loses analysis window	Helps, but is a workaround for a missing index
Reduce logging rate	Row volume growth over time	FactorySQL group edit	Loses resolution	Slows future growth; does nothing for existing rows
Simplify or flatten the view	Optimizer barriers around the waste variable	SQL development	Medium — affects pen definitions	Only relevant after the index exists

Index first. It is the only change that improves the per-client cost rather than the per-client count, and Run Diagnostics has already named the exact defect. Do the client relocation in parallel because it costs nothing.

How do you add the index on t_stamp?

Through SQL Server Management Studio Express, against each historical table the Easy Chart pens read:

1. Open SQL Server Management Studio Express and navigate down to the table.
2. Right-click the table and choose Modify.
3. Right-click the t_stamp column and choose Indexes/Keys.
4. Add a new index and set: Columns = the t_stamp column, IsUnique = No, Type = Index.
5. Save the table definition and close the designer.

The equivalent statement, if you prefer to script it across several tables:

CREATE NONCLUSTERED INDEX IX_history_t_stamp
    ON dbo.your_history_table (t_stamp);

Non-unique is correct: several tags can share the same second-resolution timestamp, and a unique index would reject those inserts. Repeat for every table feeding a pen — the view does not inherit an index, it inherits the plans of its base tables. If the view joins or aggregates, index the join keys on the underlying tables as well, otherwise the optimizer still has to build the intermediate set the hard way.

Does the logging rate matter here?

One row per second per tag is a workable rate and is what the historian is actually doing. Sub-second logging is where the arithmetic stops working: PLC scan is typically around 10 ms, the Windows clock resolution on that generation of OS is 10-20 ms, and a single database transaction costs upwards of 30-80 ms. FactorySQL has a practical lower bound near 100 ms for logging, and 100 ms is the fastest rate worth configuring for anything in it.

Do the row math before you widen a chart window. Five machines × several waste and runtime tags × 86,400 rows per tag per day is what a one-week Easy Chart range asks SQL Server to touch. With the index, it seeks to the range boundary and reads only the rows inside it. Without the index, that same request reads the entire table on every pen refresh, in every client, simultaneously — which is exactly the memory curve that killed the fifth connection.

Also check the 4 GB database size ceiling of the Express edition against your projected row count. At one row per second per tag the table grows continuously, and hitting that ceiling stops logging rather than degrading it.

Why did all five clients expire inside two hours?

That is the demo licence, not a fault. The demo grants a combined 120 minutes of runtime across all connected clients. Two clients consume it in one hour, four clients in half an hour, five clients faster still. The expiry message reaching every session at once is the pooled budget running out — it carries no information about server memory and should be excluded from your load test results.

For a valid concurrency test, either restart the demo period between runs or test against a licensed gateway, and record memory per interval rather than at the moment of expiry.

How do you verify the fix?

1. Open the Easy Chart in the Designer and run Run Diagnostics. The warning "The time column 't_stamp' is not indexed" must be gone for every pen. If it persists on one pen, that pen's table or view still resolves to an unindexed base table.
2. Note the per-pen query durations reported by the diagnostics before and after. A range that previously scanned should now return in a fraction of the time.
3. Start the five clients on five separate workstations, not on the server. Confirm in Task Manager on the server that no javaw.exe client process appears there.
4. With all five clients connected and charting the widest date range operators actually use, watch the server: total commit charge should stay well below the 2 GB physical limit, and SQL Server's working set should stop tracking the number of connected clients.
5. Repeat the redraw on all five clients at the same instant — the worst case for a shared index — and confirm the server memory returns to baseline after the queries complete rather than staying elevated.

FAQ