Transcript 슬라이드 1

물리 데이터 모델의
SQL Server 적용
목차
DBMS 선정과 물리 모델의 반영
MS SQL Server DBMS
Architecture
언제 Clustered Index를
사용할 것인가?
인덱스와 NULL
Database 구조
Index 생성 적용 단계
Datafile내의 Extent 할당 관리
Rule
Index Design Chart
Page의 구조
Data Loading 시의 주의 사항
Table 정의
SQL Server Data Types 변환
Index Scan vs. Index Access
Clustered Index
Application
Stored Procedure 장점
SQL문의 실행
Execution Plan Caching과
재사용
Trace 분석 방법
Read80Trace
1
DBMS 선정과 물리 모델의 반영
DBMS에 Database 구축 시 고려할 사항
성능 (Performance)
Data 정합성
반정규화(De-Normalization)가 성능에 유리한가 ?
지나친 반정화는 불필요한 트랜잭션 유발
데이터의 중복은 데이터 불일치(In-Consistency) 유발
2
MS SQL Server DBMS Architecture
Memory Pool
System Data Structure
Buffer Pool(Cache)
Procedure
Cache
Connection
Context
Log
Log
Caches
Caches
Memory
Manager
Lock
Manager
Flush Queue
User Mode
Scheduler
Memory
Grant Queue
Query
Executor
Optimizer
Expression
Manager
Normalizer
Worker
Thread Pool
Completion Queue
Net-Library
Command
Parser
SQL
Manager
InputInput
Buffer
Buffer
(Read(Read
Buffer)
Buffer)
DDL
Manager
DML
Manager
Relational Engine
Buffer
Manager
Log
Writer
CheckPoint
Access Methods M
- Row operation M
- Index operation M
Utility
Manager
Stored
Procedure
Manager
Page
Manager
Text
Manager
VLF
…
*.LDF
Output Buffer
(Write Buffer)
Open Database
Service(ODS)
Client
Transaction
Manager
Log
Manager
LazyWriter
Memory Grant
Scheduler
Free Queue
Data File 1
*.MDFData File 2
*.NDF
Data File 3
*.NDFData File n
*.NDF
사용자 환경설정에
대한 지극히
기본적인 사항에
대한 설명은 배제
3
Database 구조
하나의 database는 두개 이상의 물리적인 file로 구성
Primary File, [Secondary,] Transaction Log File
4개의 system database를 갖는다.
Master, model, tempdb, msdb
하나 이상의 사용자 database
4
논리적 Database 요소
Primary File
Secondary File
Transaction Log File
File Group Design Rule
File이나 File Group은 하나의
Database에 속함
File은 한 File Group에만 속함
Data와 Transaction Log File은
같은 File 또는 File Group에 속할 수 없음
Transaction Log File은 File Group의 일부가 될 수 없음
Database의 파일이 되기 위해서는 ‘0’으로 Format되는 과정이 필요하므로
점차적인 증가보다는 미리 최고크기로 할당 받고 생성되는 것을 권장
5
Database 생성
적절한 Database의 생성은 ?
몇 개의 database를 유지할 것인가 ?
• 일반적으로 업무의 단위에 따른 분할
하나의 Database ?
관리할 File의 수가 적어서 좋을 수 있다.
하나의 File의 크기가 너무 커질 수 있다.
정보보호를 위한 사용자 권한 관리가 복잡해진다.
여러 개로 분할된 Database ?
각 Database에 같은 이름의 Object 생성 허용
관리해야 할 File의 수가 많아 질 수 있다.
각 Database당 접근 권한의 제어가 보다 쉽다.
6
물리적 저장구조
Database
Data (file)
.mdf or .ndf
Tables, Indexes
Log (file)
.ldf
Extent
(8개의 연결된 8KB page들)
Data
Page (8KB)
한 row의 최대 길이는 8092 bytes
Extent 종류
Table과 Index에 할당되는 기본 단위
연속하는 8 Page, 또는 64KB
Uniform mode Extent
Mixed mode Extent
Hotfix 8.00.0702 이후
T1118 Option ON
초기부터 8 page 할당 가능
8
Page의 구조
Page
가장 작은 저장단위(8K)
구성요소
Data Rows
Free Space
Page Header
Row Offset
Data Loading 시의 주의 사항
Mixed Mode
데이터 양이 적은 테이블이어도 8KB의 extent가 할당
최대 8개의 extent에 초기 데이터들이 분산될 수 있다
Uniform Mode
Read Aheads는 Uniform Mode에서만 발생
Select … into 로 생성된 Table은 초기부터 Uniform Mode로만 생성 가능 VS.
DTS로 생성되거나 삽입되는 data는 Mixed Mode로 page할당
Read Aheads
대량의 Read시에 다음에도 읽을 가능성이 있는 데이터를 System이 미리 읽어 온다
영향을 받을 수 있는 요소는 다양하나 Uniform Mode로만 구성된 Object는 32
pages, Mixed Mode로 구성된 Object는 64 pages의 Read Ahead 발생
Read Ahead의 양의 차이가 어떠한 장, 단점을 갖는지는 좀 더 연구 필요
10
Table 정의
물리적 Mapping
가능하면 논리적 실체를 물리적 Table로 1:1 Mapping을 우선적으로 권장하나 …
Lock 경합을 줄이기 위하여
1:1 분할 수직 분할 검토
• 수정중인 Record에 대한 Before Image를 제공하지 않으므로 Update가 빈번한 컬럼 들과
Select 위주의 컬럼 분할 고려
• 1:1 대응되는 Table Data의 Key 일치성을 위한 주의 필요
수평 분할
대량 데이터 처리를 위한 Partitioning에 대한 대체로 수평분할을 고려할 수 있음
수평 분할된 Table Data의 Key 중복이 발생하지 않도록 주의
Partitioned View를 신중히 사용할 수도 있음
11
Table 정의
반정규화의 오용
Derived column의 남용
60% 이상
derived
 간단한 조인으로 얻을 수 있는
정보들을 과도하게 데이터 복사
 원본 데이터 갱신, 삽입, 삭제시
마다 복사 컬럼에 동시작업 요구로
작업 부하 기하급수적 증가
 데이터 저장 공간의 과도 증가
 불필요한 Disk I/O 발생으로
근원적인 속도 저하 요인
 데이터 불일치 발생 위험 증가
12
Table 정의
중복 속성에 따른 오류
데이터 불일치
발생
 과도한 derived
column 정의
 Derived column에
대해서는 빈번하게
나타날 수 있는 증상
13
SQL Server Data Types
암시적 변환
이 자주 발생
할 수 있음
● 명시적 변환
Cast, Convert 함수를
지정해야만 발생하는
변환
정밀도를 위하여 권장
● 암시적 변환
Cast, Convert 함수를
지정하지 않은 상태에서
발생하는 변환
○ 변환할 수 없음
14
SQL Server Data Types 선행 규칙
sql_variant
높음
datetime
smalldatetime
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar
nchar
varchar
char
varbinary
binary
낮음
select top 3 b.아파트명, b.지구명, p.아파트코드, p.평형, p.매매하한가, …
from p_apt as b ,p_aptprice as p with(readpast)
where b.도시 = '서울특별시'
and b.구시군 = '용산구'
and 읍면동 like '산천동%'
b.아파트코드 : nvarchar
p.아파트코드 : varchar
and b.아파트코드 = p.아파트코드
and ( p.매매하한가 > 0 or p.매매상한가 > 0 or p.전세하한가 >0 or p.전세상한가 > 0 )
order by b.아파트명, p.평형,p.평형타입
StmtText
묵시적 형변환에
따른 수행 경로
이상 발생
-------------------------------------------------------------select top 3 b.아파트명, b.지구명, p.아파트코드,
|--Sort(TOP 3, ORDER BY:([b].[아파트명] ASC,
|--Hash Match(Inner Join, HASH:([b].[아파트코드])=([Expr1002]), RESIDUAL: …
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([moduwww]. …
|
|--Index Scan(OBJECT:([moduwww].[modu].[P_APT].[P_APT_IDX3] …
|--Compute Scalar(DEFINE:([Expr1002]=Convert([p].[아파트코드]))) …
|--Clustered Index Scan(OBJECT:([moduwww].[modu].[P_APTPRICE] …
15
Index Scan
인덱스 사용을 선호하는 SQL Server Optimizer
테이블이 작아서 몇 페이지 되지 않아도 인덱스가 있으면 인덱스를
사용
Full Table Scan보다는 Index Merger를 하여서라도 인덱스를
사용하는 쪽으로 실행계획을 수립
Index Scan
Index scan : 적절한 인덱스가 없어 차선으로 선택된 실행계획
Index Seek
적절한 Index를 이용하여 Index를 이용한 Table Data Access
16
Index Access
Index Range Scan
Index Unique
Scan
Index Seek  수직적 probe
Index Scan  수평적 probe
Index Full Scan
17
Clustered Index
Leaf Level
모든 행 데이터는 Key 순서 대로 저장되고, 인덱스 Leaf
Block이 곧, Data Block !!
Non-clustered Index
Clustered Index를 갖는 테이블에 생성된 Nonclustered Index는 rid (record id)로 Clustered
Index의 키 값을 Bookmark로 사용
non-clustered
여러 개의 Column으로 구성된 Clustered Index
컬럼 사이즈가 크거나 여러 컬럼으로 구성된
Clustered Index를 갖는 테이블에 Non-Clustered
Index는 항상 Clustered Key를 포함한다.
clustered
SQL Server의 default Primary Key
별도로 Non-clustered로 언급하지 않는다면 기본적으로 clustered index를
생성하므로 반드시 필요한 경우가 아니라면 Non-clustered Primary Key로 권장
18
Clustered Index가 권장되는 경우
코드성 테이블
테이블의 규모가 작고, PK 이외에 별다른 Non-clustered Index가
불필요한 테이블
M : M 관계 해소에 따른 Association(=Intersection) Table
Key이외에는 별다른 속성을 가지지 않는다.
Between, Like와 같이 범위 검색을 주로 해야 하는 경우
범위 검색 위주이나 Clustered Key값에 의하여 근접한 데이터를
읽는다면 불필요한 disk I/O를 줄일 수 있다
19
인덱스와 NULL
‘’(blank)와 NULL은 다른 값으로 취급
인덱스에 NULL값도 포함
UNIQUE INDEX에 NULL값은 단 하나만 허용
IS NULL, IS NOT NULL 비교에서도 인덱스 사용 가능
SET ANSI_NULLS OFF
col = NULL, col <> NULL
Char, Datetime등에 대해서 Null로 채워지지 않는다
20
결합 인덱스의 컬럼 순서 결정
1
항상 사용하는가 ?
2
항상 ‘=‘ 로 사용되는가 ?
3
분포도가 좋은 컬럼 우선
4
SORT 순서는?
5
어떤 컬럼을 추가? (후보선수 : 예방, 도움)
21
Index 선정 절차
해당 테이블의 액세스 유형 조사
대상 컬럼의 선정 및 분포도 분석
반복 수행되는 액세스경로(Critical Access Path)의 해결
Clustered Index 검토
SORT 의 유형을 조사한다.
일련번호를 부여하는 경우를 찾는다.
인덱스 컬럼의 조합 및 순서의 결정
인덱스 시험 생성 및 테스트
수정이 필요한 Application 조사 및 수정
22
Index 선정 기준
분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상
자주 조합되어 사용되는 경우는 결합인덱스 생성
각종 액세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담
가능한 수정이 빈번하지 않는 컬럼
기본키 및 외부키 (조인의 연결고리가 되는 컬럼)
결합 인덱스의 컬럼 순서 선정에 주의
반복수행(loop 내) 되는 조건은 가장 빠른 수행속도를 내게 할 것
실제 조사된 액세스 종류를 토대로 선정 및 검증
23
Index 선정 시 고려사항
새로 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음
지나치게 많은 인덱스는 오버헤드를 발생
넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
조인(join)시에 인덱스가 사용여부에 주의
24
Index Design Chart
25
Application
모든 Application을 Stored Procedure로 작성할 것인가 ?
SQL의 재활용 면에서는 적극 권장
Stored Procedure내의 Loop Query등의 절차형 SQL 작성에 주의 !!
Trigger 사용의 자제
제대로 작동하지 않을 때 Data의 불일치 종종 유발
Transaction을 분할하여도 가능하면 Application으로 처리할 것을
권장
Parameter Query의 적극 활용
26
Stored Procedure 장점
Execution plan의 재사용
Business rule과 policy의 encapsulation
Application 모듈화
Application간 로직 공유
Object들에 대한 보다 안전한 액세스
Network bandwidth 절약
Stored Procedure 작성시 …
모든 object에는 반드시 owner명을 명시할 것
• Owner를 확인하기 위한 system table을 읽는 동안에 발생할 수 있는
불필요한 shared lock 발생 방지
27
SQL문의 실행
SQL
Manager
Procedure Cache
No
실행계획
존재?
Yes
Parsing
COMMAND
PARSER
Procedure에서
실행계획 가져오기
Sequence Tree
Full optimization for
Parallel execution
Yes
Normalization
Normalizer
No
재컴파일필요?
DML
Yes
Plan >= Parallelism
threshold
Query Graph
No
Simplification
Statistics loading
Full optimization for
Serial execution
No
Memory grant scheduler
OK 신호 기다림
실행계획 활성화
OPTIMIZER
실
Optimizer Phases 1 to n-1
Yes
Yes
Cheap
Plan
No
행
Query
Executor
28
Execution Plan Caching과 재사용
29
Trace 분석 방법
Client Side Tracing – SQL Profiler
GUI 화면을 통해 제어가 가능하다.
Trace가 쌓이는 동안 내용을 볼 수 있다.
Server Side Tracing에 비해 부하가 크다.
Trace Rowset 방식만 동작한다.
Server Side Tracing – System Procedure
SP를 작성해 자동화가 가능하다.
Trace가 쌓이는 동안 내용을 볼 수 없다.
Client Side Tracing에 비해서 부하가 적다.
Table에 Insert
select * into trace1
from ::fn_trace_gettable('d:\trace\실습1.trc', default)
기존 분석 방식의 한계
Trace 파일이 여러 개인 경우 전체를 대상으로 작업하기 어렵다.
상수만 다른 SQL이 Merge되지 않는다.
Execution 정보는 Image 형태로 저장된다.
30
Read80 Trace
Microsoft SQL Server
Support Escalation
Service utility 중 하나
기능
Trace 파일을 RML
(Replay Markup
Language) 형식으로 변환
Trace 파일 분석 및 Merge
31
Read80Trace 장점
Value만 다른 SQL Merge
문자, 상수 등을 기호로 치환해서 normalize한 후 Merge
실행계획 Parsing 및 Merge
수행 결과 DB에 저장
PerfAnalysis -- default / 수행 시 마다 drop되고 새로 생성됨
-d 로 다른 DB 지정 가능하다.
Report Generate
CPU, Duration 등으로 TOP 15개 보여줌
압축된 Trace 파일도 작업 가능
ZIP, CAB
분석 및 Merge 속도가 빠르다
32
수행 Demo
수집용 Trace생성 Script
Read80trace 수행
“c:\program files\rml\read80trace” -Imytrace.trc imytrace.zip -f -od:\test\output
결과 Report 화면
d:\test\output\index.html
generatexml.sql
33
Read80 관련 Table ERD
34
Read80 관련 Table ERD
35
활용 Point
문제 SQL 도출
수행빈도 최상위
Duration / CPU
Batch -> SQL -> Plan 연결
Execution Plan 활용
SCAN 잡아내기
사용 Index 잡아내기
Index 디자인
• 수행 SQL
• 조인 컬럼
• 사용 Index
RML 다운로드
http://www.microsoft.com/downloads/details.aspx?FamilyID=5691
ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en
36
성능을 위한 필수 사항
훌륭한 논리 모델이 기반
제대로 된 논리 모델을 기반으로 한 물리 모델의 구축이 관건
불충분한 데이터 모델에서는 고성능 SQL 작성의 한계
• OR를 종종 사용해야 한다
• 정보를 분석하기 위하여 substring을 사용해야 한다
• 하나의 컬럼에서 N개의 정보를 추출해야 한다
DBMS에 대한 충분한 이해
선택한 DBMS의 모든 사항을 충분히 파악
• 실체가 Table로 어떻게 mapping될 것인가 ?
• Application을 위한 적당한 Index는 무엇인가 ?
종류별 Index의 정확한 적용
Optimizer의 Plan 설계를 예측할 수 있는 SQL 개발자
수시로 DBMS에서 수행되는 Application 성능 모니터링 필요
37
감사합니다
38