Transcript sql_tunning
SQL Server 7.0 세미나 (Performance Tuning) 주최: Microsoft 강사: 하성희 차례 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic 차례 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic 기본 원칙 Auto-configuring, self-tuning Buffer cache의 활용 극대화 효율적인 Index 생성 및 관리 Application과 Query Tuning Query Plan 확인 Tuning Tool의 적절한 활용 Focus : 최소의 I/O SQL Server Profiler, Index Tuning Wizard 모니터링 Bottleneck 발생 여부 확인 차례 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic 주요 Parameter들 Max Async I/O 디폴트 : 32, 최대:255 Advanced Option sp_configure “show advanced options”, 1 go Affinity Mask Max Server Memory Min Server Memory 차례 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic 데이터 액세스 방식 Table Scan : 모든 Page를 순차적으로 액세스하는 방식 Data DataPages Pages … Index에 근거한 검색 : Index Page들을 검색하여 조건에 맞는 Key를 찾아 내는 방식 Index Pages Data Pages … Index 분류 SQL Server Index Type Clustered Index Non-clustered Index Uniqueness Unique Index Non-unique Index Column 개수 Single-Column Index Composite Index Index 구조 Nonclustered Index Clustered Index Index Pages Non-Leaf Level Index Pages Non-Leaf Level Leaf Level (Key Value) Data Pages Leaf Level Data Pages Covering Indexes Nonclustered Index에 해당함. Index에 Query검색에 필요한 모든 Column들이 포함되는 경우 Covering index의 예: select col1, col3 from table1 where col2 = ‘value’ go create index indexname1 on table1 (col2, col1, col3) go Automatic Covering index Indexing 기본 원칙 데이터에 대한 이해 검색 제한 Selectivity (선택성) 확인 Table에 대한 Query 형태 분석 Query들의 우선 순위 확인 Composite Index-최적의 Column 순서 결정 데이터에 대한 이해 Logical Design과 Physical Design 데이터 특성 어떻게 데이터가 저장되는지 수행되는 query들의 형태 전형적으로 수행되는 query들의 수행주기 검색 제한 Search Arguments 사용 최적의 Search Arguments 작성 Query에서 WHERE 절을 지정 WHERE절이 row의 개수를 제한하는지 확인 Query에서 참조되는 모든 Table에 대한 구문을 검증 Leading wildcard의 사용을 자제 SARG (Search Arguments) 검색을 제한할 수 있으며 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 Joshua 2 Flood Kathie . Anderson Bill Low selectivity member_no last_name first_name 1 Randall Joshua 2 Flood Kathie Number of rows meeting criteria Total number of rows in table SELECT * FROM member WHERE member_no < 9001 . . . 10000 1000 = 10000 = 10% SELECT * FROM member WHERE member_no > 8999 . . 10000 Number of rows meeting criteria Total number of rows in table Anderson Bill 9000 = 10000 = 90% Composite Index최적의 Column 순서 결정 CREATE INDEX문에 기술된 Key Column들의 순서가 중요 예: CREATE INDEX t1_ix ON t1 (A, B) WHERE A=‘Value’ : efficient WHERE A=‘Value’ and B=‘Value’: efficient WHERE B=‘Value’ : less efficient Query 형태 분석 Selectivity 고려 Index를 생성하면 효과적인 경우 특정 값과 일치하는 소수의 Row를 검색하는데 사용되는 Column들 범위에 해당하는 소수의 Row를 검색하는데 사용되는 Column들 자주 Join Key로 사용되는 Column들 특정 순서로 조회되는 Column들 (C.I) *** Good Selectivity *** Good Selectivity Selectivity 와 Density Index의 Selectivity 확인 DBCC SHOW_STATISTICS (table_name, index_name) Statistics 갱신 디폴트 : auto update statistics 수작업 : UPDATE STATISTICS table_name FILLFACTOR & PAD_INDEX FILLFACTOR 옵션 PAD_INDEX 옵션 권장 사항 - Selectivity가 높은 Column에 Index를 생성한다. - Query에서 자주 사용되는 Column들에 nonclustered index를 만들어 줌으로써 Index covering의 가능성을 높인다. - Clustered Index는 integer key, unique, non-null 또는 IDENTITY column에 생성하면 좀 더 효과적이다. - OR 연산에서 참조되는 모든 Column들에 대하여, 유용한 Index가 존재하는지 확인하고 Index를 생성한다. 제목 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic Query Optimizer의 기능 가장 효율적인 Query Plan을 결정 Index들이 존재하는지 그리고 유용한지를 확인 어떤 index나 column이 사용될 수 있는지 확인 어떻게 Join을 수행할지를 결정 Cost에 근거한 평가방식을 사용 Column statistics를 생성 Query Plan을 작성 유용한 정보를 사용 Cost-Based Optimization 사용 방식 Optimization Plan의 개수를 제한 Cost는 I/O 와 CPU cost 측면에서 평가 Query Processing Time 을 결정 Physical operator들과 operation의 순서 Parallel processing과 serial processing Query Optimization 단계 Query 분석 검색과 Join 확인 Index 선택 Index(들) 존재 여부 확인 유용한 Index(들) 수집 Join 선택 Join 전략 평가 Cost 한도 설정 Cost 상한 제한치를 지정 query governor를 사용하여 장시간 수행될 query가 수행되지 못하게 함으로써 시스템 자원이 낭비되는 것을 방지 Connection 제약 지정 sp_configure stored procedure 를 사용 sp_configure 'query governor cost limit', 7200 SET QUERY_GOVERNOR_COST_LIMIT 문을 수행 예: SET QUERY_GOVERNOR_COST_LIMIT 60 Query governor가 동작하지 않도록 하려면 값을 0 으로 설정 Query Plan 정보 sysindexes Table 을 참조 STATISTIC Statements Output 확인 Query Plan 확인 - SET SHOWPLAN_TEXT ON - SET SHOWPLAN_ALL ON - Graphical Showplan 확인 sysindexes 테이블 Table & Index 정보를 저장 Index의 타입 (indid) 사용된 Space (dpages, reserved, used) Fill factor (OrigFillFactor) 각각의 Index에 대하여 Statistics를 저장 Statistics 생성 자동으로 Statistics를 생성해 주는 경우 데이터가 있고 Index가 걸려 있는 Column들 Join 문이나 WHERE절에서 사용되면서 Index가 없는 Column들 직접 Statistics를 생성하는 경우 Index가 없는 Column들 Composite Index의 첫번째 Column이 아닌 모든 Column들 Statistics 갱신 Statistics 갱신 주기 자동 Statistics 갱신 수동 Statistics 갱신 데이터가 Table에 저장되기 전에 Index를 생성한 경우 Table의 데이터를 truncate한 경우 데이터가 아주 조금 있거나 없었던 Table에 많은 수의 row들을 추가하고, 즉시 그 Table에 대하여 query를 수행하고자 하는 경우 권장 사항 - Query Governor를 사용하여 장시간 수행되는 query가 수행되어 시스템 자원을 낭비하지 않도록 한다. - 데이터와 사용자들이 데이터를 액세스하는 방식을 완전히 이해한다. - SQL Server가 자동으로 Statistics를 생성하고 수정하도록 한다 - Query Optimizer에 도움이 될 만한 Column들에 직접 Statistics를 생성해 준다. - Query Plan을 확인하고, Index를 효율적으로 사용하도록 Index와 query tuning 작업을 지속적으로 수행한다. 차례 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic Tuning Tool 활용 SQL Profiler 와 Index Tuning Wizard를 주기적으로 연동 활용 SQL Server Performance Monitor 활용 Bottleneck 감지 주요 counter들 · (Physical or Logical) Disk Queue > 2 · · · · 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 Statistics I/O Graphical showplan 차례 Performance tuning 원칙 Configuration Parameter Index Query Optimization Performance Tuning Tool 기타 Performance 관련 Topic 기타 Issue Deadlocking Blocking Normalization Subquery Cursor View Trigger