The following categories emerge as a result of this division:
- Short-time Blocking: Average duration less than three seconds.
- Mid-time Blocking: Average duration ranges from 3 seconds to a maximum of 10 minutes.
- Long-time Blocking: Average duration more than 10 minutes.
- Deadlocks: In theory, there is no limit to the duration, and because of this, SQL Server automatically terminates one of the blocked processes.
These four different types of blocks are discussed below in terms of their possible causes and in reference to the appropriate strategies for resolving them.
Short-Time Blocking
Short-term blocking is inevitable in multi-user databases. Normally, they represent no problem for the operation of an application. Nevertheless, these types of blocks should be investigated in cases where they have begun to occur to only a few users or where they occur very frequently. If these blocks appear frequently, it can lead to a snowball effect and eventually paralyze the entire system.
Even in cases where it is likely that the problem cannot be completely solved, one should nevertheless at least attempt to defuse it.
Causes of Short-Term Blocking
Short-time blocking, which occur very frequently, can be traced back to the following basic problem: the total performance capacity of the system is not enough for the current user load.
Resolution Strategies for Short-Term Blocking
Such problems can best be met by carrying out an extensive performance tuning of the server and of the application. If this situation does not occur until there is a very high user load, then one should begin the investigation by examining the server and the network.
However, if this situation begins to occur with low user load levels, then one should investigate whether certain use cases of the client application are able to block one another or even themselves in conceptual terms. If this should be found to be the case, then an investigation needs to be carried out to determine whether the probability of the occurrence of this unwanted situation can be reduced through atomic transactions or the use of “row-level locking” or similar strategies.
Mid-Time Blocking
This type of scale block is the most difficult to find. It lasts long enough to bring the operation of an application to a stop, but at the same time is still generally too short for manual analysis. Support for this ability is available with THS Software’s SQL Guard 2000. A demo version of SQL Guard 2000 can be downloaded free-of-charge from http://www.thsfock.de/.
Causes of Mid-Time Blocking
The possible causes for this type of block are the following:
- Transactions which are (too) complex.
- Extremely important interrogations in a transaction.
- Non-error-free interrogations in transactions (e.g. unintended cross-joins).
- User interaction within transactions (e.g. message box “Should data really be deleted?”).
- Unnecessarily time-consuming processing within transactions.
- Unnecessarily distributed processing with high data transport volumes.
- Snowball effect of short-time scale blocks.
Resolution Strategies for Mid-Time Blocking
Once one has established which transactions are blocking other processes –- e.g. with the help of SQL Guard –- a determination must be made as to which type of error is present.
The following questions emerge in cases of complex transactions:
- Can the transaction be simplified?
- Can the transaction be divided up into smaller parts?
- Can the transaction be greatly accelerated through performance optimization?
- Can the transaction be carried out at a later time (e.g. a nighttime job)?
- Could the transaction also use separate resources (e.g. temporary tables) in order to carry out the actual modifications in concentrated fashion if successful?
- Can the data transport within the transaction be reduced (e.g. swapping processing out into SP’s)?
- Does the transaction block resources unnecessarily (e.g. a SELECT which locks lookup tables)?
These questions offer approaches for solving the problem. Should it happen that all of the questions are to be answered in the negative, then one is faced with the choice of either accepting the situation as it is, or of thinking through the business processes to find a way to replace the problematic transactions with others as necessary.