컴퓨토피아

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

이번에 좀 쉬운 내용으로 정했습니다 ^^

차집합을 구하라

“주문이 없는 제품을 구하라!”,

“거래가 없는 고객을 구하라!”,

“미발송된 주문 상세 내역을 구하라!”

차집합과 유사한 성격의 결과 집합을 도출하는 쿼리를 가끔 작성하게 됩니다. 이 때, 가장 큰 문제 중의 하나는 바로 쿼리 성능 문제입니다. 수행되는 집합 연산의 성격 상 모델 변경 없이 쿼리 만으로 좋은 성능을 만들기란 쉽지가 않습니다. 대량 범위의 데이터에서는 더 큰 문제를 내포하게 됩니다. 그런데, 현장에서 만들어진 쿼리를 보면 LEFT OUTER 조인과 같은 가장 나쁜 쿼리 유형을 선택해서 사용하고 있는 것을 자주 보게 됩니다. SQL Server 관련 Q/A에서도 많이 볼 수 있는 질문 유형 중의 하나였습니다.

언젠가는 한 번 비교를 해 보고 그 결과를 많은 개발자 혹은 DBA들과 공유하자 마음 먹고 있었습니다. 또한 다른 분들의 좋은 솔루션이나 의견도 듣고 싶었습니다. 얼마 전 SQL 전문 Blog에 관련된 글이 올라온 것을 보고 그 기억을 되살리게 되었고, 이제서야 잠깐 짬을 내 기본적인 쿼리 유형들을 샘플로 간단히 만들고 이를 비교한 내용들을 기록합니다. 특히, SQL Server 2005의 Query Optimizer에서 이전 버전과 두드러진 한 가지 차이점을 공통적으로 보이고 있어서 재미 있는 이슈가 될 듯 합니다. 이 또한 예제에 포함시켜 두었습니다.

간단한 예제들이지만, 차집합 성격의 결과 집합 도출 시 어떤 쿼리 유형들이 가능할지 그 선택 범위를 살펴보고 다양한 상황에 따라 적절한 접근 방법으로 활용하면 도움이 될 것이라 판단됩니다. 특히, 막연히 LEFT OUTER 조인만을 사용했던 경험이 있다면 이를 개선할 수 있는 다른 접근 방법들을 살펴볼 수 있을 겁니다.

쿼리 유형별 예제

LEFT OUTER JOIN 방식을 포함해서 쉽게 사용할 수 있는 몇 가지 예제 쿼리들을 다루고자 합니다. 각 유형별로 실행 계획과 I/O(실행 계획 이미지 하단에 포함)를 함께 표시하고 그 특징을 간단히 소개합니다. 설명을 통해 특징과 차이점을 알 수 있습니다. 특히, 몇 가지 쿼리에 대해서는 SQL Server 2000버전과의 큰 차이점을 하나 보여줍니다. 이 또한 정리해 두었습니다. 아래에서 살펴보시기 바랍니다.

1. NOT IN

2. NOT IN + 상관서브쿼리

3. NOT IN + 상관서브쿼리+ TOP 1

4. NOT EXISTS + 상관서브쿼리+ TOP 1

5. 상관 서브쿼리+ TOP 1 + IS NULL

6. OUTER JOIN + IS NULL

7. OUTER APPLY + IS NULL (SQL Server 2005 해당)

8. EXCEPT + (생략됨) 서브쿼리(혹은JOIN) (SQL Server 2005 해당)

참고. A, B 두 집합은 각각 Northwind의 Customers(91건), Orders(830건) 테이블로, 결과 집합은 주문(거래)이 없는 고객 집합을 도출하는 것을 예제로 합니다.

1. NOT IN

가장 간단하고 기본적인 유형입니다. 서브 쿼리의 결과 집합이 중복 값을 허용하지만 SQL Server Query Optimizer가 제공하는 최적화 전략 중 하나인 Constant Folding으로 인해 DISTINCT를 해 주지 않아도 됩니다. 아래 실행 계획은 오른쪽이 잘려 있는 상태입니다. 쿼리와는 달리 계획은 꽤 복잡한 형태를 띠고 있습니다. 부가적인Sort와 Merge 연산자를 동반하며, 내부 Nested Loop 조인으로 인해 실제 I/O는 매우 많은 검색 수(182)와 읽기 수(364)를 보여주고 있으므로, 성능이 좋지 않습니다. Bad!

2. NOT IN + 상관 서브쿼리

서브 쿼리를 상관 서브쿼리로 변경, 쿼리 상에서 직접적으로 Orders테이블과의 조인 관계를 제한했다. 그 결과, 조인 연산자를 Nested Loop 조인으로 선택했을 때의 과도한 I/O를 해결하기 위한 대체 방안으로 Merge Join을 선택하고 있다. 한 번의 검색(1)으로 각 테이블을 액세스하고 그 결과를 Merge 물리적 연산자로 처리하며, 논리적으로는 차집합에 해당하는Anti Semi 조인으로 해결하고 있다. Merge 조인을 위해서는 조인 키에 Clustered Index나 Covering Index를 요구하는데 두 테이블이 각각 이를 만족하는 상황이므로, Query Optimizer가 손쉽게 Merge 조인을 선택할 수 있다. 빠른 응답 속도는 아니지만 결과론적으로 I/O는 최소화되었다. 그럼 좋아진 것일까? 문제는 Merge를 하기 위해서 Index Scan을 하느라 전체 데이터를 액세스하고 있다. 불필요한 데이터를 액세스하지 않는다는 기본 쿼리 작성 규칙에 위반되므로 좋다고도 볼 수는 없다. 따라서 Not bad!

3. NOT IN + 상관서브쿼리+ TOP 1

저는 개인적으로 데이터 존재여부를 체크하는 서브 쿼리 유형에서 TOP 1 사용을 즐겨 합니다. 이는 교과서적인 내용이 전혀 아니지만, 컨설팅 경험을 통해서 인지하게 된 SQL Server Query Optimizer의 중요 특성 중의 하나입니다. 2번의 결과와 비교해 보면, 서브 쿼리 내 TOP 1을 추가한 것으로 쿼리 실행 계획의 큰 변화가 나타납니다. Query Optimizer가 Merge 조인 대신 Nested Loop 조인을 선택한 것입니다. 여기서 관심 있게 볼 부분은, 내부 입력인 Orders 테이블에 대해서는 Index Seek/Top/Filter 연산자의 순서입니다. 모든 검색 제한 조건을 처리한 후 Nested Loop 조인에 참여하고 있는 부분입니다. 이를 통해 반복적인 랜덤 I/O를 줄이게 됩니다.   이 방법은 실제로 김정선이 매우 즐겨 하는 서브쿼리 튜닝 전략 중의 하나입니다 ^^* 크게 2가지 목적을 달성하는 것입니다. 첫 번째는 조인 순서를 외부 쿼리에서 서브 쿼리로 풀리도록 강제하는 것이고, 두 번째는 Nested Loop 조인을 선택하도록 도와주는 것입니다. 이유는 Merge 조인과 같은 Stop-And-Go 방식의 연산자보다 Nested Loop 조인과 같은 Stream 방식이 훨씬 응답 속도(Response Time)가 빠르기 때문입니다. RDBMS는 일반적으로 OLTP성 쿼리로 주로 사용되며 OLTP는 쿼리 응답 속도가 제일 목표라는 사실 때문입니다. 상대적으로I/O가 커져 보이지만, 이는 실제로 필요한 만큼의 I/O임을 고려해야 합니다(앞서의 Merge 조인은 불필요한 전체 데이터를 액세스합니다) 따라서 제 의견은 (Very) Good

4. NOT EXISTS + 상관서브쿼리+ TOP 1

2번과 동일합니다. 다만IN이 아니라 EXISTS를 대신 사용했습니다. 여러분은 IN과 EXISTS중 어는 것을 선호하시나요? 아래 실행 결과를 보시면 2번과 유사하게 Merge Join으로 처리 됩니다. 그래도 전체 데이터를 조인으로 결합시키지 않고, Stream Aggregate 연산자를 이용해서 사실에 결합에 필요한 데이터(89건)만 처리하고 있다. 결국 2번보다는 개선된 계획. 그래도 역시 Not bad ^^

5. 상관 서브쿼리+ TOP 1 + IS NULL

드디어 나와 군요. 위 3번과 함께 제가 가장 좋아하는 데이터 존재 여부 판단용 쿼리 유형입니다. 교과서에도 보기 힘든 쿼리 유형이죠. 실제로 모 고객사에서 쿼리 튜닝을 하면서 고객의 도움으로 우연찮게 발견한 방법입니다. 성능 이득이 너무나 대단해서 그 이후로 저의 필살기가 되었습니다. 헉^^; 그럼 김정선의 서브쿼리 튜닝 필살기를 여러분께 알려드린 거네요? 후후후, 이미 제가 진행하는 쿼리 튜닝 강의에서 알려 드리고 있습니다. 동일한 내용이므로 역시 (Very) Good!

6. OUTER JOIN + IS NULL

이제 나왔네요. OUTER JOIN이 가진 특성을 활용한 방법. 쿼리 자체는 흔하므로 설명 드리지 않아도 될 듯 합니다. 앞의 유형처럼 Merge 조인으로 처리되어 I/O는 작지만, 실행 계획을 보면 문제가 있음을 알 수 있습니다. 내부 입력의 조인 행 수에 대한 문제입니다. Orders 테이블에서 조인 조건을 만족하는 830건을 모두 OUTER 조인으로 결합하고 그 결과 832건을 도출한 뒤에 마지막 Filter 연산자에서 CustomerID IS NULL 조건으로 실제 행 수 2건을 만족하게 됩니다. 반드시 필요한 데이터만 액세스한다는 좋은 쿼리 작성의 기본 원칙에 위배가 되는 것입니다. 따라서 (Too) Bad!

7. OUTER APPLY + IS NULL

SQL Server 2005에 새로 도입된 OUTER APPLY 문을 적용한 예제입니다. 결과론적으로 앞서 3번 및 5번과 유사한 실행 계획과 I/O를 보여줍니다. 따라서 이 방법 또한 괜찮다는 것을 알 수 있습니다. Good!

8. EXCEPT + (생략됨) 서브쿼리(혹은JOIN)

수학적인 개념의 차집합(A-B)을 정확히 표현한 것이 바로 SQL Server 2005에 새로 도입된(ANSI 표준이죠 아마?) EXCEPT 문입니다. 아래 실행 계획에서 보듯이 EXCEPT를 사용한 결과는 EXISTS 연산자를 사용한 경우와 동일하게 처리됨을 알 수 있습니다. 그런데 한 가지 문제가 남았습니다. 아래 쿼리와 이전과 다르다는 것입니다. 차집합의 키 원소만을 처리하고 있고, 실제 결과 집합으로 필요한 Customer 테이블의 다른 칼럼 집합은 언급되지 않았다는 것입니다. 코드 주석에 언급했듯이 이 코드에 더해서 다시 조인이나 서브쿼리로 Customers 테이블의 나머지 칼럼을 처리해야 하므로, 결국 부가적인 I/O가 더해질 것입니다. 따라서, 성능은 Not Good!

SQL Server 2000 vs. 2005, 누가 누가 잘하나?

지금까지 살펴본 예제 쿼리들에 대해서 SQL Server 2000과 비교했을 때 2005버전이 크게 달라진 부분이 하나 있습니다. 위 쿼리를 SQL Server 2000(SP3기반)에서 수행 시 가장 큰 차이는 바로 Mere 조인을 사용하지 않는다는 점입니다. 위 Merge조인 예제들이 모두 Nested Loop조인으로 처리됩니다. 그 중 2번) NOT IN + 상관서브쿼리 예제를 아래와 같이 비교해 보겠습니다.

SQL Server 2000버전) NOT IN + 상관 서브쿼리

Merge 조인을 사용해 I/O를 최소화했던 2005버전과 달리 2000 버전은 Nested Loop 조인을 사용해서 반복 I/O가 검색 행 수만큼 발생했습니다. 사실 상 앞서 우수하다고 판단했던 다른 유형의 쿼리들과 동일하게 처리되고 있습니다. Merge 조인을 위해 불필요한 데이터까지 액세스하지 않고 Index Seek를 통해 필요한 데이터만 제한적으로 검색하고 있습니다. 또한 앞서 언급한Nested Loop 조인의 응답 속도상의 이득도 취할 수 있습니다.

그럼, 이 두 가지를 비교했을 때 과연 어떤 실행 계획이 더 적합한 것일까? 물론 830건 밖에 되지 않는 성능을 논하기엔 매우 작은 테이블과 환경임을 감안하더라도 말입니다. 정답은 없겠지만 적어도 SQL Server 2005의 큰 변화 몇 가지는 확인할 수 있다는 점과 향후 쿼리 튜닝에 있어서 어떤 접근 방법을 우선 시 해야 할지에 대한 중요한 기준을 보여줍니다. 동일한 쿼리에 대해서 2000과 2005는 극명한 성능 차이를 보여줄 수 있다는 또 하나의 사례인 셈입니다.

철저한 I/O 이득을 우선으로 처리하고 있다는 느낌을 주는 2005의 Query Optimizer가 주는 느낌은 왠지 낯설기까지 합니다. 그 변화와 이득은 최대한 활용하되, 파생되는 문제점 또한 해결할 수 있는 다양한 접근 능력이 필요합니다.

결론

최종 판단은 여러분께 맡기겠습니다. 어떤 경우에 어떤 실행 계획이 적합하지 판단하실 수 있다면 그 유형에 맞게 선택해서 사용하시면 됩니다. 더불어 여기에 소개된 것은 기초적인 내용들입니다. 좀 더 복잡한 유형들이 존재합니다. 실제로 여러분들은 어떤 방법, 어떤 유형의 쿼리들을 사용하고 있는지요? 좋은 쿼리가 있으면 소개해 주세요. 많은 분들께 도움이 되실 것이라 믿습니다!

또한 위 쿼리 예제들에 대한 여러분들의 의견도 궁금합니다.

편하게 의견 주세요~~~

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번 이상 발생되는 거 아시죠? ^^

주의

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