My technical skills
My picture album
My code library
Search my site
Sign my guestbook
SQL Server Articles New
No reasonable strategy for the resolution of concrete blocking problems can be found until one has determined the precise nature of the scale block. The most obvious criterion for distinguishing between different scale blocks is their average duration. The following categories emerge as a result of this division:
average duration less than three seconds
average duration ranges from 3 seconds to a maximum of 10 minutes
average duration more than 10 minutes
in theory, there is no limit to the duration
These four different types of scale blocks are discussed below in terms of their possible causes and in reference to appropriate strategies for resolving them.
Short-time scale blocks
These kinds of situations are inevitable in multiuser databases; normally they represent no problem for the operation of an application. Nevertheless, these types of scale block should be investigated in cases where they have begun to occur to only a few users or where they occur very frequently.
If these scale blocks appear very frequently, it can lead to snowball effects 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.
Short-time scale blocks 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.
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 already begins to occur with low user load levels, then one should investigate whether certain use cases of the client application are able to block either one another or even themselves in conceptual terms. If this should be found to be the case in a concrete situation, 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 scale Blocks
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 is available with the THS software SQL Guard 2000. A demo version of SQL Guard 2000 can be downloaded free-of-charge fromhttp://www.thsfock.de.
The possible causes for this type of block are the following:
Once one has established which transactions are blocking other processes - e.g. with the help of SQL Guard - determination must be made as to which type of error is present.
The following questions emerge in cases of complex transactions:
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.
Additional resolution strategies:
Long-time scale Blocks
These blocks are very similar to mid-time scale blocks. As a general principle, they can also have the same causes as the mid-time scale blocks.
In addition, they can have the following causes:
The following applies to both causes: their durations could fall within the mid-time scale block range if, for example, transaction timeouts have been defined or if the end user shuts down his client.
The resolution strategy for both of the additional causes is clear: eliminate the error(s).
The special case of deadlocks
In view of the fact that a great deal of information concerning deadlocks is readily available in the relevant literature, they will be treated only briefly here: deadlocks are a special case in terms of scale blocks, because there is no unambiguously "guilty party" in such situations.
The MS SQL Server is also very adept at recognizing and treating deadlocks, as you will find when you read the MS Documentation. Essentially, the SQL Server does the same thing against deadlocks that the SQL Guard 2000 does against scale blocks: it terminates a process. The only difference is that the SQL Server recognizes no guilty party when faced with a deadlock, and therefore selects a "victim".
One can never completely exclude deadlocks from larger systems. The strategies used to reduce their quantity are the same as those which can be used to avoid scale blocks in general.
In addition, one can take care to ensure that there are no transactions present which require the same resources in the reverse order.
General strategies and guidelines for making scale blocks as infrequent as possible
Sancho Fock has been a freelance software developer since 1995. His professional area of concentration is the object-oriented software development of applications using two or more layers. In his numerous projects he has accumulated experience with various RDBM's such as MS-SQL Server, Sybase Adaptive Server and Informix.
He has had intensive experience with MS-SQL Server in Versions 6.5, 7.x and 8.x (2000). He has become familiar with MS-SQL Server in his various roles as a T-SQL developer, an applications developer and as a database administrator in a pilot environment, among firstname.lastname@example.org http://www.thsfock.de