컴퓨토피아

김정선의 좋은 글을 찾아서……
SQL Server 2005 SP2에서 변경된 Plan Caching 동작

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

저자: sangeethashekar, SQL Programmability & API Development Team 블로그

원문: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx

김정선의 의견

오랜 만의 좋은 글을 찾아서입니다 ^^

역시 전부터 계속 소개하고자 생각했던 글인데, 이제서야 올립니다.

SQL Server 2005 출시 이후, 대용량 메모리 사용이 늘어나면서 Plan Cache 용 메모리가 폭주하는 문제를 가지고 있었습니다. 그 원인이 Plan Cache를 할당하는 수식 상의 이슈가 있었는데요, SP2와 함께 변경되었다고 합니다.

대용량 메모리, 특히 64bit 장비를 운영하는 관리자 혹은 그러한 장비로의 업그레이드를 고려하고 있는 사용자라면 중요하게 알고 계셔야 할 내용일 듯 합니다.

참고로, 원문에 나와 있는 내용은 “Inside Microsoft SQL Server 2005 Query Tuning and Optimization”에 더 잘 나와 있습니다. 보다 폭 넒은 내용은 이 책을 참조해 보세요.

역시 내용이 길지 않으니 편하게 보시면 되겠습니다.

그리고 어려운 용어들이 몇 가지 있고 문맥도 힘들어서 번역에 오류가 있을 듯 합니다. 잘 아시는 분 계시면 정정해 주시면 매우 감사하겠습니다 ^^

그럼, 즐.독.하세요~~~

잠깐 질문. 혹시 이런 내용으로 세미나하면, 많이들 오실까요? ^^ (유로로...)

[본문 시작]

SQL Server 2000, 2005 RTM, 2005 SP2간의 Caching 동작 변경 사항

SQL Server 2000과 2005 RTM간의 비용 산정 알고리즘의 변화

SQL Server 2000 및 2005는 3가지 요소로 비용이 결정되는데, 디스크 IO 요청 수, 쿼리 컴파일 동안 Context Switch 수, 메모리 페이지 수(단일 또는 다중 페이지 할당자 모두). SQL Server 2005에서는 쿼리의 원본 비용이 2의 제곱에 해당하며, 디스크 IO, Context Switch, 메모리 페이지 비용에 대한 합을 그 지수로 한다.

수학적으로

Cost = 2 ^{min (IO > 0 ? (IO - 1) / 2 + 1 : 0, 19) + min (CS > 1 ? (CS - 1) / 2 + 1 : 0, 8) + min (MP / 16, 4)}

여기서

IO – IO 요청 수(바이트 수에 무관),

CS – Context Switch 수,

MP – 메모리 페이지 수(단일 혹은 다중 페이지 할당자)

Context Switch 지수는 다음과 같다: a) 1미만의 context switch을 가진 쿼리는 지수가 0이다. 두 번이면 지수는1이다. 2를 초과하는 2의 배수는 지수가 1씩 증가하며 최대 8을 가진다. 디스크 I/O 지수는 디스크 IO가 0이면 똑같이 0이다. 역시 2의 배수로 지수가 1씩 증가하며 최대 19를 가진다.  메모리 페이지 지수는 16의 배수로 나누어지며 최대 4를 가진다. 예를 들어 Disk IO 0, context switch 4, 메모리 페이지 2이면, 해당 쿼리의 원본(초기) 비용은 2 ^{(0 + 2 + 0)}  = 4가 된다.

SQL Server 2005 비용은 Plan cache 메모리 페이지에 비례한 반면, SQ Server 2000은 메모리 페이지에 반 비례하게 계산된다. 따라서 대량 메모리 페이지를 사용하는 쿼리의 경우 SQL Server 2000에서는 캐시되지 않지만, SQL Server 2005에서는 캐시된다. 업그레이드의 경우 이러한 쿼리가 많은 작업 부하를 가진 시스템이라면 Plan cache 크기가 상당히 증가할 것이다.
(역주. 무슨 말인지 이해 되시나요? 조금 애매하죠? 저두요…… ^^;)

SQL Server 2005 SP2에서 향상된 Plan Cache 동작

1. Plan cache 항목(entry)의 생성 및 제거 시 충돌 감소
   SQL Server 2005 RTM과 SP1에서 동적 쿼리의 컴파일 계획에 대한 Descriptor를 위해 하나의 할당자가 있었다. Descriptor는 컴파일 계획의 일부로서 컴파일 계획을 plan cache에 추가할 때 생성되며 반대로 제거될 때 같이 삭제된다. 이 때 하나의 Descriptor에 대한 충돌로 인해서 응용 프로그램 처리량의 40%가 감소되거나 메모리 부족과 같은 심각한 문제를 유발하기도 했다. SP2에서 이러한 충돌을 줄이기 위해 각 CPU 단위로 분할해서 할당하며, 기존 문제를 해결하는데 도움을 준다.
   
   

2. 모든 NUMA 모드에 걸쳐 공정하게 cache 항목 제거 수행
   NUMA(non-uniform memory access)로 구성된 서버에서 다중 NUMA 노드가 존재하고 각 노드 별로 캐시를 가진다. SQL Server는 cache 항목 제거(퇴거)에 의한 메모리 압박에 반응한다. SQL Server 2005 RTM과 SP1에서 작은 캐시를 가진 노드로 인해 여러 번의 캐시 제거 동작이 반복될 수 있었다. 예를 들어, 작은 메타데이터 캐시 항목은 주기적으로 재 생성되고 이로 인해 스키마 변경 잠금(Sch-m)에 충돌을 유발할 수 있다. 이러한 충돌로 전체 처리 성능이 떨어질 수 있다. SP2에서, 전체 NUMA 모드에 걸쳐 골고루 캐시 항목 제거 정책을 처리함으로써, 이러한 문제를 줄일 수 있다.
   (역주: Local 메모리 압박의 대한 NUMA 구성의 기존 문제점을 언급하고 있는 것 같습니다 à 책에 잘 설명되어 있습니다.)

3. SQL Server 2000에서와 유사한 Plan cache 크기 배정
   SQL Server 2005 RTM및 SP1에서 Plan Cache의 최대 크기가 2000 대비 상당히 커졌다. SQL Server 20052 SP에서 이를 조정했다. 다음 표를 참조.

예:

32GB를 가진 SQL Server의 경우, SQL Server 2005 RTM및 SP1에서는 75% X 8 + 50% X (32 - 8) = 18GB가 된다.

반면에 SQL Server 2005 SP2 는 75% X 4 + 10% X (32-4) = 5.8GB로 줄어든다.

참고: Plan cache에 대한 또 다른 내부 메모리 압박은 32비트에서 4만개 이상의 항목, 그리고 64비트에서 16만개 이상일 경우이다.
(역주: 이 부분도 책을 참조하는 것이 좋을 듯 합니다……소개가 조금 다른 듯 합니다)

4. 일부 0 비용 계획을 캐시하지 않도록 구성하여, 캐시 항목을 감소시킨다
   SQL Server 2005 RTM, SP1, SP2에서 컴파일 계획의 비용은 컴파일 계획의 크기와 컴파일하는  IO 및 CPU에 비례한다. 컴파일 계획은 특정 임계 값 이하는 비용 0으로 감안한다. SQL Server 2005 RTM과 SP1에서 하나의 SET 문 혹은 하나의 트랜잭션 문(begin/commit/save/rollback transaction 문)을 포함한 동적 배치에 대한 비용 0의 컴파일 계획이 캐시되었다. SP2에서 이러한 계획은 더 이상 캐시되지 않는다.

예외로는,
a) 배치 내 모든 문이 set (옵션) 문인 경우, SP2는 비용 0라도 캐시

b) 배치 내 모든 문이 IF 문과(혹은) 트랜잭션 문의 경우, SP2는 캐시

역주)위 내용 a, b 또한 이해가 잘 안되는……누가 설명 좀 해 주세요 ^^;

SP2의 이러한 변화로 비용 0이며 거의 재사용이 어려운 동적 배치에 대한 캐시 소비를 줄일 수 있다.

결론, SP2 꼭 설치하라는 얘기네요 ^^

SQL Server 관리자 분들에게 도움이 되는 중요한 내용이라 퍼왔습니다.

마이크로소프트에서 기술 지원을 하고 계신 한기환님 블로그에서 가져왔습니다.

http://optimizer.tistory.com/entry/KILL-UOW

(기환님 메시지 남기고 펐어요! ^^)

아래 본문입니다.

------------------------------------------------------------------------

김정선의 SQL Server 컨설팅 이야기

“암시적 소유자로 인한 CacheMiss 한 방에 해결하기”

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

 

무슨 얘기할려구?

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

 

시스템 환경

 

항목	결과
OS	당연히 Windows Server… ^^
SQL Server 버전	2000
솔루션	CRM 관련 외산 솔루션 (ASP 기반)

 

 

 

 

뭔 일이 있었는데?

 

배경

쿼리 성능의 중요한 한 축을 차지하는 것이 바로 “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로 지정되었습니다. (명시적이건 암시적이건)

 

예제 프로시저 생성

CREATE PROC dbo.pTest1 AS … go   CREATE PROC dbo.pTest2 AS … go

 

 

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

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

다른 사용자로 개체 호출 시 Profiler에서 CacheMiss 발생 여부 모니터링

SETUSER 'John'   -- case1: 명시적 소유자 -- case2: 암시적 소유자 WHILE 1 = 1 BEGIN --         exec dbo.pTest1            EXEC pTest1                       WAITFOR DELAY '00:00:00.200'   --         exec dbo.pTest2            EXEC pTest2                       WAITFOR DELAY '00:00:00.200' END

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

 

3) 모니터링 결과

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

 

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

 

CacheMiss가 계속 발생되고 있는 것을 볼 수 있습니다. 그렇다고 프로시저가 아예 호출되지 않는 건 아니겠죠? SQL Server가 첫 번째 현재 사용자명으로 개체를 찾지 못하면, 두 번째로 사용하는 소유자명이 바로 ‘dbo’. 따라서 두 번째 작업에서 개체를 찾고 실행할 수 있게 될 것입니다.

위 예제에서 주석으로 처리된 부분 dbo.pTest1을 해제하고 실행하면 더 이상 CacheMiss는 발생하지 않게 됩니다.

이상 김정선의 혼자만의 상상의 나래였습니다 ^^;

그래서 어떻게 해결했는데?

뭐 실행만 되면 되지, CacheMiss 발생한다고 성능이 얼마나 느려질까? 맞습니다. 뭐 얼마나 차이가 있겠습니까 ^^; (확인을 못해본 관계로). 그렇지만, 서두에서 언급한대로 발생하지 않아도 될 부가적인 Cache Miss로 인한 오버헤드는 해결하는 것이 옳을 것입니다. 문제는 여기서 시작됩니다. 결론은 모든 프로시저 호출 시 dbo.를 붙여주어야 한다는 것인데, 프로그램 안에 하드 코딩 된 그 많은 프로시저를 다 고칠 수는 없는 노릇 OTL.

뭐 좀 간단한 방법은 없을까? (이것이 모든 관리자의 마음 ^^)-모 드라마에서 유행했던 ‘거 좀 쉽게 쉽게 가자’)-공감 100%.

그래서 생각해 본 방법. 결론은 개체 소유자와 호출자가 동일하면 된다는 것인데. ‘John’을 해당 DB의 소유자(db_owner)로 만들어 버리면, 자동으로 ‘John’이 ‘dbo’ 사용자명으로 실행될 것이고 그럼 자동으로 현재 사용자명이 ‘dbo’가 되어서 첫 번째 CacheMiss를 피할 수 있지 않을까? 라는 것이었습니다. 그것도 프로시저가 호출되는 시점에서 라이브로 작업을 했을 때요……물론 여기엔 어떤 부작용이 있을 수도 있을 겁니다. 어쨌든 테스트해 봤죠 ^^

DB의 소유자를 변경할 때, ‘sp_changeobejctowner’를 이용하시면 되죠? 주의하실 것이 있습니다.

예제 코드의 ‘주의’를 달아두었으니 참조하세요.

앞의 반복 호출을 수행하고 있는 상태에서, 이 코드를 수행하면 CacheMiss가 바로 중단되는 것을 알 수 있습니다. OK~

더 할 말 있남?

너무 심각하게, 진지하게 받아들이지 않으셔도 될 것 같습니다. 그냥 오호~ 정도 ^^.

결론은 간단할 것 같습니다. 그냥 개체 참조할 때 소유자명 꼭 쓰세요~ 귀찮으면 그냥 DB에 오로지 1명의 사용자만 존재하도록 만든다? ^^

아참, 프로시저 접두사도 sp_ 시작되면 CacheMiss 2번 이상 발생되는 거 아시죠? ^^

주의

이 내용은 김정선이 개인적으로 실험한 결과입니다. 절대적인 근거 자료는 아니니 참고 사항으로 보시기 바랍니다. 그 어떤 보장도 하지 않습니다.