SQL tag update very slow

image
i have a table that have 1 line, this top1 line will refresh every second by another software
in SQL server side, if i execute select the update query it will response very quickly

But in the ignition side , i found the update is very slow, sometimes 1 minutes, sometimes 3 minutes.
my setting update rate is 1s


i found not only this tag, but also other SQL tag have the same issue

i check the scada server task manager, seems the sql server memory and Cpu is very high.
i don’t know what block the query update,here is the ignition server status

In the gateway if you navigate to Status > Databases > Connections > and then Details for the current database

Do you see any entries of long running queries and how long it is taking them to execute? I know its a small table, but if for some reason the queries are taking a while to respond to Ignition, you may see that they are bottle-necking. The “Other software” and Ignition may be fighting to get access to the table at the same time and Ignition losing the battle.



seems there are many query are carry out

i have defined many tags in the tag tree, i have define them scaning speed is 240s
but i can’t understand why they are still query very quick if i observe at the screen Status>Databases>Connections

You’re sure it’s 240 seconds? The time unit for tag groups is in milliseconds (ms).

You should also try the query in the SQL Management Studio. There you can see how long it takes, and you can even check the execution plan and take a look at what indexes are used.


i found a very big query, i have already disable that tag, but still not stop this query , perhaps in some other place have a query i not found

Yes, you should search what is calling that query.

I see you have a very high “plan count” too. This happens when you don’t use JDBC variable replacements (the ?-signs, or variables in names queries), but rather use text replacements (like expressions in the query).

However, you can force parametrisation on the SQL server with this command. It should make all your recurring queries a bit faster:

ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED

And for that query with just 6 executions, but a high CPU time, you’d best check the execution plan.


after i force at the sql server side
the plan Count number between 0-6

and the other query tag update speed is normal.


when i check the execution plan,here is the picture, still have some force solution?
image