다음을 통해 공유

Azure SQL 데이터베이스 차단 문제의 이해 및 해결

  • 아티클

적용 대상: Azure SQL Database

이 문서에서는 Azure SQL Database의 차단을 설명하고 차단 문제를 해결하는 방법을 보여줍니다.

목표

이 문서에서 연결이라는 용어는 데이터베이스의 단일 로그온된 세션을 나타냅니다. 각 연결은 많은 DMV에서 SPID(세션 ID) 또는 session_id로 표시됩니다. 이러한 각 SPID는 일반적인 의미에서 별도의 프로세스 컨텍스트가 아니지만 종종 프로세스라고 합니다. 대신 각 SPID는 지정된 클라이언트에서 단일 연결 요청을 지원하는 데 필요한 서버 리소스 및 데이터 구조로 구성됩니다. 단일 클라이언트 애플리케이션에 연결이 하나 이상 존재할 수 있습니다. Azure SQL 데이터베이스의 관점에서 단일 클라이언트 컴퓨터의 단일 클라이언트 애플리케이션에 존재하는 여러 연결 및 여러 클라이언트 애플리케이션 또는 여러 클라이언트 컴퓨터에 존재하는 여러 연결 사이에는 차이점이 없으며, 이를 원자성이라고 합니다. 원본 클라이언트에 관계없이 한 연결이 다른 연결을 차단할 수 있습니다.

교착 상태 해결에 대한 자세한 정보는 Azure SQL Database 교착 상태 분석 및 방지를 참조하세요.

참고 항목

이 콘텐츠는 Azure SQL 데이터베이스를 중점적으로 다룹니다. Azure SQL 데이터베이스는 안정적인 최신 버전의 Microsoft SQL Server 데이터베이스 엔진을 기반으로 하므로 문제 해결 옵션 및 도구는 다르지만 많은 콘텐츠가 비슷합니다. SQL Server에서 차단에 대한 자세한 내용은 SQL Server 차단 문제 이해 및 해결을 참조하세요.

차단 이해

차단은 잠금 기반 동시성을 갖는 RDBMS(관계형 데이터베이스 관리 시스템)의 불가피하며 의도된 특성입니다. 한 세션에서는 특정 리소스를 잠그고 다른 SPID에서는 동일한 리소스에 대해 충돌하는 잠금 유형을 얻으려는 경우 Azure SQL Database의 데이터베이스에서 차단이 발생합니다. 일반적으로 첫 번째 SPID가 리소스를 잠그는 시간 프레임은 짧습니다. 소유 세션에서 잠금을 해제하면 두 번째 연결이 자유롭게 리소스에 대한 자체 잠금을 획득하고 처리를 계속할 수 있습니다. 이는 정상적인 동작이며 하루 종일 시스템 성능에 별다른 영향을 주지 않으면서 여러 번 발생할 수 있습니다.

Azure SQL 데이터베이스의 각 새 데이터베이스에는 기본적으로 RCSI(읽기 커밋된 스냅샷) 데이터베이스 설정이 사용하도록 설정되어 있습니다. 데이터 읽기 세션과 데이터 쓰기 세션 간의 차단은 RCSI에서 최소화되며, 행 버전 관리로 동시성을 높입니다. 그러나 다음과 같은 이유로 Azure SQL 데이터베이스의 데이터베이스에서 차단 및 교착 상태가 발생할 수 있습니다.

  • 데이터를 수정하는 쿼리가 서로 차단할 수 있습니다.
  • 차단을 증가시키는 격리 수준에서 쿼리를 실행할 수 있습니다. Transact-SQL에서 애플리케이션 연결 문자열, 쿼리 힌트 또는 SET 문에 격리 수준을 지정할 수 있습니다.
  • RCSI를 사용하지 않도록 설정하면 데이터베이스가 공유(S) 잠금을 사용하여 읽기 커밋된 격리 수준에서 실행되는 SELECT 문을 보호할 수 있습니다. 이렇게 하면 차단 및 교착 상태가 증가할 수 있습니다.

스냅샷 격리 수준은 기본적으로 Azure SQL 데이터베이스의 새 데이터베이스에도 사용하도록 설정됩니다. 스냅샷 격리는 데이터에 대한 트랜잭션 수준 일관성을 제공하고 행 버전을 사용하여 업데이트할 행을 선택하는 추가적인 행 기반 격리 수준입니다. 스냅샷 격리를 사용하려면 쿼리 또는 연결에서 트랜잭션 격리 수준을 명시적으로 SNAPSHOT으로 설정해야 합니다. 이 작업은 데이터베이스에 대해 스냅샷 격리를 사용하도록 설정한 경우에만 수행할 수 있습니다.

Transact-SQL RCSI 및/또는 스냅샷 격리를 사용할 수 있는지 확인할 수 있습니다. Azure SQL 데이터베이스에 연결하고 다음 쿼리를 실행합니다.

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

RCSI를 사용하는 경우 is_read_committed_snapshot_on 열이 1 값을 반환합니다. 스냅샷 격리를 사용하는 경우 snapshot_isolation_state_desc 열이 ON 값을 반환합니다.

쿼리의 지속 시간 및 트랜잭션 컨텍스트는 잠금이 유지되는 기간과 다른 쿼리에 미치는 영향을 결정합니다. SELECT 문은 RCSI가 읽는 데이터에서 공유(S) 잠금을 획득하지 않음에서 실행되므로 데이터를 수정 중인 트랜잭션을 차단하지 않습니다. INSERT, UPDATE 및 DELETE 문의 경우 데이터 일관성과 필요에 따라 쿼리를 롤백할 수 있도록 쿼리 중에 잠금이 유지됩니다.

명시적 트랜잭션 내에서 실행되는 쿼리의 경우, 잠금 유형과 잠금이 보류되는 기간은 쿼리 유형, 트랜잭션 격리 수준 및 쿼리에 잠금 힌트가 사용되는지 여부에 따라 결정됩니다. 잠금, 잠금 힌트 및 트랜잭션 격리 수준에 대한 설명은 다음 문서를 참조하세요.

잠금 및 차단이 시스템 성능을 저해하는 지점까지 지속되는 경우 다음 이유 중 하나로 인해 발생합니다.

  • SPID가 오랜 시간 동안 리소스 집합을 잠근 상태로 유지한 후 해제합니다. 이 유형의 차단은 시간이 지남에 따라 자체적으로 해결되지만 성능이 저하될 수 있습니다.

  • SPID가 리소스 집합을 잠근 후 해제하지 않습니다. 이러한 유형의 차단은 자체적으로 해결되지 않으며 영향을 받는 리소스에 대한 액세스를 무기한 차단합니다.

첫 번째 시나리오에서는 시간이 지남에 따라 다른 SPID가 서로 다른 리소스에서 차단을 유발하여 이동 대상을 생성하므로 상황이 매우 유동적일 수 있습니다. SQL Server Management Studio를 사용하여 문제를 개별 쿼리로 좁히려는 경우 이러한 상황에서 문제를 해결하기 어렵습니다. 반면, 두 번째 상황은 일관된 상태가 생성되므로 진단하기 쉬울 수 있습니다.

최적화된 잠금

최적화된 잠금은 잠금 메모리와 쓰기에 필요한 동시 잠금 수를 크게 줄이는 새로운 데이터베이스 엔진 기능입니다. 최적화된 잠금은 TID(트랜잭션 ID) 잠금(다른 행 버전 관리 기능에도 사용됨) 및 LAQ(인증 후 잠금)와 같은 두 가지 기본 구성 요소를 사용합니다. 다른 추가 구성은 필요하지 않습니다.

이 문서는 현재 최적화된 잠금이 없는 데이터베이스 엔진 동작에 적용됩니다.

자세한 내용과 최적화된 잠금을 사용할 수 있는 위치를 알아보려면 최적화된 잠금을 참조하세요.

애플리케이션 및 차단

차단 문제가 발생할 때 서버 쪽 튜닝 및 플랫폼 문제에 집중하는 경향이 있을 수 있습니다. 그러나 데이터베이스에만 집중해도 문제가 해결되지 않을 수 있으며, 클라이언트 애플리케이션과 여기에서 제출하는 쿼리를 검사하는 데 필요한 시간과 에너지를 빼앗길 수 있습니다. 데이터베이스 호출과 관련하여 애플리케이션에서 노출하는 가시성 수준에 관계없이, 차단 문제는 애플리케이션에서 제출한 정확한 SQL 문을 검사하고 쿼리 취소, 연결 관리, 모든 결과 행 가져오기 등과 관련된 애플리케이션의 정확한 동작을 모두 검사해야 하는 경우가 많습니다. 개발 도구에서 연결 관리, 쿼리 취소, 쿼리 제한 시간 초과, 결과 가져오기 등에 대한 명시적 제어를 허용하지 않는 경우 차단 문제를 해결할 수 없습니다. Azure SQL Database, 특히 성능에 민감한 OLTP 환경의 경우 애플리케이션 개발 도구를 선택하기 전에 이 가능성을 면밀히 검토해야 합니다.

데이터베이스 및 애플리케이션의 디자인과 생성 단계에서 데이터베이스 성능에 주의하세요. 특히 각 쿼리에 대해 리소스 사용, 격리 수준 및 트랜잭션 경로 길이를 평가해야 합니다. 각 쿼리 및 트랜잭션은 가능한 간단하게 만들어야 합니다. 좋은 연결 관리 원칙을 실행해야 합니다. 그렇지 않으면 사용자가 적을 때 애플리케이션이 허용 가능한 성능을 지원하지만, 사용자 수가 증가하면 성능이 크게 저하될 수 있습니다.

적절한 애플리케이션 및 쿼리 설계를 통해 Azure SQL Database는 단일 서버에서 수천 명의 동시 사용자를 거의 차단하지 않고 지원할 수 있습니다.

참고 항목

애플리케이션 개발 지침에 대한 자세한 내용은 Azure SQL Database 및 Azure SQL Managed Instance 및 일시적인 오류 처리의 연결 문제 및 기타 오류 문제 해결을 참조하세요.

차단 문제 해결

어떤 차단 상황에 있든 관계없이 잠금 문제를 해결하는 방법은 동일합니다. 이러한 논리적 구분은 이 문서의 나머지 컴퍼지션을 결정합니다. 개념은 헤드 블로커를 찾아 쿼리가 수행하는 작업과 해당 쿼리가 차단되는 원인을 식별하는 것입니다. 문제가 있는 쿼리가 식별되면(즉, 장기간 잠금을 보유하는 항목) 다음 단계는 차단이 발생하는 이유를 분석하고 확인하는 것입니다. 원인을 파악한 후 쿼리와 트랜잭션을 재설계하여 변경할 수 있습니다.

문제 해결 단계:

  1. 주 차단 세션(헤드 블로커) 식별

  2. 차단을 일으키는 쿼리 및 트랜잭션 찾기(장기간 잠금을 유지하는 항목)

  3. 장기간 차단이 발생하는 원인 분석/이해

  4. 쿼리 및 트랜잭션을 다시 디자인하여 차단 문제 해결

이제 적절한 데이터 캡처를 통해 주 차단 세션을 정확히 찾아내는 방법에 대해 알아보겠습니다.

차단 정보 수집

데이터베이스 관리자는 Azure SQL Database의 데이터베이스에서 잠금 및 차단 상태를 지속적으로 모니터링하는 SQL 스크립트를 사용하여 차단 문제 해결의 어려움에 대처합니다. 이 데이터를 수집하기 위해 기본적으로 두 가지 방법이 있습니다.

첫 번째는 DMO(동적 관리 개체)를 쿼리하고 시간에 따른 비교를 위해 결과를 저장하는 것입니다. 이 문서에서 참조하는 일부 개체는 DMV(동적 관리 뷰)이며 일부는 DMF(동적 관리 함수)입니다. 두 번째 방법은 XEvents를 사용하여 실행 중인 항목을 캡처하는 것입니다.

DMV에서 정보를 수집합니다.

차단 문제를 해결하기 위해 DMV를 참조하는 것은 차단 체인 및 SQL 문의 헤드에서 SPID(세션 ID)를 식별하는 것을 목표로 합니다. 차단된 문제가 된 SPID를 찾습니다. 다른 SPID에 의해 해당 SPID가 차단되는 경우 리소스를 소유한 SPID(차단 SPID)를 조사합니다. 소유자 SPID도 차단되고 있나요? 체인을 탐색하여 헤드 블로커를 찾은 다음 체인이 잠금을 유지하는 원인을 조사할 수 있습니다.

Azure SQL Database의 대상 데이터베이스에서 이러한 각 스크립트를 실행해야 합니다.

  • sp_who 및 sp_who2 명령은 모든 현재 세션을 표시하는 이전 명령입니다. DMV sys.dm_exec_sessions는 결과 집합에서 더 많은 데이터를 반환하며, 결과 집합에서 쿼리 및 필터링을 보다 쉽게 수행할 수 있습니다. 다른 쿼리의 핵심에서 sys.dm_exec_sessions를 찾을 수 있습니다.

  • 특정 세션이 이미 식별된 경우 DBCC INPUTBUFFER(<session_id>)를 사용하여 세션에서 제출한 마지막 문을 찾을 수 있습니다. 쿼리 및 필터링하기 쉬운 결과 집합에서 sys.dm_exec_input_buffer DMF(동적 관리 함수)를 사용하여 유사한 결과를 반환하여 이때 session_id 및 request_id를 제공합니다. 예를 들어 session_id 66 및 request_id 0에서 제출한 가장 최근 쿼리를 반환하려면 다음을 수행합니다.

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • sys.dm_exec_requests의 blocking_session_id 열을 참조하세요. blocking_session_id = 0이면 세션이 차단되지 않습니다. sys.dm_exec_requests에서 현재 실행 중인 요청만 나열하는 동안 sys.dm_exec_sessions에는 모든 연결(활성 여부에 상관없이)이 나열됩니다. 다음 쿼리에서는 sys.dm_exec_requests 및 sys.dm_exec_sessions 사이의 이 일반적인 조인을 기반으로 빌드합니다.

  • 이 샘플 쿼리를 실행하여 sys.dm_exec_sql_text 또는 sys.dm_exec_input_buffer DMV를 사용해 현재 실행 중인 쿼리와 현재 SQL 일괄 처리 텍스트 또는 입력 버퍼 텍스트를 찾습니다. sys.dm_exec_sql_text의 text 필드에서 반환하는 데이터가 NULL이면 쿼리가 현재 실행 중이 아닙니다. 이 경우 sys.dm_exec_input_buffer의 event_info 필드에는 SQL 엔진에 전달된 마지막 명령 문자열이 포함됩니다. 이 쿼리를 사용하여 session_id당 차단된 session_ids 목록을 포함하여 다른 세션을 차단하는 세션을 식별할 수도 있습니다.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Microsoft 지원에서 제공하는 보다 정교한 샘플 쿼리를 실행해 차단 체인과 관련된 세션의 쿼리 텍스트를 포함하여 여러 세션 차단 체인의 헤드를 식별합니다.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • SQL의 스레드/작업 계층에 있는 sys.dm_os_waiting_tasks를 참조하세요. 그러면 현재 요청에 나타나는 SQL 대기 유형에 대한 정보가 반환됩니다. sys.dm_exec_requests과 마찬가지로 sys.dm_os_waiting_tasks에 의해 활성 요청만 반환됩니다.

참고 항목

시간 경과에 따른 집계된 대기 통계를 포함한 대기 유형에 대한 자세한 내용은 DMV sys.dm_db_wait_stats를 참조하세요. 이 DMV는 현재 데이터베이스에 대한 집계 대기 통계만 반환합니다.

  • 쿼리에 의해 배치된 잠금에 대한 자세한 내용을 보려면 sys.dm_tran_locks DMV를 사용합니다. 이 DMV는 프로덕션 데이터베이스에서 많은 양의 데이터를 반환할 수 있으며 현재 보유 중인 잠금을 진단하는 데 유용합니다.

sys.dm_os_waiting_tasks의 INNER JOIN으로 인해 다음 쿼리는 sys.dm_tran_locks의 출력을 현재 차단된 요청, 해당 대기 상태 및 잠금으로만 제한합니다.

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';
  • DMV를 통해 시간에 따른 쿼리 결과를 저장하면 지정된 시간 간격 동안 차단을 검토하여 지속형 차단 또는 추세를 식별할 수 있는 데이터 요소를 제공합니다.

확장 이벤트에서 정보 수집

이전 정보 외에도 서버에서 활동 추적을 캡처하여 Azure SQL 데이터베이스의 차단 문제를 철저히 조사해야 하는 경우가 많습니다. 예를 들어, 세션이 트랜잭션 내에서 여러 문을 실행하는 경우 제출된 마지막 문만 표시됩니다. 그러나 이전 명령문 중 하나가 잠금이 여전히 유지되는 이유일 수 있습니다. 추적을 사용하면 현재 트랜잭션 내에서 세션에 의해 실행된 모든 명령을 볼 수 있습니다.

확장 이벤트(XEvents) 및 프로파일러 추적과 같이 SQL Server에서 추적을 캡처하는 두 가지 방법이 있습니다. 그러나 SQL Server Profiler는 Azure SQL Database에서 지원하지 않는 더 이상 사용되지 않는 추적 기술입니다. 확장 이벤트는 관찰된 시스템에 주는 영향을 적으면서 더 많은 다양성을 부여하는 최신 추적 기술이며, 해당 인터페이스는 SQL Server Management Studio(SSMS)에 통합됩니다.

SSMS의 확장 이벤트 새 세션 마법사를 사용하는 방법을 설명하는 문서를 참조하세요. 그러나 Azure SQL Database의 경우 SSMS는 개체 탐색기에서 각 데이터베이스 아래에 확장 이벤트 하위 폴더를 제공합니다. 확장 이벤트 세션 마법사를 사용하여 다음과 같은 유용한 이벤트를 캡처할 수 있습니다.

  • 범주 오류:

    • 주의
    • Error_reported
    • Execution_warning

  • 범주 경고:

    • Missing_join_predicate

  • 범주 실행:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • 범주 deadlock_monitor

    • database_xml_deadlock_report

  • 범주 세션

    • Existing_connection
    • 로그인
    • 로그아웃

참고 항목

교착 상태에 대한 자세한 정보는 Azure SQL Database의 교착 상태 분석 및 방지를 참조하세요.

일반적인 차단 시나리오 식별 및 해결

이전 정보를 조사하면 대부분의 차단 문제의 원인을 확인할 수 있습니다. 이 문서의 나머지 부분에서는 이 정보를 사용하여 몇 가지 일반적인 차단 시나리오를 식별하고 해결하는 방법에 대해 설명합니다. 이 논의에서는 차단 스크립트(앞에서 참조)를 사용하여 차단 SPID에 대한 정보를 캡처하고 XEvent 세션을 사용하여 애플리케이션 작업을 캡처한다고 가정합니다.

차단 데이터 분석

  • sys.dm_exec_requests 및 sys.dm_exec_sessions의 출력을 검사하여 blocking_these 및 session_id를 사용하여 차단 체인의 헤드를 판단합니다. 이렇게 하면 차단된 요청과 차단 중인 요청이 가장 명확하게 식별됩니다. 차단된 세션과 차단 중인 세션을 자세히 살펴봅니다. 차단 체인에 공통 요소 또는 루트가 있나요? 공통 테이블을 공유할 가능성이 크며, 차단 체인에 포함된 하나 이상의 세션이 쓰기 작업을 수행합니다.

  • 차단 체인의 헤드에 있는 SPID에 대한 정보는 sys.dm_exec_requests 및 sys.dm_exec_sessions의 출력을 검사합니다. 다음 필드를 찾습니다.

    • sys.dm_exec_requests.status
      이 열에서는 특정 요청의 상태를 표시합니다. 일반적으로 중지 중 상태는 SPID가 실행을 완료했으며 애플리케이션이 다른 쿼리나 배치를 제출하기를 기다리고 있음을 나타냅니다. 실행 가능 또는 실행 중 상태는 SPID가 현재 쿼리를 처리하고 있음을 나타냅니다. 다음 테이블에서는 다양한 상태 값을 간략하게 설명합니다.
    상태 의미
    배경 SPID는 교착 상태 검색, 로그 기록기 또는 체크포인트와 같은 백그라운드 작업을 실행합니다.
    중지 중 SPID가 현재 실행되고 있지 않습니다. 이는 일반적으로 SPID가 애플리케이션에서 명령을 기다리고 있음을 나타냅니다.
    실행 중 SPID는 현재 스케줄러에서 실행 중입니다.
    실행 가능 SPID는 스케줄러의 실행 가능한 큐에 있으며 스케줄러 시간을 가져오기를 기다리고 있습니다.
    일시 중단 SPID는 잠금 또는 래치와 같은 리소스를 기다리고 있습니다.

    • sys.dm_exec_sessions.open_transaction_count
      이 필드에서는 이 세션의 열린 트랜잭션 수를 표시합니다. 이 값이 0보다 큰 경우 SPID는 열린 트랜잭션 내에 있으며 트랜잭션 내의 모든 명령문에서 획득한 잠금을 보유할 수 있습니다.

    • sys.dm_exec_requests.open_transaction_count
      마찬가지로 이 필드에서는 이 요청의 열린 트랜잭션 수를 표시합니다. 이 값이 0보다 큰 경우 SPID는 열린 트랜잭션 내에 있으며 트랜잭션 내의 모든 명령문에서 획득한 잠금을 보유할 수 있습니다.

    • sys.dm_exec_requests.wait_type, wait_time 및 last_wait_type
      sys.dm_exec_requests.wait_type이 NULL이면 요청은 현재 아무 것도 기다리지 않고 last_wait_type 값은 요청이 발생한 마지막 wait_type을 나타냅니다. sys.dm_os_wait_stats에 대한 자세한 내용 및 가장 일반적인 대기 유형에 대한 설명은 sys.dm_os_wait_stats를 참조하세요. wait_time 값을 사용하여 요청을 진행 중인지 여부를 확인할 수 있습니다. sys.dm_exec_requests 테이블에 대한 쿼리가 wait_time 열에 이전 쿼리인 sys.dm_exec_requests의 wait_time 값보다 작은 값을 반환하는 경우, 이전 잠금을 획득하여 해제한 다음, 현재 새 잠금에서 대기하고 있음을 나타냅니다(0이 아닌 wait_time인 것으로 가정). 이는 요청이 대기 중인 리소스를 표시하는sys.dm_exec_requests 출력 간의 wait_resource를 비교하여 확인할 수 있습니다.

    • sys.dm_exec_requests.wait_resource 이 필드는 차단된 요청이 대기 중인 리소스를 나타냅니다. 다음 테이블은 일반 wait_resource 형식 및 해당 의미를 나열합니다.

    리소스 형식 예시 설명
    테이블 DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 이 경우 데이터베이스 ID 5는 Pubs 샘플 데이터베이스, 개체 ID 261575970은 제목 테이블, 1은 클러스터형 인덱스입니다.
    페이지 DatabaseID:FileID:PageID PAGE: 5:1:104 이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 제목 테이블에 속하는 페이지입니다. 페이지가 속한 object_id 식별하려면 동적 관리 함수 sys.dm_db_page_info를 사용하여 wait_resource에서 DatabaseID, FileId, PageId를 전달합니다.
    키 DatabaseID:Hobt_id(인덱스 키의 해시 값) 키: 5:72057594044284928(3300a4f361aa) 이 경우 데이터베이스 ID 5는 Pubs, Hobt_ID 72057594044284928은 object_id 261575970(제목 표)의 index_id 2에 해당합니다. sys.partitions 카탈로그 뷰를 사용하여 hobt_id를 특정 index_id 및 object_id에 연결합니다. 인덱스 키 해시를 특정 키 값으로 해시 해제하는 방법은 없습니다.
    Row DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 제목 테이블에 속하는 페이지, 슬롯 3은 페이지에서 행의 위치를 나타냅니다.
    Compile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 제목 테이블에 속하는 페이지, 슬롯 3은 페이지에서 행의 위치를 나타냅니다.
    • sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV에는 커밋 또는 롤백을 기다리는 트랜잭션의 전체 상황을 파악하기 위해 다른 DMV에 조인할 수 있는 열린 트랜잭션에 대한 데이터가 포함되어 있습니다. 다음 쿼리를 사용하여 sys.dm_tran_session_transactions를 포함하는 다른 DMV에 조인된 열린 트랜잭션에 대한 정보를 반환합니다. 트랜잭션의 현재 상태, transaction_begin_time 및 기타 상황적 데이터를 고려하여 차단의 출처인지 여부를 평가합니다.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • 기타 열

      sys.dm_exec_sessions 및 sys.dm_exec_request에 나머지 열은 문제의 근원에 대한 인사이트도 제공할 수 있습니다. 해당 유용성은 문제의 상황에 따라 달라집니다. 예를 들어, 특정 클라이언트(호스트 이름)에서, 특정 네트워크 라이브러리(net_library)에서, SPID에 의해 제출된 마지막 배치가 sys.dm_exec_sessions에서 last_request_start_time이었던 때, sys.dm_exec_requests에서 start_time을 사용하여 요청이 실행된 기간에 문제가 발생하는지 확인할 수 있습니다.

일반적인 차단 시나리오

아래 테이블에서는 일반적인 증상을 가능한 원인에 매핑합니다.

Waittype, Open_Tran, Status 열은 sys.dm_exec_request에서 반환된 정보를 참조하며, 다른 열은 sys.dm_exec_sessions에서 반환될 수 있습니다. "Resolves?" 열은 차단이 자체적으로 해결되는지 또는 세션을 KILL 명령을 통해 종료해야 하는지 여부를 나타냅니다. 자세한 내용은 KILL(Transact SQL)을 참조하세요.

시나리오 Waittype Open_Tran 상태 해결 방법 기타 증상
1 NOT NULL > = 0 실행 가능 예(쿼리가 완료되는 경우). sys.dm_exec_sessions에서 reads, cpu_time 및/또는 memory_usage 열은 시간이 지남에 따라 증가합니다. 작업을 완료하면 쿼리의 기간이 증가합니다.
2 NULL >0 중지 중 아니요. 하지만 SPID는 종료할 수 있습니다. 이 SPID에 대한 확장 이벤트 세션에서 쿼리 시간 제한 또는 취소가 발생했음을 나타내는 주의 신호가 표시될 수 있습니다.
3 NULL > = 0 실행 가능 아니요. 클라이언트가 모든 행을 가져오거나 연결을 닫을 때까지 해결되지 않습니다. SPID는 종료할 수 있지만 최대 30초까지 걸릴 수 있습니다. open_transaction_count = 0이고 트랜잭션 격리 수준이 기본값인 동안 SPID가 잠금을 보유하는 경우(READ COMMMITTED) 이 상태가 원인일 수 있습니다.
4 상황에 따라 다름 > = 0 실행 가능 아니요. 클라이언트가 쿼리를 취소하거나 연결을 닫을 때까지 해결되지 않습니다. SPID는 종료할 수 있지만 최대 30초까지 걸릴 수 있습니다. 차단 체인의 헤드에 있는 SPID에 대한 sys.dm_exec_sessions의 hostname 열은 차단 중인 SPID 중 하나와 동일합니다.
5 NULL >0 rollback 예. 이 SPID에 대한 확장 이벤트 세션에서 쿼리 시간 제한 또는 취소가 발생했거나 롤백 문이 발행되었음을 나타내는 주의 신호가 표시될 수 있습니다.
6 NULL >0 중지 중 최종. Windows NT에서 세션이 더 이상 활성 상태가 아니라고 판단하면 Azure SQL 데이터베이스 연결이 끊어집니다. sys.dm_exec_sessions의 last_request_start_time 값이 현재 시간보다 훨씬 이전입니다.

자세한 차단 시나리오

  1. 실행 시간이 긴 정상적으로 실행되는 쿼리로 인한 차단

    해결 방법: 이러한 유형의 차단 문제에 대한 솔루션은 쿼리를 최적화할 수 있는 방법을 찾는 것입니다. 실제로 이 클래스의 차단 문제는 단지 성능 문제일 수 있으며, 따라서 이를 추진해야 합니다. 느리게 실행되는 특정 쿼리 문제를 해결하는 방법에 대한 자세한 내용은 SQL Server에서 느리게 실행되는 쿼리 문제 해결을 참조하세요. 자세한 내용은 성능 모니터링 및 조정을 참조하세요.

    SSMS의 쿼리 저장소에 있는 보고서는 또한 가장 비용이 많이 드는 쿼리, 차선책 실행 계획을 식별하기 위한 매우 권장되고 가치 있는 도구입니다. 또한 Query Performance Insight를 포함하여 Azure SQL database에 대한 Azure Portal의 지능형 성능 섹션을 검토합니다.

    쿼리가 SELECT 작업만 수행할 때, 특히 RCSI를 사용하지 않는 경우처럼 데이터베이스에서 사용하도록 설정되었다면 스냅샷 격리에서 문을 실행하는 것이 좋습니다. RCSI를 사용하는 경우와 마찬가지로 데이터를 읽는 쿼리에는 스냅샷 격리 수준에서 공유(S) 잠금이 필요하지 않습니다. 또한 스냅샷 격리는 명시적 다중 문 트랜잭션의 모든 문에 대한 트랜잭션 수준 일관성을 제공합니다. 스냅샷 격리가 데이터베이스에서 이미 활성화되어 있을 수 있습니다. 스냅샷 격리는 수정을 수행하는 쿼리와 함께 사용할 수도 있지만 업데이트 충돌을 처리해야 합니다.

    다른 사용자를 차단하고 있고 최적화될 수 없는 장기 실행 쿼리가 있으면 쿼리를 OLTP 환경에서 전용 보고 시스템(데이터베이스의 동기 읽기 전용 복제본(replica))으로 이동하는 것이 좋습니다.

  2. 커밋되지 않은 트랜잭션이 있는 일시 중지 SPID로 인한 차단

    이러한 차단 유형은 일시 중지 중이거나 명령을 기다리고 있지만 트랜잭션 중첩 수준(sys.dm_exec_requests의 @@TRANCOUNT, open_transaction_count)이 0보다 큰 SPID로 식별되는 경우가 많습니다. 이 문제는 애플리케이션이 필요한 수의 롤백 및/또는 커밋 문을 발행하지 않고 쿼리 시간 제한을 경험하거나 취소를 발행하는 경우에 발생할 수 있습니다. SPID에서 쿼리 제한 시간이 초과되거나 취소를 수신하면 현재 쿼리 및 일괄 처리를 종료하지만 트랜잭션을 자동으로 롤백하거나 커밋하지 않습니다. Azure SQL Database는 단일 쿼리가 취소되어 전체 트랜잭션이 롤백되어야 한다고 가정할 수 없으므로 애플리케이션이 이를 담당합니다. 쿼리 시간 제한 또는 취소는 확장 이벤트 세션에서 SPID에 대한 주의 신호 이벤트로 표시됩니다.

    커밋되지 않은 명시적 트랜잭션을 시연하려면 다음 쿼리를 실행합니다.

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

    그런 다음 동일한 창에서 이 쿼리를 실행합니다.

    SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

    두 번째 쿼리의 출력은 트랜잭션 중첩 수준이 1임을 나타냅니다. 트랜잭션에서 획득한 모든 잠금은 트랜잭션이 커밋되거나 롤백될 때까지 계속 유지됩니다. 애플리케이션이 트랜잭션을 명시적으로 열고 커밋하는 경우 통신 또는 기타 오류로 인해 세션과 해당 트랜잭션이 열린 상태로 남을 수 있습니다.

    sys.dm_tran_active_transactions에 따라 이 문서의 앞부분에 나오는 스크립트를 사용하여 인스턴스 간에 현재 커밋되지 않은 트랜잭션을 식별합니다.

    해결 방법:

    • 또한 이 클래스의 차단 문제는 단지 성능 문제일 수 있으며, 따라서 이를 추진해야 합니다. 쿼리 실행 시간을 줄일 수 있는 경우 쿼리 제한 시간 초과 또는 취소가 발생하지 않습니다. 제한 시간 초과 또는 취소 시나리오가 발생하면 애플리케이션이 해당 문제를 처리할 수 있어야 하는 것도 중요하지만, 쿼리의 성능을 검사하면 도움이 될 수도 있습니다.

    • 애플리케이션은 트랜잭션 중첩 수준을 적절하게 관리해야 합니다. 그렇지 않으면 이러한 방식으로 쿼리를 취소한 후 차단 문제가 발생할 수 있습니다. 다음을 고려하십시오.

      • 클라이언트 응용 프로그램의 오류 처리기에서 클라이언트 응용 프로그램에서 트랜잭션이 열려 있다고 생각하지 않더라도 오류 발생 후 IF @@TRANCOUNT > 0 ROLLBACK TRAN을 실행합니다. 일괄 처리 중에 호출된 저장 프로시저가 클라이언트 애플리케이션의 지식 없이 트랜잭션을 시작할 수 있으므로 열린 트랜잭션을 확인해야 합니다. 쿼리 취소와 같은 특정 조건은 현재 문을 지나 프로시저가 실행되지 않도록 하기 때문에 절차에 IF @@ERROR <> 0를 확인하고 트랜잭션을 중단하는 논리가 있더라도 이러한 경우에는 이 롤백 코드가 실행되지 않습니다.
      • 연결 풀링이 연결을 열고 웹 기반 애플리케이션과 같이 풀로 연결을 다시 해제하기 전에 소수의 쿼리를 실행하는 애플리케이션에서 사용되는 경우 연결 풀링을 일시적으로 해제하면 클라이언트 응용 프로그램이 오류를 적절하게 처리하도록 수정할 때까지 문제가 완화되는 데 도움이 될 수 있습니다. 연결 풀링을 사용하지 않도록 설정하면 연결을 해제할 때 Azure SQL 데이터베이스 연결이 물리적으로 끊어지게 되므로 서버가 열린 트랜잭션을 롤백합니다.
      • 연결 또는 트랜잭션을 시작하고 오류 후 정리되지 않는 저장 프로시저에 SET XACT_ABORT ON을 사용합니다. 런타임 오류가 발생할 경우 이 설정은 열려 있는 트랜잭션을 중단하고 클라이언트로 컨트롤을 반환합니다. 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 검토 하세요.

    참고 항목

    연결 풀에서 다시 사용할 때까지 연결은 다시 설정되지 않으므로, 사용자가 트랜잭션을 열고 연결 풀에 대한 연결을 해제할 수 있지만 몇 초 동안 다시 사용되지 않을 수 있으며 그동안 트랜잭션은 열린 상태로 남아 있습니다. 연결을 다시 사용하지 않으면 연결 시간이 초과되고 연결 풀에서 제거될 때 트랜잭션이 중단됩니다. 따라서 클라이언트 응용 프로그램이 오류 처리기에서 트랜잭션을 중단하거나 SET XACT_ABORT ON을 사용하여 이러한 잠재적 지연을 방지하는 것이 좋습니다.

    주의

    SET XACT_ABORT ON 이후 오류 원인이 되는 명령문 다음에 오는 T-SQL 문은 실행되지 않습니다. 이는 기존 코드의 의도된 흐름에 영향을 줄 수 있습니다.

  3. 해당 클라이언트 응용 프로그램이 완료될 때까지 모든 결과 행을 가져오지 않은 SPID로 인한 차단

    서버에 쿼리를 보낸 후 모든 애플리케이션에서 즉시 모든 결과 행을 가져와 완료해야 합니다. 애플리케이션에서 모든 결과 행을 가져오지 않으면 테이블을 잠가 다른 사용자를 차단할 수 있습니다. SQL 문을 서버에 투명하게 제출하는 애플리케이션을 사용하는 경우 애플리케이션은 모든 결과 행을 가져와야 합니다. 현재 그렇지 않고 이를 수행하도록 애플리케이션을 구성할 수 없으면 차단 문제를 해결하지 못할 수도 있습니다. 이 문제를 방지하려면 기본 OLTP 데이터베이스와 별도로 제대로 동작하지 않는 애플리케이션을 보고 또는 의사 결정 지원 데이터베이스로 제한할 수 있습니다.

    Azure SQL Database의 기본 구성인 읽기 커밋된 스냅샷을 데이터베이스에서 사용하면 이 시나리오의 영향이 줄어듭니다. 이 문서의 차단 이해 섹션에서 자세히 알아보세요.

    참고 항목

    Azure SQL Database에 연결하는 애플리케이션의 재시도 논리에 대한 지침을 참조하세요.

    해결 방법: 완료할 결과의 모든 행을 가져오려면 애플리케이션을 다시 작성해야 합니다. 이는 서버측 페이징을 수행하기 위해 쿼리의 ORDER BY 절의 OFFSET 및 FETCH 사용을 배제하지 않습니다.

  4. 세션에서 롤백 상태로 인한 차단

    사용자 정의 트랜잭션 외부에서 종료되거나 취소된 데이터 수정 쿼리는 롤백됩니다. 이는 클라이언트 네트워크 세션 연결 끊김의 부작용으로 발생하거나 요청이 교착 상태의 희생자로 선택된 경우에 발생할 수 있습니다. sys.dm_exec_requests의 출력(ROLLBACK 명령을 나타낼 수 있음)을 관찰하여 종종 이 경우인지 식별할 수 있으며, percent_complete 열에 진행률이 표시될 수 있습니다.

    2019년에 도입된 가속 데이터베이스 복구 기능 덕분에 긴 롤백은 거의 발생하지 않습니다.

    해결 방법: SPID가 변경 내용 롤백을 완료할 때까지 기다립니다.

    이러한 상황을 방지하려면 OLTP 시스템에서 바쁜 시간 동안 대규모 일괄 처리 쓰기 작업이나 인덱스 생성 또는 유지 관리 작업을 수행하지 않습니다. 가능하면 활동이 적은 기간 동안 이러한 작업을 수행합니다.

  5. 분리된 연결로 인한 차단

    클라이언트 응용 프로그램에서 오류가 발생하거나 클라이언트 워크스테이션이 다시 시작되면 상황에 따라 서버에 대한 네트워크 세션이 즉시 취소되지 않을 수 있습니다. Azure SQL 데이터베이스 관점에서 클라이언트는 여전히 존재 하는 것처럼 보이지만 획득한 모든 잠금은 계속 유지될 수 있습니다. 자세한 내용은 SQL Server에서 분리된 연결 문제를 해결하는 방법을 참조하세요.

    해결 방법: 클라이언트 응용 프로그램의 리소스를 적절하게 정리하지 않고 연결을 끊은 경우 KILL 명령을 사용하여 SPID를 종료할 수 있습니다. KILL 명령은 SPID 값을 입력으로 사용합니다. 예를 들어, SPID 99를 종료하려면 다음 명령을 발행합니다.

    KILL 99

관련 콘텐츠