물리 데이터 모델의 SQL Server 적용

1. 물리 데이터 모델의SQL Server 적용

2. DBMS 선정과 물리 모델의 반영 MS SQL Server DBMS Architecture Database 구조 Datafile내의 Extent 할당 관리 Rule Page의 구조 Data Loading 시의 주의 사항 Table 정의 SQL Server Data Types 변환 Index Scan vs. Index Access Clustered Index 언제 Clustered Index를 사용할 것인가? 인덱스와 NULL Index 생성 적용 단계 Index Design Chart Application Stored Procedure 장점 SQL문의 실행 Execution Plan Caching과 재사용 Trace 분석 방법 Read80Trace 목차

3. DBMS 선정과 물리 모델의 반영 • DBMS에 Database 구축 시 고려할 사항 • 성능 (Performance) • Data 정합성 • 반정규화(De-Normalization)가 성능에 유리한가 ? • 지나친 반정화는 불필요한 트랜잭션 유발 • 데이터의 중복은 데이터 불일치(In-Consistency) 유발

4. Memory Pool System Data Structure Connection Context Buffer Pool(Cache) Log Caches Procedure Cache Free Queue Flush Queue Lock Manager Stored Procedure Manager DML Manager Utility Manager Expression Manager DDL Manager Memory Grant Scheduler Transaction Manager Memory Grant Queue Memory Manager Output Buffer (Write Buffer) VLF Completion Queue … *.LDF Input Buffer (Read Buffer) Input Buffer (Read Buffer) Open Database Service(ODS) Data File 3 *.NDF Data File 1 *.MDF Data File n *.NDF Data File 2 *.NDF Client Relational Engine Log Caches CheckPoint LazyWriter Log Manager Command Parser Buffer Manager Log Writer Normalizer Optimizer Access Methods M - Row operation M - Index operation M Text Manager Query Executor SQL Manager Page Manager Net-Library User Mode Scheduler Worker Thread Pool MS SQL Server DBMS Architecture 사용자 환경설정에 대한 지극히 기본적인 사항에 대한 설명은 배제

5. Database 구조 • 하나의 database는 두개 이상의 물리적인 file로 구성 • Primary File, [Secondary,] Transaction Log File • 4개의 system database를 갖는다. • Master, model, tempdb, msdb • 하나 이상의 사용자 database

6. 논리적 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되는 과정이 필요하므로 점차적인 증가보다는 미리 최고크기로 할당 받고 생성되는 것을 권장

7. Database 생성 • 적절한 Database의 생성은 ? • 몇 개의 database를 유지할 것인가 ? • 일반적으로 업무의 단위에 따른 분할 • 하나의 Database ? • 관리할 File의 수가 적어서 좋을 수 있다. • 하나의 File의 크기가 너무 커질 수 있다. • 정보보호를 위한 사용자 권한 관리가 복잡해진다. • 여러 개로 분할된 Database ? • 각 Database에 같은 이름의 Object 생성 허용 • 관리해야 할 File의 수가 많아 질 수 있다. • 각 Database당 접근 권한의 제어가 보다 쉽다.

8. Database Data (file) .mdf or .ndf Log (file) .ldf Tables, Indexes Extent (8개의 연결된 8KB page들) Data Page (8KB) 한 row의 최대 길이는 8092 bytes 물리적 저장구조

9. Extent 종류 • Table과 Index에 할당되는 기본 단위 • 연속하는 8 Page, 또는 64KB • Uniform mode Extent • Mixed mode Extent Hotfix 8.00.0702 이후 T1118 Option ON 초기부터 8 page 할당 가능

10. Page의 구조 • Page • 가장 작은 저장단위(8K) • 구성요소 • Data Rows • Free Space • Page Header • Row Offset

11. 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의 양의 차이가 어떠한 장, 단점을 갖는지는 좀 더 연구 필요

12. 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를 신중히 사용할 수도 있음

13. Table 정의 반정규화의 오용 60% 이상 derived Derived column의 남용 • 간단한 조인으로 얻을 수 있는 정보들을 과도하게 데이터 복사 • 원본 데이터 갱신, 삽입, 삭제시 마다 복사 컬럼에 동시작업 요구로 작업 부하 기하급수적 증가 • 데이터 저장 공간의 과도 증가 • 불필요한 Disk I/O 발생으로 근원적인 속도 저하 요인 • 데이터 불일치 발생 위험 증가

14. Table 정의 중복 속성에 따른 오류 데이터 불일치 발생 • 과도한 derived column 정의 • Derived column에 대해서는 빈번하게 나타날 수 있는 증상

15. SQL Server Data Types 암시적 변환이 자주 발생할 수 있음 ●명시적 변환 Cast, Convert 함수를 지정해야만 발생하는 변환정밀도를 위하여 권장 ●암시적 변환Cast, Convert 함수를 지정하지 않은 상태에서 발생하는 변환 ○변환할 수 없음

16. 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 '산천동%' 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] … b.아파트코드 : nvarchar p.아파트코드 : varchar 묵시적 형변환에 따른 수행 경로 이상 발생 낮음

17. Index Scan • 인덱스 사용을 선호하는 SQL Server Optimizer • 테이블이 작아서 몇 페이지 되지 않아도 인덱스가 있으면 인덱스를 사용 • Full Table Scan보다는 Index Merger를 하여서라도 인덱스를 사용하는 쪽으로 실행계획을 수립 • Index Scan • Index scan : 적절한 인덱스가 없어 차선으로 선택된 실행계획 • Index Seek • 적절한 Index를 이용하여 Index를 이용한 Table Data Access

18. Index Access • Index Seek  수직적 probe • Index Scan  수평적 probe Index Range Scan Index Unique Scan Index Full Scan

19. non-clustered clustered Clustered Index • Leaf Level • 모든 행 데이터는 Key 순서 대로 저장되고, 인덱스 Leaf Block이 곧, Data Block !! • Non-clustered Index • Clustered Index를 갖는 테이블에 생성된 Non-clustered Index는 rid (record id)로 Clustered Index의 키 값을 Bookmark로 사용 • 여러 개의 Column으로 구성된 Clustered Index • 컬럼 사이즈가 크거나 여러 컬럼으로 구성된 Clustered Index를 갖는 테이블에 Non-Clustered Index는 항상 Clustered Key를 포함한다. • SQL Server의 default Primary Key • 별도로 Non-clustered로 언급하지 않는다면 기본적으로 clustered index를 생성하므로 반드시 필요한 경우가 아니라면 Non-clustered Primary Key로 권장

20. Clustered Index가 권장되는 경우 • 코드성 테이블 • 테이블의 규모가 작고, PK 이외에 별다른 Non-clustered Index가 불필요한 테이블 • M : M 관계 해소에 따른 Association(=Intersection) Table • Key이외에는 별다른 속성을 가지지 않는다. • Between, Like와 같이 범위 검색을 주로 해야 하는 경우 • 범위 검색 위주이나 Clustered Key값에 의하여 근접한 데이터를 읽는다면 불필요한 disk I/O를 줄일 수 있다

21. 인덱스와 NULL • ‘’(blank)와 NULL은 다른 값으로 취급 • 인덱스에 NULL값도 포함 • UNIQUE INDEX에 NULL값은 단 하나만 허용 • IS NULL, IS NOT NULL 비교에서도 인덱스 사용 가능 • SET ANSI_NULLS OFF • col = NULL, col <> NULL • Char, Datetime등에 대해서 Null로 채워지지 않는다

22. 1 항상 사용하는가 ? 2 항상 ‘=‘로 사용되는가 ? 3 분포도가 좋은 컬럼 우선 4 SORT 순서는? 5 어떤 컬럼을 추가? (후보선수 : 예방, 도움) 결합 인덱스의 컬럼 순서 결정

23. Index 선정 절차 • 해당 테이블의 액세스 유형 조사 • 대상 컬럼의 선정 및 분포도 분석 • 반복 수행되는 액세스경로(Critical Access Path)의 해결 • Clustered Index 검토 • SORT 의 유형을 조사한다. • 일련번호를 부여하는 경우를 찾는다. • 인덱스 컬럼의 조합 및 순서의 결정 • 인덱스 시험 생성 및 테스트 • 수정이 필요한 Application 조사 및 수정

24. Index 선정 기준 • 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상 • 자주 조합되어 사용되는 경우는 결합인덱스 생성 • 각종 액세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담 • 가능한 수정이 빈번하지 않는 컬럼 • 기본키 및 외부키 (조인의 연결고리가 되는 컬럼) • 결합 인덱스의 컬럼 순서 선정에 주의 • 반복수행(loop 내) 되는 조건은 가장 빠른 수행속도를 내게 할 것 • 실제 조사된 액세스 종류를 토대로 선정 및 검증

25. Index 선정 시 고려사항 • 새로 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음 • 지나치게 많은 인덱스는 오버헤드를 발생 • 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생 • 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성 • 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음 • 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐 • 조인(join)시에 인덱스가 사용여부에 주의

26. Index Design Chart

27. Application • 모든 Application을 Stored Procedure로 작성할 것인가 ? • SQL의 재활용 면에서는 적극 권장 • Stored Procedure내의 Loop Query등의 절차형 SQL 작성에 주의 !! • Trigger 사용의 자제 • 제대로 작동하지 않을 때 Data의 불일치 종종 유발 • Transaction을 분할하여도 가능하면 Application으로 처리할 것을 권장 • Parameter Query의 적극 활용

28. Stored Procedure 장점 • Execution plan의 재사용 • Business rule과 policy의 encapsulation • Application 모듈화 • Application간 로직 공유 • Object들에 대한 보다 안전한 액세스 • Network bandwidth 절약 • Stored Procedure 작성시 … • 모든 object에는 반드시 owner명을 명시할 것 • Owner를 확인하기 위한 system table을 읽는 동안에 발생할 수 있는 불필요한 shared lock 발생 방지

29. SQL문의 실행 SQL Manager A Procedure Cache B No Yes 실행계획 존재? Parsing COMMAND PARSER Procedure에서 실행계획 가져오기 A Sequence Tree Full optimization for Parallel execution Normalization Yes Normalizer 재컴파일필요? B Yes No C DML No Yes Plan >= Parallelism threshold Query Graph Memory grant scheduler OK 신호 기다림 No Simplification Full optimization for Serial execution 실행계획 활성화 Statistics loading OPTIMIZER C 실 행 A Optimizer Phases 1 to n-1 Query Executor No Yes Cheap Plan A

30. Execution Plan Caching과 재사용

31. 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 trace1from ::fn_trace_gettable('d:\trace\실습1.trc', default) • 기존 분석 방식의 한계 • Trace 파일이 여러 개인 경우 전체를 대상으로 작업하기 어렵다. • 상수만 다른 SQL이 Merge되지 않는다. • Execution 정보는 Image 형태로 저장된다.

32. Read80 Trace • Microsoft SQL Server Support Escalation Service utility 중 하나 • 기능 • Trace 파일을 RML (Replay Markup Language) 형식으로 변환 • Trace 파일 분석 및 Merge

33. Read80Trace 장점 • Value만 다른 SQL Merge • 문자, 상수 등을 기호로 치환해서 normalize한 후 Merge • 실행계획 Parsing 및 Merge • 수행 결과 DB에 저장 • PerfAnalysis -- default / 수행 시 마다 drop되고 새로 생성됨 • -d 로 다른 DB 지정 가능하다. • Report Generate • CPU, Duration 등으로 TOP 15개 보여줌 • 압축된 Trace 파일도 작업 가능 • ZIP, CAB • 분석 및 Merge 속도가 빠르다

34. 수행 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

35. Read80 관련 Table ERD

36. Read80 관련 Table ERD

37. 활용 Point • 문제 SQL 도출 • 수행빈도 최상위 • Duration / CPU • Batch -> SQL -> Plan 연결 • Execution Plan 활용 • SCAN 잡아내기 • 사용 Index 잡아내기 • Index 디자인 • 수행 SQL • 조인 컬럼 • 사용 Index • RML 다운로드 • http://www.microsoft.com/downloads/details.aspx?FamilyID=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

38. 성능을 위한 필수 사항 • 훌륭한 논리 모델이 기반 • 제대로 된 논리 모델을 기반으로 한 물리 모델의 구축이 관건 • 불충분한 데이터 모델에서는 고성능 SQL 작성의 한계 • OR를 종종 사용해야 한다 • 정보를 분석하기 위하여 substring을 사용해야 한다 • 하나의 컬럼에서 N개의 정보를 추출해야 한다 • DBMS에 대한 충분한 이해 • 선택한 DBMS의 모든 사항을 충분히 파악 • 실체가 Table로 어떻게 mapping될 것인가 ? • Application을 위한 적당한 Index는 무엇인가 ? • 종류별 Index의 정확한 적용 • Optimizer의 Plan 설계를 예측할 수 있는 SQL 개발자 • 수시로 DBMS에서 수행되는 Application 성능 모니터링 필요

39. 감사합니다