IMD SolutionComputing LibraryShop main store

Troubleshooting

Historian trend flat line: check Query Row Limit

Edge computing and IIoT Published 6 min read

Short answer

A historian trend that draws one straight value in history mode while live mode and a direct SQL query both return good data is truncating rows client-side. The tag's Query Row Limit on the graph was set to 2 instead of the documented default 0, so only two samples came back. Set it to 0, then confirm the trend's saved row count matches the SQL count.

On this page
  1. Why two samples draw a straight line
  2. Five checks that split the fault
  3. What browsing and live mode actually prove
  4. The row-count cross-check
  5. Set Query Row Limit back to 0
  6. A worked count with the arithmetic
  7. Close the remaining branches: date order and clock
  8. The decision at each outcome
  9. Questions asked

When a new historian client draws a flat line in history mode while live mode moves and a direct SQL query returns the full archive, the connection and the stored data are fine and the client is truncating its own result set. In the case that motivated this article, a newly commissioned client station on a Historian 2020 system, the tag's Query Row Limit on the graph read 2 instead of the default 0, so the trend received two samples and had nothing to draw between them. Set the option back to 0, then verify by counting rows: the trend's save-data function and the raw SQL query must return the same number for the same window. The checks below close one branch each and take minutes.

Why two samples draw a straight line #

A pen interpolates between the samples the query returns. Two samples of nearly equal value stretched across a long window render as one straight line at whatever value that pair happens to hold, which is why the constant looked random rather than meaningful.

The server logged no error because there was none to log. The client asked for at most two rows and received exactly two; from the historian's side the request was valid and completely answered.

Query Row Limit caps the rows a retrieval returns for that tag. The vendor-documented default is 0, which uses all stored values; any positive number is a hard cap on the retrieval, and the documentation's own illustration of the option is 200 rows. A value of 2 is a stray edit or a restored template, not a working configuration, because no trend is readable at two samples. The option is held per tag, in the tag options of the trend graph on that client station.

Five checks that split the fault #

  1. Watch live mode. Put the tag in live mode and read the cursor value. If it changes, the client is connected, authenticated and receiving current values, so the fault is in how history is requested or rendered. If live mode is dead too, you have a connection fault instead, and the steps for troubleshooting historian trend client connection failures are the next read. The same live-works, history-flat split on a PI system usually has a different cause, covered under why PI shows live values but no trend history.
  2. Compare a working client. Pull the same tag into a trend on a station that renders correctly, over the same window. Varying values there mean the archive is intact and the fault belongs to the new client. If no station can draw variation, the problem sits upstream of the trend, so start where you trace a PLC tag from controller to historian display.
  3. Watch a system tag. SysPulse toggles. On the faulty client it sat at a constant OFF in history mode, which is exactly what a toggling tag looks like when only two rows come back. A system tag needs no process knowledge and exists on every server.
  4. Compare clocks. Check date, time and time zone on the client against the historian server. A client hours or a day off requests a window the server has no data for. In the case above they matched, which closed this branch.
  5. Count rows. Query the tag's history directly in SQL from the client machine, then use the trend's save-data function on the same tag and window, and count what each returns. This is the check that finds the fault.

What browsing and live mode actually prove #

Windows authentication that browses the full tag list proves metadata access, nothing more. A moving cursor value in live mode proves the current-value path. Neither proves the history query is well formed or uncapped, so neither can clear the client. The row count, not the browse, is the decisive test.

The row-count cross-check #

Raw SQL for the window Trend save-data for the window Conclusion
Many varying samples, identical to the server 2 rows Data and permissions are good; the client's request is capped, so go to Query Row Limit
Many varying samples Same count as SQL Rendering or window fault; recheck date order and clock
Empty or short Empty Server-side data or access problem; not this fault

Set Query Row Limit back to 0 #

  1. Open the tag options on the faulty graph.
  2. Find Query Row Limit; the faulty value read 2.
  3. Change it to 0 and apply.
  4. Reload the historical view over the same window used for the count.
  5. Repeat on every pen on that graph, because each tag keeps its own value and one pen can sit at 2 while its neighbor renders correctly.
  6. Save the tag's data again and confirm the row count now equals the SQL count. In the case above, the graph then drew the varying values, matching the working client and the server.

The option exists for a reason: it keeps an ad-hoc query from dragging an excessively large result set across the network. After setting 0, if a very long window makes the client sluggish, narrow the window rather than restoring a cap that silently truncates the pen. Client-side row settings fail the other way in other edge tools too, when the component ignores the saved value instead of truncating against it; why an Ignition table ignores its saved rows-per-page setting works through that variant.

A worked count with the arithmetic #

Illustrative numbers, not measurements from the site above. A tag logs one sample every 5 s with no compression, and you examine an 8 h window.

  • Window in seconds: 8 h × 3,600 s/h = 28,800 s
  • Expected rows: 28,800 s ÷ 5 s/sample = 5,760 samples
  • COUNT(*) run on the client against the historian: 5,760 rows, matching the server
  • Trend save-data on the same window: 2 rows
  • Rows never drawn: 5,760 − 2 = 5,758, which is 5,758 ÷ 5,760 = 99.97 % of the window

After the fix, both counts read 5,760. Treat the clock arithmetic as a sanity check only: with deadband compression active, the true row count is whatever COUNT(*) returns, so compare SQL against save-data rather than computing an expectation from the scan rate.

Close the remaining branches: date order and clock #

If the row counts already match and the pen still flattens, test date order before anything else. Pick a past range whose day number is above 12, such as the 13th to the 15th of any month: those dates cannot be misread as month-first. If the trend draws correctly on that range, the SQL login's default language is interpreting dates in the other order; the SQL Server language setting english uses M-D-Y and uk_english uses D-M-Y. Change the login's default language in the SQL Server management tools, and change the default for new logins so the next client station does not inherit the mismatch. A clock disagreement surfaces in check 4 and is fixed by syncing the station.

The decision at each outcome #

  • SQL count high, saved trend rows at 2, live mode moving: Query Row Limit. Set it to 0; the fix is complete when the two counts match.
  • SQL itself returns constant values: the pen is telling the truth. The flat line is real data, and the fault is upstream at the source or the logging trigger, not on this client.
  • Live mode dead on the new station: connection or acquisition; stop the client-side checks and work the connection path.
  • Trend correct only on the day-above-12 range: date order on the SQL login. Change the language and the flat line on ambiguous ranges goes with it.

Questions asked

Will a Query Row Limit of 2 damage the stored historian data?

No. The cap acts on the client's retrieval only; the archive keeps every sample it logged, which is why a direct SQL query returns full history and other clients draw varying values for the same tag. Clearing the option to 0 changes what the pen requests, not what the server stored.

When is a Query Row Limit above 0 the right choice?

Only for interactive spot checks on very large tags, where you want an answer in seconds rather than a complete series; the documentation's example value is 200 rows. Never read a value or judge a process from a capped pen, and never leave a cap inside a saved template or a graph an operator inherits.

Which system tags work for the flat-line cross-check?

SysPulse and SysTimeSec. SysPulse toggles between states, so a constant OFF in history mode is immediately suspicious; SysTimeSec counts seconds, so any flat historical display of it means the window is not being retrieved. Both exist on every historian server and need no process knowledge to interpret.

Does changing Query Row Limit on one station affect the server or other clients?

No. The option is held per tag on that graph on that station; the archive and every other client are untouched. That is also the trap: a second pen on the same graph keeps its own value, so audit all pens on graphs the faulty station saved, not just the one you noticed.

Keep reading

  • Troubleshooting

    Fix historian silent data gaps with a registry value

    The data is stored; the retrieval view of it is stale. On the Historian server, create the DWORD32 value UseFileCacheForRetrieval set to 0 under HKLM\SOFTWARE\ArchestrA\Historian\Programs\aahStorage, then refresh the trend, with no reboot required. Confirm the fix by loading a previously blank window before running any manual query.

    Edge computing and IIoT

  • Troubleshooting

    Historian trend empty: PLC tag collection troubleshooting

    A live PLC value proves nothing about what the historian stored. Work one known, changing tag through four boundaries — controller to data server, server to collector, collector to point, point to chart — and with a 2 s update rate, a 1% deadband on a 200 in span and a 24 in/h fill, a 20 min window must show about 4 stored events. Zero events means the collector; four events with a flat chart means the display.

    Edge computing and IIoT

  • Comparison

    Operator access to historian trends on night shift

    Put routine historian trend access in front of operators: prebuilt HMI or SCADA trend screens for the recurring questions, point selection over already-collected points for everything else, and a read-only web portal as the alternate route. Keep collection configuration with engineering. No permission setting retrieves samples for a point the historian never collected, so that gap is closed by adding collection, not by access rights.

    Edge computing and IIoT