Transcript 실행계획최적화제안(1).ppt

SQL Server 2005
실행계획 이해 및
최적화 제안
이종인
[email protected]
이종인
•
•
•
•
•
[email protected]
다우교육원 SQL Server 전임강사
온디멘드 수석 컨설턴트
PASS Korea, SQL Specialist 창립 멤버
MCT, MCDBA, MCITP, MSCE+I,HPCP-Master ASE,
OCP
목차
• Session 1: 실행 계획을 살펴보는 방법
– 예상 실행 계획 / 실제 실행 계획
– 그래픽 실행 계획 / 텍스트 실행 계획 / XML 실행 계획
– SQL Server 2005 새 기능
• Session 2: 실행 계획의 연산자
– SCAN, SEEK, JOIN , UNION 등 실행 계획 연산자
– 실행 계획과 관련된 동적 관리 개체
• Session 3: 최적화를 위한 제안 사항
– 실행 계획 연산자 가운데 가장 비용을 많이 소모하는 연산자는?
– 어디에 어떻게 인덱스를 만들어야 하나?
– 커서를 사용해야 하나?
Session 1: 실행 계획을 살펴보는 방법
•
•
•
•
예상 실행 계획 vs. 실제 실행 계획
그래픽 실행 계획 vs. 텍스트 실행 계획
XML 실행 계획
SQL Server 2005 새 기능
예상 실행 계획 vs. 실제 실행 계획
• 모든 실행 계획은 데이터 분포 예측을 기반으로 함
• 예상 실행 계획은 실제로 쿼리의 실행 없이 생성됨
– 임시 테이블은 생성되지 않음
– 데이터나 스키마는 쿼리 수행 전 변경 가능
• 실제 실행 계획은 쿼리 수행 중에 생성됨
– 임시 테이블이 생성됨
– 추가 정보 반환
텍스트 기반 실행 계획
• 예상 실행 계획
– 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 }
그래픽 실행 계획
• 예상 실행 계획 표시
– 선택한 모든 쿼리의 실행 계획 표시
• 실제 실행 계획 포함
– 토클 아이콘
– 실행 결과와 함께 추가 탭 제공
• SSMS의 속성창
– 보다 상세한 정보 제공
– 실제 실행 계획에서는 추가적인 상세 정보 제공
텍스트 실행 계획 예제
• 텍스트 형태 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개행적용됨)
그래픽 실행 계획 예제
그래픽 vs. 텍스트 실행 ?
• 그래픽
–
–
–
–
보기가 편리함
전체적인 개요
상대적인 비용 표시
각 연산자 상세 표시
• 텍스트
–
–
–
–
복잡한 쿼리에 적합
Cntl+F로 검색 지원
저장이 용이함
예상과 실제 행수 비교
가능
XML 실행 계획
• 다음을 통해서 가능
–
–
–
–
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
다양한 DMV
추적 이벤트
SHOWPLAN_XML
• 배치 당 하나의 XML 문서 생성
– <Query Plan> :
• CachePlanSize, CompileTime, CompileCPU, CompileMemory
– <RelOp> : 연산자
• 논리적/물리적 연산자 이름, 예상 비용
• 출력 컬럼 리스트, 참조 개체 등
STATISTICS XML
• 추가 정보를 포함하는 실제 실행 계획
– <Query Plan> 부분에 DOP 속성 제공
– 각 <RelOp>에 실행시 정보 제공
•
•
•
•
처리 행 수
실행 횟수
검색 인덱스
조인 순서 등
– 실행 시 정보는 각 스레드 별 제공됨
– 추가 메모리 요구 연산자 정보 제공
• Sort
• Hash Join
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
– 필요한 인덱스 정보 제공
실행 계획 보기 비교
쿼리
실행
텍스트
실행 계획
예상 행
수 표시
실제 행 수
표시
SHOWPLAN_TEXT
X
O
X
X
SHOWPLAN_ALL
X
O
O
X
STATISTICS PROFILE
O
O
O
O
예상 실행 계획 표시
X
X
툴팁에서
X
실제 실행 계획 포함
O
X
툴팁에서
툴팁에서
SHOWPLAN_XML
X
O
O
X
STATISTICS XML
O
O
O
O
옵션
텍스트
그래픽
XML
Session 1: 데모
• 실행 계획 확인
Session 2: 실행 계획의 연산자
• SCAN, SEEK, JOIN , UNION 등 실행 계획 연산자
• 실행 계획과 관련된 동적 관리 개체
쿼리 실행 계획
• 실행 계획은 연산자 트리로 구성
• 각 실행 계획 연산자
- 입력 행 검색
- 출력 행 생성
- 하나 이상의 자식 노드를 가질 수 있음
• 각 연산자는 다음 기능 수행
- Open
- GetRow
- Close
• 그래픽 실행 계획은 오른쪽에서 왼쪽, 위에서 아래로
연산자 속성
• 메모리 소비
• blocking vs non-blocking
메모리 소비
• 모든 연산자는 메모리 소비
- 상태 정보 저장, 연산 수행 등
• 메모리 소비 연산자(MCI) 가운데 추가 메모리 소모
- 행 저장
- 정렬, 해쉬조인, 해쉬 집계
• 성능 영향
- 메모리 할당 대기
- 메모리가 없는 경우 tempdb 사용
blocking vs. non-blocking
• 2가지 종류의 연산자
- GetRow 메소드를 사용하여 동시에 입력행으로 부터 출력행을
반환 : Count-Scalar
- Open 메소드를 사용하여 모든 행의 입력 이후 출력행을 반환
(Blocking or stop-and-go) : Sort 연산자…
기본 연산자
•
•
•
•
•
•
•
•
Scan
Seek
Bookmark lookup
Join
Aggregation
Union
Parallelism
Update
SCAN
• Scan 은 전체 인덱스 또는 테이블 반환
• 추가적인 필터와 함께 사용 가능
• Argument 열에 ORDERD 연산자가 있는
경우는 인덱스 키 순서대로 정렬하고
ORDERD가 없는 경우는 출력을 정렬하지
않고 인덱스를 최적의 방법으로 검색(정말로?)
SEEK
• Seek는 인덱스 검색 기능을 사용해서 특정
범위의 행을 검색
• 기반 테이블의 행을 반환하기 위해서는
Bookmark Lookup 연산 수행
(인덱스가 쿼리를 커버하는 경우는 Bookmark
Lookup이 수행되지 않음)
Bookmark Lookup
• Bookmark Lookup 연산자는 책갈피(행 ID 또는
클러스터링 키)를 사용하여 테이블이나 클러스터형
인덱스에서 해당 행을 조회
• SQL Server 2005 에서는 Bookmark Lookup이
사용되지 않고 Clustered Index Seek 및 RID
Lookup이 책갈피 조회 기능을 제공
(키 조회 or RID Lookup 후 조인 연산?)
조인(JOIN)
• Loop Join
• Merge Join
• Hash Join
Loop Join
• 기본 동작
- 왼쪽 테이블에서 한 행씩 추출
- 오른쪽 테이블에서 조인 연결 고리에 부합하는 행 추출
• 성능 고려
- 왼쪽 테이블은 행수와 테이블 크기가 작고 오른쪽 테이블은 조인의 연결
고리에 유용한 인덱스가 있는 경우에 적합
- 왼쪽 테이블에 중복 값이 있는 경우는 Lazy Spool또는 Index Spool 사용
• 연결 고리에 동등 비교(=)가 없는 경우 지원
Merge Join
• 조인 연결고리가 정렬되어있어야 함
• 기본 동작
-
양쪽 테이블 모두 조인의 연결 고리로 정렬 확인
왼쪽 테이블에서 한 행을 추출하고 오른쪽 테이블에서 일치 여부 확인
일치하는 경우 행 반환
일치하지 않는 경우 오른쪽 테이블에서 다음 행으로 이동
• 일대다, 다대다 조인 지원
• Loop Join으로 처리하기에는 큰 테이블 조인 시 사용
Hash Join
• 기본 동작
- 왼쪽 테이블에서 메모리상의 해쉬 테이블 생성
- 오른쪽 테이블에서 해쉬 함수를 사용해서 해쉬 테이블과 일치
여부 확인
- 메모리가 부족한 경우 디스크 사용
• 성능 고려
- 작은 테이블을 기준 테이블(build input)으로 사용
- 추가 인덱스 생성 등을 통해서 Merge Join 으로 해결 가능한지
확인
Join 비교
concourency
Loop
Merge
Hash
동시 다중 사용자
지원
동시 다중
사용자 지원
소수 사용자에
최적
Stop & go
유일하지 않거나 Build input
중복이 맣은
경우
동등 비교 필요
예
예
추가 메모리
소비
정렬 시
예
Tempdb 사용
다대다 조인
메모리를 모두
사용한 경우
Subquery
• 조인으로 변경 가능
• EXISTS 서브 쿼리는 Semi join 으로 변경
- not exists는 anti-semi join
Aggregation
• 2 가지 연산자
- Stream Aggregation
- Hash Aggregation
• GROUP BY, DISTINCT 키워드 사용시 등…
Stream Aggregation
• 입력값이 반드시 그룹핑 키로 정렬되어야 함
• 각 그룹별 결과를 하나씩 반환
• 유용한 인덱스가 있는 경우 Hash Aggregation 보다
효과적
Hash Aggregation
• 입력값이 정렬될 필요 없음
• 모든 처리가 완료된 뒤 결과 세트 반환
• 추가적인 메모리 소모  tempdb 사용 가능
UNION
• UNION vs. UNION ALL
- UNION ALL은 중복을 체크하지 않음
• 세 가지 알고리즘 :
- MERGE UNION
- HASH UNION
- CONCAT UNION (UNION ALL)
Parallelism
• 대량 배치 작업에 유용
• 쿼리 최적화기가 병렬 실행 계획 생성
• DOP는 런타임 시 결정됨
- set statistics profile on
- set statistics xml on
- 실제 그래픽 실행 계획…
쿼리 실행 관련 동적관리 개체
•
•
•
•
•
•
•
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
Session 3: 최적화를 위한 제안 사항
• 일반적인 성능 최적화 팁
• 쿼리 구성 요소
• 실행 계획 구성 요소
일반 적인 성능 최적화 팁
•
•
•
•
각 실행 계획의 상대적인 비용 분석
어떤 연산자가 가장 많은 행을 처리하는 가?
실행 계획에 메모리 소비?
어떤 인덱스를 만들어야 하나?
- 테이블 스캔 또는 인덱스 스캔이 인덱스 검색으로 대체 가능한지?
- 정렬이 인덱스 스캔으로 대체 가능한지?
- 해쉬 조인이 루프 조인 또는 머지 조인으로 대체 가능한지?
처리되는 데이터 량 고려
• 실행 화살표의 굵기 검토
• 각 실행 계획 연산자의 예상 처리 행 수 검토
• 실제 처리되는 행 검토
- set statistics profile on / set statistics xml on
- trace event of Profiler
쿼리 구성 요소
•
•
•
•
•
•
SARG
암시적인 데이터 형식 변환
변수 처리
조건 로직 처리
행 필터
커서
SARG (검색 제한 조건)
• SARG 정의
• 효율적인 인덱스 사용 여부 확인
• Non-SARG를 SARG로 자동 변환
- au_lname like ‘J%’
• Non-SARG를 SARG로 수동 변환
- 컬럼 분할 등
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 ;
• 인라인 테이블 반환 함수로 변환
- 연산 처리가 복잡한 경우
암시적인 데이터 형식 변환
• 상수와 변수의 데이터 형식 비교
- 7.0과 2000/2005의 처리 방식이 다름
- 7.0 : 상수를 컬럼의 데이터 형식으로 변환
- 2000/2005 : 우선 순위가 낮은 형식을 보다 높은 형식으로
변환
- 데이터 형식 변환은 인덱스 사용이 불가한 경우 유발 가능
• 데이터 형식 우선 순위 참조
- 온라인 설명서
• 해결책
- DB 호환성을 70
- 상수의 데이터 형식을 명시적으로 변환
- 변수 선언시 적합한 데이터 형식 선언
변수
• 변수에 할당되는 상수값은 최적화 시 참조되지 않음?
- 밀도 정보는 등호 연산을 위해서 사용 가능
- 히스토그램은 사용 불가
• 해결 방법
- 저장 프로시저 사용 (RECOMPILE 옵션 사용)
- SQL Server 2005 힌트 사용
(OPTIMIZE FOR 옵션 등)
조건 로직 처리
• 실행 계획을 각 조건 별 모두 생성
IF @direction =‘1’
SELECT * FROM charge
WHERE charge_amt<@value
ELSE
SELECT * FROM charge
WHERE charge_amt>=@value
• 일부 조건은 실행 시 의미 없음
- 실제 입력 값에 의해서 결정
- 전체를 최적화하기 위한 부하 가중
• 해결 방법
- WITH RECOMPILE 옵션의 저장 프로시저
- 각각 저장 프로시저로 생성
다양한 조건에 부합하는 쿼리
• 요구 별 쿼리 생성
- 다양한 목적의 쿼리 지양
- WHERE 절에 CASE 문 사용 쿼리
- WEHRE 절에 ISNULL / COALESCE 사용 쿼리
SELECT * FROM charge
WHERE charge_amt = ISNULL (@value, charge_amt)
• 변수와 유사한 문제 발생
- 실제 입력 값이 최적화 시점에는 알 수 없음
• 해결 방법
- WITH RECOMPILE 옵션 및 매개 변수 사용
- 각각 저장 프로시저로 생성
커서
• 커서만이 가능한 가?
- 먼저 RDBMS의 엔진에 대한 정확한 이해 필요
- 커서와 데이터세트 중심 쿼리 비교
- 빗맞아도 한방 vs. 여러방 잽?
• 해결 방법
- 관계형 연산자 지원 여부
- 사용자 정의 함수 등 고려
- 커서가 최선인 경우
실행 계획 구성 요소
•
•
•
•
•
•
스캔 (테이블/인덱스)
해쉬 조인
커버링 인덱스가 아닌 경우 (북마크 검색)
Paralleism
정렬
평가
- 비용과 쿼리내의 비중
- 화살표의 굵기
- 실제 처리 행 vs. 예상 행
테이블 스캔
• 모든 행이 필요한지 확인
(그렇지 않은 경우 인덱스 사용 권장)
• 필요한 인덱스는 다음 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 엘리먼트
누락된 인덱스
• 누락된 인덱스는 필터 평가에만 점검함
• 누락된 인덱스 정보 유지는 추가 부하 발생
- SQL Server 시작 옵션 -x
• 제한 (BOL)
- 인덱스 구성을 미세 조정하지 않음
- 500개 이상 누락된 인덱스 그룹에 대한 통계 수집 제한
- 인덱스의 열 순서를 지정하지 않음
- 같지 않음 조건자만 포함하는 경우 덜 정확한 비용 정보 반환
- 일부 쿼리에 대해서 INCLUDE 열만 보고하므로 인덱스 키 열은 수동으로
선택해야 함
- 인덱스가 누락되었을 수 있는 열에 대한 원시 정보만 반환
인덱스 스캔
• 클러스터형 인덱스 스캔은 테이블 스캔과 동일한
개념(?)
• 인덱스 스캔은 인덱스 키열의 순서대로 정렬됨
• 복합 인덱스의 컬럼 순서 고려
 인덱스 변형이 안되면 쿼리 변경(UNION)
해쉬 조인
• Adhoc 쿼리에 사용
• 추가적인 리소스를 필요로 할 수 있음
- 추가적인 메모리 소모
- 메모리 할당을 위한 대기 시간 발생
• Statistics IO 결과가 잘못 표시될 수 있음
• 효율적인 인덱스 전략이 가장 최적의 해결책
커버되지 않은 인덱스
• 북마크 검색 비용 발생
• SQL Server 2005의 INCLUDE 열 사용 고려
• 커버된 인덱스는 미니 클러스터형 인덱스의 개념
병렬 처리
• 하나의 작업이 다중 CPU 사용 가능
• 쿼리 최적화기가 병렬 처리 실행 계획 생성 결정
- 병렬 처리 스레드는 실행시 결정됨
• 대량 처리 시 성능 개선
 대량 처리가 아니라면 오히려 부담이 될 수도…
정렬
• SQL Server는 효율적인 정렬 알고리즘 사용
• 정렬이 필요한 경우 다음 사항 고려
- 클라이언트에서 정렬하는 비용
- 적절한 인덱스 생성
- FAST (N) 또는 FASTFIRSTROW 힌트 고려
Session 3: 데모
• 최적화 제안
Q&A.