ETIMEOUT error - MSSQL PLUS

General
1

Hi,

We are using MSSQL PLUS node to connect MSSQL database server. Calling few stored procedures from Node RED.

We are getting an error like Timeout: Request failed to complete in 15000ms in some random scenarios for a particular stored procedure (Say STPR).

STPR is called in very frequently from Node RED. Is there any solution for this issue? We are getting this error once in a 3 days or a week.

Regards,
Sathishkumar C.

2

There are any number of things that might be the cause of that.

  • The node has a fault - you need to contact the node's author
  • You are running a proc that takes >1.5sec - again, ask the author of the node if the timeout can be altered
  • You have other things running against the database that is sometimes causing it to slow down
  • You have an intermittent network issue

There are probably others as well. If you have access to the sql server, monitor it to see if it is struggling. Also monitor the device running Node-RED to see if it is getting performance hits from time to time.

3

(@TotallyInformation ...15000ms - that's 15 sec not 1.5 sec - but yes - all the above.)

4

Doh! OK, so I've not had my morning coffee yet! (I know, late start, but it was a looooong week).

And that would be a hell of a SQL query.

5

Since it works most of the time & your problem is intermittent, your issue is likely external & could be one of a few things...

  • It could be you are returning a lot of data and sometimes it take a little bit longer.
    Question: How much data (count of rows) does this procedure return?

  • If could be contention / locks on the table(s).
    Question: what is this stored procedure doing? Is it reading / writing to a table that maight be read / written by another application or process?

Have the table(s) got good indexes? Perhaps there is a JOIN or WHERE clause in the stored procedure that are slow to return results due to lack of good indexes?

Have you ran these queries that the stored procedure runs through query analyser / profiler?

Does the SQL server do other tasks? Perhaps the CPU is occasionally 100% causing slow queries?

6

Thanks for the reply.

  1. How can I justify node has a fault?
  2. Timeout can be editable. But I have 15000 ms (15 sec). Stored Procedure returning result in less than 2 or 3 seconds and It returns 1 row and 3 column.
    3.I don't think so.. with other things running against the database because I am getting timeout error with same stored procedure.
  3. Both database and node red installed in same system, so network may not be a issue.
7

Q1:
My stored procedure returning 1 row and 3 columns.

Q2:
It is simply getting row count of one table and returning as a result.

We are not using JOIN on any tables.
SQL query look like...
SELECT COUNT([ID]) FROM [sampleDB].[dbo].[sampleTable] WHERE [AssetID] = @AID AND [Status] = '1'

8

Few questions to try help resolving this (please try to answer all)...

  • what system is node-red and sql server running on?
    • OS?, hardware?
  • is the CPU hitting 100% at any time?
  • how frequently are you calling this procedure?
  • how frequently are records in [sampleDB].[dbo].[sampleTable] being updated or inserted?
  • how many rows are in [sampleDB].[dbo].[sampleTable]?
  • do you have an index against AssetID and Status?
9

Also, is it just a temporary issue, does it work ok when the query is retried?

Also look the SQL server log to see if anything relevant there at the time of failure.

10
  1. Windows 10 / 32 GB RAM / 8 Core
  2. It never hitting 100% of CPU usage.
  3. Insertion and Updation is completely depends on the end user. Roughly we can say, 10 to 15 times in 60 seconds.
  4. [sampleDB].[dbo].[sampleTable] having less than 20 rows.
  5. No. We don't have a indexing on any columns.
11

Yes Coln. It seems to be a temporary issue. But we are getting 3 to 5 times in a week. We were not able to figure out root cause of this.

12

can you share your flow? (perhaps something odd is occurring)

also, can you capture the full msg object when error occurs ?

if you can access console log, paste that too?

PS: when pasting logs / code press the </> button first then paste

13

I've had this happen a few times. What is the max pool size in your MSSQL config? Ours ended up set to 1 at some point, and I'd get that error whenever two queries occur at the same time. I bumped it to 5 and the issue went away.

I am thinking that if

  1. [sampleDB].[dbo].[sampleTable] having less than 20 rows.

and

Stored Procedure returning result in less than 2 or 3 seconds and It returns 1 row and 3 column.

There is something not quite right. We have a similar./lower spec SQL server (12 Core/6gb ram) on our network, and can easily run far more complicated queries that amount of time. Sounds like the server is being hammered, or has a config issue.

Other things to try:

Bump the timeout to 60s say, just to see if the query ever does return.
Change it to a query directly in the SQL node, rather than a stored procedure.
Run the query a bunch of times in MSSQL Management Studio, see if it ever happens there.

1 Like
14

DB Config 1
DB Config 1545Ă—877 89.3 KB
DB COnfig 2
DB COnfig 2529Ă—867 111 KB

I am attaching database configurations here.

15

I think, we are getting this error calling that particular Stored Procedure in high frequency. Anyway we tried to increase the "Max Pool Size" to 10. Still we are getting the same timeout error.

16

I think it is most likely down to the fact it is a stored procedure then - See this about stored procedures and parameters. tsql - Parameter Sniffing (or Spoofing) in SQL Server - Stack Overflow

To test that, I'd try my no.2 idea above - Change it to a query directly in the SQL node, rather than a stored procedure.

17

You still haven't shared your flow.

How are you calling the procedure? Using query mode? Or procedure mode?

Did you try upping the timeout to see if it still happens?

18

The connection should never take more than a second I don't believe. I would reduce the connect timeout down to 1 second (1000). It is better to see connection errors sooner.

19

Actually it is in "Query" mode with following SQL commands.

EXEC sampleProcedure @input1 = 'data'
20

Try Procedure mode like this...

image
image721Ă—598 16 KB