Finding large historic granules

[Anonymous user]

📖 Home  Back  
Geo SCADA Expert is designed to handle individual historic files (granules) that load quickly from storage (see the online help article "Operational Limitations - Server"). Slow file loads may cause extended database locking when the historic granules are loaded, particularly on slower media such as magnetic disks.

Where possible, large granules should be identified and the cause of the abnormally high historic storage rate rectified. It may be advisable to remove the affected granules from the system to prevent performance degradation when searching, although this usually requires a server to be stopped and started. An alternative approach for events, alarm summary or configuration change log is to alter the stream size, although changing those may require significant downtime.

The largest granule for each stream can be seen at a glance in the Server Status tool or DBSnapshot in the Historic/Historian page, check the Largest File column.

This article covers a few further approaches to identifying large historic granules.

SQL Query

These queries will list the granule sizes and file locations ordered by size.
The queries select data from the historic index tables CDBHISTORICFILE and CDBEVENTFILE without having to load the granules themselves. However, if your historic data configuration includes use of the 'Index After' feature, then executing this query for historic data files will cause enumeration and indexing of files which may affect performance. In that case you may wish to perform the query on a backup or standby server.

Find large historic granules in the Raw Historic stream.

select top (10) f.stream, f.RecordCount, f.StartTime, 
'History\Historic\HdI' || formatvalue( f.stream/256 using '00000') || '\' || formatvalue( f.stream using '00000') || '\WK' || formatvalue( f.granule using '000000') || '.HRD' as filename
from cdbeventfile f 
order by f.RecordCount desc

Find large historic granules in the Event stream.

select top (10) f.stream, f.RecordCount, f.StartTime, 
'Journal\JnJ' || formatvalue( f.stream using '00000') || '\H' || formatvalue( f.granule using '0000000') || '.MWJ' as filename
from cdbeventfile f 
order by f.RecordCount desc

Locating the large streams of events is useful because a stream contains the events from a range of database item IDs. Geo SCADA also has a feature which counts and alarms on the number of events in a stream and for an item. Search the Help for "Define the Acceptable Limits for Historic Data Files".

To analyze the distribution of event files per stream:

SELECT MIN( STREAM ) AS "STREAM No.", COUNT( RECORDCOUNT ) AS "TotalFiles", SUM( RECORDCOUNT ) AS "TotalRecords", 
SUM( CASE WHEN RECORDCOUNT > 7500 THEN 1 ELSE 0 END ) AS "7500 plus", 
SUM( CASE WHEN RECORDCOUNT > 15000 THEN 1 ELSE 0 END ) AS "15000 plus", 
SUM( CASE WHEN RECORDCOUNT > 30000 THEN 1 ELSE 0 END ) AS "30000 plus", 
SUM( CASE WHEN RECORDCOUNT > 60000 THEN 1 ELSE 0 END ) AS "60000 plus" 
FROM CDBEVENTFILE GROUP BY STREAM ORDER BY "STREAM No." ASC

To analyze the distribution of event files over time (day base):

SELECT MIN( FORMATVALUE( STARTTIME USING 'YYYY-MM-dd' ) ) AS "Date", COUNT( RECORDCOUNT ) AS "TotalFiles", 
SUM( RECORDCOUNT ) AS "TotalRecords", 
SUM( CASE WHEN RECORDCOUNT > 7500 THEN 1 ELSE 0 END ) AS ">7500", 
SUM( CASE WHEN RECORDCOUNT > 15000 THEN 1 ELSE 0 END ) AS ">15000", 
SUM( CASE WHEN RECORDCOUNT > 30000 THEN 1 ELSE 0 END ) AS ">30000", 
SUM( CASE WHEN RECORDCOUNT > 60000 THEN 1 ELSE 0 END ) AS ">60000"
FROM CDBEVENTFILE
GROUP BY FORMATVALUE( STARTTIME USING 'YYYY-MM-dd' )
ORDER BY "Date" ASC

"DIR /s"

"DIR /s" can be used to achieve similar results. Use the "DIR /s" command from the root of the historic folders and pipe the output to files.

This could be searched to identify large files.

Go: Home Back