question

DarrylHoar-5879 avatar image
0 Votes"
DarrylHoar-5879 asked DavidBrowne-msft commented

SQL Server 2019 Express Edition with C# commandtime outs starting to occur frequently.

I have created a c# application using .NET 4.5.2 framework. It uses a SQL database I created in SQL Server 2019 express edition. The problem I am experiencing is now that I have been using my application for some months and the database has some size, the Sql commands sent from my c# app to Sql Server and failing to complete with commandtimout errors. If I specify a commandtimeout value (ie override the default 30 seconds) it works, but it bothers me that this happens. Am I going to have to continually bump the commandtimeout value as the database grows ?

It isn't one table or stored procedure that is getting timed out, it is all of them.

Ideas and thoughts greatly appreciated.

sql-server-generaldotnet-csharpsql-server-transact-sql
· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This may be happening from a database design that is not optimal for what it's being used for. Have you looked at the execution plan in SSMS (SQL-Server Management Studio) ?

Possible solutions may include a redesign of the database (which means refactoring your SQL), adding indices to start with. This all can be done by making a copy of the current database or creating a backup of the current database before any changes are done.

0 Votes 0 ·

I am not a SQL guru. I may in fact not have a good design on my database tables.
One of my big tables the index (and key) is an identity field. The c# application does
not use this field when searching, filtering, updating, etc. Is this a problem ?

0 Votes 0 ·

Could be. If you're scanning large tables over and over that could cause the symptoms you are seeing. So turn on the Query Store and look for expensive queries.


0 Votes 0 ·
Show more comments

Hi @DarrylHoar-5879 ,
What statements do you run? Will there be timeout problems when these statements are run directly in SSMS?
Best Regards,
Daniel Zhang

0 Votes 0 ·

The timeouts occur when the the c# sqlcommand default timeout (30 seconds) expires and terminates. Any of the commands typed into SSMS will complete,
just in a time frame longer than 30 seconds.

0 Votes 0 ·

Thanks for the information. Should the database backup also be doing this? It was a sudden change.
The backup command is: BACKUP Database databasename TO DISK = destination. In c# code it command
times out (just started doing it). Issue the command in a query window of SSMS and it takes roughly 35 seconds
which is slower than normal (don't have previous time).

0 Votes 0 ·

Are you saying that it took 35 seconds to backup the database?

Given that you are on Express and thus your database cannot be more than 10 GB that is a tad slow. On what sort of machine are you running this? And particularly what sort of disks?

Unfortunately, from what you have told us, we can only give generic advice like turn Query Store to find your slow queries. But if you say that backups are taking 35 seconds, there may also be a hardware issue.

0 Votes 0 ·

what kind of application is it? And how is it being used?

0 Votes 0 ·

It collects data from a custom device and stores it in a sql database. Then the user runs the application to look at or alter the data.

0 Votes 0 ·

So when is it blowing up? Is it when the user is using the app? If so, how many users are using the app and doing it concurrently?

0 Votes 0 ·
DavidBrowne-msft avatar image
0 Votes"
DavidBrowne-msft answered

You'll need to troubleshoot your query performance to determine why your queries are running so long. So turn on the Query Store and look for long-running and expensive queries.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

In addition to what David says, also check for blocking. Although, if the problem goes away with extending the command timeout, blocking may not be the primary reason. Then again, if there is an update operation that takes a long time, that is going affect all queries.

It is actually quite easy to determine this from the Query Store data, because if the execution time is 35 seconds, and the CPU time is just 1-2 seconds, it is very likely due to blocking.

In the end, you will need to start to considering better indexing for your database.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrylHoar-5879 avatar image
0 Votes"
DarrylHoar-5879 answered DavidBrowne-msft commented

Interesting side note: No changes to the database and the application worked without any changes to the commandtimeout value. Strange.

I'll do the following:
1. Evaluate the current table indexes and refine.
2. Try and examine the Query Store data to determine slowness cause(s).

Since the computer is not close to me, I can't do the plan examination which I assume I need to do during execution.

Thanks.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Query Store is a kind of flight recorder, so you can access data after the fact. By default, Query Store stores data in one-hour intervals, so it is possible to narrow down on a certain interval.

0 Votes 0 ·

And the data is stored in the database so you can examine the Query Store from a backup.

0 Votes 0 ·