SQL Server 7 0 Performance Tuning Microsoft Performance

SQL Server 7. 0 세미나 (Performance Tuning) 주최: Microsoft 강사: 하성희

차례 Performance tuning 원칙 v Configuration Parameter v Index v Query Optimization v Performance Tuning Tool v 기타 Performance 관련 Topic v

차례 Performance tuning 원칙 v Configuration Parameter v Index v Query Optimization v Performance Tuning Tool v 기타 Performance 관련 Topic v

기본 원칙 v v Auto-configuring, self-tuning Buffer cache의 활용 극대화 n v v 효율적인 Index 생성 및 관리 Application과 Query Tuning n v Query Plan 확인 Tuning Tool의 적절한 활용 n v Focus : 최소의 I/O SQL Server Profiler, Index Tuning Wizard 모니터링 n Bottleneck 발생 여부 확인

차례 Performance tuning 원칙 v Configuration Parameter v Index v Query Optimization v Performance Tuning Tool v 기타 Performance 관련 Topic v

주요 Parameter들 v Max Async I/O n 디폴트 : 32, 최대: 255 n Advanced Option sp_configure “show advanced options”, 1 go v v v Affinity Mask Max Server Memory Min Server Memory

차례 v v v Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic

Index 분류 v v v SQL Server Index Type n Clustered Index n Non-clustered Index Uniqueness n Unique Index n Non-unique Index Column 개수 n Single-Column Index n Composite Index

Index 구조 Nonclustered Index Clustered Index Pages Non-Leaf Level (Key Value) Data Pages Leaf Level Data Pages

Covering Indexes v v v Nonclustered Index에 해당함. Index에 Query검색에 필요한 모든 Column들 이 포함되는 경우 Covering index의 예: select col 1, col 3 from table 1 where col 2 = ‘value’ go create indexname 1 on table 1 (col 2, col 1, col 3) go v Automatic Covering index

SARG (Search Arguments) v 검색을 제한할 수 있으며 INDEX를 사용할 수 있 는 경우 SARG Non-SARG WHERE name=‘Smith’ WHERE salary=commission WHERE salary < 3000 WHERE Salary!= 3000 WHERE price = 100/12 WHERE price*12=100 WHERE au_lname like ‘S%’ WHERE substring(au_lname, 1, 1)=‘S’ WHERE price between 9 and 20 WHERE price < 2 and price > 4 WHERE au_lname like ‘Sm%’ WHERE au_lname like ‘%Sm’

Selectivity (선택성) 확인 High selectivity member_no last_name first_name 1 Randall 2. Flood . . 10000 Anderson Joshua Kathie SELECT * FROM member WHERE member_no > 8999 Bill member_no last_name first_name 1 2. Randall Flood . . 10000 Anderson Number of rows meeting criteria 1000 Total number of rows in table = 10000 = 10% Low selectivity Number of rows meeting criteria 9000 Joshua Total number of rows in table = 10000 = 90% Kathie SELECT * FROM member WHERE member_no < 9001 Bill

Composite Index최적의 Column 순서 결정 v CREATE INDEX문에 기술된 Key Column들의 순서가 중요 n 예: CREATE INDEX t 1_ix ON t 1 (A, B) n n n v v WHERE A=‘Value’ : efficient WHERE A=‘Value’ and B=‘Value’: efficient WHERE B=‘Value’ : less efficient Query 형태 분석 Selectivity 고려

Good Selectivity v v Selectivity 와 Density Index의 Selectivity 확인 n v DBCC SHOW_STATISTICS (table_name, index_name) Statistics 갱신 n 디폴트 : auto update statistics n 수작업 : UPDATE STATISTICS table_name

FILLFACTOR & PAD_INDEX v v FILLFACTOR 옵션 PAD_INDEX 옵션

제목 v v v Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic

Cost-Based Optimization 사 용 방식 v v Optimization Plan의 개수를 제한 n Cost는 I/O 와 CPU cost 측면에서 평가 Query Processing Time 을 결정 n Physical operator들과 operation의 순서 n Parallel processing과 serial processing

Query Optimization 단계 v v v Query 분석 n 검색과 Join 확인 Index 선택 n Index(들) 존재 여부 확인 n 유용한 Index(들) 수집 Join 선택 n Join 전략 평가

Cost 한도 설정 v v Cost 상한 제한치를 지정 n query governor를 사용하여 장시간 수행될 query가 수행되지 못하게 함으로써 시스템 자원이 낭비되는 것을 방지 Connection 제약 지정 n sp_configure stored procedure 를 사용 n sp_configure 'query governor cost limit', 7200 n SET QUERY_GOVERNOR_COST_LIMIT 문을 수행 n 예: SET QUERY_GOVERNOR_COST_LIMIT 60 n Query governor가 동작하지 않도록 하려면 값을 0 으로 설정

Query Plan 정보 v v v sysindexes Table 을 참조 STATISTIC Statements Output 확인 Query Plan 확인 - SET SHOWPLAN_TEXT ON - SET SHOWPLAN_ALL ON - Graphical Showplan 확인

sysindexes 테이블 v v Table & Index 정보를 저장 n Index의 타입 (indid) n 사용된 Space (dpages, reserved, used) n Fill factor (Orig. Fill. Factor) 각각의 Index에 대하여 Statistics를 저장

차례 v v v Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic

Tuning Tool 활용 v v SQL Profiler 와 Index Tuning Wizard를 주기적으로 연동 활용 SQL Server Performance Monitor 활용 n Bottleneck 감지 n 주요 counter들 n · (Physical or Logical) Disk Queue > 2 n n v · System: Processor Queue Length > 2 (per CPU) · Memory: Pages/sec > 0, Memory: Page Reads/sec > 5 · Memory: Pages Faults/sec > 0 · Processor: %Processor Time > 95 Query Analyzer n Statistics I/O n Graphical showplan

차례 v v v Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic

기타 Issue v v v v Deadlocking Blocking Normalization Subquery Cursor View Trigger