SQL Server에서 Blocking을 줄이기 위한 전략!!

출처 : http://www.sql-server-performance.com

읽어보시면 알겠지만, 많은 도움이 됩니다.
영어를 잘 못하는 관계로 해석 되는거 같은 부분만 번역 달아 놓겠습니다. 혹시 틀린 부분이 있으면 답글 부탁드립니다. 바로 정정하겠습니다.
번역 안되어 있는 부분 번역해 주시면 바로 원문에 반영하겠습니다.

Strategies to Reduce SQL Server Blocking
(SQL Server Blocking을 줄이기 위한 전략)

by Sancho Fock

No reasonable strategy for the resolution of SQL Server blocking problems can be found until one has determined the precise nature of the block. The most obvious criterion for distinguishing between different blocking issues is their average duration.
The following categories emerge as a result of this division:

Short-time Blocking: Average duration less than three seconds.
(짧은 시간 블러킹 : 평균 3초 이하의 수행 시간을 갖는 트랜잭션)

Mid-time Blocking: Average duration ranges from 3 seconds to a maximum of 10 minutes.
(중간 시간 블러킹 : 평균 3초 ~ 10분 이하의 수행 시간을 갖는 트랜잭션)

Long-time Blocking: Average duration more than 10 minutes.
(긴 시간 블러킹 : 10분 이상의 수행 시간을 갖는 트랜잭션)

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.
(만약 블러킹이 매우 자주 발생된다면, snowball 효과로 인하여 (이게 뭔지 아시는 분 ^^;;) 전체 시스템의 마비를 가져오게 된다.)

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 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?").
(트랜잭션 내의 사용자 interaction(예를들면 '이 데이터를 지우겠습니까?'라는 메세지 박스)

- 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.



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

(실수로 transaction을 종료하지 않는 경우나 트랜잭션 안의 무한루프)

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
(SQL 서버의 블러킹을 줄이기 위한 일반적인 전략과 가이드 라인)

* Forbid user interactions in transactions.
(트랜잭션 안에 사용자와의 interaction(입력을 기다린다던가 하는 작업)을 금한다.)

* Always keep transactions as limited in size and as brief as possible.
(트랜잭션은 항상 가능한 한 짧은 시간동안 최소한의 범위를 유지해야 한다.)

* Use no unnecessary resources in transactions.
(트랜잭션 안에서는 불필요한 리소스는 사용하지 않는다.)

* Carry out as little external processing as possible during a transaction.

* Always select with the option "with no locks" except in cases where you have an important reason for doing otherwise.

(다른 중요한 이유로 뭔가를 수행하는 경우가 아니라면, 항상 SELECT 구문에 'with no locks' 옵션을 사용하라.)

* Use row level locking.
(row단위 락킹을 사용한다. 예를 들면 from tableName WITH (NOLOCK)과 같이)

* Use "dirty reads" wherever possible (never make this option global, because it is not possible except with only a very few interrogations).

(가능하면 'dirty reads'를 이용하라

* Performance tune your systems as much as possible.
(가능한 한 많은 시스템 성능 튜닝을 한다.- 당연한 얘기 --;;)

* Carry out necessary complex transactions such as invoicing at times when the user load is as low as possible.

* 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)
(서버에 추가적인 부하를 주는 maintenance task(예를 들면, DB Full Backup, index 조각모음)의 스케쥴은 서버의 부하가 최소인 시간으로 잡는다.

* Always select as little as possible and only as much as is necessary.
(가능한한 적은 row만을 Select하고, 꼭 필요한 만큼만 Select한다.)

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.