Strategies to Reduce SQL Server Blocking

Additional Mid-Time Resolution Strategies

• If the transaction involves extremely important interrogations, replace them with faster ones.
  
  
• If user interactions –- such as message boxes –- take place during database transactions, then these must be eliminated! User interactions have lost absolutely nothing within database transactions. Make sure without fail that your client programmers are also aware of this. Programmers with a background in desktop database development are particularly susceptible to having frequent problems distinguishing between business transactions and database transactions.
  
  
• The same holds true for unnecessarily time-consuming processing within the transaction as much it does for more complex transactions. You could however also ask yourself the question, "Does this processing really have to take place within the transaction?"
  
  
• In cases where a large amount of data is transported from the server to the client within a transaction, this should be minimized where possible. The processing of x-amount of data is fundamentally just as fast at the client end as it as on the server. The bottleneck is as a rule the transport through the network. Therefore, always carry out the processing at the place where most of the necessary information is to be found. For example: if one needs only to know the total number of certain types of data sets (and not to have the data sets themselves), then using a "select count(*)" will always be faster than even the most wonderful algorithm at the client end (because the client first needs to obtain all the data via the network). If, on the other hand, an SP requires kilobytes of parameters, then the client may be considerably faster.
  
  
• If a snowball effect is present, then follow the strategies used against short-time scale blocks.

Long-time Blocking

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:

• Unintentionally non-closed transactions, and
  
  
• Endless loops within transactions

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.

Resolution strategy for Log-Time Blocking

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.

SQL Server is also very adept at recognizing and treating deadlocks, as you will find when you read the SQL Server documentation. Essentially, SQL Server does the same thing against deadlocks that the SQL Guard 2000 does against 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 that can be used to avoid 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.

A Summary of the General Strategies and Guidelines for Reducing SQL Server Blocking

1. Forbid user interactions in transactions.
   
   
2. Always keep transactions as limited in size and as brief as possible.
   
   
3. Use no unnecessary resources in transactions.
   
   
4. Carry out as little external processing as possible during a transaction.
   
   
5. Always select with the option "with no locks" except in cases where you have an important reason for doing otherwise.
   
   
6. Use row level locking.
   
   
7. Use "dirty reads" wherever possible (never make this option global, because it is not possible except with only a very few interrogations).
   
   
8. Performance tune your systems as much as possible.
   
   
9. Carry out necessary complex transactions such as invoicing at times when the user load is as low as possible.
   
   
10. Schedule maintenance tasks which put extra loads on the server (e.g. Full Backup, defragmentation of indexes) at times when the user load is minimal (nights, weekends)
    
    
11. Always select as little as possible and only as much as is necessary.

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. Contact him at: sancho@thsfock.de  Visit his website at: www.thsfock.de.

Published with the express written permission of the author. Copyright 2003.