강사 소개

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. 2장 저장 프로시저 성능 문제 분석 및 해결 방안 일편단심(一片丹心), “하나의 쿼리는 하나의 실행 계획만을 가진다.”

9. 2장. 목차 • 응용 프로그램 이슈 • RPC Event를 통하지 않은 호출 • 파라미터 개체 자동 생성 • 파라미터 암시적 데이터 형식 지정 • 암시적 소유자 지정혹은 sp_ 프로시저 호출 • 재컴파일(Recompile) • SARG 위반 • Parameter Sniffing • 파라미터 vs.변수 • 파라미터에 따라 다른 실행 계획 • 동적 검색 조건 • 유의 사항 정리

10. 실행 계획 재사용 능력 및 성능 향상 파라미터 Caching/재사용 능력 제공 SQL Injection을 포함한 보안 강화 SQL 엔진이 원하는 것은 RPC Event RPC Message (RPC Event) SET rs = New ADODB.Recordset ... rs.Open “EXEC Proc param1” ... SQL Language Message (Batch Event) NO NO cmd.Connection = cnn; cmd.CommandText = “EXEC Proc param1”; cmd.CommandType = CommandType.Text; rdr = cmd.ExecuteReader(); cmd.Connection = cnn; cmd.CommandText = “dbo.Proc”; cmd.CommandType = StoredProcedure; cmd.Parameters.Add(“@member_no” , SqlDbType.Int , 4).Value = param_value; ... rdr = cmd.ExecuteReader(); RPC Event를 통하지 않은 호출

11. sp_procedure_params_rowset 실행 서버의 라운드 트립 유발 시스템 테이블 접근 유발-sysobjects 등 추가 Request, I/O 등 성능 문제 유발 WITH cmd .ActiveConnection = cn .CommandText = “Proc” .CommandType = adCmdStoredProc .Parameters.Refresh .Parameters(1).Value = 1000 ... Parameter 개체 암시적 생성 NO 혹은 이것도 생략 가능 파라미터 개체 자동 생성

12. 불필요한 Procedure Cache 소비 SARG 위반할 경우 성능 문제 유발 Parameterized Query에서 특히 주의 App/DB에서 암시적 형 변환 유발 cmd.Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandText = “SELECT * FROM dbo.member WHERE firstname = @fname”; cmd.Parameters.AddWithValue(“@fname”, lstFname.SelectedItem); ... NO 파라미터 암시적 데이터 형식 지정 Parameter 데이터 형식의 생략 혹은 암시적 지정

13. EXEC Proc Proc 존재? System Procedure? Cache Miss Cache Miss Hit OBJECT_ID (Proc) 존재? 암시적 소유자혹은 sp_ 프로시저 호출 암시적 소유자로 호출한 경우 sp_ 접두사를 사용한 프로시저 EXEC sp_Proc 이름으로 검사 A System Proc 찾기 불일치 dbo.Proc dbo.sp_Proc 불일치 A 컴파일 단계 ID로 검사

14. 데모 • 응용 프로그램 이슈 • RPC Evnet를 통하지 않은 호출 • 파라미터 개체 자동 생성 • 파라미터 암시적 데이터 형식 지정 • 암시적 소유자 혹은 sp_ 프로시저 호출

15. 재컴파일 • 재컴파일은 필요 조건이지만 충분 조건은 아님 • 재컴파일의 유형별 특징을 숙지하고 적절한 조치 필요 • 유형 • 자동 재컴파일 • 실행 전 자동 재컴파일 원인 • 실행 시 재컴파일 원인 - 과도한(조정 가능한) 재컴파일 • 수동 재컴파일

16. 실행 전 자동 재컴파일 • 재컴파일 원인 • WITH RECOMPILE 절 사용 • 제약조건, 기본값 또는 규칙의 추가, 제거를 포함한 참조 개체의 스키마 변경* • 통계 정보 변경* • 인덱스 추가 / 제거* • 참조한 테이블에 대해 sp_recompile 수행 • 데이터베이스 복원 • 오래된 계획으로 프로시저 캐시에서 제거 • 주의 대상 • (* 항목) Batch 형식의 코드 실행 시 자동 재컴파일이 안될 수도 있다 • DB 옵션 확인 – “통계 자동 업데이트”, “통계 자동 작성” • Batch 코드를 재 작성 • 수동 재컴파일 코드 추가 • 검증 필요 • 실행 계획 재사용 부작용을 내포한 쿼리의 경우 재컴파일에 주의 • 실행 계획 분리 (앞으로 다룰 내용) • 개체 이름 참조 시에는 항상 2-part 형식 이상으로 표기 • EXEC dbo.up_OrdersInfo • SELECT * FROM dbo.Orders

17. 실행 시 자동 재컴파일 1/2 • 재컴파일 원인 • 주의. 과도한 재컴파일을 유발, 조정 가능 • 참조 테이블의 충분한 비율의 데이터 변경 (*MS 기술 자료 내용) • SET 옵션(Connection 상태) 변경 • “추가 자료들” – (기술자료) 참조 • DDL 및 DML 문의 교차 실행 • 임시 테이블에서 특정 작업 수행 • 존재하지 않는 시점의 테이블 참조 CREATE PROC dbo.Interleave AS CREATE TABLE dbo.#t1 (a int, b char(5)) SELECT ... CREATE INDEX nc_#t1_a ON #t1(a) SELECT ...

18. Recompile 시 EventSubClass 측정 값 • Event = SP:Recompile, column = EventSubClass

19. 실행 시 자동 재컴파일 2/2 • 조정 가능한 작업들 • 전체 프로시저의 재컴파일을 구문 단위로 유도 • sp_executesql / EXEC() 로 대체 • 하위 저장 프로시저로 대체 • (임시 테이블의 경우) KEEP PLAN / KEEPFIXED PLAN 옵션 사용 • SET 옵션으로 인한 재컴파일 방지 • 재컴파일에 영향을 주지 않는 제한된 옵션만 사용 • 프로시저 호출 시에 옵션이 달라지지 않도록 유의 • DDL과 DML 구문을 교차 실행하지 않고 순서대로 실행 • CREATE; CREATE; SELECT; SELECT • 임시 테이블 사용 시 고려 사항 • 프로시저 자신의 임시 테이블만 참조 • 임시 테이블 이름을 만들기 전 또는 삭제된 후에 참조하지 않도록 유의 • 프로시저 시작 부분에서 생성 • IF...ELSE, WHILE 같은 제어 문에서 생성하지 않도록 유의 • 필요 시 테이블 변수로 대체

20. 재컴파일 강제 표시 설정 • 이후 실행 시 스스로 재컴파일 • 테이블 지정 시 참조하는 모든 계획을 재컴파일 • 긴급 활용 • CPU Killer 제거 시 • 실행 시 지정 • Ad hoc과 동일? • Cache 저장되지 않는다 • 기존 Cache • 재사용 안됨 • 그대로 존재 • 옵션 제거 시 재사용 수동 재컴파일 • 적재적소(適材適所) sp_recompile procedure sp_recompile table EXEC... WITH RECOMPILE CREATE PROC ... WITH RECOMPILE • 프로시저 작성 시 지정 • Ad hoc과 동일? • Cache 저장되지 않는다 • 최후의 보류?

21. 데모 • 재컴파일 이슈 • 자동 재컴파일 • 수동 재컴파일

22. CREATE PROC dbo.up_1 @ID char(2) AS SELECT * FROM dbo.table WHERELEFT(char_col, 2)= @ID <column_name><inclusive_operator><value>[AND…] CREATE PROC dbo.up_4 @ds datetime AS ... SET @s = @ds + 7 SELECT * FROM dbo.table WHERE date_col BETWEEN @ds AND @s CREATE PROC dbo.up_2 @ID int AS SELECT * FROM dbo.table WHERE char_col = @ID CREATE PROC dbo.up_3 @ID char(10) AS SELECT * FROM dbo.table WHERE char_col LIKE RTRIM(@ID) SARG 위반 @ID varchar(5) char_col LIKE @ID ... @ID @ID varchar(5) @ds AND (@ds+7) @ds AND @ds2

23. 선택도 = 65/830 • 실행계획 = CL Index Scan • 선택도 = 2/830 • 실행계획 = CL Index Scan • 선택도 = 2/830 • 실행계획 = Index Seek Parameter Sniffing • 문제: (초기/재)컴파일 시 사용된 파라미터로 생성된 실행 계획이, 재사용 시에는 잘못된 실행 계획이 되는 경우 @B_Date=‘19960710’ @E_Date=‘19960930’ FROM dbo.Orders WHERE OrderDate Between @B_Date AND @E_Date @B_Date=‘19960710’ @E_Date=‘19960711’ @B_Date=‘19960710’ @E_Date=‘19960711’

24. dbo.ParamVsVar @mem_no = 10000 @v_no = ? • 선택도 = 11 / 100,000 • 실행계획 = Index Seek • 선택도 = 2,752/100,000 • 실행계획 = Table Scan @v_no = @no @v_no = 10000 파라미터 vs.변수 • 전제: 변수의 값은 쿼리 실행 시점에 알 수 있다 • 문제: 컴파일 시 쿼리 선택도 판단에 있어, 변수는 그 값을 알 수 없으므로 인한 최적 실행 계획 오류 @no int AS ... FROM dbo.Charge WHERE memeber_no=@no AS DECLARE @v_no int SET @v_no = @no FROM dbo.Charge WHERE memeber_no=@v_no

25. @id = 10248 @date = NULL @date = ? @id = NULL @date = ‘19960930’ • 선택도 = 1/830 • 실행계획 = Index Seek • 선택도 = 70/830 • 실행계획 = Index Seek @id = NULL @date = ‘19960930’ • 선택도 = 70/830 • 실행계획 = CL Index Scan 파라미터에 따라 다른 실행 계획 • 전제: 파라미터에 따라 실행할 쿼리 형식이 다른 경우 • 문제: (초기/재)컴파일 시 모든 쿼리의 정확한 선택도를 판단할 수 없으므로 인한 최적 실행 계획 오류 @id int ,@date datetime AS IF @id IS NOT NULL SELECT * FROM ... WHERE OrderID=@id ELSE SELECT * FROM ... WHERE OrderDate<=@date

26. 모든 경우의 수에 따른 성능 확인 필요 • 개별 구문 별 재사용 처리 • 동일 구문에 대해서는 여전히 재사용 문제 내포 • 프로시저와 다른 Scope로 처리 • Recompile 강제 어려움 • sp_recompile 테이블 동적 검색 조건-예제 @orderid int = NULL , @fromdate datetime = NULL , @todate datetime = NULL , @prodname nvarchar(40) = NULL ... SELECT @sql = 'SELECT * FROM Northwind.dbo.Orders o JOIN Northwind.dbo.[Order Details] od ON o.OrderID = od.OrderID JOIN Northwind.dbo.Products p ON p.ProductID = od.ProductID WHERE 1 = 1 ' IF @orderid IS NOT NULL SET @sql = @sql + 'AND o.OrderID = @xorderid' IF @fromdate IS NOT NULL SET @sql = @sql + 'AND o.OrderDate >= @xfromdate ' IF @todate IS NOT NULL SET @sql = @sql + 'AND o.OrderDate <= @xtodate ' IF @prodname IS NOT NULL SET @sql = @sql + 'AND p.ProductName LIKE @xpname + ''%''' ... EXEC sp_executesql @sql, @paramlist, @orderid, @fromdate, @todate, @prodname

27. 데모 • 저장 프로시저 쿼리 성능 이슈 • Parameter Sniffing • Parameter vs. variable • Parameter 에 따른 다른 실행 계획 • 동적 검색 조건 처리

28. 유의 사항 • 하나의 쿼리는 하나의 실행 계획만을 가지도록 구성 • 쿼리를 분리 혹은 프로시저를 분리 • SET NOCOUNT ON 세션 옵션 사용 • OUTPUT 파라미터, RETURN 활용 • sp_executesql vs. EXEC 명확히 구분해서 사용 • sp_ 접두사는 금기 • 소유자명 명기 (내부 쿼리 포함), ex. dbo.UP • 프로시저 내에서 파라미터 값의 변형은 금기 • 조건 식에서 함수 호출에 주의 • 조건 식에서 파라미터에 함수 적용 시에도 주의 • 불필요한 / 과도한 재컴파일 유의 • 대형 프로시저의 경우 전체 프로시저의 재컴파일을 방지 • 세션 옵션 사용에 유의 • 제한된 세션 옵션만 사용 (다음 장 참조) • 특히 ANSI_NULLS, QUOTED_IDENTIFIER는 소용 없음 • DDL / DML 구문은 교차하지 않도록 처리 • 임시테이블 / 테이블 변수 사용에 주의 • 임시 저장 프로시저 방지

29. SQL Server 2005 New Features재컴파일(Recompile) • 쿼리 최적화기의 변화 및 기능 향상 • 구문-단위 재컴파일 • EXEC (...) 쿼리에 대한 Cache 처리 지원 • 쿼리 플랜용 비용 계산식의 변환 • CLR에서 호출된 배치에 대한 재사용 지원 • 칼럼 단위 통계 처리 지원 • 향상된 암시적 형 변환 처리 기능 • 추가 힌트 지원, ex. OPTION (RECOMPILE) • 향상된 자동 파라미터화 기능

30. SQL Server 2005 New Features통계정보(Statistics) • 문자열 요약 통계 – LIKE 가 빨라졌다 • 비동기적 통계 업데이트 • 계산된 열에 통계 지원 향상 • 대용량 개체 지원 • 향상된 통계 로딩 구조 • 최대 통계 수량 증가 – 2,000/테이블, 32 칼럼 • DBCC SHOW_STATITSTICS 출력 향상 • 내부 테이블에 대한 통계 지원 – xml, fulltext index, etc • 풀 스캔 시의 병렬 수집 • 생략된 통계 정보에 대한 자동 생성 및 재컴파일 지원 • 빈 테이블에 대한 통계 업데이트 및 재컴파일 기능 향상 • sp_updatestats 기능 향상

31. 세션 요약 • 저장 프로시저를 호출하는 데이터베이스용 어플리케이션의 성능 문제를 이해한다. • 저장 프로시저와 일반적인 Ad hoc 쿼리와 어떻게 다른지를 이해한다. • 재컴파일로 인한 성능 문제를 이해하고 다양한 해결 방안들을 알 수 있다. • 저장 프로시저 사용시 발생 가능한 성능 문제의 유형들을 이해하고 그 해결 방안들을 알 수 있다. • SQL Server 2005 향상된 기능을 활용하자.

32. 추가 자료들 • 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 교육 과정

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

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

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

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

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

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

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

41. 어디서 정보를 얻을 수 있나요? • 웹 캐스트 또는 온라인 채팅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