How do you choose a SQL statement timeout?

Question

We have a huge (part-VBA, part-.NET-based) LOB application, with thousands of (different) SQL statements littered all over the code¹. Currently, there is one global "Query Timeout" that can be configured. Since the application (among others) allows the user to create quite complex reports, this default timeout is set very high (5 minutes).

This, however, leads to problems when, during normal operation, undetected deadlocks² occur: All machines involved freeze for 5 minutes, until the timeout resolves the issue. So, clearly, different timeouts are suitable for different situations: For a complex report, a few minutes might be OK, whereas for querying a single, indexed database value, a few seconds should suffice³.

What's a good strategy to implement this? My first idea would be to categorize SQLs into "fast", "medium" and "slow" and, thus, have three different timeouts that can be configured. What are your experiences with this issue? How have you solved it and how did it work?

---

_{¹ Yes, I know that there are object-relational mappers available, but (1) we do have a lot of legacy code and (2) I don't think it makes a difference to the question.}

_{² An undetected deadlock is a deadlock that is not resolved by the database engine (for example by detecting the deadlock and aborting one of the transactions involved). Such deadlocks can occur under certain circumstances (ask me, if you want details, but I don't think it's relevant for the question).}

_{³ I know that such a DB access should only take a few ms, but the single, quick query might need to wait for a resource to be released by a longer-running transaction.}

Answer 1

For a similar situation I once made a concept, but never realized it. However I have it in my drawer, ready to be pulled out any time. I cannot refer to some experience for the following solution, but maybe it helps you.

A problem is to categorize and maintain the mapping between SQLs and timeouts. Suppose that the user need to categorize it then users will have the tendency to choose the category "slow" just to be sure. Suppose that a statement has been optimized or an index has been introduced, then you also need to think about to choose another category. The point is, that the mapping can be hard to maintain.

One solution for this is an auto-tuning capability: There is just one global maximum timeout. Every time a SQL statement is used, the query time is recorded and a new threshold is calculated for the next time the same SQL statement is executed. A rule could be the average of all previous query times plus 20 percent tolerance.

Additionally there is a retry mechanism: If a SQL statement reaches the threshold it will be retried with the global maximum timeout. The query timeout history must be then deleted. This allows automatic readjustment for SQL statements.

You can mix that with your approach where there are four categories: "fast", "medium", "slow" and "auto".

Answer 2

Sounds like a reasonable approach, especially if paired with a profiling tool.

I like the following excellent tools:

• https://sites.google.com/site/sqlprofiler/
• http://nhprof.com/
• http://efprof.com/

Answer 3

If you are going to categorize the SQL statements you might as well make each one it's own category. That's easier to do correctly for thousands of statements and you don't have to change your code when the relative runtimes change based on changes in the database (something got a lot bigger/smaller).

Do the mapping to timeouts in a separate step. You could either do it manually by maintaining a list of SQL statements for each timeout value or do it automatically based on a runtime history of that statement.

Answer 4

First thing, try to put all sql into procedures and not in the code. It is better this way from a maintainabilty and performance point of view. It also makes profiling and debugging easier as you can isolate them.

The next thing to remember is that locks occur only when data is being modified (because only 1 connection can be made to data/table that is being modified, multiple can read), so you should try to isolate updates (inserts/deletes/updates) from transactions that read. You should look at your updates and make sure that they are running efficiently, which means makeing sure the data structure is correct, indexing is optimised and maybe making sure that disk is fast. Also try and update small amounts of data at a time (if possible) as this opens up more 'windows' for other queries to run.

Now look at your queries. Look for the ones that are executed a lot and the ones that take a long time and look at optimising them. Check that they are only getting the data you need and that your indexes are a good match for the join (where) conditions.

Next think about your data, is there data that can periodically be hived off to an archive table? is there data that is redundant that can be periodically removed? are your key fields text types that you be changed to intergers? With databases, less is more, and matching integers is faster than strings when joining/filtering and indexes == performance.

Next think about your application, is it possible to run the long running reports or updates when no other activity is taking place? Do the long running reports include updates that could be done elsewhere (split up the transaction into smaller transactions)? Can you batch updates so they do not run when there are reads (overnight)?

The next item to look at is physical disk. Check data is not fragmented, that there is no contention for disk resoruces and disk is as fast as possible (maybe try a different replication/raid)

Now you can thinkg about setting timouts. If you allow long timeouts, you will get locks if you have long running queries - even if the others have short timeouts. But short timeouts will mean that long running queries will never finish. This is a bit "art" where you will need to make changes to settings and try them out to see what works. There is not rule that can be applied other than you will need to take an iterative approach looking to see what works and what needs to be tuned.

In summary, optimise your data structure, optimise indexes, optimise quesries and speed up/break up updates (inserts/deletes/updates). Try to keep long running updates away from reads (or other updates), becase these are casuing locks you see, hence timouts.

Answer 5

My view on this(no phun intended) would be to look into options for introducing an analysis engine. This moves the heavy lifting for creating the extensive reports to another instance/process of your database and thereby not mixing with the crud operations of your regular users. Hence the blocking behaviour will be reduced. It will also allow you to specify different command timeout properties for these instances and thus being able to chop this 5 minute to something more appropriate for crud operations.

I understand however that this might introduce a great pain, i.e. to figure out what queries should go where, but by separating the load you will be able to better address the expectations of the systems users.

Answer 6

well if you have SQL statement all over your code the first thing you should try to do is move them to stored procedures to make maintenance easier, and you can get a profile of how often each query is run and how long it takes much easier, i believe this is built in functionality for sql server if that is your DB. Keeping your queries inside your code is going to make it much harder to diagnose which queries are the problem and fix them. There are several things you can do to improve your apps performance. You can identify the slow queries and attempt to further optimize them. If some queries aren't time sensitive you can create views that are updated on some interval. You should also check to see if the deadlock problems you are getting are on tables that don't necessarily need to be locked by every query, like mapping tables that aren't changed often. Since you are using .net if you do identify a query giving you a lot of trouble that you can't seem to fix you could fairly easily try using linq on that query as well.