컴파일 잠금으로 인한 차단 문제 해결

  • 아티클

이 문서에서는 컴파일 잠금으로 인한 차단 문제를 해결하고 resolve 방법을 설명합니다.

원래 제품 버전: SQL Server
원본 KB 번호: 263889

요약

Microsoft SQL Server 저장 프로시저 계획의 복사본은 일반적으로 한 번에 하나의 캐시에만 있습니다. 이를 적용하려면 컴파일 프로세스의 일부 부분을 직렬화해야 하며, 이 동기화는 컴파일 잠금을 사용하여 부분적으로 수행됩니다. 많은 연결이 동시에 동일한 저장 프로시저를 실행하고 컴파일 잠금이 실행될 때마다 해당 저장 프로시저에 대해 가져와야 하는 경우, 세션 ID(SPID)는 각각 개체에 대한 단독 컴파일 잠금을 가져오려고 할 때 서로 차단되기 시작할 수 있습니다.

다음은 차단 출력에서 관찰할 수 있는 컴파일 차단의 몇 가지 일반적인 특성입니다.

  • waittype 차단된 및 (일반적으로) 차단 세션 SPID는 LCK_M_X (배타적)이고 waitresource 는 형식 OBJECT: dbid: object_id [[COMPILE]]입니다. 여기서 object_id 은 저장 프로시저의 개체 ID입니다.

  • 차단에는 waittype NULL, 상태 실행 가능이 있습니다. 차단은 waittypeLCK_M_X (배타적 잠금), 상태 자고 있습니다.

  • 차단 인시던트 기간이 길어질 수 있지만 다른 SPID를 오랫동안 차단하는 단일 SPID는 없습니다. 롤링 차단이 있습니다. 하나의 컴파일이 완료되는 즉시 다른 SPID는 몇 초 이하 동안 헤드 블로커 역할을 맡습니다.

다음 정보는 이러한 종류의 차단 중에 의 sys.dm_exec_requests 스냅샷.

session_id   blocking_session_id   wait_type   wait_time   waitresource ---------- ------------------- --------- --------- ----------------------------
221           29                   LCK_M_X     2141        OBJECT: 6:834102
[[COMPILE]]
228           29                   LCK_M_X     2235        OBJECT: 6:834102
[[COMPILE]]
29            214                  LCK_M_X     3937        OBJECT: 6:834102
[[COMPILE]]
13            214                  LCK_M_X     1094        OBJECT: 6:834102
[[COMPILE]]
68            214                  LCK_M_X     1968        OBJECT: 6:834102
[[COMPILE]]
214           0                    LCK_M_X     0           OBJECT: 6:834102
[[COMPILE]]

waitresource 열(6:834102)에서 6은 데이터베이스 ID이고 834102 개체 ID입니다. 이 개체 ID는 테이블이 아닌 저장 프로시저에 속합니다.

추가 정보

저장 프로시저 다시 컴파일은 저장 프로시저 또는 트리거의 컴파일 잠금에 대한 한 가지 설명입니다. 이 경우 해결 방법은 다시 컴파일을 줄이거나 제거하는 것입니다.

컴파일 잠금으로 이어지는 추가 시나리오

  1. 정규화된 이름 없이 저장 프로시저가 실행됩니다.

    • 저장 프로시저를 실행하는 사용자는 프로시저의 소유자가 아닙니다.
    • 저장 프로시저 이름은 개체 소유자의 이름으로 정규화되지 않았습니다.

    예를 들어 사용자 dbo가 개체 dbo.mystoredproc 를 소유하고 다른 사용자인 Harry가 명령을 exec mystoredproc사용하여 이 저장 프로시저를 실행하는 경우 개체가 소유자로 한정되지 않아 개체 이름별 초기 캐시 조회가 실패합니다. (라는 Harry.mystoredproc 다른 저장 프로시저가 있는지 여부는 아직 알 수 없습니다. 따라서 SQL Server 에 대한 dbo.mystoredproc 캐시된 계획이 실행하기에 올바른 계획인지 확인할 수 없습니다.) SQL Server 프로시저에 대한 단독 컴파일 잠금을 가져오고 프로시저를 컴파일하기 위한 준비를 합니다. 여기에는 개체 이름을 개체 ID로 확인하는 것이 포함됩니다. SQL Server 계획을 컴파일하기 전에 SQL Server 이 개체 ID를 사용하여 프로시저 캐시를 보다 정확하게 검색하고 소유자 자격 없이도 이전에 컴파일된 계획을 찾을 수 있습니다.

    기존 계획이 발견되면 SQL Server 캐시된 계획을 재사용하고 저장 프로시저를 실제로 컴파일하지 않습니다. 그러나 소유자 자격이 없으면 프로그램에서 기존 캐시된 실행 계획을 재사용할 수 있다고 결정하기 전에 두 번째 캐시 조회를 수행하고 배타적 컴파일 잠금을 가져올 SQL Server 있습니다. 잠금을 가져오고 이 지점에 도달하는 데 필요한 조회 및 기타 작업을 수행하면 컴파일 잠금이 지연되어 차단이 발생할 수 있습니다. 이는 저장 프로시저의 소유자가 아닌 많은 사용자가 소유자의 이름을 제공하지 않고 동시에 프로시저를 실행하는 경우에 특히 그렇습니다. 컴파일 잠금을 기다리는 SPID가 표시되지 않더라도 소유자 자격이 부족하면 저장 프로시저 실행이 지연되고 CPU 사용률이 높아질 수 있습니다.

    이 문제가 발생하면 다음 일련의 이벤트가 SQL Server 확장 이벤트 세션에 기록됩니다.

    이벤트 이름 텍스트
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss 는 이름별 캐시 조회가 실패할 때 발생하지만, 모호한 개체 이름이 개체 ID sp_cache_hit 로 확인되고 이벤트가 발생한 후 캐시에서 일치하는 캐시된 계획이 발견되었습니다.

    이 컴파일 잠금 문제에 대한 해결 방법은 저장 프로시저에 대한 참조가 소유자로 한정되었는지 확인하는 것입니다. (exec 대신 execmystoredprocdbo.mystoredproc를 사용합니다.) 소유자 자격은 성능상의 이유로 중요하지만 추가 캐시 조회를 방지하기 위해 저장된 프로시전자를 데이터베이스 이름으로 한정할 필요는 없습니다.

    컴파일 잠금으로 인한 차단은 표준 차단 문제 해결 방법을 사용하여 검색할 수 있습니다.

  2. 저장 프로시저에 접두사로 sp_

    저장 프로시저 이름이 접두사로 sp_ 시작되고 master 데이터베이스에 없는 경우 저장 프로시저를 소유자로 한정하더라도 각 실행에 대한 캐시 적중 전 sp_cache_miss 표시됩니다. 이는 접두사에서 sp_ SQL Server 저장 프로시저가 시스템 저장 프로시저이고 시스템 저장 프로시저에 다른 이름 확인 규칙이 있음을 알려주기 때문입니다. (기본 설정 위치는 master 데이터베이스에 있습니다.) 사용자가 만든 저장 프로시저의 이름은 로 sp_시작해서는 안 됩니다.

  3. 저장 프로시저는 다른 대/소문자(위/아래)를 사용하여 호출됩니다.

    소유자 정규화된 프로시저가 해당 프로시저를 만드는 데 사용된 사례와 다른 사례(위 또는 아래)를 사용하여 실행되는 경우 프로시저는 CacheMiss 이벤트를 트리거하거나 COMPILE 잠금을 요청할 수 있습니다. 결국 프로시저는 캐시된 계획을 사용하며 다시 컴파일되지 않습니다. 그러나 COMPILE 잠금에 대한 요청으로 인해 동일한 프로시저를 만드는 데 사용된 사례와 다른 사례를 사용하여 동일한 프로시저를 실행하려는 SPID가 많은 경우 차단 체인 상황이 발생할 수 있습니다. 이는 서버 또는 데이터베이스에서 사용되는 정렬 순서 또는 데이터 정렬에 관계없이 마찬가지입니다. 이 동작의 이유는 캐시에서 프로시저를 찾는 데 사용되는 알고리즘이 해시 값(성능을 위해)을 기반으로 하며 케이스가 다른 경우 해시 값이 변경될 수 있기 때문입니다.

    해결 방법은 애플리케이션이 프로시저를 실행할 때 사용되는 사례와 동일한 사례를 사용하여 프로시저를 삭제하고 만드는 것입니다. 올바른 대/소문자(위 또는 아래)를 사용하여 모든 애플리케이션에서 프로시저가 실행되는지 확인할 수도 있습니다.

  4. 저장 프로시저는 언어 이벤트로 호출됩니다.

    저장 프로시저를 RPC 대신 언어 이벤트로 실행하려는 경우 SQL Server 언어 이벤트 쿼리를 구문 분석하고 컴파일하고 쿼리가 특정 프로시저를 실행하려고 하는지 확인한 다음 해당 프로시저에 대한 캐시에서 계획을 찾아야 합니다. SQL Server 언어 이벤트를 구문 분석하고 컴파일해야 하는 이러한 상황을 방지하려면 쿼리가 SQL에 RPC로 전송되었는지 확인합니다.

    자세한 내용은 온라인 설명서 문서 저장 프로시저 만들기의 시스템 저장 프로시저 섹션을 참조하세요.

참조

OPEN SYMMETRIC KEY 명령은 쿼리 계획 캐싱을 방지합니다.