강사 소개

2. SQL Server 저장 프로시저 성능 최적화김정선필라넷 / DB 사업부 / 수석 컨설턴트삼성SDS 멀티캠퍼스 / 전임 교수Microsoft SQL Server MVP

3. 강사 소개 김 정선Email: visualdb@unitel.co.krBlog: visualdb.onblog.com자격/인증 사항MCDBA MCITP Database AdministratorMicrosoft SQL Server MVP근무 이력 현) 필라넷(Feel@NET)/ DB 사업부 / 수석 컨설턴트 현) 삼성SDS 멀티캠퍼스 / 전임 교수 전) 삼성 중공업 조선해양사업본부 ECIM팀주요 업무SQL Server 컨설팅 / 기술지원 / 개발 / 교육강사 활동MSDN, TechNet, Devdays, MCPWorld, 등출판물SQL Server 2005 관리자 가이드 (MS) SQL Server 2005 개발자 가이드 (MS) SQL Server 2005 포켓 컨설턴트 관리자용(정보문화사)

4. 목표 • 저장 프로시저의 실행 구조 및 환경 이해 • 저장 프로시저의 득과 실 이해 • 저장 프로시저의 성능 문제 요인 분석 • 재컴파일의 득과 실 이해

5. 대상 기술범위: • 대상 • 현업 개발자 및 관리자 • 저장 프로시저 성능에 관심 있는 개발자 • 저장 프로시저 실행 구조, 환경 및 그 특징에 관심 있는 사용자 • 기술범위 • 쿼리 최적화기 • 저장 프로시저 성능 • Database API 성능

6. 이 주제를 이해하는 데 필요한 지식 • SQL Server T-SQL Programming • 인덱스 • 쿼리 최적화기(Query Optimizer) • 저장 프로시저 • 데이터베이스 프로그래밍 Level 300

7. 목차 • 저장 프로시저 이해 • 저장 프로시저 성능 문제 분석 및 해결 방안

8. 1장 저장 프로시저 이해 각인(刻印), “새끼 오리들은 태어나는 순간(결정적 시기)에 처음 본움직이는 대상을 마치 어미처럼 평생을 따른다.”

9. 1장. 목차 • 응용 프로그램 쿼리 유형 • 작업 부하 유형 • 컴파일 비교 - VB vs. Ad hoc Query vs. 저장 프로시저 • 프로시저 캐시 • 저장 프로시저 처리 단계 • 저장 프로시저 특징에 대한 고민 • 실행 계획재사용 부작용 • Recompile 이슈 • Adhoc vs. Prepared • 성능 추적 및 모니터링

10. 응용 프로그램 쿼리 유형 • Presentation • Middle Tier • SQL Server 저장 프로시저 3 dbo.up_OrdersInfo 2 Parameterized *RPC 이벤트 SELECT ... FROM dbo.Orders WHERE OrderID = @OrderID 영업 Database 저장 프로시저(dbo.up_OrdersInfo) 1 Adhoc SELECT ... FROM dbo.Orders WHERE OrderID= 10248 *Batch 이벤트

11. 작업 부하 유형

12. 동일 쿼리 기준 = 정확한 구문 .exe 재실행 / 재컴파일 이진코드 생성 컴파일 최적화 .exe 실행 코드작성 파일 저장 아니요 (옵션) Cache 저장 컴파일 비교, VB vs. Ad hoc Query • VB • Ad hoc 작성 단계 실행 단계 • 컴파일 단계 동일쿼리가 Cache에 존재? 최적화 컴파일 단계 실행 계획 결정 컴파일 실행 단계 예 쿼리 실행

13. 동일 쿼리 기준 = 프로시저 이름 DB 저장 코드작성 파일 저장 최적화 컴파일 이진코드 생성 .exe 실행 .exe 재실행 / 재컴파일 코드작성 아니요 아니요 예 Cache 저장 컴파일 비교, VB vs. 저장 프로시저 • VB • 저장 프로시저 작성 단계 작성 단계 • 컴파일 단계 실행 단계 컴파일 단계 최적화 동일Proc가 Cache에 존재? 실행 계획 결정 컴파일 Cache Plan 재사용 가능? 실행 단계 Recompile 프로시저 실행 예

14. 프로시저 캐시 • master.dbo.syscacheobjects

15. 데모 • 응용 프로그램 쿼리 유형별 Caching • 응용 프로그램 쿼리 유형 • 프로시저 캐시 살펴보기 • 작업 부하 유형별 Caching 처리

16. 저장 프로시저 처리 과정-전체 [sysobjects]: Name, type, etc. [syscomments]: Text of object [syscolumns]: Parameter list [sysdepends]: Object dependencies Parsing 생성 Resolution Resolution* 실행(처음 or 재컴파일) Optimization Compiled plan placed inunified cache Compilation

17. Resolution • 저장 프로시저를 생성하는 시점의 참조하는 모든 개체를 해석하는 단계(존재 여부 등) • 개체가 존재하지 않아도 생성 작업은 성공 • 호출되는 프로시저가 존재하지 않으면 오류 발생 • “존재하지 않는 개체 ‘?'에 의존하고 있으므로 현재 저장 프로시저의 sysdepends에 행을 추가할 수 없습니다. 저장 프로시저는 만들어집니다.” – SQL Server 2000 메시지 • 장점: 순환 참조 허용! • 참조되는 테이블, 뷰, 함수 등이 존재하지 않는 경우엔 오류 발생 안 함(6.5 호환 모드 제외) • 개체를 삭제하기 전에 sp_depends로 종속 관계 확인

18. Compilation/Optimization • 제공된 파라미터에 기반 • 이후 실행에서 해당 계획을 재사용 • 모든 코드에 대해 최적화 수행 • (중요)코드 모듈화가 필요 • 잘못 작성된 코드는 과도한 잠금/차단을 유발 가능 • 과도한 재컴파일 또한 성능 저하를 유발 가능

19. Execution/Recompilation • 계획이 캐시에 없으면 새로운 계획을 컴파일하고 캐시에 적재해서 실행 • 캐시에 플랜이 부적합하거나 캐시에서 제거되는 상황 • 서버 재 시작 • 낮은 재사용으로 인한 퇴출 • DBCC FREEPROCCACHE 등의 명령문 실행 (필요한 경우도 있다) • 그 외에도 다양한 상황들이 존재 – 관리자 유의 사항 • 테이블 데이터 - 변경 • AutoStats 알고리즘, Q195565 INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work 참조

20. 저장 프로시저 특징에 대한 고민 • T-SQL의 거의 모든 구문을 사용한 절차적 프로그래밍 가능 • VB? or ASP?...NO, T-SQL • 미리 작성해서 DB에 저장한 쿼리 개체 • DB 용량이 커진다? 그럼, 다른 건 안 커지나? • 초기 실행 시 컴파일을 수행하고 실행 계획 산출 • 초기라는 시점의 의미는? Cache에 없으면 초기? • 실행 계획(직렬/병렬)을 Procedure Cache에 저장 • Procedure Cache가 커진다? 안 쓰면 작아지나? • 모든 사용자가 저장된 실행 계획을 공유하고 재사용 가능 • 재사용해서는 안 된다면? 공유하고 싶지 않다면? • 자동/수동으로 재컴파일 요청 시 재컴파일 수행 • 재컴파일은 컴파일과 다르다!, 왜 재컴파일을 하는가? 왜 해야하는가? • 자동/수동으로 Procedure Cache 제거 시 컴파일 수행 • 실제 서버에서 하지 말라?...청개구리, 필요할 때도 있을까?

21. 선택도 = 1/1,000 • 실행계획 = Table Scan 실행 계획 재사용 부작용 • 각인(刻印)의 부작용 • 새끼 오리가 태어나서 처음 강아지를 본다면? • 실행 계획 재사용 부작용 • 정리 • 필요 시 쿼리가 서로 다른 실행 계획을 가져야 하는 경우 • 쿼리 실행 계획 자체가 분리될 수 있어야 한다. 초기 컴파일 Recompile 하면? 실행 • 선택도 = 100/1,000 • 실행계획 = Table Scan • 선택도 = 1/1,000 • 실행계획 = Index Seek

22. 데모 • 실행 계획 재사용 • 실행 계획 재사용 유형 • 재사용 부작용

23. Recompilation 이슈 • 재컴파일 = 최적화 • 최적화 = 재컴파일 • 언제 재컴파일이 일어나길 원하나요? • 재컴파일에는 어떤 옵션들이 있는지 알고 있나요? • 재컴파일이 필요하다고 판단하는 기준은 무엇인가요? • 프로시저 전체가 재컴파일이 되길 원하나요? 아니면 일부만? • 테스트하고 검증할 수 있나요?

24. 언제 Recompile이 필요한가? • Cache 실행 계획이 비 효율적이라고 판단되는 경우 • 새로 실행 계획을 만들도록 요구 받는 경우 • 프로시저 내 쿼리가항상 동일한 실행 계획을 가지지 않는 경우 • 파라미터에 따라 실행 계획이 달라지는 경우 • 데이터 변경이 이루어지는 경우 (예, 반복적인 배치 작업) • 재컴파일 비용이 잘못된 실행 계획으로 인한 쿼리 실행 비용보다 확연히 적은 경우! • 왜? • 더 좋은실행 계획을 도출할 수 있다 • 잘못된 실행 계획으로 서버의 장애가 발생할 수 있다 • 재사용을 위해서 계획을 저장하는 것이 항상 좋은 것은 아니다.

25. 동적(Ad hoc) vs. 파라미터(Prepared) 쿼리 • 동적으로 구성된 쿼리 • 여러 가지 표현들: Ad hoc, Hard-coded, Dynamic, Embedded • 종류 • 순수한 Ad hoc 쿼리 • 자동 파라미터화 쿼리로 자동 처리도 가능 (일명 Safe Auto-params) • 컴파일 비용 반복 소비 • 순수 Ad hoc으로 Caching될 경우 과도한 Cache 소비 원인 • 이유: 순수 Query 그 자체로 재사용 가능하므로...정말? • 파라미터화 쿼리 • 여러 가지 표현들: Parameterized, Prepared, Static • 종류 • sp_executesql 같은 명령 직접 사용 • Database API (ADO, ADO.NET) 를 통해서 역시 같은 명령들이 간접 호출 • Ad hoc 과 Procedure 의 중간 형태 • 구문 별 Cache 처리 및 재사용 기능 제공 • 구문이나 파라미터 형식, 길이 등이 다를 경우에도 개별 Cache 처리 • 주의. 프로시저와 같은 재사용 문제 내포

26. SP vs. Prepared Sql Plan vs. EXEC • 자동 파라미터화 • 저장 프로시저 vs. sp_executesql • sp_executesql vs. EXEC (프로시저 내에서)

27. 데모 • 재사용 비교 • Proc Plan vs. Prepared Plan

28. 성능 추적 및 모니터링 • master.dbo.syscacheobjects • 각 DB별 Cache Plan의 유형, 소비되는 크기, 재사용 률 등의 진단 분석 • 프로필러 혹은 SQL Trace • 성능 카운터 • T-SQL 명령 • DBCC CACHESTATS, DBCC PROCCACHE, DBCC MEMORYSTATUS

29. 세션 요약 • 저장 프로시저는 처음 실행 시(혹은 재컴파일시)에 실행 계획을 결정한다. • 한 번 결정된 실행 계획은 이후 실행에서 계속 재사용되어진다. • 실행 계획 재사용으로 인한 부작용은 심각한 성능 문제를 유발할 수 있다. • 재컴파일에 필요성과 함께 그 문제점을 이해한다. • 저장 프로시저 실행 구조의 대한 이해를 통해서 문제점을 해결할 수 있다.

30. 추가 자료들 • Microsoft SQL Server Query Processor Internals and Architecturehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp • (기술자료) 저장 프로시저 재컴파일 문제 해결http://support.microsoft.com/kb/243586/ko • Microsoft SQL Server 성능 튜닝 Technical ReferenceWhalen, Garcial, Deluca, Thompson | 정보문화사 • Inside Microsoft SQL Server 2000Kalen Delaney | 정보문화사 • SQL Server 최적화 튜닝정원혁, iti 교육센타 • Programming a Microsoft SQL Server 2000 Database 2073, MOC 교육 과정

32. 참고자료 : IT 전문가를 위한 Microsoft 인증

33. 여러분의 Microsoft 기술 능력 평가 Microsoft Skills Assessment무엇인가? • 현재 제품 및 기술 솔루션에 대한 능력 평가 • Windows Server 2003, Exchange Server 2003, Windows Storage Server 2003, Visual Studio .NET, Office 2003 • 무료, 온라인, 누구나 사용 가능 • 평가결과를 기초로 Microsoft 교육 프로그램을 제안합니다. • 평가항목과 최고점수 표시 • 방문하세요!www.microsoft.com/assessment

34. Microsoft Certified Systems Administrator(MCSA)가되자! • MCSA 무엇인가? • Microsoft Windows Server 기반의 시스템, 네트워크 유지보수와 관리를 하는 IT 전문가를 위한 인증제도 • 어떻게 MCSA(Windows Server 2003) 합격? • 3개 코어 시험 통과 • 1개 선택 과목 • 자세한 정보는 아래 URL를 참고하세요? www.microsoft.com/mcsa

35. Microsoft Certified Systems Engineer (MCSE)가 되자! • MCSE 무엇인가? • Microsoft Windows Server System 기반의 비즈니스 솔루션, 인프라스트럭처의 설계, 도입계획, 도입방법, IT 운영자의 요구분석 능력을 인증하는 제도 • 어떻게MCSE(Microsoft Windows 2003) 합격? • 6개 코어 시험 통과 • 1새 선택 시험 통과 • 자세한 정보는 아래 URL를 참고하세요? www.microsoft.com/mcse

36. Microsoft Certified Desktop Support Technician(MCDST) • What is the MCDST certification? • Microsoft Windows 오퍼레이팅 시스템에서 실행되는 데스크톱 환경의 문제해결 및 전문가의 기술지원 능력을 인증하는 제도 • 어떻게MCDST(Microsoft Windows XP) 합격? • 2개 코어 시험 통과 • 오퍼레이팅 시스템 • 데스크톱 애플리케이션 지원 • 자세한 정보는 아래 URL를 참고하세요? www.microsoft.com/mcse

37. Specialization인증을 도전하세요. • MCSA/MCSE specializations? • IT 전문가를 위한 메시징, 보안 전문분야의 인증제도 • 현재 인증 가능한 전문? • MCSA: Security –MCSA: Messaging • MCSE: Security –MCSE: Messaging • 자세한 정보는 아래 URL를 참고하세요? www.microsoft.com/mcsaorwww.microsoft.com/mcse

38. TechNet에 가입하세요.최신 기술 뉴스를 받고 싶으세요? 평가기간 없는 소프트웨어!: Technet Plus 가입자는 평가 목적으로 Microsoft 정품제품을 다양하게 시험을 할 수 있다. 무료 기술지원: 가입자는 2개의 무료 기술지원을 받을 수 있으며, 중요한 문제해결을 위해 시간을 절약할 수 있다. 최신 TechNet 정보를 오프라인에서 사용: TechNet 사이트의 Microsoft 평가, 설치, 솔루션의 정보를 CD 또는 DVD로 받을 수 있다. www.microsoft.com/technet/subscriptions

39. 어디서 정보를 얻을 수 있나요? • 웹 캐스트 또는 온라인 채팅www.microsoft.com/technet/community/chats www.microsoft.com/technet/community/webcasts • 뉴스그룹 목록 www.microsoft.com/technet/community/newsgroups • Microsoft 커뮤니티 사이트 www.microsoft.com/technet/community • 커뮤니티 이벤트www.microsoft.com/technet/community/events • 커뮤니티 칼럼 www.microsoft.com/technet/community/columns