Transcript 저장_프로시저의_이해.ppt

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
목차
• 저장 프로시저 이해
• 저장 프로시저 성능 문제 분석 및 해결 방안
1장
저장 프로시저 이해
각인(刻印),
“새끼 오리들은 태어나는 순간(결정적 시기)에 처음 본
움직이는 대상을 마치 어미처럼 평생을 따른다.”
1장. 목차
•
•
•
•
•
•
•
•
•
•
응용 프로그램 쿼리 유형
작업 부하 유형
컴파일 비교 - VB vs. Ad hoc Query vs. 저장 프로시저
프로시저 캐시
저장 프로시저 처리 단계
저장 프로시저 특징에 대한 고민
실행 계획 재사용 부작용
Recompile 이슈
Adhoc vs. Prepared
성능 추적 및 모니터링
응용 프로그램 쿼리 유형
Presentation
3
2
Middle Tier
저장 프로시저
dbo.up_OrdersInfo
Parameterized
SELECT ...
FROM dbo.Orders
WHERE OrderID = @OrderID
1
SQL Server
Adhoc
SELECT ...
FROM dbo.Orders
WHERE OrderID = 10248
*RPC 이벤트
*Batch 이벤트
영업 Database
저장 프로시저
(dbo.up_OrdersInfo)
작업 부하 유형
작업 유형
상세
캐시유형
특징
Adhoc
상시 컴파일
Auto
Parameterization*
Prepared
단순 쿼리에 해당의존하지 말 것
저장 프로시저*
Proc
적극적 활용-부작용 문제
해결
sp_executesql*
Prepared
SP와 유사-같은 문제 포함
Prepare/Execute
Prepared
SP와 유사
- 과거의 유산 물, 여전히
사용, JDBC 포함, 임시
프로시저도 관련
기타
...
...
순수 Ad hoc Ad hoc
잘 정의된
컴파일 비교, VB vs. Ad hoc Query
VB
동일 쿼리 기준 = 정확한 구문
Ad hoc
작성 단계
코드작성
실행 단계
파일 저장
컴파일 단계
동일쿼리가
Cache에
아니요
존재?
최적화
컴파일 단계
최적화
실행 계획 결정
컴파일
컴파일
이진코드 생성
(옵션) Cache 저장
.exe 실행
실행 단계
.exe 재실행 / 재컴파일
예
쿼리 실행
컴파일 비교, VB vs. 저장 프로시저
VB
동일 쿼리 기준 = 프로시저 이름
저장 프로시저
작성 단계
코드작성
작성 단계
파일 저장
코드작성
DB 저장
실행 단계
동일Proc가
Cache에 아니요
존재?
컴파일 단계
최적화
컴파일
컴파일 단계
최적화
실행 계획 결정
이진코드 생성
.exe 실행
실행 단계
.exe 재실행 / 재컴파일
컴파일
예
Cache Plan
재사용
아니요
가능?
예
Cache 저장
Recompile
프로시저 실행
프로시저 캐시
• master.dbo.syscacheobjects
중요 칼럼
설명
cacheobjtype 캐시 내의 개체 유형(Compiled Plan, Executable Plan, Parse Tree,
Extended Proc, etc)
objtype
개체 유형 (Proc, Prepared, Adhoc, ReplProc, Trigger, View, etc)
uid
계획을 만든 사용자 ID ( -2는 비 암시적 소유자, 사용자간 공유
가능)
usecounts
사용된 횟수
pageused
사용된 메모리 페이지 수
setopts
컴파일된 계획에 영향을 미치는 SET 옵션 설정 값, 캐시 키의
일부
sqlbytes
전송된 배치 또는 프로시저 이름의 길이
sql
전송된 배치의 처음 128자 또는 프로시저 이름
데모
응용 프로그램 쿼리 유형별 Caching



응용 프로그램 쿼리 유형
프로시저 캐시 살펴보기
작업 부하 유형별 Caching 처리
저장 프로시저 처리 과정-전체
생성
Parsing
Resolution
[sysobjects]: Name, type, etc.
[syscomments]: Text of object
[syscolumns]: Parameter list
[sysdepends]: Object dependencies
Resolution*
실행
(처음 or
재컴파일)
Optimization
Compilation
Compiled plan placed in
unified cache
Resolution
• 저장 프로시저를 생성하는 시점의 참조하는 모든 개체를
해석하는 단계(존재 여부 등)
• 개체가 존재하지 않아도 생성 작업은 성공
• 호출되는 프로시저가 존재하지 않으면 오류 발생
• “존재하지 않는 개체 ‘?'에 의존하고 있으므로 현재 저장
프로시저의 sysdepends에 행을 추가할 수 없습니다. 저장
프로시저는 만들어집니다.” – SQL Server 2000 메시지
• 장점: 순환 참조 허용!
• 참조되는 테이블, 뷰, 함수 등이 존재하지 않는 경우엔 오류
발생 안 함(6.5 호환 모드 제외)
• 개체를 삭제하기 전에 sp_depends로 종속 관계 확인
Compilation/Optimization
• 제공된 파라미터에 기반
• 이후 실행에서 해당 계획을 재사용
• 모든 코드에 대해 최적화 수행
– (중요)코드 모듈화가 필요
• 잘못 작성된 코드는 과도한 잠금/차단을 유발 가능
• 과도한 재컴파일 또한 성능 저하를 유발 가능
Execution/Recompilation
• 계획이 캐시에 없으면 새로운 계획을 컴파일하고 캐시에
적재해서 실행
• 캐시에 플랜이 부적합하거나 캐시에서 제거되는 상황
•
•
•
•
서버 재 시작
낮은 재사용으로 인한 퇴출
DBCC FREEPROCCACHE 등의 명령문 실행 (필요한 경우도 있다)
그 외에도 다양한 상황들이 존재 – 관리자 유의 사항
• 테이블 데이터 - 변경
• AutoStats 알고리즘, Q195565 INF: How SQL Server 7.0 and SQL
Server 2000 Autostats Work 참조
저장 프로시저 특징에 대한 고민
• T-SQL의 거의 모든 구문을 사용한 절차적 프로그래밍 가능
– VB? or ASP?...NO, T-SQL
• 미리 작성해서 DB에 저장한 쿼리 개체
– DB 용량이 커진다? 그럼, 다른 건 안 커지나?
• 초기 실행 시 컴파일을 수행하고 실행 계획 산출
– 초기라는 시점의 의미는? Cache에 없으면 초기?
• 실행 계획(직렬/병렬)을 Procedure Cache에 저장
– Procedure Cache가 커진다? 안 쓰면 작아지나?
• 모든 사용자가 저장된 실행 계획을 공유하고 재사용 가능
– 재사용해서는 안 된다면? 공유하고 싶지 않다면?
• 자동/수동으로 재컴파일 요청 시 재컴파일 수행
– 재컴파일은 컴파일과 다르다!, 왜 재컴파일을 하는가? 왜 해야하는가?
• 자동/수동으로 Procedure Cache 제거 시 컴파일 수행
– 실제 서버에서 하지 말라?...청개구리, 필요할 때도 있을까?
실행 계획 재사용 부작용
• 각인(刻印)의 부작용
– 새끼 오리가 태어나서 처음 강아지를 본다면?
• 실행 계획 재사용 부작용
초기 컴파일
선택도 = 100/1,000
실행계획 = Table Scan
실행
선택도 = 1/1,000
실행계획 = Table Scan
Recompile 하면?
선택도 = 1/1,000
실행계획 = Index Seek
• 정리
– 필요 시 쿼리가 서로 다른 실행 계획을 가져야 하는 경우
– 쿼리 실행 계획 자체가 분리될 수 있어야 한다.
데모
실행 계획 재사용


실행 계획 재사용 유형
재사용 부작용
Recompilation 이슈
• 재컴파일 = 최적화
• 최적화 = 재컴파일
•
•
•
•
•
언제 재컴파일이 일어나길 원하나요?
재컴파일에는 어떤 옵션들이 있는지 알고 있나요?
재컴파일이 필요하다고 판단하는 기준은 무엇인가요?
프로시저 전체가 재컴파일이 되길 원하나요? 아니면 일부만?
테스트하고 검증할 수 있나요?
언제 Recompile이 필요한가?
• Cache 실행 계획이 비 효율적이라고 판단되는 경우
• 새로 실행 계획을 만들도록 요구 받는 경우
• 프로시저 내 쿼리가 항상 동일한 실행 계획을 가지지 않는 경우
– 파라미터에 따라 실행 계획이 달라지는 경우
– 데이터 변경이 이루어지는 경우 (예, 반복적인 배치 작업)
• 재컴파일 비용이 잘못된 실행 계획으로 인한 쿼리 실행
비용보다 확연히 적은 경우!
• 왜?
– 더 좋은 실행 계획을 도출할 수 있다
– 잘못된 실행 계획으로 서버의 장애가 발생할 수 있다
– 재사용을 위해서 계획을 저장하는 것이 항상 좋은 것은 아니다.
동적(Ad hoc) vs. 파라미터(Prepared) 쿼리
• 동적으로 구성된 쿼리
– 여러 가지 표현들: Ad hoc, Hard-coded, Dynamic, Embedded
– 종류
• 순수한 Ad hoc 쿼리
• 자동 파라미터화 쿼리로 자동 처리도 가능 (일명 Safe Auto-params)
– 컴파일 비용 반복 소비
– 순수 Ad hoc으로 Caching될 경우 과도한 Cache 소비 원인
• 이유: 순수 Query 그 자체로 재사용 가능하므로...정말?
• 파라미터화 쿼리
– 여러 가지 표현들: Parameterized, Prepared, Static
– 종류
• sp_executesql 같은 명령 직접 사용
• Database API (ADO, ADO.NET) 를 통해서 역시 같은 명령들이 간접 호출
– Ad hoc 과 Procedure 의 중간 형태
• 구문 별 Cache 처리 및 재사용 기능 제공
• 구문이나 파라미터 형식, 길이 등이 다를 경우에도 개별 Cache 처리
• 주의. 프로시저와 같은 재사용 문제 내포
SP vs. Prepared Sql Plan vs. EXEC
•
•
•
자동 파라미터화
저장 프로시저 vs. sp_executesql
sp_executesql vs. EXEC (프로시저 내에서)
비교 항목
sp_executesql
EXEC
소유권 체인 지원
N
N
입/출력 파라미터 지원
Y
N
Prepared 에 해당
Ad hoc 에 해당
Y
Y
Y (계획 재사용 문제 여전)
Y
배치 취소 시 SP 취소 여부
Y
Y
변수나 파라미터 참조
Y
N
USE 문의 영향
N
N
배치 내의 임시 테이블
N
N
RETURN 값 사용
Y
N
Cache 재사용 능력
별도 배치(범위) 실행
실행 계획 분리
데모
재사용 비교

Proc Plan vs. Prepared Plan
성능 추적 및 모니터링
• master.dbo.syscacheobjects
– 각 DB별 Cache Plan의 유형, 소비되는 크기, 재사용 률 등의 진단 분석
• 프로필러 혹은 SQL Trace
재컴파일 SP:Recompile,
원인 추적 SP:Starting,
SP:StmtStarting(혹은
SP:StmtCompleted),SP:Completed
EventClass, EventSubClass, Object ID,
Object Name, TextData
• 성능 카운터
SQLServer:Buffer Manager
SQLServer:Cache Manager
SQLServer:Memory Manager
SQLServer:SQL Statistics
• T-SQL 명령
Procedure cache pages
Cache Hit Ratio, Cache Object Counts, Cache Pages,
Cache Use Counts/sec
SQL Cache Memory
SQL Compilations/sec, SQL Re-Compilations/sec
– DBCC CACHESTATS, DBCC PROCCACHE, DBCC MEMORYSTATUS
세션 요약
• 저장 프로시저는 처음 실행 시(혹은 재컴파일시)에
실행 계획을 결정한다.
• 한 번 결정된 실행 계획은 이후 실행에서 계속
재사용되어진다.
• 실행 계획 재사용으로 인한 부작용은 심각한 성능
문제를 유발할 수 있다.
• 재컴파일에 필요성과 함께 그 문제점을 이해한다.
• 저장 프로시저 실행 구조의 대한 이해를 통해서
문제점을 해결할 수 있다.
추가 자료들
• 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