SQL Server 차단 최소화

jevida(강성욱) 2016.09.29 03:56 Views : 1216

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) 될 수도 있다. 이 경우 여러 행으로 분할하고 완료될 때 까지 하나씩 처리하는 것이 좋다.

 

  • 트랜잭션 순서 지정 : 트랜잭션 내에서 의도적으로 순서를 지정하여 차단 가능성을 줄일 수 있다. 주의해야 할 점은 시스템의 모든 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

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38421
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20740
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1429
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1323
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1733
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1863
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4797
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1223
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1353
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2456
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2281
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 4418
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1538
» SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1216
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1115
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2547
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1158
1799 Hot Add CPU jevida(강성욱) 2016.09.29 911
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1107
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1833
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1514
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1290





XE Login