Transcript 슬라이드 1 - 디비랑(dBRang) :: WBS

SQL Server
저장 프로시저 성능 최적화
김정선
필라넷 / DB 사업부 / 수석 컨설턴트
삼성SDS 멀티캠퍼스 / 전임 교수
Microsoft SQL Server MVP
강사 소개
김 정선
Email: [email protected]
Blog: visualdb.onblog.com
자격/인증 사항
MCDBA
MCITP Database Administrator
Microsoft 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 포켓 컨설턴트 관리자용(정보문화사)
목표
• 저장 프로시저의 실행 구조 및 환경 이해
• 저장 프로시저의 득과 실 이해
• 저장 프로시저의 성능 문제 요인 분석
• 재컴파일의 득과 실 이해
대상 기술범위:
• 대상
– 현업 개발자 및 관리자
– 저장 프로시저 성능에 관심 있는 개발자
– 저장 프로시저 실행 구조, 환경 및 그 특징에 관심 있는
사용자
• 기술범위
– 쿼리 최적화기
– 저장 프로시저 성능
– Database API 성능
이 주제를 이해하는 데 필요한 지식
• SQL Server T-SQL Programming
• 인덱스
• 쿼리 최적화기(Query Optimizer)
• 저장 프로시저
• 데이터베이스 프로그래밍
Level 300
목차
• 저장 프로시저 이해
• 저장 프로시저 성능 문제 분석 및 해결 방안
2장
저장 프로시저 성능 문제 분석 및
해결 방안
일편단심(一片丹心),
“하나의 쿼리는 하나의 실행 계획만을 가진다.”
2장. 목차
• 응용 프로그램 이슈
– RPC Event를 통하지 않은 호출
– 파라미터 개체 자동 생성
– 파라미터 암시적 데이터 형식 지정
•
•
•
•
•
•
•
•
암시적 소유자 지정 혹은 sp_ 프로시저 호출
재컴파일(Recompile)
SARG 위반
Parameter Sniffing
파라미터 vs. 변수
파라미터에 따라 다른 실행 계획
동적 검색 조건
유의 사항 정리
RPC Event를 통하지 않은 호출
RPC Message
(RPC Event)
SQL Language Message
(Batch Event)
SET rs = New ADODB.Recordset
...
rs.Open “EXEC Proc param1”
...
cmd.Connection = cnn;
cmd.CommandText = “dbo.Proc”;
cmd.CommandType = StoredProcedure;
NO
cmd.Parameters.Add(“@member_no”
, SqlDbType.Int
, 4).Value = param_value;
...
rdr = cmd.ExecuteReader();
NO
cmd.Connection = cnn;
cmd.CommandText = “EXEC Proc param1”;
cmd.CommandType = CommandType.Text;
rdr = cmd.ExecuteReader();
SQL 엔진이 원하는 것은 RPC Event
실행 계획 재사용 능력 및 성능 향상
파라미터 Caching/재사용 능력 제공
SQL Injection을 포함한 보안 강화
파라미터 개체 자동 생성
Parameter 개체 암시적 생성
NO
WITH cmd
.ActiveConnection = cn
.CommandText = “Proc”
.CommandType = adCmdStoredProc
혹은 이것도 생략 가능
.Parameters.Refresh
.Parameters(1).Value = 1000
...
추가 Request, I/O 등 성능 문제 유발
sp_procedure_params_rowset 실행
서버의 라운드 트립 유발
시스템 테이블 접근 유발-sysobjects 등
파라미터 암시적 데이터 형식 지정
Parameter 데이터 형식의 생략
혹은 암시적 지정
NO
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “SELECT * FROM dbo.member WHERE firstname = @fname”;
cmd.Parameters.AddWithValue(“@fname”, lstFname.SelectedItem);
...
App/DB에서 암시적 형 변환 유발
불필요한 Procedure Cache 소비
SARG 위반할 경우 성능 문제 유발
Parameterized Query에서 특히 주의
암시적 소유자 혹은 sp_ 프로시저 호출
sp_ 접두사를 사용한 프로시저
암시적 소유자로 호출한 경우
A EXEC Proc
Proc
존재?
EXEC sp_Proc
이름으로
검사
불일치
dbo.Proc
dbo.sp_Proc
Cache
Miss
Hit
OBJECT_ID
(Proc)
컴파일
존재?
단계
ID로
검사
System
Procedure?
System
Proc 찾기
Cache
Miss
불일치
A
데모
응용 프로그램 이슈




RPC Evnet를 통하지 않은 호출
파라미터 개체 자동 생성
파라미터 암시적 데이터 형식 지정
암시적 소유자 혹은 sp_ 프로시저 호출
재컴파일
• 재컴파일은 필요 조건이지만 충분 조건은 아님
• 재컴파일의 유형별 특징을 숙지하고 적절한 조치 필요
• 유형
– 자동 재컴파일
• 실행 전 자동 재컴파일 원인
• 실행 시 재컴파일 원인 - 과도한(조정 가능한) 재컴파일
– 수동 재컴파일
실행 전 자동 재컴파일
• 재컴파일 원인
–
–
–
–
–
–
–
WITH RECOMPILE 절 사용
제약조건, 기본값 또는 규칙의 추가, 제거를 포함한 참조 개체의 스키마 변경*
통계 정보 변경*
인덱스 추가 / 제거*
참조한 테이블에 대해 sp_recompile 수행
데이터베이스 복원
오래된 계획으로 프로시저 캐시에서 제거
• 주의 대상
– (* 항목) Batch 형식의 코드 실행 시 자동 재컴파일이 안될 수도 있다
•
•
•
•
DB 옵션 확인 – “통계 자동 업데이트”, “통계 자동 작성”
Batch 코드를 재 작성
수동 재컴파일 코드 추가
검증 필요
– 실행 계획 재사용 부작용을 내포한 쿼리의 경우 재컴파일에 주의
• 실행 계획 분리 (앞으로 다룰 내용)
– 개체 이름 참조 시에는 항상 2-part 형식 이상으로 표기
• EXEC dbo.up_OrdersInfo
• SELECT * FROM dbo.Orders
실행 시 자동 재컴파일 1/2
• 재컴파일 원인
– 주의. 과도한 재컴파일을 유발, 조정 가능
– 참조 테이블의 충분한 비율의 데이터 변경 (*MS 기술 자료 내용)
테이블 유형
빈 테이블 기준
빈 테이블 시의 임계 값
비어 있는 않은 경우 임계 값
일반
< 500 rows
>= 500 변경 시
500 + (선택도의 20%) 변경 시
임시
< 6 rows
>= 6 변경 시
500 + (선택도의 20%) 변경 시
테이블 변수
해당 없음
해당 없음
해당 없음
– 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 ...
Recompile 시 EventSubClass 측정 값
• Event = SP:Recompile, column = EventSubClass
번호
설명
1
컴파일이나 실행 사이의 스키마, 바인딩 또는 권한 변경
2
통계정보 변경(sysindexes.rowmodctr)
3
Recompile DNR(Deferred Name Resolution)
컴파일 시에 존재하지 않은 개체. 즉, 실행 시에 재 검사 수행
해당 프로시저를 처음 실행하는 경우에 주로 발생
4
SET 옵션 변경
5
임시 테이블 스키마, 바인딩 또는 권한 변경
6
원격 ROWSET 스키마, 바인딩 또는 권한 변경
실행 시 자동 재컴파일 2/2
• 조정 가능한 작업들
– 전체 프로시저의 재컴파일을 구문 단위로 유도
• sp_executesql / EXEC() 로 대체
• 하위 저장 프로시저로 대체
• (임시 테이블의 경우) KEEP PLAN / KEEPFIXED PLAN 옵션 사용
– SET 옵션으로 인한 재컴파일 방지
• 재컴파일에 영향을 주지 않는 제한된 옵션만 사용
• 프로시저 호출 시에 옵션이 달라지지 않도록 유의
– DDL과 DML 구문을 교차 실행하지 않고 순서대로 실행
• CREATE; CREATE; SELECT; SELECT
– 임시 테이블 사용 시 고려 사항
• 프로시저 자신의 임시 테이블만 참조
• 임시 테이블 이름을 만들기 전 또는 삭제된 후에 참조하지 않도록 유의
– 프로시저 시작 부분에서 생성
• IF...ELSE, WHILE 같은 제어 문에서 생성하지 않도록 유의
• 필요 시 테이블 변수로 대체
수동 재컴파일
• 적재적소 (適材適所)
CREATE PROC ...
WITH RECOMPILE
EXEC...
WITH RECOMPILE
sp_recompile procedure
sp_recompile table
프로시저 작성 시 지정
Ad hoc과 동일?
Cache 저장되지 않는다
최후의 보류?
실행 시 지정
Ad hoc과 동일?
Cache 저장되지 않는다
기존 Cache
재사용 안됨
그대로 존재
옵션 제거 시
재사용
재컴파일 강제 표시 설정
이후 실행 시
스스로 재컴파일
테이블 지정 시
참조하는 모든
계획을 재컴파일
긴급 활용
CPU Killer 제거 시
데모
재컴파일 이슈


자동 재컴파일
수동 재컴파일
SARG 위반
CREATE PROC dbo.up_1
@ID char(2)
AS
SELECT *
FROM dbo.table
WHERE char_col
LEFT(char_col,
2) = @ID
LIKE @ID ...
CREATE PROC dbo.up_3
@ID
char(10)
@ID
varchar(5)
AS
SELECT *
FROM dbo.table
WHERE char_col LIKE RTRIM(@ID)
@ID
<column_name> <inclusive_operator> <value> [AND…]
CREATE PROC dbo.up_2
@IDvarchar(5)
int
@ID
AS
SELECT *
FROM dbo.table
WHERE char_col = @ID
CREATE PROC dbo.up_4
@ds datetime
AS
...
SET @s = @ds + 7
SELECT * FROM dbo.table
WHERE date_col BETWEEN @ds
@ds AND
AND (@ds+7)
@s
@ds AND @ds2
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’
선택도 = 65/830
실행계획 = CL Index Scan
선택도 = 2/830
실행계획 = CL Index Scan
선택도 = 2/830
실행계획 = Index Seek
파라미터 vs. 변수
• 전제: 변수의 값은 쿼리 실행 시점에 알 수 있다
• 문제: 컴파일 시 쿼리 선택도 판단에 있어, 변수는 그 값을 알 수
없으므로 인한 최적 실행 계획 오류
@no int
AS
...
FROM dbo.Charge
WHERE
memeber_no=@no
dbo.ParamVsVar
@mem_no = 10000
AS
DECLARE @v_no int
SET @v_no = @no
FROM dbo.Charge
WHERE
memeber_no=@v_no
@v_no = ?
@v_no = @no
@v_no = 10000
선택도 = 2,752/100,000
실행계획 = Table Scan
선택도 = 11 / 100,000
실행계획 = Index Seek
파라미터에 따라 다른 실행 계획
• 전제: 파라미터에 따라 실행할 쿼리 형식이 다른 경우
• 문제: (초기/재)컴파일 시 모든 쿼리의 정확한 선택도를 판단할
수 없으므로 인한 최적 실행 계획 오류
@date = ?
@id int
,@date datetime
AS
IF @id IS NOT NULL
SELECT * FROM ...
WHERE OrderID=@id
ELSE
SELECT * FROM ...
WHERE
OrderDate<=@date
@id = 10248
@date = NULL
@id = NULL
@date = ‘19960930’
@id = NULL
@date = ‘19960930’
선택도 = 1/830
실행계획 = Index Seek
선택도 = 70/830
실행계획 = Index Seek
선택도 = 70/830
실행계획 = CL Index Scan
동적 검색 조건-예제
모든 경우의 수에 따른 성능 확인 필요
@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 '
프로시저와 다른 Scope로 처리
IF @orderid IS NOT NULL
Recompile 강제 어려움
SET @sql = @sql + 'AND o.OrderID = @xorderid '
sp_recompile 테이블
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
데모
저장 프로시저 쿼리 성능 이슈




Parameter Sniffing
Parameter vs. variable
Parameter 에 따른 다른 실행 계획
동적 검색 조건 처리
유의 사항
• 하나의 쿼리는 하나의 실행 계획만을 가지도록 구성
– 쿼리를 분리 혹은 프로시저를 분리
•
•
•
•
•
•
SET NOCOUNT ON 세션 옵션 사용
OUTPUT 파라미터, RETURN 활용
sp_executesql vs. EXEC 명확히 구분해서 사용
sp_ 접두사는 금기
소유자명 명기 (내부 쿼리 포함), ex. dbo.UP
프로시저 내에서 파라미터 값의 변형은 금기
– 조건 식에서 함수 호출에 주의
– 조건 식에서 파라미터에 함수 적용 시에도 주의
• 불필요한 / 과도한 재컴파일 유의
– 대형 프로시저의 경우 전체 프로시저의 재컴파일을 방지
– 세션 옵션 사용에 유의
• 제한된 세션 옵션만 사용 (다음 장 참조)
• 특히 ANSI_NULLS, QUOTED_IDENTIFIER는 소용 없음
– DDL / DML 구문은 교차하지 않도록 처리
– 임시테이블 / 테이블 변수 사용에 주의
• 임시 저장 프로시저 방지
SQL Server 2005 New Features
재컴파일(Recompile)
• 쿼리 최적화기의 변화 및 기능 향상
• 구문-단위 재컴파일
• EXEC (...) 쿼리에 대한 Cache 처리 지원
• 쿼리 플랜용 비용 계산식의 변환
• CLR에서 호출된 배치에 대한 재사용 지원
• 칼럼 단위 통계 처리 지원
• 향상된 암시적 형 변환 처리 기능
• 추가 힌트 지원, ex. OPTION (RECOMPILE)
• 향상된 자동 파라미터화 기능
SQL Server 2005 New Features
통계정보(Statistics)
•
•
•
•
•
•
•
•
•
•
•
•
문자열 요약 통계 – LIKE 가 빨라졌다
비동기적 통계 업데이트
계산된 열에 통계 지원 향상
대용량 개체 지원
향상된 통계 로딩 구조
최대 통계 수량 증가 – 2,000/테이블, 32 칼럼
DBCC SHOW_STATITSTICS 출력 향상
내부 테이블에 대한 통계 지원 – xml, fulltext index, etc
풀 스캔 시의 병렬 수집
생략된 통계 정보에 대한 자동 생성 및 재컴파일 지원
빈 테이블에 대한 통계 업데이트 및 재컴파일 기능 향상
sp_updatestats 기능 향상
세션 요약
• 저장 프로시저를 호출하는 데이터베이스용
어플리케이션의 성능 문제를 이해한다.
• 저장 프로시저와 일반적인 Ad hoc 쿼리와 어떻게
다른지를 이해한다.
• 재컴파일로 인한 성능 문제를 이해하고 다양한 해결
방안들을 알 수 있다.
• 저장 프로시저 사용시 발생 가능한 성능 문제의
유형들을 이해하고 그 해결 방안들을 알 수 있다.
• SQL Server 2005 향상된 기능을 활용하자.
추가 자료들
• Microsoft SQL Server Query Processor Internals and Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/enus/dnsql7/html/sqlquerproc.asp
• (기술자료) 저장 프로시저 재컴파일 문제 해결
http://support.microsoft.com/kb/243586/ko
• Microsoft SQL Server 성능 튜닝 Technical Reference
Whalen, Garcial, Deluca, Thompson | 정보문화사
• Inside Microsoft SQL Server 2000
Kalen Delaney | 정보문화사
• SQL Server 최적화 튜닝
정원혁, iti 교육센타
• Programming a Microsoft SQL Server 2000 Database
2073, MOC 교육 과정
참고자료 :
IT 전문가를 위한 Microsoft 인증
여러분의 Microsoft 기술 능력 평가
Microsoft Skills Assessment 무엇인가?
• 현재 제품 및 기술 솔루션에 대한 능력 평가
• Windows Server 2003, Exchange Server 2003, Windows
Storage Server 2003, Visual Studio .NET, Office 2003
• 무료, 온라인, 누구나 사용 가능
• 평가결과를 기초로 Microsoft 교육 프로그램을 제안합니다.
• 평가항목과 최고점수 표시
• 방문하세요! www.microsoft.com/assessment
Microsoft Certified Systems
Administrator(MCSA)가 되자!
• MCSA 무엇인가?
– Microsoft Windows Server 기반의 시스템, 네트워크
유지보수와 관리를 하는 IT 전문가를 위한 인증제도
• 어떻게 MCSA(Windows Server 2003) 합격?
– 3개 코어 시험 통과
– 1개 선택 과목
• 자세한 정보는 아래 URL를 참고하세요?
www.microsoft.com/mcsa
Microsoft Certified Systems Engineer
(MCSE)가 되자!
• MCSE 무엇인가?
– Microsoft Windows Server System 기반의 비즈니스
솔루션, 인프라스트럭처의 설계, 도입계획, 도입방법, IT
운영자의 요구분석 능력을 인증하는 제도
• 어떻게 MCSE(Microsoft Windows 2003) 합격?
– 6개 코어 시험 통과
– 1새 선택 시험 통과
• 자세한 정보는 아래 URL를 참고하세요?
www.microsoft.com/mcse
Microsoft Certified Desktop Support
Technician(MCDST)
• What is the MCDST certification?
– Microsoft Windows 오퍼레이팅 시스템에서 실행되는 데스크톱 환경의
문제해결 및 전문가의 기술지원 능력을 인증하는 제도
• 어떻게 MCDST(Microsoft Windows XP) 합격?
– 2개 코어 시험 통과
• 오퍼레이팅 시스템
• 데스크톱 애플리케이션 지원
• 자세한 정보는 아래 URL를 참고하세요?
www.microsoft.com/mcse
Specialization 인증을 도전하세요.
• MCSA/MCSE specializations?
– IT 전문가를 위한 메시징, 보안 전문분야의 인증제도
• 현재 인증 가능한 전문?
– MCSA: Security
– MCSE: Security
–
–
MCSA: Messaging
MCSE: Messaging
• 자세한 정보는 아래 URL를 참고하세요?
www.microsoft.com/mcsa or www.microsoft.com/mcse
TechNet에 가입하세요.
최신 기술 뉴스를 받고 싶으세요?
평가기간 없는 소프트웨어!: Technet Plus 가입자는
평가 목적으로 Microsoft 정품제품을 다양하게
시험을 할 수 있다.
무료 기술지원: 가입자는 2개의 무료 기술지원을
받을 수 있으며, 중요한 문제해결을 위해 시간을
절약할 수 있다.
최신 TechNet 정보를 오프라인에서 사용: TechNet
사이트의 Microsoft 평가, 설치, 솔루션의 정보를 CD
또는 DVD로 받을 수 있다.
www.microsoft.com/technet/subscriptions
어디서 정보를 얻을 수 있나요?
• 웹 캐스트 또는 온라인 채팅
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