This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(316.8, 81%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDH%3C/text%3E%3C/svg%3E)
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.
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 7.000000000000001%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDZ%3C/text%3E%3C/svg%3E)
Hi @Darryl Hoar ,
What statements do you run? Will there be timeout problems when these statements are run directly in SSMS?
Best Regards,
Daniel Zhang
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 ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 21%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDB%3C/text%3E%3C/svg%3E)
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.
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).
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.
what kind of application is it? And how is it being used?
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.
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?
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.
I will.
Does Query Store generate large amounts of data ? Can I turn it on and leave it for day(s). Or is
it an hours thing ?
It's intended to be left on permanently, and has data size and retention settings you can customize if you need to.
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.
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.
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:
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.
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.
And the data is stored in the database so you can examine the Query Store from a backup.