We migrated some piece of old software to a new server. We used SQL Server 2008 Enterprise in the past and now we are using SQL Server 2014 Enterprise on a new machine, so it should be faster now.
The old software is legacy software and about to expire, therefore I don't want to put much effort in fixing it. But for some reason there is a C# function running a SQL query against the database for which I get the error message
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
All I read about that is, that I have to extend the timeout time by using CommandTimeout. But unfortunately everything runs under "context connection = true". Therefore, it would take quite a bit work to rebuild this function with an opportunity to change the timeout.
And I'm asking myself, why did this run on the old machine and way it won't on the new one. So it has to do something about the new machine or the new SQL Server engine. Is there any way to change the standard timeout of 30 seconds for a command in the .NET Framework or in the SQL Server?
Thanks a lot for any suggestions!
Ok, I didn't find a sollution for the problem, yet, but the timeout is not really the source of the problem. I gained access to the old system and run some tests and it turned out that the same function on the old machine with the old server software runs a lot faster, such that there is no timeout.
Hence, I have to focus on server speed and database tuning.
Thanks to everyone who occupied himself with this question!
Edit:
I found a solution to my problem, indirectly. I couldn't find out, why the execution of the statement on the new machine takes so long. But it turned out that the statement itselft uses table variables. I changed them to a local temporary table in the database tempdb. Now the execution takes less than one second instead of more than 7 minutes!
For me, it looks like a problem with some cache or a miss-configured SQL server. Unfortunately, I'm not really the server administrator and I will not twiddle with it. But I will mention it to the administrators. At least, the program runs now perfectly.