Proof that the SQL Server is not at fault for the timeout
1
vote
2
answers
350
views
We have been experiencing (perceived) issues with the write performance of our MS SQL Server for some time. As I believe it might be on the client side, I am looking for evidence to refute the accusations against the SQL Server. Here is the situation: A NodeRed Flow collects data from a machine and writes it to an SQL Server in the end. If writing is not possible, the data is buffered in Redis until it can be written again. As mentioned, for some time now, the buffers have been filling up repeatedly, going from a normal workload of about 50 messages to over 1000 - 30000 messages. After several minutes (10 - 60), the buffers are emptied again. The only error message received is a timeout within 2 seconds. The writing node is more or less a black box for us.
What we have done so far: When the problem occurred, we set up monitoring of the buffers. Additionally, at the time of increasing buffers, we could look at the SQL Server and found nothing unusual in the Resource Monitor of the Windows Server... CPU, Disk, and Network were at a normal operating level. Only when the buffers were emptied, did we see a change on the SQL Server.
On the monitoring (Telegraf + Grafana) of the SQL Server, during that time, you can see a Write Latency of < 1 second on the database.
A restart of the SQL Server was also performed. However, this did not affect the full buffers; they neither increased nor decreased.
As the database manager, I am now seeking evidence that the SQL Server is not responsible for the timeout in the data flow. In addition to the option of comparing the monitoring, I would like to provide proof that during the time when the buffers are filling up, the client is not sending any requests to the SQL Server.
Are there any possibilities to achieve this with the existing resources?
Thank you and best regards, Martin
Asked by Martin
(11 rep)
Feb 5, 2024, 12:38 AM
Last activity: Feb 5, 2024, 09:14 PM
Last activity: Feb 5, 2024, 09:14 PM