SQL Server ™ 2000 에서 성능 좋은 어플리케이션 구현하기

1. SQL Server™ 2000에서 성능 좋은 어플리케이션 구현하기 (Building High Performance Applications With SQL Server™ 2000) 우 철웅㈜인브레인 기술이사 chwoong@inbrein.com www.inbrein.com

2. 세션의 목적 • 어플리케이션 개발자 관점에서 SQL 서버의 확장성과 수행 속도에 대한 논의 • 어플리케이션 개발과 직접 관계 있는 SQL 서버의 특징 점검 • 성능 좋은 어플리케이션을 만들기 위한 예제

3. SQL 서버 성능 관리 Self Configuring, Managing, Tuning • 자동화된 memory/lock/object 관리 • 확장되고 스마트한 I/O • 복합 구문에 대한 병행 실행 계획 • 자동화된 미리 읽기(Readahead) • 자동화된 통계정보의 생성/관리 • 고급 쿼리 최적화 • 자동화된 인덱스 튜닝관리 • 자동화된 쿼리 실행 계획의 캐싱 • 기타, 여러 가지…

4. “SQL 서버의 성능은 좋은데, 왜 나의 어플리케이션은 그렇지 못한가?” • 어플리케이션 수행속도와 확장성을 위해 살펴 보아야 할 내용 • 최적화된 실행 계획의 케싱 보장 • 네트웍 라운드트립(roundtrip)의 최소화 • 효과적인 커서의 사용 • 데이터를 접근하는 방법에 따라 수행 속도의 결과는 굉장한 차이를 보일 수 있다.

5. 실행 계획의 캐싱아키텍쳐 살펴보기 • 쿼리한 SQL 구문은 파서에 의해 쿼리 트리로 만들어 진다. • 옵티마이저는 최적화된 실행 계획을 만들기 위해 쿼리 트리를 만든다. • 정규화 (Normalization) • 통계 정보를 이용한 최적화 (비용 기준) • 가장 빠른 경로를 위한, 최소 비용 처리 방법 발견 • 실행 계획 – 데이터 구조 생성 • 실행 계획의 저장 • 같은 실행 컨택스트 내에서 재사용

6. 실행 계획의 케싱(Caching)케싱 메커니즘의 형태 • 임의 질의에 대한 케싱 • 임의 질의(Ad-hoc) 케싱 • 자동 파라메터화 • 고정된 질의에 대한 케싱 • 저장 프로시저 • ExecuteSQL • Prepare/Execute/Unprepare

7. 임의 질의에 대한 케싱어떻게 작업하는가? • 임의 질의 구문에 대한 실행 계획 케싱 • 정확히 같은 구문을 수행한다면 케시된 수행계획을 재 사용 예 : Q1: select * from employees where fname like ‘S%’ Q2: select * from employees where fname like ‘D%’ Q3: select * from employees where fname like ‘S%’ • Q3 는 Q1의 케시된 실행 계획을 사용하게 된다. • Q2 컴파일을 필요로 한다.

8. 자동 파라메터화어떻게 작업하는가? • 단순 구문에 대해서만 처리된다. • 옵티마이저는 상수에 대해 파라메터화 할 것인지 검토한다. • 만약 파라메터화가 결정되면 템플릿 실행 계획은 캐시에 저장된다. • 연속적으로 동종의 쿼리가 수행되어 캐시의 저장된 템플릿 실행 계획을 사용한다면 “safe”로 만들것을 고려한다. • 권고 사항: 잘 정의된 작업에 대해 이런 기능을 믿고 작업하지는 않는 것이 좋겠다.

9. 자동 파라메터화지원하는 기본 구문 • INSERT <table> VALUES ({constant} | NULL | DEFAULT}, ..) • DELETE <table> WHERE <key-exp> • UPDATE <table> SET <col> = <constant> WHERE <key-exp> • SELECT <col-list> FROM <table> WHERE <key-exp> ORDER BY <col-list>

10. 자동 파라메터화템플릿 예제 • Safe: • INT @P, SELECT fname, lname FROM employees WHERE emp_id = @P • Unsafe: • SELECT fname, lname FROM employees WHERE (salary + bonus) > 30000 • 옵티마이저의 이러한 결정은 매우 보수적이다.

11. 저장 프로시저어떻게 작업 하는가? • 서버에 컴파일된 구문 저장 • 객체 관리 계속적 지원 • 어플리케이션에 의한 파라메터 명시 • 케시에 있는 컴파일된 계획 실행 • WITH RECOMPILE 옵션으로 재 컴파일 • 저장 프로시저로 동적 생성 구문 수행 • Sp_executesql, EXEC

12. 저장 프로시저장점 • 데이터에 대한 비즈니스 로직의 은익화 • 수행 속도 • 재사용성 • 네트웍 트래픽의 감소 • RPC 수행을 통한 구문 파싱과 파라메터 프로세싱 작업을 안함 • 사용자 정의 함수(UDFs)도 비슷한 장점이 제공됨

13. 저장 프로시저잘못된 저장 프로시저 호출 방법 • 값으로 직접 호출하지 마라… //나쁜 수행 방법이다. //호출 할 때 마다 보내어진 값 ‘myval’에 대해 컴파일 한다. //RPC event 대신에 SQLBatch로 수행되어 진다. SQLExecDirect(hStmt,“execute mysp‘myval’”, SQL_NTS); //최적화된 상태는 아니다. //호출할 때마다 값에 대해 컴파일하여야 하기 때문에 SQLExecDirect(hStmt,“{ CALL mysp(‘myval’)}”, SQL_NTS);

14. 저장 프로시저SP를 효과적으로 호출하는 방법 • 파라메터를 이용한 호출 구문 // Bind parameterschar szParam[32];SQLBindParameter(hStmt,…,szParam);// Set parametersstrcpy(&szParam[0], “myval”);// Execute the stored procedureSQLExecDirect(hStmt, “{ CALL mysp(?) }”, SQL_NTS); //Even better on SQL Server 2000 SQLPrepare ( hStmt, “{ CALL mysp(?) }”, SQL_NTS); SQLExecute ( hStmt ); 다음 호출시 케시에 있는 플랜을 사용

15. ExecuteSQL어떻게 작업되는가? • 어플리케이션에 의해 생성되며 • 지속적 객체 관리가 안됨 Example: Sp_ExecuteSQL 'insert T1 values (@p) ', '@p float',1 Sp_ExecuteSQL 'insert T1 values (@p) ', '@p float', 3 Sp_ExecuteSQL 'insert T1 values (@p) ', '@p float', 1 • ODBC SQLExecDirect 또는 OLEDB ICommandWithParameters 로 구현 가능

16. Prepare / Execute어떻게 작업하는가? • 어플리케션에 의해 생성 • 파라메터를 Question marks(?)와 문자열로 쿼리를 수행한다. • 반복적 수행에 대해 효과적인 지원 • UnPrepare가 발생할 때 초기화됨 • ODBC, OLEDB, or DB-Library의 ICommandPrepare에 의해 지원됨. • DELETE FROM AUTHORS WHERE au_fname = ? AND au_lname = ?

17. 부적절한 실행 계획의 공유 • 케시의 수행 계획을 사용하고 싶지 않을 때 • 최적화된 수행 계획은 선택 파라메터에 의해 결정되기 때문 예제 : Select * from T where c>=5 and c<=10 (index lookup) Select * from T where c>=5 and c<=100 (table scan) • 단일 실행 계획으로 두 구문에 대해 최적화된 실행 계획을 가질 수 없다.

18. SQL 7.0 수행 속도의 핵심ExecDirect 대 Prepare/Execute • 대부분의 많은 어플리케이션들은 독립적인 드라이버 레이어로 구현된다 • 어플리케이션 로직으로 독립된 데이터베이스 • 특별한 목적을 위한 프레임웍 • Passing hints • 사용자 사용의 예측 • Application 서버 • N+2 costing

19. 적은 Round-TripsSQL Server 2000 개선 부분 • Prepare/Execute model: • 모든 파라메터의 타입을 명시할 수 있음 • Prepare와 Execute 간의 요청 사항 없음 • N 번의 라운드 트립은 N 번의 수행을 이야기 한다. • 권고 사항 : 항상 Prepare/Execute 사용하자!

20. Prepare Execute Execute Prepare Execute Execute UnPrepare UnPrepare Prepare Execute Execute Response Prepare Execute Execute Response Handle Response Response UnPrepare Handle Response UnPrepare Response Prepare/Execute Round-TripsSQL 서버 7.0에서 User Code … … 7.0 Client … … SQL Server 7.0

21. Prepare Execute Execute Prepare Execute Execute UnPrepare UnPrepare Prepare+Execute UnPrep+Prep+Exec Execute Execute Response Response NewHandle+Response Handle +Response Prepare/Execute Round-TripsSQL 서버 2000에서 User Code … … 2000 Client … … SQL Server 2000

22. Meta-Data 감소 SQL 서버 2000에서 개선 • Selects: 각 컬럼을 위한 meta-data • 클라이언트 사이드에 Meta-data를 케싱 (prepare/execute의 사용으로) • meta-data와 파라메터에 의한 처리 • 많은 컬럼의 테이블에 대한 비용 감축(30%) • SQL2000 클라이언트에 의한 최적화 • 단지 SQL 서버의 데이터와 데이터 길이만 조회

23. 권고 사항SQL 서버 2000에서 • 가능한 저장 프로시저를 사용하라. • Prepare/Execute를 사용하라. (SQLExecute 대신) • 가능한 파라메터화 하라 • 파라메터의 변동 폭이 큰 경우는 주의할 필요가 있다. • 가능하다면 어플리케이션에서 파라메터의 데이터 형을 명시하라. • 공통적인 룰을 관찰하고 적용하라. • 툴을 사용하여 compiles/recompiles에 대한 통계를 관측하여 적용하라.

24. 커서를 사용하는 이유? • 데이터베이스 드라이버와 함께 어플리게이션을 구현할 수 있음 • 행 작업에 대해 스크롤(scroll)과 수정(update) 가능 • 하나의 커넥션에 여러 반복적 구문 수행 가능 • 일부 행에 대한 부분 처리를 위해 • 어플리케이션에서 커서 사용을 필요로 하는 경우

25. 효과 적인 커서의 사용 • 커서 타입의 선택에 따라 수행 성능의 차이가 커질 수 있음 • 요구 수행할 수 있는 최소 비용이 드는 커서 타입을 사용 • 커서 기본 옵션이나 Fast Forward Only를 사용 • 다른 기능을 원한다면 다른 커서 타입을 고려한다. • Server/Network 자원 사용에 대한 고려를 한다. • 최소 비용이 드는 트랜젝션을 고려한다.

26. 기본 결과 셋 • 기본 적인 모델은 커넥션에 대해 단일 SQL 구문이다. • 결과 셋을 모두 사용하게 최적화된 결과 셋을 조회하여야 한다. • 잇점: • 적은 서버 자원의 사용 • 불필요한 자료 요구의 제거 • 핵심: 결과 셋을 사용하는 동안 커넥션 은 쉬지 않고 움직인다.

27. Get Next Rows Cursor Open Result Set Packet Result Set Packet Result Set Packet 기본 결과 셋 예 Client … SQL Server

28. Fast Forward-Only 커서 • forward-only와 read-only를 조합한 • 서버 커넥션이 많은 경우 유리 • 네트웍이 약한 기반에서 유리 • 기초 테이블에 직접 작업 • Fetch next 만 지원 • 수정된 데이터에 대한 반영 됨 • ODBC에서 AutoFetch를 활성화 하여 놓으면, AutoClose 역시 자동으로 작동됨

29. Cursor Open Get Next Rows Get Next Rows Get Next Rows Result Set Packet Result Set Packet Result Set Packet Fast Forward Only Example Client … SQL Server

30. 정적(Static) 커서 • 커서 오픈 시 스냅샷(Snapshot) 형태로 TempDB에 임시 테이블을 생성하여 사용 • 읽기 전용 – 기초 테이블 수정 불가 • 모든 스크롤(scroll) 옵션 가능 • 데이터의 동적 반영이 안됨

31. Cursor Open Get Next Rows Get Next Rows Get Next Rows Result Set Packet Result Set Packet Result Set Packet Query Base Tables TempTable 정적(Static) 커서동작원리 Client … SQL Server

32. 키셋(Keyset) 커서 • 커서 오픈 시 키셋에 대해 TempDB에 임시 테이블로 저장하여 사용 • 커서를 오픈하면 키셋의 구성요소가 고정됨 • 모든 스크롤(scroll) 옵션 가능 • SELECT 관계된 모든 테이블에 unique index가 필요 • 인덱스 컬럼 이외의 데이터는 동적으로 수정된 자료 조회 가능 • 새로운 데이터의 추가는 조회 불가능 • 기존 데이터의 삭제는 에러 @@FETCH_STATUS = -2인지를 확인 • WHERE CURRENT OF구문으로 인덱스 컬럼의 변경에 대해서는 조회 가능

33. Cursor Open Get Next Rows Get Next Rows Get Next Rows Result Set Packet Result Set Packet Result Set Packet Keys Query Base Tables Base Tables TempTable Keyset Cursors Example Client … SQL Server

34. Dynamic Cursors • 기초 테이블을 직접 조작 • 모든 스크롤 옵션 가능 절대(absolute position)위치 변경은 지원 안됨 • 스크롤 할 때 마다 변경된 모든 데이터 조회 가능 • Join으로 열린 커서에 대한 제약이 따름 (INSERT,UPDATE) • 성능 저하의 우려가 있음

35. 묵시적인 Cursor 형 변환 • 서버에 필요 결과 셋을 요구할 때 • 원래의 질의에 영향을 받아 • ORDER BY (index에 포함되지 않은 컬럼으로) • TOP, GROUP BY, UNION, DISTINCT …. • 대부분은 static으로 변환 • 형 변환 룰에 의한 커서형 변환 • 권고 사항: 커서 형 변환을 확인하기 위해 SQL_SUCCESS_WITH_INFO를 확인

36. Static 대 Keyset • Static • 전체 구성 요소 셋을 만듬 • 연속적인 페치 시 가장 저렴한 비용 • Keyset • 키 구성 요소 셋을 만듬 • 페치 시 인덱스 외의 컬럼을 페치 • Static 보단 저렴한 비용 • Asynchronous 정도 설정 • Sp_configure ‘cursor threshold’ n

37. 커서 강화된 기능SQL 서버 2000에서 • 커서 컴파일 플랜 • 커서 수행 계획 • Static과 keysets에 대한 Fetch next • Keyset fetch • Cursor foot print (memory)

38. 개별 행 접근 시 향상된 기능SQL Server 2000 • Selects • 내부 메모리에 정보 사용 • 컬럼 접근 속도 향상 • IRow interface (OLE-DB) • 단일 행 접근 시 직접 접근 • 연속적인 다음행 접근

39. 예제Dynamic Cross-Tab 만들기 Create Proc up_Cross_tab AS …. Create Table #CrossTab (ColKey varchar(20)) While … exec ('ALTER TABLE #Matrix ADD…' ) … DECLARE Data_Cursor CURSOR FOR … WHILE @@FETCH_STATUS = 0 BEGIN ….. END SELECT * FROM #CrossTab

40. Demo • Ad-Hoc Plan Caching • Auto-Parameterization • Stored Procedures • Reduced Round-Trips • Cursor

41. General Performance 추가 권고 사항 • 배치 프로세싱 처리 하라. (inserts와 selects를 한 배치에) • Combine inserts • Network Library의 자원 공유 • SAN(System Area Network) 사용 고려

42. Connection Pooling • SQL Server에 대한 클라이언트 커넥션의 재사용 • 네트웍 트래픽과 CPU의 부하를 줄일수 있다. • SQL 서버의 메모리 자원을 적게 사용한다.

43. Summary • 강력한 수행 계획의 케싱을 위한 • 저장 프로시저 • Prepare/Execute • 파라메터화(Parameterize) • 커서 • 효과 적인 커서 타입 설정 • Test for downgrade • SQL 서버 2000 내부적으로 최적화를 지원하는 다양한 잊점을 사용하자!

44. SQL Server Today… • CRM • ERP • E-Commerce • Data Warehousing

45. More Information… • Books On-Line • Beta2 News groups Microsoft.beta.Shiloh.* • SQL Server Home page http://www.microsoft.com/sql • Microsoft Product Support Services http://search.support.microsoft.com

46. Questions And Feedback chwoong@inbrein.com