Microsoft로 로그인
로그인하거나 계정을 만드세요.
안녕하세요.
다른 계정을 선택하세요.
계정이 여러 개 있습니다.
로그인할 계정을 선택하세요.

요약

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

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

  • 차단되고 (일반적으로) 차단 세션 SPIDs에 대한 대기 유형은 LCK_M_X(배타적)이며 대기 리소스는 "object: dbid:object_id [컴파일]]"이라는 형식의 "object_id"가 저장된 프로시저의 개체 ID입니다.

  • 차단기에는 대기 유형 NULL이 있으며 상태 실행 가능. 차단자는 대기 유형 LCK_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]] 

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

추가 정보

저장 프로시저 재컴파일은 저장 프로시저 또는 트리거에 대한 컴파일 잠금에 대한 하나의 설명입니다. 이 경우 해결 방법은 다시 컴파일을 줄이거나 제거하는 것입니다. 저장 프로시저를 다시 컴파일해야 하는 가장 일반적인 이유에 대한 설명과 다시 컴파일 빈도를 줄이는 방법에 대한 몇 가지 유용한 정보는 다음 Microsoft 기술 자료 문서를 참조하십시오.

243586 저장 프로시저 재컴파일 문제 해결

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

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

    • 저장 프로시저를 실행하는 사용자는 프로시저의 소유자가 아닙니다.

    • 저장된 프로시저 이름은 개체 소유자의 이름으로 완전히 정규화되지 않았습니다.

    예를 들어 사용자 "dbo"가 object 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

    마이스드프락

    sp_cache_miss

    마이스드프락

    sql_batch_starting

    마이스드프락

    sp_cache_hit

    마이스드프락

    ...

    ...

    sp_cache_miss 이름으로 캐시 조회가 실패하지만 ambiguos 개체 이름이 개체 ID로 확인되고 sp_cache_hit 이벤트가 발생한 후 일치하는 캐시된 계획이 캐시에서 결국 발견될 때 발생합니다. 컴파일 잠금의 이 문제에 대한 해결책은 저장된 프로시저에 대한 참조가 소유자 자격을 갖추었는지 확인하는 것입니다. (대신 exec mystoredproc,exec dbo.mystoredproc를사용 .) 성능상의 이유로 소유자 자격도 중요하지만 추가 캐시 조회를 방지하기 위해 저장된 proc를 데이터베이스 이름으로 한정할 필요는 없습니다. 컴파일 잠금으로 인한 차단은 표준 차단 문제 해결 방법을 사용하여 검색할 수 있습니다.

  2. 저장 프로시저에 "sp_"이 붙습니다.

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

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

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

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

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

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

알려진 문제

다음은 계획 캐싱을 방지할 수 있는 몇 가지 알려진 문제입니다.

  • BLOB 변수를 저장 프로시저 매개 변수로 사용합니다. 자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료의 문서를 봅니다.

    2380435 FIX: 저장 프로시저가 BLOB 변수를 사용하고 변수가 Microsoft SQL Server 2008의 문자열 함수에 사용되는 경우 저장 프로시저에 대한 쿼리 계획이 캐시되지 않습니다.

  • 저장 프로시저/쿼리 일괄 처리에서 열린 대칭 키를 사용합니다. 자세한 내용은 다음 MSDN 블로그 항목을 참조하십시오.

    열린 대칭 키 명령은 쿼리 계획 캐싱을 방지합니다.

추가 도움이 필요하신가요?

기술 향상
교육 살펴보기
새로운 기능 우선 가져오기
Microsoft Insider 참가

이 정보가 유용한가요?

언어 품질에 얼마나 만족하시나요?
사용 경험에 어떠한 영향을 주었나요?

의견 주셔서 감사합니다!

×