Option available for individual query or sproc level timeout

Sanjoy Jana 21 Reputation points
2022-01-24T12:53:32.043+00:00

Hi Team,

Greetings!

I'm currently working on a module where I am getting query timeout against a comparatively big data set. The data set size vary at the runtime and kind of bulk update or retrieval. I already tried DBCommand.CommandTimeout to 1800, did not help in certain cases.

I was trying to write an sproc around this which will first determine the volume of data (by the bulk xml size or by querying the Count()). Can anyone please let me know how to set query timeout through sql in sproc.

Thanks in advance!
Sanjoy.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2022-01-24T13:37:21.847+00:00

    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

    https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option

    (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

    167827-image.png

    167855-image.png

    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).

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-01-24T14:21:14.483+00:00

    SQL Server does not have a timeout. This is a client level setting. If 1800 is not enough, you need to increase your client timeout to a number which works for you.

    1 person found this answer helpful.
    0 comments
  2. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2022-01-25T03:09:36.843+00:00

    Hi @Sanjoy Jana ,

    >>> Can anyone please let me know how to set query timeout through sql in sproc.

    Agree with Tom.

    Quote from the similar thread Is it possible to set a timeout for a SQL query on Microsoft SQL Server

    >As far as I know, apart from setting the command or connection timeouts in the client, there is no way to change timeouts on a query by query basis in the server.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments