random command timeouts in custom service

Question

I am using Visual Studio Professional 2019.

I have created a custom service that collects data and writes it to tables in a sql database.

Sql Server version is 2019 if it matters.

I have created custom Stored procedures in the database. I call these stored procedures to save data to the tables. For instance, I have a stored procedure to add a record to the table. From my custom service, build parameters to send the data to the stored procedure. The problem I am experiencing is that for a period of time, the executenonquery will fail with a command time out. Command Timeout is set to 120 so it should not be timing out. How do I figure out the cause? Given time, it will self correct, and start working again. Can't figure out what is causing the problem. Thanks for any help.

Answer 1

Two possible reasons:

1. Long execution time due to bad query plan or similar.
2. Blocking. That is, another process is holding locks that blocks the other process.

One way to determine what is going on is to use my beta_lockinfo, which you find at https://www.sommarskog.se/sqlutil/beta_lockinfo.html. If there is blocking you will see columns with double exclamation marks in them. You should run beta_lockinfo when you are experiencing that you service stalls.

By the way, how much RAM do you have in your machine?

Answer 2

the executenonquery will fail with a command time out.

You have to check your stored procedure and may optimize it. Check it execution plan, if suitable index(es) are used, etc.

You can use https://www.brentozar.com/pastetheplan/ to share the execution plan.