이 문서에서는 컴파일 잠금으로 인한 차단 문제를 해결하고 해결하는 방법을 설명합니다.
원래 제품 버전: SQL Server
원래 KB 번호: 263889
요약
Microsoft SQL Server에서 저장 프로시저 계획의 복사본은 일반적으로 한 번에 하나의 캐시에만 있습니다. 이를 적용하려면 컴파일 프로세스의 일부 부분을 직렬화해야 하며, 이 동기화는 컴파일 잠금을 사용하여 부분적으로 수행됩니다. 많은 연결이 동일한 저장 프로시저를 동시에 실행하고 해당 저장 프로시저가 실행될 때마다 해당 저장 프로시저에 대해 컴파일 잠금을 가져와야 하는 경우 SPID(세션 ID)는 각각 개체에 대한 단독 컴파일 잠금을 가져오려고 할 때 서로를 차단하기 시작할 수 있습니다.
다음은 차단 출력에서 관찰할 수 있는 컴파일 차단의 몇 가지 일반적인 특성입니다.
waittype차단되고(일반적으로) 차단 세션 SPID가LCK_M_X(배타적)이고waitresource저장 프로시저의 개체 ID인 형식OBJECT: dbid: object_id [[COMPILE]]object_id입니다.차단기는
waittypeNULL, 상태를 실행할 수 있습니다. 차단된 세션에는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는 테이블이 아닌 저장 프로시저에 속합니다.
컴파일 잠금으로 이어지는 시나리오
다음 시나리오에서는 저장 프로시저 또는 트리거에 대한 단독 컴파일 잠금의 원인을 설명합니다.
정규화된 이름 없이 저장 프로시저가 실행됩니다.
- 저장 프로시저를 실행하는 사용자는 프로시저의 소유자가 아닙니다.
- 저장 프로시저 이름은 개체 소유자의 이름으로 정규화되지 않았습니다.
예를 들어 사용자 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를 사용합니다.) 소유자 자격은 성능상의 이유로 중요하지만 추가 캐시 조회를 방지하기 위해 데이터베이스 이름으로 저장된 프록시를 한정할 필요는 없습니다.
컴파일 잠금으로 인한 차단은 표준 차단 문제 해결 방법을 사용하여 검색할 수 있습니다.
저장 프로시저가 자주 다시 컴파일됩니다.
다시 컴파일은 저장 프로시저 또는 트리거에 대한 컴파일 잠금에 대한 한 가지 설명입니다. 저장 프로시저를 다시 컴파일하는 방법에는 포함 또는 CREATE PROCEDURE ...WITH RECOMPILE사용이 포함EXECUTE... WITH RECOMPILEsp_recompile됩니다. 자세한 내용은 저장 프로시저 다시 컴파일을 참조하십시오. 이 경우 해결 방법은 다시 컴파일을 줄이거나 제거하는 것입니다.
저장 프로시저에 접두사로 sp_**
저장 프로시저 이름이 접두사로 sp_ 시작되고 master 데이터베이스에 없는 경우 저장 프로시저를 소유자로 한정하더라도 각 실행에 캐시가 적중되기 전에 sp_cache_miss 표시됩니다. 이는 접두사에서 sp_ SQL Server에 저장 프로시저가 시스템 저장 프로시저이고 시스템 저장 프로시저의 이름 확인 규칙이 다르기 때문입니다. (기본 설정 위치는 master 데이터베이스에 있습니다.) 사용자가 만든 저장 프로시저의 이름은 .로 sp_시작하지 않아야 합니다.
저장 프로시저는 다른 대/소문자를 사용하여 호출됩니다(위/아래).
소유자 정규화된 프로시저를 만드는 데 사용된 사례와 다른 문자 대/소문자(상하)를 사용하여 실행하는 경우 이 프로시저는 CacheMiss 이벤트를 트리거하거나 COMPILE 잠금을 요청할 수 있습니다. 설명하려면 대에서 사용되는 CREATE PROCEDURE dbo.SalesData ... 다른 문자 대/소문자를 확인합니다.EXEC dbo.salesdata 결국 이 프로시저는 캐시된 계획을 사용하며 다시 컴파일되지 않습니다. 그러나 COMPILE 잠금에 대한 요청으로 인해 앞에서 설명한 차단 체인 상황이 발생할 수 있습니다. 차단 체인은 SPID(세션)를 만드는 데 사용된 사례와 다른 사례를 사용하여 동일한 프로시저를 실행하려고 하는 세션이 많은 경우 발생할 수 있습니다. 이는 서버 또는 데이터베이스에서 사용되는 정렬 순서 또는 데이터 정렬에 관계없이 마찬가지입니다. 이 동작의 이유는 캐시에서 프로시저를 찾는 데 사용되는 알고리즘이 해시 값(성능에 대한)을 기반으로 하며, 대/소문자 차이가 있으면 해시 값이 변경될 수 있기 때문입니다.
해결 방법은 애플리케이션이 프로시저를 실행할 때 사용되는 것과 동일한 문자 대/소문자를 사용하여 프로시저를 삭제하고 만드는 것입니다. 올바른 대/소문자(상하)를 사용하여 모든 애플리케이션에서 프로시저가 실행되는지 확인할 수도 있습니다.
저장 프로시저가 언어 이벤트로 호출됨
저장 프로시저를 RPC 대신 언어 이벤트로 실행하려는 경우 SQL Server는 언어 이벤트 쿼리를 구문 분석 및 컴파일하고, 쿼리가 특정 프로시저를 실행하려고 하는지 확인한 다음, 해당 프로시저에 대한 캐시에서 계획을 찾아야 합니다. SQL Server가 언어 이벤트를 구문 분석하고 컴파일해야 하는 상황을 방지하려면 쿼리가 SQL Server에 RPC로 전송되었는지 확인합니다. 예를 들어 .NET 코드에서 RPC 이벤트를 확인하는 데 사용할 SqlCommand.CommandType.StoredProcedure 수 있습니다.
저장 프로시저 또는 sp_executesql 8KB보다 큰 문자열 매개 변수를 사용합니다.
저장 프로시저 또는 sp_executesql 호출하고 8KB보다 큰 문자열 매개 변수를 전달하는 경우 SQL Server는 BLOB(Binary Large Object) 데이터 형식을 사용하여 매개 변수를 저장합니다. 따라서 이 실행에 대한 쿼리 계획은 계획 캐시에 유지되지 않습니다. 따라서 저장 프로시저를 실행할 때마다 새 sp_executesql 계획을 컴파일하기 위해 컴파일 잠금을 획득해야 합니다. 이 계획은 실행이 완료되면 삭제됩니다. 자세한 내용은 실행 계획 캐싱의 참고를 참조하고 8KB보다 큰 문자열 리터럴에 대해 다시 사용합니다 . 이 시나리오에서 컴파일 잠금을 방지하려면 매개 변수의 크기를 8KB 미만으로 줄입니다.