Time Out for SQL Timeouts

here are two common SQL timeouts:

  • the connection timeout
  • the command timeout

To anyone familiar with the web.config file located at the root of an ASP.NET application, the connection timeoutappears inside the database connection string that Kentico code relies on:

<add name="CMSConnectionString" connectionString="Data Source=SERVERURI;Initial Catalog=KENTICODB;User ID=SQLUSER;Password=SQLPASSWORD;Persist Security Info=False;Connect Timeout=60;Current Language=English;" />

The important part is this:

Connect Timeout=60

Connect Timeout directly maps to the ConnectionTimeout property of the SqlConnection class used by an ASP.NET application to connect to a database. This simply means the time in seconds that the application waits for the connection to complete. If it completes, nothing special happens and it begins executing commands. If it does not and reaches this time limit, a timeout error appears and probably our site does not load.

That generally does not happen outside of network weather or a problem with the credentials in the connection string. The more common issue is when a command times out, because while a connection is one and done, commands can be complex and can occur hundreds of times over the course of a user visiting a page on the site.

A SQL command can be anything: a read-only SELECT, a modifying UPDATE or DELETE, a change to the structure of a table such as dropping an index, or any other permutation of valid SQL. The more complex the command, the longer it could take.

The SQL server takes steps to optimize queries to ensure they are as fast as they can be. The design of the database can also speed up queries with proper indexes and foreign keys. Kentico tables are already optimized with the appropriate indexes for common scenarios and we can assume that the SQL server won't make a suboptimal query, so we are left with the most efficient path taking some amount of non-trivial time to complete.

If a SQL command does not complete within the command timeout, we see the dreaded Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

SQL command timeout, can I change it?

The answer is: Yes. It is as simple as adding this to the <appSettings> section in the root web.config:

<add key="CMSSQLCommandTimeout" value="100" />

Where the value is in seconds. The default is 30 and a value of 0 has no effect.