Good day,
Option available for individual query or sproc level timeout
(1) You can configure the remote query timeout in the Server Configuration Option
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO
(2) The connection timeout is configured in the connection string in the client side
Using SQL Server Management Studio:
you can configure the connection timeout parameter in the connection string, using these steps: Open the connection windows -> click on Options>>
-> move to the Connection Properties
tab -> change the value of the Connection Time-out
Using .Net connection class
You can configure the parameter connection.ConnectionTimeout
Using direct connection string
change the parameter of the time out. FOr example in the following connection string the configuration is Connection Timeout=30
string connStr = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";
What next?
You should check the source of the timeout: there are multiple reasons that can lead to timeout and some of these you can control or improve. For example
(1) query that normally process data for long time.
This can be a normal scenario which related to the amount of data the server need to process. In this case you should raise the timeout configuration in the client side.
(2) issue with the query plan (the way the server process the query). You should improve your queries to reduce the time of execution and confirm that the server does not lack of resources, confirm that the statistics is updated and so on...
(3) Check for waits and locks which block the execution can be related to other queries (if the execution is slow only from time to time then this might be the reason).