Hi humans
IT did a server migration of the Ignition database yesterday.
The tag historian data seems to have moved ok and can still be accessed by charts etc, but this is being really slow, taking up 99% of CPU, and stressing out the IT person.
He's had a look into it and has emailed as follows:
Found the root cause of the problem , a missing index. Based on the information shared earlier in this email exchange I looked at the execution plan for the query; and although very light in its output (a single record), how it gets it is the killer (literally)…
As the where clause has the condition tag id in () and t_stamp > its very hard for SQL to quickly find the records as it’s looking for a collection (by default) and not a set number of records (determined by the t_stamp > ) so it has to start scrolling through the records one by one asking “does it meet my criteria” and as shown below.. it has a lot of records to get through. Now, it’s doing this every time something is refreshed and as these tables have records added at a potentially rapid rate, then it’s only going to get slower and slower… or take up more and more resources.
How do we solve it.. we must create the index recommended by SQL
/* USE [IGDB] GO CREATE NONCLUSTERED INDEX [IX_SLG_SAMPLE] ON [dbo].[sqlt_data_1_2024_07] ([tagid],[t_stamp]) INCLUDE ([intvalue],[floatvalue],[stringvalue],[datevalue],[dataintegrity]) GO */
Once done (and it did take a while), SQL Server can now quickly locate those records and doesn’t need to trawl through the whole table. It does mean the database size will grow due to the additional index information, but it drops the CPU down to 25% (until PBi kicked in)
The big challenge is, these tables are created (by the application I guess) every month, thus this newly created index will not exist in the new table until we create it so we need to come up with a plan to either get these indexes added by the vendor (highly unlikely) or create them ourselves automatically (SQL Job running first day of each month).
I’ll let it run for the remainder of today and see how it’s performing.
"
Since it wasn't killing the CPU to run history queries before the migration, I assume:
- That the historian tables are in fact indexed by some mechanism; and
- That this can be restored in some way...
Does anyone know what might have happened and how I might be able to restore the indexing without the aforementioned rigmarole?
Thanks
Cameron