SQL Server
SQL Server의 차단 최소화
Cherié Warren
한 눈에 보기:
- 잠금 에스컬레이션이 발생하는 이유
- 불필요한 차단 방지
- 쿼리 최적화
- 잠금이 성능에 미치는 영향 모니터링
잠금은 데이터베이스에서 동시 읽기 및 쓰기 작업을 지원하는 데 필수적인 반면, 차단은 시스템 성능에 부정적인(때로는 미미한 방식으로) 영향을 줄 수 있습니다. 이 기사에서는 차단을 최소화할 수 있도록 SQL Server 2005나 SQL Server 2008 데이터베이스를 최적화하는 방법과
잠금이 성능에 미치는 영향을 보다 효과적으로 파악할 수 있도록 시스템을 모니터링하는 방법에 대해 살펴보겠습니다.
잠금 및 에스컬레이션
SQL Server®는 잠금에 영향을 받는 레코드 수와 시스템에 존재하는 동시 작업을 기준으로 가장 적절한 잠금 수준을 선택합니다. 기본적으로 SQL Server는 가장 작은 수준의 잠금을 선택하며, 시스템 메모리를 보다 효율적으로 사용할 수 있는 경우에만 성긴 잠금을 선택합니다. SQL Server는 에스컬레이션이 전체 서버 성능에 도움이 된다면 잠금을 에스컬레이션합니다. 그림 1에서 볼 수 있듯이 특정 검색에서 잠금 수가 5,000개를 초과하거나 시스템에서 잠금에 사용된 메모리가 사용 가능한 용량을 초과하면 에스컬레이션이 발생합니다.
그림 1** 잠금 에스컬레이션을 일으키는 조건 **(더 크게 보려면 이미지를 클릭하십시오.)
- 잠금 설정이 0인 경우 데이터베이스 엔진에 사용된 비AWE(Address Windowing Extensions) 메모리의 24%
- 잠금 설정이 0이 아닌 경우 데이터베이스 엔진에 사용된 비AWE 메모리의 40%
에스컬레이션이 발생하면 항상 테이블이 잠깁니다.
불필요한 차단 방지
차단은 모든 잠금 수준에서 발생할 수 있지만, 에스컬레이션이 발생하면 차단 노출이 증가합니다. 잠금 에스컬레이션은 응용 프로그램이 비효율적으로 디자인, 코딩 또는 구성되었다는 신호일 수 있습니다.
좁은 범위의 키를 통해 정규화된 스키마를 사용하고 트랜잭션 시스템에서 대량 데이터 작업을 피하는 등 데이터베이스 디자인의 기본 사항을 준수하는 것은 차단을 방지하는 데 중요합니다. 트랜잭션 시스템에서 보고 시스템을 분리하거나 업무 시간 외에 데이터 피드를 처리하는 등 원칙을 따르지 않으면 시스템을 튜닝하기가 어려워집니다.
인덱싱은 데이터 액세스에 필요한 잠금 수를 결정하는 데 중요한 요소가 될 수 있습니다. 인덱스는 데이터베이스 엔진에서 수행해야 하는 내부 조회 수를 줄임으로써 쿼리에서 액세스하는 레코드의 수를 줄일 수 있습니다. 예를 들어 테이블의 인덱싱되지 않은 열에서 단일 행을 선택할 경우 테이블의 각 행은 원하는 레코드가 확인될 때까지 임시로 잠겨 있어야 합니다. 반대로 이 열이 인덱싱되어 있으면 단일 잠금만 필요합니다.
SQL Server 2005와 SQL Server 2008에는 누적된 사용 통계를 기준으로 인덱스를 통해 이점을 얻는 테이블과 열을 보여 주는 동적 관리 뷰(sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details)가 포함되어 있습니다.
또한 조각화도 성능에 영향을 줄 수 있습니다. 즉, 데이터베이스 엔진은 조각화로 인해 더 많은 페이지에 액세스해야 할 수 있습니다. 더욱이 잘못된 통계로 인해 쿼리 최적화 프로그램에서 효율성이 떨어지는 계획을 선택할 수도 있습니다.
인덱스는 데이터 액세스 속도를 높이지만 기본 데이터를 변경해야 할 뿐만 아니라 인덱스도 업데이트해야 하므로 인덱스로 인해 데이터 수정 속도가 느려질 수 있습니다. 동적 관리 뷰 sys.dm_db_index_usage_stats에는 인덱스가 사용되는 빈도가 강조되어 표시됩니다. 비효율적 인덱싱의 흔한 예로는 격리 및 조합 시 동일한 열이 인덱싱되는 복합 인덱스를 들 수 있습니다. SQL Server는 왼쪽에서 오른쪽으로 인덱스에 액세스하므로 맨 왼쪽 열이 유용한 경우에는 인덱스가 사용됩니다.
테이블을 분할하면 차단 노출을 최소화할 수 있도록 시스템을 최적화하고 데이터를 개별적으로 경쟁할 수 있는 별도의 물리적 개체로 분할할 수 있습니다. 행 파티션을 사용하면 데이터를 보다 명확하게 분리할 수 있지만, 데이터를 가로로 분할하는 또 다른 방법도 생각해 볼 수 있습니다. 테이블을 행과 키의 개수는 같지만 열이 다른 개별 테이블로 분할하는 방법을 통해 의도적으로 비정규화함으로써 개별 프로세스가 동시에 데이터에 독점적으로 액세스할 가능성을 줄일 수도 있습니다.
응용 프로그램이 특정 데이터 행에 액세스하는 방법이 다양해지고 이러한 행에 포함되는 열이 많아질수록 열 분할 방식의 장점이 더욱 부각될 수 있습니다. 경우에 따라서는 이러한 방식이 응용 프로그램 큐와 상태 테이블에 유용할 수도 있습니다. SQL Server 2008에서는 파티션마다(또는 테이블에 대해 파티션이 설정되어 있지 않은 경우에는 테이블마다) 잠금 에스컬레이션을 해제하는 기능을 제공합니다.
쿼리 최적화
쿼리 최적화는 성능을 향상시키는 데 중요한 역할을 합니다. 다음과 같은 세 가지 방법을 사용할 수 있습니다.
트랜잭션 단축 차단을 줄일 뿐만 아니라 전반적인 성능을 향상시키는 가장 중요한 방법 중 하나는 트랜잭션을 가능한 작게 만드는 것입니다. 관련 데이터 조회, 인덱싱 및 데이터 정리 등 트랜잭션 무결성에 있어 중요하지 않은 모든 처리 작업을 제거하여 트랜잭션의 크기를 줄여야 합니다.
SQL에서는 각각의 문을 암시적 트랜잭션으로 간주합니다. 이러한 문이 다수의 행에 영향을 주는 경우 단일 문이 큰 트랜잭션을 구성할 수 있습니다. 특히, 관련된 열이 많거나 열에 큰 데이터 형식이 포함되어 있는 경우가 이에 해당합니다. 또한 단일 문을 사용하면 채우기 비율이 높은 경우 또는 UPDATE 문이 할당된 것보다 큰 값으로 열을 채우는 경우 페이지가 분할될 수 있습니다. 이 경우 트랜잭션을 여러 행으로 분할하고 완료될 때까지 한 번에 하나씩 처리하는 것이 좋습니다. 일괄 처리는 성공하든 또는 실패하든 하나의 작업 단위로 완료된 것으로 간주할 수 있는 보다 작은 일괄 처리로 개별 문이나 여러 개의 문을 분할할 수 있는 경우에만 고려해야 합니다.
트랜잭션 순서 지정 트랜잭션 내에서 의도적으로 문의 순서를 지정하면 차단 가능성을 줄일 수 있습니다. 이 경우 주의해야 할 두 가지 원칙이 있습니다. 첫째, 시스템의 모든 SQL 코드 내에서 동일한 순서로 개체에 액세스합니다. 일정한 순서 없이 두 개의 경쟁 프로세스가 서로 다른 순서로 데이터에 액세스하는 경우 교착 상태가 발생하여 프로세스 중 하나에 시스템 오류가 발생할 수 있습니다. 둘째, 자주 액세스하는 개체 또는 액세스하는 데 많은 비용이 드는 개체를 트랜잭션 끝에 배치합니다. SQL은 트랜잭션에서 필요할 때까지 개체를 잠그기 위해 대기합니다. "핫 스폿"에 대한 액세스를 지연시키면 이들 개체가 더 짧은 시간 동안 잠금을 유지할 수 있습니다.
잠금 힌트 사용 잠금 힌트는 특정 테이블 또는 뷰의 세션 수준 또는 문 수준에서 사용할 수 있습니다. 세션 수준 힌트를 사용하는 일반적인 시나리오로는 데이터 웨어하우스의 일괄 처리가 있습니다. 이 경우 개발자는 해당 데이터 집합에서 지정된 시간에 하나의 프로세스만 실행된다는 것을 알 수 있습니다. 저장 프로시저 시작 부분에서 SET ISOLATION LEVEL READ UNCOMMITTED와 같은 명령을 사용하면 SQL Server가 읽기 잠금을 예약하지 않으므로 전체 잠금 오버헤드가 줄어들고 성능이 향상됩니다.
문 수준 힌트를 사용하는 일반적인 시나리오로는 개발자가 더티 읽기가 안전하게 이루어짐(예: 다른 동시 프로세스에서는 필요하지 않은 단일 행을 테이블에서 읽는 경우)을 알고 있는 경우 또는 다른 모든 성능 튜닝 조치가 실패하여(스키마 디자인, 인덱스 디자인 및 유지 관리, 쿼리 튜닝 등) 개발자가 특정한 유형의 힌트를 사용하도록 컴파일러를 강제 실행하려고 하는 경우가 있습니다.
행 잠금 힌트는 쿼리에 영향을 받는 레코드가 거의 없어서 차단이 줄어들 수 있는 보다 큰 수준의 잠금이 발생한 경우에 도움이 될 수 있습니다. 테이블 잠금 힌트는 테이블의 거의 모든 레코드가 쿼리에 영향을 받아서 잠금을 유지하는 데 필요한 시스템 리소스가 줄어들 수 있는 보다 작은 수준의 잠금이 유지(에스컬레이션되지 않음)되는 경우에 유용할 수 있습니다. 잠금 힌트를 지정하더라도 잠금 수가 시스템 메모리 임계값에 도달할 때 잠금이 에스컬레이션되지 않는 것은 아닙니다. 그러나 잠금 힌트를 지정하면 다른 모든 에스컬레이션은 차단됩니다.
구성 조정
그림 2에서 볼 수 있듯이 SQL Server 시스템을 구성할 때 고려해야 요소가 많이 있습니다.
그림 2** SQL Server에서 잠금에 사용할 수 있는 메모리 크기를 결정하는 방식 **(더 크게 보려면 이미지를 클릭하십시오.)
메모리 잠금은 항상 비AWE 메모리에 유지되므로 비AWE 메모리의 크기를 늘리면 잠금을 유지할 시스템 용량이 늘어납니다.
비AWE 메모리 크기는 64비트 아키텍처에서는 제한이 없지만 32비트 아키텍처에서는 4GB로 제한되므로 잠금 용량을 늘리려면 64비트 아키텍처를 선택해야 합니다.
32비트 시스템에서는 Boot.ini 파일에 /3GB 스위치를 추가하여 SQL Server가 설치된 운영 체제에서 메모리(GB)를 추가로 확보할 수 있습니다.
SQL Server 구성 설정 잠금에 영향을 주는 sp_configure를 통해 여러 가지 설정을 조정할 수 있습니다. 잠금 설정을 통해 오류가 발생하기 전에 시스템에서 유지할 수 있는 잠금 수를 구성합니다. 기본 설정은 0입니다. 이는 메모리 확보를 위해 경쟁하는 다른 프로세스에 예약된 잠금이 서버에서 동적으로 조정됨을 의미합니다. SQL은 초기에 2,500개의 잠금을 예약하고 각각의 잠금에는 96바이트의 메모리가 사용됩니다. 페이징 메모리는 사용되지 않습니다.
최소 및 최대 메모리 설정에서는 SQL Server에 사용되는 크기의 메모리가 예약되므로 메모리를 정적으로 유지하도록 서버가 구성됩니다. 잠금 에스컬레이션은 사용 가능한 메모리와 관련이 있으므로 경쟁 중인 프로세스에서 예약하는 메모리 크기는 에스컬레이션 발생 여부에 영향을 줄 수 있습니다.
연결 설정 기본적으로 차단을 유발하는 잠금에는 제한 시간이 없지만, @@LOCK_TIMEOUT 설정을 사용할 수는 있습니다. 이 설정을 사용하면 잠금 해제에 대해 지정한 대기 임계값이 초과될 경우 오류가 발생합니다.
추적 플래그 특히, 잠금 에스컬레이션과 관련하여 두 가지 추적 플래그가 있습니다. 하나는 잠금 에스컬레이션을 해제하는 추적 플래그 1211입니다. 소비된 잠금 수가 사용 가능한 메모리를 초과하면 오류가 발생합니다. 또 다른 하나는 개별 문에 대해 잠금 에스컬레이션을 해제하는 추적 플래그 1224입니다.
시스템 관찰
추가 정보
- Inside Microsoft SQL Server 2005: The Storage Engine(저자: Kalen Delaney)
- SQL Server 2005 및 SQL Server 2000 블로킹을 모니터링하는 방법
- SQL Server 2005 성능 통계 스크립트
- SQL Server의 잠금 에스컬레이션으로 인해 발생한 차단 문제 해결 방법
- 잠금 에스컬레이션(데이터베이스 엔진)
지정된 간격으로(예: 시간별) 상태 데이터를 폴링하고 유지된 잠금에 대한 현재 통계를 캡처하여 차단과 잠금이 전체 시스템 성능에 미치는 영향을 모니터링할 수 있습니다. 캡처할 주요 정보는 다음과 같습니다.
- 영향 받는 개체, 수준 및 잠금 유형
- 잠금 및 차단 기간
- 실행된 SQL 명령(저장 프로시저 이름, 내부 SQL 문)
- 차단 체인 정보(해당하는 경우)
- 시스템에서 사용 가능한 잠금 용량을 소비하는 방식
그림 3에서 볼 수 있듯이 스크립트를 실행하여 이 정보를 캡처하고 적절한 타임스탬프를 통해 테이블에 기록할 수 있습니다. 그리고 차단된 데이터의 ResourceId를 추가로 분석하여 그림 4에서처럼 스크립트를 실행할 수 있습니다.
Figure 4 차단된 데이터에 대한 자세한 정보
DECLARE @SQL nvarchar(max)
, @CallingResourceType varchar(30)
, @Objectname sysname
, @DBName sysname
, @resource_associated_entity_id int
-- TODO: 조회할 개체의 변수 설정
SET @SQL = N'
USE ' + @DbName + N'
DECLARE @ObjectId int
SELECT @ObjectId = CASE
WHEN @CallingResourceType = ''OBJECT''
THEN @resource_associated_entity_id
WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = @resource_associated_entity_id)
WHEN @CallingResourceType = ''ALLOCATION_UNIT''
THEN (SELECT CASE
WHEN type IN (1, 3)
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = allocation_unit_id)
WHEN type = 2
THEN (SELECT object_id
FROM sys.partitions
WHERE partition_id = allocation_unit_id)
ELSE NULL
END
FROM sys.allocation_units
WHERE allocation_unit_id = @resource_associated_entity_id)
ELSE NULL
END
SELECT @ObjectName = OBJECT_NAME(@ObjectId)'
EXEC dbo.sp_executeSQL
@SQL
, N'@CallingResourceType varchar(30)
, @resource_associated_entity_id int
, @ObjectName sysname OUTPUT'
, @resource_associated_entity_id = @resource_associated_entity_id
, @CallingResourceType = @CallingResourceType
, @ObjectName = @ObjectName OUTPUT
Figure 3 잠금 통계 캡처
SELECT er.wait_time AS WaitMSQty
, er.session_id AS CallingSpId
, LEFT(nt_user_name, 30) AS CallingUserName
, LEFT(ces.program_name, 40) AS CallingProgramName
, er.blocking_session_id AS BlockingSpId
, DB_NAME(er.database_id) AS DbName
, CAST(csql.text AS varchar(255)) AS CallingSQL
, clck.CallingResourceId
, clck.CallingResourceType
, clck.CallingRequestMode
, CAST(bsql.text AS varchar(255)) AS BlockingSQL
, blck.BlockingResourceType
, blck.BlockingRequestMode
FROM master.sys.dm_exec_requests er WITH (NOLOCK)
JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
ON er.session_id = ces.session_id
CROSS APPLY fn_get_sql (er.sql_handle) csql
JOIN (
-- 호출 프로세스의 잠금 정보 검색, 세션 수준에서 정보 보고를 위해
-- 한 개의 레코드만 반환
SELECT cl.request_session_id AS CallingSpId
, MIN(cl.resource_associated_entity_id) AS CallingResourceId
, MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType
, MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode
-- (예: 스키마, 업데이트 등)
FROM master.sys.dm_tran_locks cl WITH (nolock)
WHERE cl.request_status = 'WAIT' -- 잠금 요청의 상태 = 대기
GROUP BY cl.request_session_id
) AS clck
ON er.session_id = clck.CallingSpid
JOIN (
-- 차단 프로세스의 잠금 정보 검색
-- 한 개의 레코드만 반환됨(예를 들어
-- 여러 행 잠금이 발생할 가능성)
SELECT bl.request_session_id AS BlockingSpId
, bl.resource_associated_entity_id AS BlockingResourceId
, MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType
, MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode
FROM master.sys.dm_tran_locks bl WITH (nolock)
GROUP BY bl.request_session_id
, bl.resource_associated_entity_id
) AS blck
ON er.blocking_session_id = blck.BlockingSpId
AND clck.CallingResourceId = blck.BlockingResourceId
JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
ON er.blocking_session_id = ber.session_id
CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE ces.is_user_process = 1
AND er.wait_time > 0
또한 SQL 프로파일러(Lock:Escalation 이벤트), dm_db_index_operational_stats 동적 관리 뷰(index_lock_promotion_count) 또는 시스템 잠금 정보의 일반적인 폴링을 통해 시스템의 에스컬레이션을 모니터링할 수도 있습니다. 에스컬레이션 모니터링을 통해 처리 과정에서 에스컬레이션이 발생하는지 여부에 관한 정보를 수집합니다. 그렇지 않은 경우에는 적절한 저장 프로시저를 통해 성능 문제에 대한 근본적인 원인을 파악할 수 있습니다. 많은 양의 데이터가 포함된 테이블이나 동시 사용률이 높은 테이블은 분석 시 중점적으로 고려해야 할 대상입니다.
잠금, 차단 및 에스컬레이션과 관련된 데이터를 수집한 후에는 이러한 데이터를 분석하여 개체별로 누적된 차단 및 잠금 시간(발생 횟수에 지속 시간을 곱함)을 확인할 수 있습니다. 일반적으로 이를 통해 변경 사항이 배포, 모니터링, 분석 및 수정되는 반복적인 성능 튜닝 작업이 시작될 수 있습니다. 경우에 따라서는 눈에 띄는 성능 향상을 위해 인덱스를 추가하는 등의 간단한 변경을 수행하기만 하면 되며 이를 통해 시스템에서 가장 심각한 성능 병목 상태를 유발하는 영역이 변경되기도 합니다.
SQL Server에서 차단을 줄이는 방법에 대한 자세한 내용은 "추가 정보"에서 확인할 수 있습니다. 디자인, 코딩 및 안정화의 모든 단계에서 트랜잭션을 작게 유지하는 데 각별한 주의를 기울인다면 여러 가지 차단 문제를 최소화할 수 있습니다. 적절한 하드웨어를 사용하면 불필요한 에스컬레이션이 발생할 가능성도 크게 낮출 수 있습니다. 그러나 결국 성능 문제의 근본 원인을 빠르게 파악하는 방법은 시스템에서 차단을 지속적으로 분석하는 것입니다.
Cherié Warren은 Microsoft IT의 선임 개발 팀장으로, 현재 Microsoft에서 가장 큰 트랜잭션 데이터베이스 중 하나를 담당하고 있습니다. 또한 그녀는 문제의 근본 원인을 조사하고 차단과 관련된 성능 문제를 해결하는 데 많은 시간을 보내고 있으며 10년 동안 엔터프라이즈급 SQL Server 데이터베이스 전문가로 일해 왔습니다.
© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..