이종인 jilee@daoudata.co.kr

1. SQL Server 2005 실행계획 이해 및 최적화 제안 이종인 jilee@daoudata.co.kr

2. 이종인 jilee@daoudata.co.kr 다우교육원 SQL Server 전임강사 온디멘드 수석 컨설턴트 PASS Korea, SQL Specialist 창립 멤버 MCT, MCDBA, MCITP, MSCE+I,HPCP-Master ASE, OCP

3. Session 1: 실행 계획을 살펴보는 방법 예상 실행 계획 / 실제 실행 계획 그래픽 실행 계획 / 텍스트 실행 계획 / XML 실행 계획 SQL Server 2005 새 기능 Session 2: 실행 계획의 연산자 SCAN, SEEK, JOIN , UNION 등 실행 계획 연산자 실행 계획과 관련된 동적 관리 개체 Session 3: 최적화를 위한 제안 사항 실행 계획 연산자 가운데 가장 비용을 많이 소모하는 연산자는? 어디에 어떻게 인덱스를 만들어야 하나? 커서를 사용해야 하나? 목차

4. Session 1: 실행 계획을 살펴보는 방법 • 예상 실행 계획 vs. 실제 실행 계획 • 그래픽 실행 계획 vs. 텍스트 실행 계획 • XML 실행 계획 • SQL Server 2005 새 기능

5. 예상 실행 계획 vs. 실제 실행 계획 • 모든 실행 계획은 데이터 분포 예측을 기반으로 함 • 예상 실행 계획은 실제로 쿼리의 실행 없이 생성됨 • 임시 테이블은 생성되지 않음 • 데이터나 스키마는 쿼리 수행 전 변경 가능 • 실제 실행 계획은 쿼리 수행 중에 생성됨 • 임시 테이블이 생성됨 • 추가 정보 반환

6. 텍스트 기반 실행 계획 예상 실행 계획 SET SHOWPLAN_TEXT { ON | OFF } SET SHOWPLAN_ALL { ON | OFF } 실제 실행 계획 SET STATISTICS PROFILE { ON | OFF } 다음 세션 옵션 조합 가능 SET STATISTICS IO{ ON | OFF } SET STATISTICS TIME { ON | OFF }

7. 그래픽 실행 계획 예상 실행 계획 표시 선택한 모든 쿼리의 실행 계획 표시 실제 실행 계획 포함 토클 아이콘 실행 결과와 함께 추가 탭 제공 SSMS의 속성창 보다 상세한 정보 제공 실제 실행 계획에서는 추가적인 상세 정보 제공

8. 텍스트 실행 계획 예제 텍스트 형태 vs. 표 형태 ? StmtText ------------------------------------------------------------------------------------ SELECT SalesOrderID,OrderDate FROM Sales.SalesOrderHeader WHERE SalesOrderID=43659 (1개행적용됨) StmtText ---------------------------------------------------------------------------------------------------------------------------- |--Clustered Index Seek(OBJECT: ([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]) , SEEK:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID]=[@0]) ORDERED FORWARD) (1개행적용됨)

9. 그래픽 실행 계획 예제

10. 그래픽 vs. 텍스트 실행 ? • 그래픽 • 보기가 편리함 • 전체적인 개요 • 상대적인 비용 표시 • 각 연산자 상세 표시 • 텍스트 • 복잡한 쿼리에 적합 • Cntl+F로 검색 지원 • 저장이 용이함 • 예상과 실제 행수 비교 가능

11. XML 실행 계획 • 다음을 통해서 가능 • SET SHOWPLAN_XML ON • SET STATISTICS XML ON • 다양한 DMV • 추적 이벤트

12. SHOWPLAN_XML • 배치 당 하나의 XML 문서 생성 • <Query Plan> : • CachePlanSize, CompileTime, CompileCPU, CompileMemory • <RelOp> : 연산자 • 논리적/물리적 연산자 이름, 예상 비용 • 출력 컬럼 리스트, 참조 개체 등

13. STATISTICS XML • 추가 정보를 포함하는 실제 실행 계획 • <Query Plan> 부분에 DOP 속성 제공 • 각 <RelOp>에 실행시 정보 제공 • 처리 행 수 • 실행 횟수 • 검색 인덱스 • 조인 순서 등 • 실행 시 정보는 각 스레드 별 제공됨 • 추가 메모리 요구 연산자 정보 제공 • Sort • Hash Join

14. SQL Server 2005 New Features • XML 실행 계획 • SET SHOWPLAN_XML ON • SET STATISTICS XML ON • 추적 이벤트 • 5개의 실행 계획 관련 정보 • XML 실행 계획을 별도의 파일로 추출 가능 • 새롭게 튜닝을 위해서 제공하는 메타데이터 • sys.dm_index_usage_stats • sys.dm_exec_query_stats • 필요한 인덱스 정보 제공

15. 실행 계획 보기 비교

16. Session 1: 데모 실행 계획 확인

17. Session 2: 실행 계획의 연산자 SCAN, SEEK, JOIN , UNION 등 실행 계획 연산자 실행 계획과 관련된 동적 관리 개체

18. 쿼리 실행 계획 실행 계획은 연산자 트리로 구성 각 실행 계획 연산자 - 입력 행 검색 - 출력 행 생성 - 하나 이상의 자식 노드를 가질 수 있음 각 연산자는 다음 기능 수행 - Open - GetRow - Close 그래픽 실행 계획은 오른쪽에서 왼쪽, 위에서 아래로

19. 연산자 속성 메모리 소비 blocking vs non-blocking

20. 메모리 소비 모든 연산자는 메모리 소비 - 상태 정보 저장, 연산 수행 등 메모리 소비 연산자(MCI) 가운데 추가 메모리 소모 - 행 저장 - 정렬, 해쉬조인, 해쉬 집계 성능 영향 - 메모리 할당 대기 - 메모리가 없는 경우 tempdb 사용

21. blocking vs. non-blocking 2가지 종류의 연산자 - GetRow 메소드를 사용하여 동시에 입력행으로 부터 출력행을 반환 : Count-Scalar - Open 메소드를 사용하여 모든 행의 입력 이후 출력행을 반환 (Blocking or stop-and-go) : Sort 연산자…

22. 기본 연산자 Scan Seek Bookmark lookup Join Aggregation Union Parallelism Update

23. SCAN Scan 은 전체 인덱스 또는 테이블 반환 추가적인 필터와 함께 사용 가능 Argument 열에 ORDERD 연산자가 있는 경우는 인덱스 키 순서대로 정렬하고 ORDERD가 없는 경우는 출력을 정렬하지 않고 인덱스를 최적의 방법으로 검색(정말로?)

24. SEEK Seek는 인덱스 검색 기능을 사용해서 특정 범위의 행을 검색 기반 테이블의 행을 반환하기 위해서는 Bookmark Lookup 연산 수행 (인덱스가 쿼리를 커버하는 경우는 Bookmark Lookup이 수행되지 않음)

25. Bookmark Lookup Bookmark Lookup연산자는 책갈피(행 ID 또는 클러스터링 키)를 사용하여 테이블이나 클러스터형 인덱스에서 해당 행을 조회 SQL Server 2005 에서는 Bookmark Lookup이 사용되지 않고 Clustered Index Seek및 RID Lookup이 책갈피 조회 기능을 제공 (키 조회 or RID Lookup 후 조인 연산?)

26. 조인(JOIN) Loop Join Merge Join Hash Join

27. Loop Join 기본 동작 - 왼쪽 테이블에서 한 행씩 추출 - 오른쪽 테이블에서 조인 연결 고리에 부합하는 행 추출 성능 고려 - 왼쪽 테이블은 행수와 테이블 크기가 작고 오른쪽 테이블은 조인의 연결 고리에 유용한 인덱스가 있는 경우에 적합 - 왼쪽 테이블에 중복 값이 있는 경우는 Lazy Spool또는 Index Spool 사용 연결 고리에 동등 비교(=)가 없는 경우 지원

28. Merge Join 조인 연결고리가 정렬되어있어야 함 기본 동작 - 양쪽 테이블 모두 조인의 연결 고리로 정렬 확인 - 왼쪽 테이블에서 한 행을 추출하고 오른쪽 테이블에서 일치 여부 확인 - 일치하는 경우 행 반환 - 일치하지 않는 경우 오른쪽 테이블에서 다음 행으로 이동 일대다, 다대다 조인 지원 Loop Join으로 처리하기에는 큰 테이블 조인 시 사용

29. Hash Join 기본 동작 - 왼쪽 테이블에서 메모리상의 해쉬 테이블 생성 - 오른쪽 테이블에서 해쉬 함수를 사용해서 해쉬 테이블과 일치 여부 확인 - 메모리가 부족한 경우 디스크 사용 성능 고려 - 작은 테이블을 기준 테이블(build input)으로 사용 - 추가 인덱스 생성 등을 통해서 Merge Join 으로 해결 가능한지 확인

30. Join 비교

31. Subquery 조인으로 변경 가능 EXISTS 서브 쿼리는 Semi join 으로 변경 - not exists는 anti-semi join

32. Aggregation 2 가지 연산자 - Stream Aggregation - Hash Aggregation GROUP BY, DISTINCT 키워드 사용시 등…

33. Stream Aggregation 입력값이 반드시 그룹핑 키로 정렬되어야 함 각 그룹별 결과를 하나씩 반환 유용한 인덱스가 있는 경우 Hash Aggregation 보다 효과적

34. Hash Aggregation 입력값이 정렬될 필요 없음 모든 처리가 완료된 뒤 결과 세트 반환 추가적인 메모리 소모  tempdb 사용 가능

35. UNION UNION vs. UNION ALL - UNION ALL은 중복을 체크하지 않음 세 가지 알고리즘 : - MERGE UNION - HASH UNION - CONCAT UNION (UNION ALL)

36. Parallelism 대량 배치 작업에 유용 쿼리 최적화기가 병렬 실행 계획 생성 DOP는 런타임 시 결정됨 - set statistics profile on - set statistics xml on - 실제 그래픽 실행 계획…

37. 쿼리 실행 관련 동적관리 개체 sys.exec_cached_plan(V) sys.exec_query_stats(V) sys.dm_exec_requests(V) sys.dm_exec_query_plan(F) sys.dm_exec_query_plan_attributes(F) sys.dm_exec_sql_text(F) 함수와 view의 plan_handle값과 CROSS APPLY

38. Session 3: 최적화를 위한 제안 사항 일반적인 성능 최적화 팁 쿼리 구성 요소 실행 계획 구성 요소

39. 일반 적인 성능 최적화 팁 각 실행 계획의 상대적인 비용 분석 어떤 연산자가 가장 많은 행을 처리하는 가? 실행 계획에 메모리 소비? 어떤 인덱스를 만들어야 하나? - 테이블 스캔 또는 인덱스 스캔이 인덱스 검색으로 대체 가능한지? - 정렬이 인덱스 스캔으로 대체 가능한지? - 해쉬 조인이 루프 조인 또는 머지 조인으로 대체 가능한지?

40. 처리되는 데이터 량 고려 실행 화살표의 굵기 검토 각 실행 계획 연산자의 예상 처리 행 수 검토 실제 처리되는 행 검토 - set statistics profile on / set statistics xml on - trace event of Profiler

41. 쿼리 구성 요소 SARG 암시적인 데이터 형식 변환 변수 처리 조건 로직 처리 행 필터 커서

42. SARG (검색 제한 조건) SARG 정의 효율적인 인덱스 사용 여부 확인 Non-SARG를 SARG로 자동 변환 - au_lname like ‘J%’ Non-SARG를 SARG로 수동 변환 - 컬럼 분할 등

43. SELECT 리스트에 스칼라 함수 사용 SELECT 리스트의 스칼라 함수는 커서와 유사함 create function dbo.fnMaskSSN (@ssn char(11)) returns char(11) as begin select @ssn=‘XXX-XX-’+right(@ssn,4) return @ssn end; select dbo.fnMaskSSN(au_id), au_lname, au_fname from authors ; 인라인 코드로 변환 select @ssn=‘XXX-XX-’+right(@ssn,4), au_lname, au_fname from authors ; 인라인 테이블 반환 함수로 변환 - 연산 처리가 복잡한 경우

44. 암시적인 데이터 형식 변환 상수와 변수의 데이터 형식 비교 - 7.0과 2000/2005의 처리 방식이 다름 - 7.0 : 상수를 컬럼의 데이터 형식으로 변환 - 2000/2005 : 우선 순위가 낮은 형식을 보다 높은 형식으로 변환 - 데이터 형식 변환은 인덱스 사용이 불가한 경우 유발 가능 데이터 형식 우선 순위 참조 - 온라인 설명서 해결책 - DB 호환성을 70 - 상수의 데이터 형식을 명시적으로 변환 - 변수 선언시 적합한 데이터 형식 선언

45. 변수 변수에 할당되는 상수값은 최적화 시 참조되지 않음? - 밀도 정보는 등호 연산을 위해서 사용 가능 - 히스토그램은 사용 불가 해결 방법 - 저장 프로시저 사용 (RECOMPILE 옵션 사용) - SQL Server 2005 힌트 사용 (OPTIMIZE FOR 옵션 등)

46. 조건 로직 처리 실행 계획을 각 조건 별 모두 생성 IF @direction =‘1’ SELECT * FROM charge WHERE charge_amt<@value ELSE SELECT * FROM charge WHERE charge_amt>=@value 일부 조건은 실행 시 의미 없음 - 실제 입력 값에 의해서 결정 - 전체를 최적화하기 위한 부하 가중 해결 방법 - WITH RECOMPILE 옵션의 저장 프로시저 - 각각 저장 프로시저로 생성

47. 다양한 조건에 부합하는 쿼리 요구 별 쿼리 생성 - 다양한 목적의 쿼리 지양 - WHERE 절에 CASE 문 사용 쿼리 - WEHRE 절에 ISNULL / COALESCE 사용 쿼리 SELECT * FROM charge WHERE charge_amt = ISNULL (@value, charge_amt) 변수와 유사한 문제 발생 - 실제 입력 값이 최적화 시점에는 알 수 없음 해결 방법 - WITH RECOMPILE 옵션 및 매개 변수 사용 - 각각 저장 프로시저로 생성

48. 커서 커서만이 가능한 가? - 먼저 RDBMS의 엔진에 대한 정확한 이해 필요 - 커서와 데이터세트 중심 쿼리 비교 - 빗맞아도 한방 vs. 여러방 잽? 해결 방법 - 관계형 연산자 지원 여부 - 사용자 정의 함수 등 고려 - 커서가 최선인 경우

49. 실행 계획 구성 요소 스캔 (테이블/인덱스) 해쉬 조인 커버링 인덱스가 아닌 경우 (북마크 검색) Paralleism 정렬 평가 - 비용과 쿼리내의 비중 - 화살표의 굵기 - 실제 처리 행 vs. 예상 행

50. 테이블 스캔 모든 행이 필요한지 확인 (그렇지 않은 경우 인덱스 사용 권장) 필요한 인덱스는 다음 DMVs와 SHOWPLAN XML을 통해서 확인 - dm_db_missing_index_groups - dm_db_missing_index_details - dm_db_missing_index_columns (F) - dm_db_missing_index_group_stats - SHOWPLAN XML의 MissingIndexes 엘리먼트