INF: 이해 하 고 SQL Server 블로킹 문제를 해결 합니다.

적용 대상: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Standard Edition

요약


이 문서의 "연결" 데이터베이스의 단일 로그온 세션을 나타냅니다. 각 연결 된 세션 ID (SPID)로 나타납니다. 각이 Spid는 라고도 과정은 상식적으로는 별도 프로세스 컨텍스트가 아니지만. 각 SPID는 대신, 서버 리소스 및 데이터 구조에 지정된 된 클라이언트에서 단일 연결 요청을 처리 하는 데 필요한 구성 됩니다. 단일 클라이언트 응용 프로그램은 하나 이상의 연결이 있을 수 있습니다. SQL Server 측면에서 단일 클라이언트 컴퓨터에서 클라이언트 응용 프로그램의 여러 연결과 여러 클라이언트 응용 프로그램 또는 여러 클라이언트 컴퓨터에서 여러 개의 연결 사이는 차이점이 있습니다. 한 연결이 다른 연결을 블로킹할 두 개의 서로 다른 클라이언트 컴퓨터에서 별도 응용 프로그램 또는 동일한 응용 프로그램을 차단할 수 있습니다.

자세한 내용


차단 잠금 기반 동시성을 사용 하 여 모든 관계형 데이터베이스 관리 시스템 (RDBMS)의 특성입니다. SQL Server 블로킹 발생 한 SPID는 특정 리소스에 잠금을 유지 하 고 두 번째 SPID가 동일한 리소스에는 충돌 하는 잠금 유형을 얻으려고 합니다. 일반적으로, 첫째 spid가 리소스를 잠그는 시간 프레임 매우 낮습니다. 잠금을 해제 하는 경우 두 번째 연결은 자유롭게 리소스에서 자체 잠금 획득 하 고 처리를 계속할 수입니다. 이 정상적인 동작이 며 시스템 성능에 크게 영향을 주지 않습니다 하루 과정에서 여러 번 발생할 수 있습니다.

쿼리의 기간 및 트랜잭션 컨텍스트는 잠금 보유 시간 결정을 나타내고 다른 쿼리에 미치는 영향. 쿼리가 트랜잭션에서 실행 되지 않습니다 (및 없음 잠금 힌트 사용) SELECT 문에 대 한 잠금만 보류 됩니다 리소스 쿼리 기간에 실제로 읽고 당시. INSERT, UPDATE 및 DELETE 문에 대 한 쿼리를 쿼리를 필요에 따라 다시 롤백할 수 있도록 하 고 데이터 일관성을 위해 둘 다 동안 잠금이 보유 됩니다.

트랜잭션 내에서 실행 되는 쿼리에 대 한 기간을 쿼리를 트랜잭션 격리 수준이의 유형에 따라 결정 됩니다 잠금 여부 및 잠금이 보유 됩니다 힌트는 쿼리에 사용 된. 잠금 힌트 및 트랜잭션 격리 수준 잠금의 내용은 SQL Server 온라인 설명서의 다음 항목을 참조.
  • 데이터베이스 엔진에서 잠금
  • 사용자 정의 잠금 및 행 버전 관리
  • 잠금 모드
  • 잠금 호환성
  • 데이터베이스 엔진의 행 버전 관리 기반 격리 수준
  • 트랜잭션 (데이터베이스 엔진)를 제어합니다.
잠금 및 차단 지점까지 증가 하는 경우 다음과 같은 이유 중 하나로 인해 일반적으로 시스템 성능에 나쁜 영향을가.
  • 릴리스하기 전에 오랜된 기간에 대 한 리소스 집합에 대해 잠금을 보유 하는 SPID. 이러한 블로킹 유형은 시간이 지나면서 스스로 해결 되지만 성능이 저하 될 수 있습니다.
  • SPID는 리소스 집합에 대해 잠금을 보유 하 고 풀지 않습니다. 이러한 블로킹 유형은 자체적으로 해결 되지 및 영향을 받는 리소스에 무제한으로 액세스를 방지 합니다.
위의 처음 시나리오에서 블로킹 문제는 시간이 지나면서 스스로 해결 SPID 잠금을 해제 하는 때. 그러나 상황이 매우 유체를 시간에 따라 서로 다른 리소스 차단, 이동 대상을 만들면서 여러 Spid 원인 될 수 있습니다. 따라서 이러한 경우 개별 SQL 쿼리 또는 SQL Server 엔터프라이즈 관리자를 사용 하 여 문제를 해결 하는 데 어려울 수 있습니다. 두 번째 상황에서는 쉽게 진단할 수 있는 일관 된 상태에 발생 합니다.

블로킹 정보 수집

블로킹 문제 해결의 어려움을 대항 하기 위해 데이터베이스 관리자가 잠금 및 차단에 SQL Server 상태를 지속적으로 모니터 하는 SQL 스크립트를 사용할 수 있습니다. 이러한 스크립트는 문제의 전체적인 그림을 특정 인스턴스의 스냅샷을 시간이 지남에 따라 제공할 수 있습니다. SQL 스크립트로 블로킹을 모니터 하는 방법에 대 한 Microsoft 기술 자료의 다음 문서를 참조 하십시오.
271509 SQL Server 2005 및 SQL Server 2000 블로킹을 모니터링 하는 방법

이 문서의 스크립트는 아래 작업을 수행 합니다. 가능한 경우 SQL Server Management Studio이 정보를 가져오기 위한 메서드가 제공 됩니다.
  1. 블로킹 체인 헤드에서 SPID (세션 ID) 및 SQL 문을 확인 합니다.
    앞에서 언급 한 기술 자료 문서에서 스크립트를 사용 하는 것 외에도 SQL Server Management Studio 통해 제공 되는 기능을 사용 하 여 블로킹 체인의 헤드를 식별할 수 있습니다. 이렇게 하려면 다음 방법 중 하나를 사용 합니다.
    • 서버 개체를 마우스 오른쪽 단추로 클릭 보고서표준 보고서확장 한 다음 작업-모든 차단 트랜잭션을클릭 합니다. 이 보고서는 블로킹 체인의 헤드에 트랜잭션을 나타냅니다. 트랜잭션을 확장 하면 헤드 트랜잭션에 의해 차단 된 트랜잭션 보고서에 표시 됩니다. 이 보고서는 "차단 SQL 문" 및 "차단 됨 SQL 문." 표시 됩니다.
    • DBCC INPUTBUFFER(<spid>)를 사용 하 여 SPID에 제출 된 마지막 문을 찾습니다.
  2. 블로킹 SPID의 처리 상태 및 트랜잭션 중첩 수준을 찾습니다.
    SPID의 트랜잭션 중첩 수준을 @@TRANCOUNT 전역 변수에 제공 됩니다. 그러나 확인할 수에서 SPID 외부 sysprocesses 테이블을 쿼리하여 다음과 같습니다.

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    반환 되는 값은 SPID에 대 한 @@TRANCOUNT 값입니다. 차례 대로 설명할 수 잠금을 보유 이유는 블로킹 SPID의 트랜잭션 중첩 수준을 표시 합니다. 예를 들어, 값이 0 보다 크면 SPID는 트랜잭션 (있는 경우 예상 그대로 트랜잭션 격리 수준에 따라 확보 한 특정 잠금) 중간은.

    DBCC opentran 문을 사용 하 여 데이터베이스에 장기 개방 트랜잭션이 존재 하는지 확인 하려면 확인할 수 있습니다.
    database_name.

SQL Server Profiler 추적 정보를 수집합니다.

위의 정보 외에 캡처할 프로필러 추적 SQL Server 블로킹 문제를 철저 하 게 조사를 서버에서 활동 하는 데 필요한 경우가 많습니다. SPID는 트랜잭션 내에서 여러 문을 실행 하는 경우 마지막 statementthat에만 활동 모니터 출력, 입력된 버퍼 또는 보고서에 표시 됩니다 제출 되었습니다. 그러나 여전히 유지 되 고 잠금 원인일 수 있습니다 이전 명령 중 하나입니다. 프로필러를 사용 하면 현재 트랜잭션에서 SPID에 의해 실행 되는 명령을 볼 수 있습니다. 다음 단계는 SQL Server Profiler 추적 캡처 설정 하는 데 도움이 됩니다.
  1. SQL Server Profiler 엽니다.
  2. 파일 메뉴에서 새로 만들기를 가리킨 다음 추적을 클릭 하 고
  3. 일반 탭에서 추적 이름과 데이터를 캡처하는 파일 이름을 지정 합니다.

    중요: 추적 파일은 빠른 로컬 또는 공유 디스크에 작성 되어야 합니다. 느린 디스크 또는 네트워크 드라이브에 대 한 추적을 하지 않습니다. 또한 서버 처리 추적 데이터가 선택 되어 있는지 확인 하십시오.
  4. 이벤트 선택 탭에서 모든 이벤트를 표시모든 열 표시 확인란을 선택 하려면 클릭 합니다.
  5. 이벤트 선택 탭에서 표 1에 나열 된 이벤트 유형을 추적에 추가 합니다.

    또한 대 한 자세한 내용은 표 2에 나열 된 추가 이벤트 형식을 포함할 수 있습니다. 대량 생산 환경에서 실행 하는 경우 일반적으로 충분히 블로킹 문제의 대부분을 해결 하는 표 1의 이벤트만 사용할 수도. 추가 이벤트를 포함 하 여 표 2에는 쉽게 문제의 원인을 쉽게 확인할 수 또는 이러한 이벤트 원인 문을 여러 문 프로시저에서를 식별 하기 위해 필요할 수 있습니다. 그러나 이벤트를 포함 하 여 표 2에는 또한 시스템에 부하 및 추적 출력의 크기를 늘립니다.
표 1: 이벤트 유형
제목이벤트
오류 및 경고예외
오류 및 경고주의
보안 감사로그인 감사
보안 감사감사 로그 아웃
세션기존 연결
저장된 프로시저RPC:Starting
TSQLSQL:BatchStarting

표 2: 추가 이벤트 유형
제목이벤트
트랜잭션Dtc 트랜잭션
트랜잭션SQLTransaction
저장된 프로시저RPC:Completed
TSQLSQL:BatchCompleted
저장된 프로시저SP:StmtStarting
저장된 프로시저SP:StmtCompleted

SQL Server Profiler 사용 하는 방법에 대 한 자세한 내용은 SQL Server 온라인 설명서를 참조 하십시오.

확인 하 고 일반 블로킹 시나리오를 해결 합니다.

위의 정보를 살펴보면 대부분 블로킹 문제의 원인을 확인할 수 있습니다. 이 정보를 사용 하 여 확인 하 고 일부 일반 블로킹 시나리오를 해결 하는 방법 설명 하는이 문서의 나머지 부분입니다. 이 토론 위에서 설명한 이벤트를 사용 하 여 문서 271509 (앞서 참조 된) 블로킹 Spid에 대 한 정보를 캡처하고 프로필러 추적에 대 한 블로킹 스크립트를 사용한 가정 합니다.

블로킹 스크립트 출력 보기

블로킹 체인의 헤드를 확인 하려면 출력 옵션 을 검토합니다
블로킹 스크립트의 고속 모드를 지정 하지 않았으면 "블로킹 체인 헤드에서 Spid" 이라는 제목의 섹션 스크립트 출력에 다른 Spid를 차단 하는 Spid를 나열 하는 없을 것입니다.
SPIDs at the head of blocking chains
Fast 옵션을 지정 하는 경우 출력 옵션 및 차단 된 열에 보고 된 SPID의 계층 구조를 따르면 보고 블로킹 헤드를 확인할 수 있습니다.
블로킹 체인 헤드에서 Spid에 대 한 내용은 옵션 출력을 검토 합니다.
다음 옵션 필드를 평가 하는.

상태

이 칼럼은 특정 SPID의 상태를 나타냅니다. 일반적으로 휴면 상태는 SPID가 실행을 완료 하 고 기다리고 다른 쿼리 또는 일괄 처리를 전송 하는 응용 프로그램을 나타냅니다. 실행 가능, 실행또는 sos_scheduler_yield 상태는 SPID 현재 처리 하는 쿼리를 나타냅니다. 다음 표에서 제공 다양 한 상태 값에 간략하게 설명 합니다.
상태의미
배경SPID는 교착 상태 감지와 같은 백그라운드 작업을 실행 중입니다.
절전 모드SPID가 현재 실행 하 고 있지. 이 SPID 응용 프로그램에서 명령을 기다리고 있음을 나타냅니다.
실행SPID가 현재 스케줄러에서 실행 됩니다.
실행 가능SPID는 스케줄러를 얻으려고 스케줄러의 실행 가능한 큐입니다.
Sos_scheduler_yieldSPID가 실행 중인 있지만 다른 SPID 스케줄러 시간을 얻도록 허용 하도록 스케줄러에 해당 시간 조각 자발적으로 낳.
일시 중단SPID를 래치 잠금 등의 이벤트를 기다리고 있습니다.
롤백SPID는 트랜잭션 롤백 중입니다.
DefwakeupSPID 해제 되 고 있는 프로세스에 있는 리소스를 기다리고 있음을 나타냅니다. 대기 필드에는 리소스에 지정 되어야 합니다.

Open_tran

이 필드는 SPID의 트랜잭션 중첩 수준을 나타냅니다. 이 값이 0 보다 크면 SPID는 열려 있는 트랜잭션에서 고 트랜잭션의 문으로 확보 한 잠금 유지 있습니다.

Lastwaittype 및 waittype, waittime

Lastwaittype 필드는 예약 된 내부 이진 열 유형 필드의 문자열 표현입니다. Waittype 0x0000 이면 SPID가 현재 기다리고 있지 아무것도 및 lastwaittype 값 SPID를가지고 있던 마지막 waittype 을 나타냅니다. Waittype 이 0이 아니면 lastwaittype 값은 현재 waittype SPID의 나타냅니다.

여러 lastwaittypewaittype 값의 간단한 설명과 Microsoft 기술 자료의 다음 문서를 참조 하십시오.
SQL Server 2000 및 SQL Server 2005는 master.dbo.sysprocesses 테이블의 waittype 및 lastwaittype 열 822101 설명

Sys.dm_os_wait_stats에 대 한 자세한 내용은 SQL Server 온라인 설명서를 참조 합니다.

Waittime 값은 SPID가 진행 하는 경우 확인 하려면 사용할 수 있습니다. 이전 잠금이 획득 된 나타냅니다 옵션 테이블에 대해 쿼리 옵션의 이전 쿼리의 waittime 값 보다 작으면 waittime 열에 값을 반환 하 고 해제 하 고 새 잠금 대기 중 (가정 0 waittime). 출력 옵션 사이의 대기 를 비교 하 여이 확인할 수 있습니다.

대기

이 필드는 SPID가 기다리고 있는 리소스를 나타냅니다. 다음 표에 일반적인 대기 형식과 그 의미를 보여 줍니다.
리소스형식예제
테이블DatabaseID:ObjectID:IndexID탭: 5:261575970:1
이 경우 데이터베이스 ID 5는 pubs 예제 데이터베이스 및 개체 ID 261575970은 titles 테이블 및 1은 클러스터 된 인덱스.
페이지DatabaseID:FileID:PageID페이지: 5:1:104
이 경우 데이터베이스 ID 5는 pubs파일 ID 1은 기본 데이터 파일 및 페이지 104는 titles 테이블에 속하는 페이지입니다.

페이지가 속해 있는 개체 id를 확인 하려면 DBCC PAGE (dbid, 필드, pageid, output_option) 명령을 사용 하 고 있는 m_objId 확인 합니다. 예를 들어:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
DatabaseID:Hobt_id (인덱스 키에 대 한 해시 값)키: 5:72057594044284928 (3300a4f361aa)

이 경우 데이터베이스 ID 5는 Pubs, 개체 id 261575970 (titles 테이블)에 대 한 2 비 클러스터 된 인덱스에 해당 하는 Hobt_ID 72057594044284928. Sys.partitions 카탈로그 뷰를 사용 하 여 특정 인덱스 id 및 개체 id hobt_id에 연결할. 특정 인덱스 키 값을 인덱스 키 해시를 취소 하는 방법은 없습니다.
DatabaseID:FileID:PageID:Slot(row)5:1:104:3 제거:

이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 titles 테이블에 속하는 페이지 및 슬롯 3은 페이지에서 행의 위치를 나타냅니다.
컴파일DatabaseID:ObjectID [[컴파일]]탭: 5:834102012 [[컴파일]] 이것이 테이블 잠금을 하지만 컴파일이 아니라 잠그는 저장된 프로시저. 데이터베이스 ID 5는 pubs, 개체 ID 834102012 usp_myprocedure 저장된 프로시저입니다. 차단 컴파일 잠금을 인해 발생에 대 한 자세한 내용은 기술 자료 문서 263889을 참조 하십시오.
다른 열

나머지 옵션 열은 문제의 루트에 대 한 정보를 제공할 수 있습니다. 유용 문제 상황에 따라 달라 집니다. 예를 들어, 특정 클라이언트 (호스트)의 문제가 발생 하는 경우 특정 네트워크 라이브러리 (net_library), SPID에서 제출한 마지막 일괄 처리가 (last_batch) 때에 확인할 수는 있습니다.
DBCC inputbuffer가 지워지기 출력을 검토 합니다.
또는 0이 아닌 waittype 블로킹 체인의 헤드에 있는 SPID를 블로킹 스크립트 그 SPID에 대해 현재 쿼리를 확인 하려면 DBCC inputbuffer가 지워지기 실행 됩니다.

이것은 대부분의 경우에서 잠금은 다른 사용자가 보류를 차단 하는 원인이 되는 쿼리입니다. 그러나 SPID는 트랜잭션 내에서 경우 잠금 수 취득 이전에 실행 된 쿼리에서 현재 슬라이드가 아니라. 따라서 프로파일러 출력 inputbuffer가 지워지기 뿐 아니라 해당 SPID에 대 한 확인도 해야 합니다.

참고: 없기 때문에 블로킹 스크립트는 여러 단계로 구성 됩니다, SPID는 블로킹 체인의 헤드와 첫 번째 섹션에 나타날 수 없습니다 있지만 DBCC inputbuffer가 지워지기 쿼리가 실행 될 때, 차단 더 이상이 하 여 inputbuffer가 지워지기 캡처되지 않습니다. 한다는 의미 차단 해결 자체가 그 SPID에 대해 수도 문제가 되지 않을 수 있습니다. 이 시점에서 사용 하 여 블로킹 스크립트의 고속 버전 (비록 보장할 수는 없습니다)를 삭제 하기 전에 여 inputbuffer가 지워지기를 캡처할 수 있도록 하거나 SPID를 실행 하는 쿼리를 결정할 시간 프레임에서 프로파일러 데이터 보기.

프로필러 데이터 보기

프로필러 데이터를 효과적으로 보기는 블로킹 문제 해결에서 아주 중요 합니다. 실현 하는 데 가장 중요 한 것은 캡처한 모든 것; 볼 필요가 없습니다. 선택 되어야 합니다. 프로필러는 캡처한 데이터를 분석할 수 있도록 효과적으로 기능을 제공 합니다. 속성 대화 상자에서 파일 메뉴의 등록 정보를 클릭 프로필러 데이터 열 이나 이벤트를 제거 하 고 데이터 열 그룹화 (정렬) 필터를 적용 하 여 표시 되는 데이터를 제한할 수 있습니다. 전체 추적 이나 특정 열 에서만 특정 값을 검색할 수 있습니다 ( 편집 메뉴에서 찾기를 클릭). 프로파일러 데이터를 SQL Server 테이블에 저장할 수도 있습니다 ( 파일 메뉴에서 다른 이름으로 저장 을 가리킨 다음 를 클릭)에 대 한 SQL 쿼리를 실행 합니다.

조심 그 이전에 저장된 한 추적 파일에만 필터링을 수행합니다. 활성화 된 추적에서 이러한 단계를 수행 하면 추적을 시작한 후에 캡처한 데이터가 손실 될 위험이 있습니다. 활성화 된 추적 파일을 저장 하거나 먼저 표 ( 파일 메뉴에서 다른 이름으로 저장클릭) 한 다음 닫았다가 ( 파일 메뉴에서 열기를 클릭) 계속 하기 전에. 저장된 한 추적 파일에서 작업 하는 경우는 제거 하지 않으며 영구적으로 필터링 된 데이터, 단지 모든 데이터를 표시 하지 않습니다. 추가 및 검색 이벤트와 데이터 열을 지정할 수를 제거할 수 있습니다.

찾을 대상:
  • 어떤 명령을 실행 현재 트랜잭션에서 블로킹 체인의 헤드에 있는 SPID에 있습니까?
    블로킹 체인의 헤드에 있는 특정 SPID에 대 한 추적 데이터를 필터링 ( 파일 메뉴에서 속성을 클릭) 다음 필터 탭에서 SPID 값을 지정 합니다. 그런 다음 다른 Spid를 차단 된 시간 전에 실행 한 명령을 확인할 수 있습니다. 트랜잭션 이벤트를 포함 하면 트랜잭션이 시작 될 때 쉽게 식별할 수 있습니다. 그렇지 않으면 검색할 수 있습니다 텍스트 열 BEGIN, SAVE, COMMIT 또는 ROLLBACK 트랜잭션 작업입니다. Sysprocesses 테이블에서 open_tran 값을 사용 하 여 모든 트랜잭션 이벤트를 catch 할 수 있도록 합니다. 실행 된 명령과 트랜잭션 컨텍스트를 알면 SPID 잠금을 유지 하는 이유를 확인할 수 있습니다.

    기억, 이벤트 및 데이터 열을 제거할 수 있습니다. 모두 시작 하는 대신 완료 이벤트 하나를 선택 합니다. 블로킹 Spid 없는 저장된 프로시저를 제거 하면
    SP: 시작 또는 SP: 완료 이벤트 SQLBatchRPC 이벤트 프로시저 호출이 표시 됩니다. SP 이벤트 수준의 세부 정보를 확인 해야 할 때 볼만 합니다.
  • 블로킹 체인의 헤드에 있는 Spid에 대 한 쿼리 입니까?
    위의 완료 된 이벤트를 포함 하는 경우 쿼리 실행 시간 기간 열에 표시 됩니다. 그러면 차단 하 여 발생 하는 장기 실행 쿼리를 식별할 수 있습니다. 쿼리가 느리게 이유를 확인 하려면 실행 계획 이벤트 뿐만 아니라 CPU, 읽기쓰기 열을 봅니다.

일반 블로킹 시나리오 분류

다음 표에서 해당 되는 원인을 일반적인 증상을 매핑합니다. Scenario 열에 표시 된 번호는 아래 문서의 "일반 블로킹 시나리오 및 해결책" 절에서 수에 해당 합니다. Waittype, Open_Tran상태sysprocesses 정보를 참조 하십시오. 해결? 열 표시 여부를 자체적으로 해결을 차단 합니다.

시나리오유형Open_Tran상태해결?다른 현상
10이 아닌> = 0실행 가능예, 쿼리가 완료할 때입니다.Physical_IO, CPU 및/또는 Memusage 열은 시간이 지남에 따라 증가 합니다. 쿼리의 기간은 완료 될 때 높은 됩니다.
20x0000> 0절전 모드아니오, SPID를 종료할 수 있지만.취소가 또는 쿼리 시간 제한을 나타내는이 SPID에 대해 프로파일러 추적에서 주의 신호를 발생할 수 있습니다.
30x0000> = 0실행 가능아니요. 클라이언트가 모든 행을 반입 하거나 연결을 닫을 때까지 해결 되지 않습니다. SPID는 중단 됩니다 있지만 30 초 정도 걸릴 수 있습니다.경우 open_tran = 0, 및 spid에 잠금 트랜잭션 격리 수준을 기본값 (읽기 COMMMITTED)은, 일반적인 원인입니다.
4달라 집니다.> = 0실행 가능아니요. 클라이언트 쿼리를 취소 하거나 연결을 닫을 때까지 해결 되지 않습니다. Spid는 중단 됩니다 있지만 30 초 정도 걸릴 수 있습니다.블로킹 체인의 헤드에 있는 SPID의 sysprocesses hostname 열 차단 하는 SPID 중 하 나와 같습니다.
50x0000> 0롤백예입니다.쿼리 시간 제한을 나타내는이 SPID에 대해 프로파일러 추적에서 주의 신호를 볼 수 있습니다 또는 취소가 단순히 롤백 문이 실행 되었습니다.
60x0000> 0절전 모드결국. Windows NT 세션 활성화 되어 더 이상 결정을 때 SQL Server 연결이 중단 됩니다.Sysprocesseslast_batch 값은 현재 시간 보다 훨씬 빠릅니다.

일반 블로킹 시나리오 및 해결책

아래에 나열 된 시나리오는 위의 표에 나열 된 특성을 갖습니다. 해결 뿐만 아니라 해당 하는 경우, 추가 정보를 제공이 합니다.
  1. 정상적으로 실행에 한 블로킹 실행 시간이 긴 쿼리

    해결 방법:
    이러한 유형의 블로킹 문제를 해결 하려면 쿼리를 최적화 하는 방법을 확인 하는 것입니다. 사실이 유형의 블로킹 문제는 단지 성능 문제일 수 및 이러한 관점에서 해결 해야 할. 특정 실행 속도가 느린 쿼리 문제 해결에 대 한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조 하십시오.
    243589 SQL Server 7.0 또는 이후 버전에서 실행이 느린 쿼리를 해결 하는 방법

    전반적인 응용 프로그램 성능 문제 해결에 대 한 기술 자료의 다음 문서를 참조 하십시오.
    224587 방법: SQL Server 사용 하 여 응용 프로그램 성능 문제를 해결

    자세한 내용은 다음 MSDN 웹 사이트의 성능 모니터링 및 튜닝 방법 항목 SQL Server 2008 온라인 설명서 항목을 참조.다른 사용자를 블로킹 하 고 최적화 될 수 있는 장기 실행 쿼리가 OLTP 환경에서 의사 결정 지원 시스템으로 이동 하는 것이 좋습니다.
  2. 잠을 SPID의 트랜잭션 중첩 수준 추적 손실에 한 블로킹

    이러한 블로킹 유형은 명령 대기 시키거나 대기를 하면서도 (@@TRANCOUNT, 시스템 프로세스에서 open_tran ) 해당 트랜잭션 중첩 수준을 0 보다 크면 SPID에 종종 확인할 수 있습니다. 응용 프로그램 쿼리 시간 초과가 발생 하거나도 필요한 수의 ROLLBACK 및/또는 COMMIT 문 실행 하지 않고 취소를 발급 하는 경우 발생할 수 있습니다. SPID가 쿼리 시간 제한 또는 취소를 받으면 됩니다 현재 쿼리 및 일괄 처리를 종료 하지만 자동으로 원상 복구 하거나 않는 트랜잭션을 커밋합니다. 응용 프로그램이이 책임 지는 SQL Server 전체 트랜잭션이 롤백되어야 취소 중인 단일 쿼리로 인해 단순히 가정할 수 없습니다. 쿼리 시간 제한 또는 취소 프로필러 추적에 SPID에 대 한 주의 신호 이벤트로 표시 됩니다.

    이 보여 주기 위해 쿼리 분석기에서 다음과 같은 간단한 쿼리를 실행 합니다.

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2

    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    있는 빨간색을 클릭 하면 쿼리를 실행 하는 동안 취소 단추입니다. 쿼리가 취소 된 후 선택 @@TRANCOUNT 트랜잭션 중첩 수준이 하나 임을 나타냅니다. 이렇게 이었다 삭제 또는 업데이트 쿼리 또는 선택에서 획득 한 잠금을 계속 유지 됩니다 모든 HOLDLOCK 사용 된. 위의 쿼리를 사용 하 여도 다른 쿼리 획득 했으며 이전 트랜잭션에서 잠금을 보유 하는 경우은 계속 유지 됩니다 위의 SELECT가 취소 하는 경우.

    해결 방법:

    • 응용 프로그램은 트랜잭션 중첩 수준을 적절히 관리 해야 하거나 이러한 방식으로 쿼리 취소 다음에 블로킹 문제를 발생할 수 있습니다. 이 여러 가지 방법 중 하나로 수행할 수 있습니다.
      1. 클라이언트 응용 프로그램의 오류 처리기에서 IF 제출 @@TRANCOUNT > 0 오류를 클라이언트 응용 프로그램은 트랜잭션을 인식 하지 않으므로 경우에 뒤에 ROLLBACK TRAN는 열. 저장된 프로시저 호출 일괄 처리 하는 동안 클라이언트 응용 프로그램의 지식 없이 트랜잭션을 시작 수 때문에 이것이 필요 합니다. 특정 조건에서 쿼리를 취소 하는 등 현재는 문으로 이런이 롤백 코드를 실행 되지 것입니다 논리 IF @@ERROR <> 0를 확인 하는 트랜잭션을 중단 프로시저에 있는 경우 실행 프로시저를 방지 note 사례입니다.
      2. SET XACT_ABORT ON을 사용 하 여 연결 또는 저장된 프로시저는 트랜잭션을 시작 하 고 다음 오류가 정리 하지. 하면 런타임 오류가 발생 하는 경우이 설정은 열려 있는 모든 트랜잭션을 중단 하 고 컨트롤을 클라이언트에 반환 됩니다. Note T SQL 문은 오류를 일으킨 문 다음 실행 되지 않습니다.
      3. 연결 및 웹 기반 응용 프로그램과 같은 풀에 적은 수의 연결을 해제 하기 전에 쿼리를 다시 실행 되는 응용 프로그램에서 연결 풀링을 사용 중인 경우 일시적으로 연결 풀링을 사용 하지 않도록 설정 될 수 있습니다 완화 하기는 클라이언트 응용 프로그램 오류를 적절 하 게 처리 하기 위해 수정 될 때까지 문제가 발생 했습니다. 연결 풀링을 사용 하지 않음으로써 연결 해제 하면 열려 있는 모든 트랜잭션을 롤백하는 중 서버에 SQL Server 연결의 실제 아웃을 발생 합니다.
      4. 연결 풀링을 사용 하도록 설정 하는 경우 대상 서버는 SQL Server 2000 업그레이드 MDAC 2.6 또는 나중에 클라이언트 컴퓨터의 도움이 될 수 있습니다. 이 버전의 MDAC 구성 요소를 연결 합니다 "재설정"이 재사용 되기 전에 ODBC 드라이버 및 OLE DB 공급자에 코드를 추가 합니다. 이 호출은 sp_reset_connection (클라이언트 응용 프로그램에 의해 시작 된 DTC 트랜잭션은 영향을 받지 않습니다) 서버 기반의 트랜잭션 중단, 기본 데이터베이스, 옵션 설정 등을 다시 설정 합니다. 참고 연결 되므로 사용자가 트랜잭션을 열 수를 다음 연결 풀으로 연결을 해제 하지만 그러는 동안 몇 초간 다시 사용할 수 있습니다 연결 풀에서 재사용 될 때까지 다시 설정 하지는 트랜잭션 열린 상태로 유지 됩니다. 연결이 되지를 재사용 하는 경우 연결 제한 시간이 초과 하는 연결 풀에서 제거 될 때 트랜잭션이 중단 됩니다. 따라서 오류 처리기가 트랜잭션을 중단 하거나 SET XACT_ABORT ON을 사용 하 여 이러한 잠재적인 지연을 방지 하기 위해 클라이언트 응용 프로그램에 대 한 최적입니다.
    • 사실이 유형의 블로킹 문제 수 또한 성능 문제일 수 고 이러한 관점에서 해결 해야 할. 쿼리 실행 시간이 줄어들면 쿼리 시간 제한 또는 취소 발생 하지 않습니다. 응용 프로그램 처리 시간 제한 또는 취소 시나리오가 수 반드시 발생할 때 있지만 쿼리 성능 검사에서 얻을 수 있습니다.
  3. 완료 될 때까지 해당 클라이언트 응용 프로그램의 모든 결과 행을 반입 하지 못한 SPID에의 한 블로킹

    쿼리 서버에 보낸 후 모든 응용 프로그램이 완료 될 때까지 즉시 모든 결과 행 반입 해야 합니다. 응용 프로그램에서 모든 결과 행을 반입 하지, 하는 경우 다른 사용자를 블로킹 하면서 테이블에 잠금은 유지할 수 있습니다. 투명 하 게 SQL 문을 서버에 제출 하는 응용 프로그램을 사용 하는 응용 프로그램이 모든 결과 행을 반입 해야 합니다. 그렇지 않은 경우 (및 이렇게 하려면 구성할 수 없거나), 블로킹 문제를 해결할 수 있습니다. 이 문제를 방지 하려면 응용 프로그램을 보고 또는 의사 결정 지원 데이터베이스에 제대로 작동을 제한할 수 있습니다.

    해결 방법:

    응용 프로그램이 완료 될 때까지 결과의 모든 행을 반입 하 여 다시 작성 되어야 합니다.
  4. 분산된 클라이언트/서버 교착 상태에의 한 블로킹

    전형적인 교착 상태와는 달리 분산된 교착 상태가 아닙니다 RDBMS 잠금 관리자를 사용 하 여 검색할 수 있습니다. 관련된 된 리소스 중 하나는 SQL Server 잠금 것 이기 때문입니다. 반대편으로 교착 상태는 SQL Server 권한이 없는 클라이언트 응용 프로그램 수준입니다. 다음은 두 가지 방법 보다 우선 되도록 및 가능한 방식으로 응용 프로그램에서 피할 수 있습니다.

    1. 클라이언트/서버 분산 교착 상태는 단일 클라이언트 스레드와의
      클라이언트 연결이 여러 개 열려 있고 단일 실행 스레드가 있으면 다음과 같은 분산된 교착 상태가 발생할 수 있습니다. 간결 dbproc"용어" 사용 하는 여기는 클라이언트 연결 구조를 의미 합니다.


      SPID1------blocked on lock------->SPID2
      /\ (waiting to write results
      | back to client)
      | |
      | | Server side
      | ================================|==================================
      | <-- single thread --> | Client side
      | \/
      dbproc1 <------------------- dbproc2
      (waiting to fetch (effectively blocked on dbproc1, awaiting
      next row) single thread of execution to run)
      위의 경우에 단일 클라이언트 응용 프로그램 스레드에 열린 연결이 두 개에 있습니다. 또한 dbproc1에서 SQL 작업을 비동기적으로 제출합니다. 즉, 진행 하기 전에 반환 예제에서 기다리지 않습니다. 다음 응용 프로그램, dbproc2에서 다른 SQL 작업을 제출 하 고 반환된 된 데이터를 처리 하기 시작 하 고 결과. 데이터가 되돌아 오기 시작 하면 (어떤 dbproc이 먼저 응답-생각 하는 것이 dbproc1는) 그 dbproc에 반환 된 모든 데이터가 완료 될 때까지 처리. SPID1 잠금을 보유 SPID2 (두 쿼리 서버에서 비동기적으로 실행 하는) 때문에 차단 될 때까지 dbproc1에서 결과를 페치합니다. 이제 dbproc1 더 많은 데이터에 대 한 무기한 대기 합니다. SPID2에서 잠금을 차단 되지 않습니다 하지만 클라이언트에 dbproc2 데이터를 보내려고 합니다. 그러나 dbproc2는 효과적으로 차단 dbproc1는 응용 프로그램 계층에 단일 응용 프로그램에 대 한 실행 스레드가 사용 되기 dbproc1가. 이 인해 교착 상태가 발생 하는 SQL Server 검색 하거나 관련 된 리소스 중 하나는 SQL Server 리소스를 확인할 수 없습니다.
    2. 클라이언트/서버 분산 교착 상태 각 연결 마다 스레드가

      별도 스레드는 클라이언트에서 각 연결에 대해 존재 하는 경우 다음과 같이이 분산된 교착 상태의 변형이 계속 발생할 수 있습니다.


      SPID1------blocked on lock-------->SPID2
      /\ (waiting on net write) Server side
      | |
      | |
      | INSERT |SELECT
      | ================================|==================================
      | <-- thread per dbproc --> | Client side
      | \/
      dbproc1 <-----data row------- dbproc2
      (waiting on (blocked on dbproc1, waiting for it
      insert) to read the row from its buffer)
      Dbproc2 및 SPID2 행 시 처리 하 고 버퍼를 통해 각 행 삽입, 업데이트에 대 한 dbproc1 전달 하려는 의도로 SELECT 문을 실행 하는 또는 동일한 테이블에 문을 삭제이 경우는 예제 A와 비슷합니다. 결국, SPID1 SPID2가 차단 되 면 (INSERT, UPDATE 또는 DELETE 수행) (선택 수행). SPID2는 클라이언트 dbproc2에 결과 행을 기록합니다. Dbproc2는 다음 행 버퍼에 dbproc1를 전달 하려고 하지만 발견 dbproc1 사용 중입니다 (차단 SPID1 SPID2에서 차단 되는 현재 삽입, 끝나기를 기다리고). 이 시점에서 dbproc2 dbproc1 (SPID1) 해당 SPID SPID2 하 여 데이터베이스 수준에서 차단 하 여 응용 프로그램 계층에서 차단 됩니다. 다시이 인해 교착 상태가 발생 하는 SQL Server 검색 하거나 관련 된 리소스 중 하나는 SQL Server 리소스를 확인할 수 없습니다.
    A와 B 두 예제는 응용 프로그램 개발자가 알고 있어야 하는 기본적인 문제입니다. 이러한 경우를 적절 하 게 처리 하기 위해 응용 프로그램을 코딩 해야 들.

    해결 방법:

    두 가지 확실 한 해결책은 쿼리 시간 제한을 사용 하거나 바운드 연결을 사용 하 여

    • 쿼리 제한 시간
      쿼리 시간 제한을 제공 된 분산된 교착 상태가 발생 하는 경우, 됩니다 끊어진 경우 시간 제한이 일어날. 쿼리 시간 제한을 사용 하 여 Db-library 또는 자세한 내용은 ODBC 설명서를 참조 하십시오.
    • 바운드 연결
      이 기능에는 여러 개의 연결이 있는 클라이언트를 연결은 서로 블로킹 하지 않습니다 있으므로 단일 트랜잭션 공간으로 바인드할 수 있습니다. 자세한 내용은 SQL Server 7.0 온라인 설명서의 "바인딩된 연결을 사용 하 여" 항목을 참조 하십시오.
  5. 블로킹 SPID에 "골든" 또는 롤백 상태

    데이터 수정 쿼리를 중단 되었거나 사용자 정의 트랜잭션 외부에서 취소 된 롤백됩니다. 클라이언트 컴퓨터가 다시 시작 하 고 해당 네트워크 세션 연결이 끊어지질 때의 부작용으로도 발생할 수 있습니다. 마찬가지로, 교착 상태 희생자로 선택 된 쿼리는 롤백됩니다. 데이터 수정 쿼리는 종종 롤백할 수 없습니다 변경 사항이 처음 보다 더 빠르게. 예를 들어, 한 시간에 대 한 DELETE, INSERT 또는 UPDATE 문을 실행 했을 경우 걸릴 수 이상 원상 복구 하는 데 한 시간. 이것이 예상 되는 동작은 변경 내용을 완전히 롤백되어야 합니다 또는 데이터베이스의 트랜잭션 및 실제 무결성이 손상 될 것입니다. 이 동작은 반드시 발생 해야 하므로 SQL Server SPID를 "골든" 또는 롤백 상태를 표시 하는 (즉 죽을 수 없습니다 또는 교착 상태 희생자로 선택). 이렇게 sp_who, ROLLBACK 명령을 나타내는 출력을 관찰 하 여 종종 확인할 수 있습니다. 옵션상태 열에는 sp_who 출력 또는 SQL Server Management Studio 활동 모니터에 나타납니다 롤백 상태를 표시 합니다.
    해결 방법:

    SPID가 수행 된 변경 내용의 롤백을 완료할 때까지 기다려야 합니다.

    데이터베이스를 다시 시작 되 면 복구 모드에서 수, 됩니다 없습니다 액세스할 수 있는 열려 있는 모든 서버가이 작업 중간에 종료 될 경우 트랜잭션 처리 됩니다. 시작 복구는 런타임 복구로 서 트랜잭션마다의 동일한 시간에 기본적으로 하 고이 기간 동안 데이터베이스를 액세스할 수 없습니다. 따라서 강제로 롤백 상태에 있는 SPID 수정 하면 서버는 성능이 저하 됩니다.

    이러한 상황을 방지 하려면 큰 일괄 삽입, 업데이트를 수행 하지 않거나 OLTP 시스템 사용량이 많은 시간 동안 작업을 삭제 합니다. 활동이 적은 기간 동안 가능 하면 이러한 작업을 수행 합니다.
  6. 고아가 된 연결에의 한 블로킹

    하는 경우 클라이언트 응용 프로그램이 트랩 되거나 클라이언트 워크스테이션이 다시 시작 되 면 어떤 조건 서버에 대 한 네트워크 세션을 즉시 취소 되지 않을 수 있습니다. 서버의 관점에서 볼 때 클라이언트 여전히 있는 것으로 나타나고 얻은 모든 잠금은 계속 유지 되. 대 한 자세한 내용은 Microsoft 기술 자료의 다음 문서 번호를 클릭 합니다.:

    137983 분리 된 SQL Server 연결 문제를 해결 하는 방법


    해결 방법:

    클라이언트 응용 프로그램의 리소스를 제대로 정리 하지 않고 연결이 끊긴 경우 KILL 명령을 사용 하 여 SPID를 종료할 수 있습니다. KILL 명령은 SPID 값 입력 변수로 사용 합니다. 예를 들어, SPID 9를 중지로 간단 하 게 다음 명령을 실행:

    KILL 9

    참고: KILL 명령은 KILL 명령에 대 한 검사 간의 간격으로 인해 완료 하는데 30 초까지 걸릴 수 있습니다.

응용 프로그램 참여의 블로킹 문제 관련

블로킹 문제가 발생 하는 경우 서버 측 조정 및 플랫폼 문제에 집중 하는 경향이 있을 수 있습니다. 그러나이 해상도 일반적으로 발생 하지 않습니다 및 시간과 에너지를 클라이언트 응용 프로그램 및 제출 하는 쿼리 검사에 흡수 수 있습니다. 만들어지는 데이터베이스 호출에 대 한 응용 프로그램에서 노출 하는 가시성 수준에 관계 없이 블로킹 문제를 그럼에도 불구 하 고 자주 필요한 응용 프로그램 및 응용 프로그램의를 정확 하 게 제출한 정확한 SQL 문 검사 쿼리 취소, 연결 관리, 모든 가져오기에 대 한 동작 결과 행을. 개발 도구가 연결 관리, 쿼리 취소, 쿼리 시간 제한, 결과 반입 등에 대 한 명시적인 제어를 허용 하지 않으면, 블로킹 문제를 해결할 수 없습니다. 이러한 업무용 OLTP 환경에 특히 SQL Server 대 한 응용 프로그램 개발 도구를 선택 하기 전에 밀접 하 게 검사 해야 합니다.

데이터베이스 및 응용 프로그램의 설계 및 구성 단계 신중 될 수는 반드시. 특히, 리소스 사용, 격리 수준, 트랜잭션 경로 길이 각 쿼리에 대 한 평가 해야 합니다. 각 쿼리 및 트랜잭션은 가능한 일정 해야 합니다. 제대로 연결 관리 규칙을 적용 해야 합니다. 이렇게 하지 않으면 응용 프로그램 적절 한 성능을 낮은 숫자의 사용자에 게 나타날 수 있지만 성능이 저하 될 수 있습니다 사용자가 확연하게 수로는 표시 됩니다.

적절 한 응용 프로그램 및 쿼리 설계를 사용 하 여 Microsoft SQL Server 수천 명의 동시 사용자를 거의 블로킹 하지 않고 단일 서버에서 지원할 수 있습니다.