How to find slow query in which project / page /user client

Ignition
1

There is a slow query in my project that is taking more than 12 minutes to run. From the active query page in the database, I can see the following query:

SELECT a."tagpath", b."drvid" FROM sqlth_te a left join sqlth_scinfo b on b."id"=a."scid" WHERE a."retired" is null OR a."retired"=0

I have searched my project using keywords like "sqlth_scinfo", "sqlth_te", and "retired", but I did not find any query or script with these names embedded in my views. I assume that these scripts are system generated.

Could anyone suggest how I should debug this slow query issue and identify which session, project, or user triggered it?
If anyone can suggest how to improve it, that's even better.

image
image1493×416 22.6 KB

2

This is tag history. You are most likely requesting a lot of data in a table, trend, chart, etc.

1 Like
3

See Ignition Database Table Reference | Ignition User Manual for some information on the table descriptions.

4

Thanks for your reply.
But how can I find out which session/page requesting lots of data.

5

How many do you have with these types of components? Perhaps if nothing is showing up in the search, you could take a systematic approach and temporarily remove them one at a time until you find the one that is causing the problem. If nothing else, those components will be definitively ruled out as the problem.

6

Tidied-up query:

SELECT 
    a."tagpath", 
    b."drvid" 
FROM sqlth_te a 
LEFT JOIN sqlth_scinfo b 
  ON b."id" = a."scid" 
WHERE a."retired" IS NULL OR a."retired" = 0

From the Ignition Database Table Reference | Ignition User Manual

sqlth_te.tagpath: Path of the Tag in the Tag Provider, i.e., Folder1/tag1.

sclth_scinfo.scname: Name of the tag group. A value of "exempt" is used in cases where the execution rate is not recorded, such as tags set to an "On Change" sample mode.

This shouldn't take long unless you have millions of tags or a table index is missing or corrupt.

As a first step in debugging, paste the query into whatever tool you use to manage your database and see what the execution time is.

The next thing I'd do is check that the indexes exist using the same tool.

sqlth_te should have two indexes:

  • PRIMARY on the id column.
  • sqlth_tetagpathndx on the tagpath column.

sclth_scinfo should have one index:

  • PRIMARY on the id column.
7

It's production enviroment, I can't risk to do this.

1 Like
8

The index is present and the table is not corrupt. However, when the slow query occurs, running the script in MSSQL studio also gets stuck and does not produce any result. During non-busy hours, it takes less than a second to retrieve the result, which consists of 4500 records.

9

Sounds like your database server needs more resources.

10

OK.
You'll need to outline your configuration.

  • Ignition gateway is running on Windows? Which version of Ignition and which of Windows?
  • MS SQL Server is running on Windows?
  • Is SQL Server on a different machine or the same one as the gateway?
  • Are there any virtual machines involved?
  • What does Task Manager show on the database machine?
11
  1. Ignition gateway is running on Windows server 2019.
  2. MS SQL Server is running on Windows server 2019.
  3. SQL Server on a different machine as the gateway
  4. Are there any virtual machines involved , yes, both virtual machine.
  5. What does Task Manager show on the database machine - did not capature this informatiion, need server administrator to assist.
12

Is there a report from Ignition support me to apply more resource to my database administrator , similar to CPU/ memory usage report.
Can the report verify that the issue is not due to the application's architecture, but rather a bottleneck within the database itself?

13

No, database load issues are database brand specific, and are outside Ignition's scope. You need your database admin to help you further.