SQL Server 차단 최소화

SQL Server 차단 최소화

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

잠금은 데이터베이스에서 동시 읽기 및 쓰기 작업을 지원하는데 있어서 필수적이면서도 차단을 발생시켜 시스템 성능에 부정적인 영향을 줄 수 있다.

 

 

[잠금 및 에스컬레이션]

SQL Server는 잠금에 영향을 받는 레코드 수와 시스템에 존재하는 동시 작업을 기준으로 가장 적절한 잠금 수준을 선택 한다. 기본적으로 SQL Server는 가장 작은 수준의 잠금을 선택하며 시스템 메모리를 보다 효율적으로 사용할 수 있는 경우에만 잠금 수준이 달라진다.

 

SQL Server에서는 전체 서버 성능에 도움이 된다면 잠금을 에스컬레이션 한다. 에스컬레이션이 발생하면 테이블이 잠긴다.

• 특정 검색에서 잠금 수가 5000개를 초과
• 잠금 설정이 0인 경우 데이터베이스 엔진에 사용된 Non-AWE 메모리가 24% 초과
• 잠금 설정이 0이 아닌 경우 데이터베이스 엔진에서 사용된 Non-AWE 메모리가 40%초과

 

 

 

[불필요한 차단 방지]

차단은 모든 잠금 수준에서 발생할 수 있지만 에스컬레이션이 발생하면 차단 노출이 증가한다. 잠금 에스컬레이션은 응용프로그램이 비효율적으로 디자인, 코딩 되었을 신호일 수 있다.

 

좁은 범위의 키를 통해 정규화된 스키마를 사용하고 OLTP 시스템에서 대량 데이터 작업을 피하는 등의 디자인을 고려하는 것이 차단을 방지하는데 중요하다. (OLTP와 OLAP의 분리. 배치 작업의 분산 등)

 

인덱싱은 데이터 액세스에 필요한 잠금 수를 결정하는데 중요한 요소가 될 수 있다. 인덱스는 데이터베이스 엔진에서 수행 해야 하는 내부 조회 수를 줄임으로써 쿼리에서 액세스하는 레코드의 수를 줄일 수 있다. 인덱싱 되지 않은 열에서 단일 행을 선택할 경우 요청 값을 찾기 위해 원하는 레코드가 확인 될 때까지 각 행은 잠겨있어야 한다. 인덱싱이 되어 있다면 단일 잠금만 필요하다.

 

SQL Server 2005부터는 누적된 사용 통계를 기준으로 인덱스를 통해 이점을 얻는 테이블과 열을 보여주는 DMV가 포함되어 있다.

• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_details

 

조각화도 성능에 많은 영향을 줄 수 있다. 데이터베이스엔진은 조각화로 인해 더 많은 페이지에 액세스 해야 할 수 있다. 더욱이 잘못된 통계로 인해 쿼리 최적화 프로그램에서 최적의 실행계획을 선택하지 못할 수도 있다.

 

인덱스는 데이터 액세스 속도를 높이지만 기본 데이터 변경 및 인덱스도 업데이트해야 하기 때문에 데이터 수정 속도가 느려질 수 있다. DMV를 통해 인덱스가 사용되는 빈도를 확인 할 수 있다.

• sys.dm_db_index_usage_stats

 

select * from sys.dm_db_index_usage_stats

 

 

 

비효율적인 인덱스의 예로는 격리 및 조합 시 동일한 열이 인덱싱 되는 복합 인덱스가 있다. SQL Server는 왼쪽에서 오른쪽으로 인덱스에 액세스하므로 맨 왼쪽 열이 유용한 경우 인덱스가 사용된다.

 

테이블을 분할 하면 데이터를 개별적으로 경쟁 할 수 있는 물리적 개체로 분할 한다. 행 파티션을 사용하면 데이터를 보다 명확하게 분리할 수 있지만 데이터를 가로 분할하는 방법도 생각해 볼 수 있다. SQL Server 2008에서는 파티션마다 잠금 에스컬레이션을 해제하는 기능을 제공 한다.

 

 

[쿼리 최적화]

• 트랜잭션 단축 : 트랜잭션을 작게 만들어 차단을 줄여야 한다. SQL에서는 각각의 문을 암시적 트랜잭션으로 간주 한다. 이러한 문이 다수의 행에 영향을 주는 경우 단일 문의 큰 트랜잭션으로 구성 할 수 있다. 단일 문 사용시에도 update 구문에서 할당된 값보다 큰 경우에 페이지가 분할(forwarded record) 될 수도 있다. 이 경우 여러 행으로 분할하고 완료될 때 까지 하나씩 처리하는 것이 좋다.
  • Forwarded Record (행 이동에 대한 포워드) : http://sqlmvp.kr/140165058738

 

• 트랜잭션 순서 지정 : 트랜잭션 내에서 의도적으로 순서를 지정하여 차단 가능성을 줄일 수 있다. 주의해야 할 점은 시스템의 모든 SQL 코드 내에서 동일한 순서로 개체에 액세스 해야 한다. 자주 액세스하는 개체 또는 액세스하는데 비용이 많이 드는 개체는 트랜잭션 끝에 배치 한다. SQL은 트랜잭션에서 필요 할 때 까지 개체를 잠그기 위해 대기한다. 핫스폿에 대한 액세스를 지연 시키면 이들 개체가 더 짧은 시간 동안 잠금을 유지 할 수 있다.

 

• 잠금 힌트 사용 : 의도적인 잠금 힌트를 사용하여 차단을 줄일 수 있다. 잠금 힌트를 지정하더라도 잠금 수가 시스템 메모리 임계값에 도달 할 때 잠금은 에스컬레이션 된다. 그러나 잠금 힌트를 지정하면 다른 모든 에스컬레이션은 차단 된다.

   

 

[SQL 서버에서 잠금에 사용할 수 있는 메모리 크기를 결정하는 방식]

 

• 메모리 잠금은 항상 Non-AWE 메모리에 유지되므로 Non-AWE 메모리의 크기를 늘리면 잠금을 유지할 시스템 용량이 늘어난다. Non-AWE 용량은 64bit는 제한이 없지만 32bit 환경에서는 4GB로 제한되어 있어 잠금 용량을 늘리려면 64bit를 환경을 선택해야 한다.

 

• SQL Server의 기본 잠금 설정은 0이며 이는 메모리 확보를 위해 경쟁하는 다른 프로세스에 예약된 잠금이 서버에서 동적으로 조정됨을 의미한다. 초기 2500개의 잠금을 예약하며 각각의 잠금에는 96바이트의 메모리가 사용된다. 페이징 메모리는 사용되지 않는다.

 

• 기본적으로 차단을 유발하는 잠금에는 제한 시간이 없지만 @@LOCK_TIMEOUT 설정을 사용하여 임계값을 지정할 수 있다.

 

• 잠금 에스컬레이션을 해제하는 추적 플래그 1211은 소비된 잠금 수가 사용 가능한 메모리를 초과하면 오류가 발생한다. 추적 플래그 1224는 개별 문에 대해 잠금 에스컬레이션을 해제 한다.

 

 

 

 

[참고자료]

http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx

 

 

 

2013-06-26 / 강성욱 / http://sqlmvp.kr