컴퓨토피아

김정선(jskim@feelanet.com) 필라넷 DB사업부 수석컨설턴트 SQLServer 아카데미/트라이콤 교육센터 강사   Microsoft SQL Server MVP MCT/MCITP/MCDBA

무슨 얘기할려구?

역시 낚시용 제목인가? 너무 거창한 듯 합니다. 이번 컨설팅 사례는 SQL Server에서 개체 참조 시 소유자명을 생략한 경우, 즉 흔히 말하는 “암시적 소유자” 상태에서 Procedure Cache(이젠 Plan Cache라고 부르는)에서 실행할 쿼리의 플랜을 찾지 못하는 현상(CacheMiss)이 발생하는지? 그리고 이를 한 방에 해결할 수 있는 방법이 무엇이 있을까?를 고민하고 그 결과 생각해낸 특정 방법을 테스트해 본 결과입니다. 해당 고객 사에는 전반적인 기술 이슈는 전달을 했지만 아직 직접적으로 반영된 상태는 아닙니다. 그러나 이와 유사한 환경을 가진 다른 사용자 시스템이 많을 것으로 보여 컨설팅 결과를 공유하고자 합니다. 혹시 관심 있다면 테스트해보시기 바랍니다.

시스템 환경

뭔 일이 있었는데?

배경

쿼리 성능의 중요한 한 축을 차지하는 것이 바로 “Procedure(Plan) Cache”의 재사용 능력입니다. 쿼리를 처음 실행 할 때 한 번 컴파일 과정을 거치고 나면, 그 결과로 도출되는 쿼리 계획(Query Plan)을 Cache에 저장해 두고, 이후 동일한 쿼리가 호출되고 재사용 요건을 만족하게 되면 다시 컴파일 과정을 거치지 않고 해당 쿼리 계획을 Cache에서 바로 참조해서 실행하는 것이죠. 이러한 쿼리 계획 재사용 능력을 높이기 위해서는 기본적으로 두 가지 튜닝 이슈가 발생합니다. 바로 1) 쿼리가 잘 Caching될 수 있도록 도와주는 것 그리고 2) Cache된 계획을 잘 재사용할 수 있도록 도와주는 것이 그것입니다-김정선의 생각 ^^.

그런데, Cache된 계획을 재사용할 때 즉 동일한 쿼리 계획이면 재사용 요건을 만족하는가? 라는 것을 평가할 때 중요한 키의 하나로 검사 받는 것이 바로 개체 소유자 지정에 관한 이슈입니다.

결론부터 얘기하죠,

SQL Server에서는 개체(특히 이 경우의 프로시저)를 참조할 때 개체 이름만 지정하지 말고 개체 소유자명(owner.objectname)을 함께 지정하는 것입니다-사실 여기에는 개체의 종류 및 기타 요소에 다른 차이가 많으므로 현재 사례로만 국한해서 설명합니다-특히 저장 프로시저의 경우, 프로시저를 만들 때 지정한 사용자명(정확히 소유자로 지정된 사용자명)과 해당 프로시저를 호출할 때 사용자명이 다른 경우에 개체 소유자명을 생략하고 호출하면 문제가 될 수 있다는 것입니다.

상황 재현

예를 들어 보죠,

Sales라는 데이터베이스는 로그인 명이 ‘sa’인 관리자가 만들었습니다. 따라서 ‘sa’라는 로그인이 Sales 데이터베이스의 생성자이며 자동으로 해당 DB에서 ‘dbo’라는 사용자명(USER_NAME())으로 작업하게 됩니다. 여기에 해당 DB를 사용하는 ‘John’이라는 로그인이 있으면 해당 DB의 사용자명 또한 ‘John’으로 지정되어 있다고 가정합니다.

1) ‘dbo’ 사용자명으로 개체 생성하기

DB를 만들고 소유한, ‘dbo’라는 사용자가 다음과 같이 프로시저를 만듭니다. 두 프로시저의 소유자는 dbo로 지정되었습니다. (명시적이건 암시적이건)

2) ‘John’ 사용자로 개체 참조 시 암시적 소유자명 사용하기

이제 ‘sa’-‘dbo’가 아닌 ‘John’이라는 사용자로 위에서 생성된 프로시저를 호출할 때, 소유자명을 생략한다면 어떻게 될까요?

SQL Server가 컴파일 과정의 일부인 이름 해석 단계에서 개체를 검사할 때 어떤 규칙을 사용하는지 알고 계시죠? 첫 번째는 누구? 두 번째는 누구? … 무려 다섯 단계 정도나 되죠. 그 첫 번째는? 네, 현재 사용자입니다. 예제대로라면 “EXEC pTest1”는 사실 상 “EXEC John.pTest1”로 해석이 된다는 것이죠. 즉 John.pTest1라는 프로시저가 Cache에 존재하는지 이름 해석을 수행하게 됩니다. 그럼 못 찾겠죠? CacheMiss라는 이벤트가 Profiler를 통해서 발생할 수 있습니다.

3) 모니터링 결과

예제는 두 프로시저는 0.2초 간격으로 무한 루프를 돌면서 호출합니다. 다음은 CacheMiss가 발생하는 것을 Profiler에서 추적한 화면입니다.

[그림] Profiler에서는 SP:CacheMiss 이벤트 추적 결과