Microsoft Services SQL Server Performance Monitoring Tuning Guide

Microsoft Services SQL Server Performance Monitoring & Tuning Guide 작성자: Yong Seon Kim (yongkim@microsoft. com)

Microsoft Premier Support Services 목 차 1. 2. 3. 4. SQL Server 세미나 Overview Performance Monitoring How to use profiler System Configuration(memory architecture, sql option, page & transaction architecture) 5. User Query Monitoring (sysprocesses & syscacheobjects, query parsing procedure, procedure reuse) 6. Lock Isolation Levels 7. Index 8. JOIN 9. Hint 10. Case © 2005 Microsoft

Microsoft Premier Support Services SQL Server 세미나 분류 v. SQL Server Administration v. SQL Server System Tuning v. SQL Server Query Tuning v. SQL Server Profiler v. SQL Server DTS v. SQL Server Disaster Recovery v. SQL Server High Availibility v. SQL Server Stored Procedure Programming v. SQL Server Analysis Service Overview v. SQL Server Reporting Service © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. Processor: u%Processor Time u%Privileged Time - OS u%User Time – APP u%interrupt Time v. Process: u. Priority Base (SQLSERVR) u. Thread Count (SQLSERVR) u. Working Set (SQLSERVR) v. System: u. Context Switch / sec u. Processor Queue Length / sec © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. UMS v. Affinity mask v. Max worker Threads v. Priority Boost v. NT Fiber (커널모드/사용자모드) v. Max. DOP -> CXPacket v. Cost Threshold for parallelism © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. Memory: u. Pages / sec u. Pages Input / sec u. Pages Output / sec u. Page Faults / sec 알아야할 개념 : Working Set, 3 GB, PAE, AWE(Address Windowing Extension), Soft/Hard Fault © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. Physical Disk: u%Disk Time u%Disk Read Time u%Disk Write Time u. Avg. Disk Queue Length u. Avg. Disk sec / Read u. Avg. Disk sec / Write © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. SQL Server : Access Methods u. Full Scan / sec u. Range Scan / sec u. Page Splits / sec u. Mixed Page Allocations / sec © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. SQL Server : Buffer Manager u. Buffer Cache Hit Ratio u. Lazy Writes / sec u. Procedure Cache Pages u. Total Pages © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. SQL Server : SQLDatabase u. Transactions / sec - User DB - Tempdb © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. SQL Server : General Statistics u. Logins / sec u. User Connections / sec © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. SQL Server : Locks u. Lock Requests / sec u. Number of Dead. Locks / sec - Database - Table - Extent - Key © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters v. SQL Server : SQL Statistics u. SQL Compilation /sec u. SQL Re-Compilation /sec u. Auto-Param Attempt /sec Set? © 2005 Microsoft

Microsoft Premier Support Services Performance Monitoring System Monitor - System Counters SP Recompilation Demo © 2005 Microsoft

Microsoft Premier Support Services Recommendations v주기적인 History Data 수집 v. Save to location named sys. History v. Compare history u. Before/After tuning © 2005 Microsoft

Microsoft Premier Support Services Profiler 이벤트 u제외: 보안 감사 / 세션 u Existing. Connection u. RPC completed u. TSQL Batch completed u 경고 ü Attention ü Exception ü Execution Warnings © 2005 Microsoft

Microsoft Premier Support Services Profiler 이벤트 u 잠금 üLock: Deadlock Chain üLock: Timeout u성능 üExecution Plan üShow Plan All u프로시저 üSP: Cache. Hit üSP: Exec. Context. Hit üSP: Recompile © 2005 Microsoft

Microsoft Premier Support Services Profiler 데이터와 열 u Event. Class u CPU / Duration u Reads/ Writes u Text. Data u Client. Process. ID / SPID u Binary Data u Application Name /Host Name u Database ID u Grouping © 2005 Microsoft

Microsoft Premier Support Services Profiler 필터 © 2005 Microsoft

Microsoft Premier Support Services Profiler 추적분석 v. By duration/ CPU/ reads / writes v. By user v. Find v. Select textdata, cpu, duration, writes, reads from Trace order by cpu desc select convert(varchar(80), textdata), avg(duration) dr, avg(cpu) c, avg(reads) r, avg(writes ) w from Trace group by convert(varchar(80), textdata) © 2005 Microsoft

Microsoft Premier Support Services Profiler 추적재생 v. Stpe by step v한꺼번에 (부하 걸기) v실제로 수행 vdb context / login, user context/ password/ permission 등이 맞아야 제대로 재생 © 2005 Microsoft

Microsoft Premier Support Services Profiler 블로킹 찾기 vreads나 cpu는 얼마 되지 않는데 duration은 길다 vwhere duration > 10000 and reads < 1000 order by duration desc © 2005 Microsoft

Microsoft Premier Support Services Profiler SQL 2005의 데드락 처리 USE pubs go go CREATE TABLE My. DLock 1 CREATE TABLE My. DLock 2 ( ( id INT PRIMARY KEY, value VARCHAR(30) ) ) go go INSERT My. DLock 1 VALUES(1, 'DATA 1') INSERT My. DLock 2 VALUES(1, 'DATA 1') go go BEGIN TRAN 1 BEGIN TRAN 2 INSERT My. DLock 1 VALUES(2, 'DATA 2') INSERT My. DLock 2 VALUES(2, 'DATA 2') SELECT * FROM My. DLock 2 WITH(TABLOCKX) SELECT * FROM My. DLock 1 WITH(TABLOCKX) © 2005 Microsoft

Microsoft Premier Support Services Profiler SQL 2005의 데드락 처리 © 2005 Microsoft

Microsoft Premier Support Services Profiler SQL 2005의 showplan © 2005 Microsoft

Microsoft Premier Support Services Profiler Index Tuning Advisor © 2005 Microsoft

Microsoft Premier Support Services PSSDiag & Read 80 Trace PSSDiag 다운경로 : http: //support. microsoft. com/? kbid=830232 Read 80 trace 다운 : http: //support. microsoft. com/? kbid=887057 © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources v각종 자동시작 옵션 설정 v시작 매개변수 설정 - d, e, l - m http: //support. microsoft. com/defau lt. aspx? scid=kb; ko; kr 601286 v네트웍크 구성 설정 - 서버 포트 설정 © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources v각종 연결 옵션 설정 - implicit transaction - close cursor on commit - ANSI Null © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources ANSI PADDING 사 례 소개 © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources v각종 연결 옵션 설정 - fillfactor (※유지관리계획때 주의) - recovery interval © 2005 Microsoft

Microsoft Premier Support Services System Configuration Configuring SQL Resources v. SQL Server의 페이지 구조 - header, offset, timestamp - page - extent - data file - RID - Page Split - fillfactor - DBCC Showcontig © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources v. SQL Server의 데이터 처리 절차 - Dirty Pages - Transaction Log - MDF/NDF - Transaction - Commit / Rollback - Checkpoint - Roll forward / Roll Back © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources v. Sp_configure v. Reconfigure with override v. Sp_configure ‘allow updates’, 1 v. Sp_configure ‘show advanced options’, 1 © 2005 Microsoft

Microsoft Premier Support Services SQL Architecture & System Configuration Configuring SQL Resources v. DBCC u. Monitoring Performance üSQLPERF(WAITSTATS) - 대기분석 üSQLPERF(LRUSTATS) – 캐쉬분석 üMEMUSAGE – 캐쉬내 테이블 점유율 üPROCCACHE – 프로시져의 캐쉬 사용율 üPINTABLE(DBID, 테이블ID) üOPENTRAN üMEMORYSTATUS – 각종 Mem정보 üFREEPROCCACHE üDROPCLEANBUFFERS © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring sysprocesses를 이용한 사용자 쿼리 진단 SYSPROCESSES Select * from master. . sysprocesses where blocked > 0 Dbcc inputbuffer(51) KILL 51 © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 SYSCACHEOBJECTS © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 Query Processor Statement Compilation Process Parse the statement Create a sequence tree Normalise the tree Compile the TSQL Statement procedurally No Yes SQL DML Statement? © 2005 Microsoft Create Query Graph Optimize and create Plan Compile the TSQL Statement procedurally

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 Query Processor optimizing a statement Trivial plan optimizer No Simplification Statistics loading Cost-based optimizer Phases 1 to n-1 Found a cheap Plan? Yes No Found Plan? Yes 1 Yes Cheapest cost Full optimization for Is > than parallelism parallel execution Threshold? No Full optimization for Serial execution 1 Output plan © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 Query Processor Caching – costing a query plan Ad-hoc & Cheap to Compile? Yes Don’t Cache No Allocate memory From buffer cache Type of Plan? Ad-hoc Set initial cost to Creation cost © 2005 Microsoft Set initial cost To Zero Put plan in cache

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 실행 계획의 케싱(Caching) 케싱 메커니즘의 형태 v임의 질의에 대한 케싱 u임의 질의(Ad-hoc) 케싱 u자동 파라메터화 v고정된 질의에 대한 케싱 u저장 프로시저 u. Execute. SQL u. Prepare/Execute/Unprepare udbcc freeproccache © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 SYSCACHEOBJECTS 을 이용한 Library Cache 모니터링 Demo © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring syscacheobjects를 이용한 쿼리 Reuse 모니터링 SP Sniffings 데모 © 2005 Microsoft

Microsoft Premier Support Services User Query Monitoring sysperfinfo SYSPERFINFO © 2005 Microsoft

Microsoft Premier Support Services Lock Isolation Levels v. Supports all 4 ANSI and ISO isolation levels u. Serializable u. Repeatable Read u. Read Committed - default u. Read Uncommitted © 2005 Microsoft

Microsoft Premier Support Services Locking Isolation Leveles “Transaction Isolation Level in COM+ / SQL Server” Transaction Isolation Level Read uncommitted Dirty Reads Nonrepeatable Reads Phantoms occur occur Read committed Repeatable Read occur Serializable MS SQL Server Default © 2005 Microsoft COM+ Default

Microsoft Premier Support Services Lock Isolation Levels Isolation Level Demo © 2005 Microsoft

Microsoft Premier Support Services Index SQL Server의 데이터 액세스 방법 Table Scan Data. Pages Data … Index 검색 Index Pages Data Pages … © 2005 Microsoft

Microsoft Premier Support Services Index 인덱스 분류 v. SQL Server Index Type u. Clustered Index u. Non-clustered Index v. Uniqueness u. Unique Index u. Non-unique Index v. Column 갯수 u. Single-Column Index u. Composite Index © 2005 Microsoft

Microsoft Premier Support Services Index 인덱스 정보보기 vsp_helpindex System Stored Procedure 사용 vsp_help tablename System Stored Procedure 사용 USE Northwind EXEC sp_helpindex Customers © 2005 Microsoft

Microsoft Premier Support Services Index sysindexes 테이블 indid Object Type 0 Heap 1 Clustered Index 2 to 250 Nonclustered Index 255 text, ntext, or image v. Table & Index 정보를 저장 u Index의 타입 (indid) u 사용된 Space (dpages, reserved, used) u Fill factor (Orig. Fill. Factor) v각각의 Index에 대하여 Statistics를 저장 © 2005 Microsoft

Microsoft Premier Support Services Index 인덱스 구조 Non-Clustered Index Pages Nonclustered Index Clustered Index Non-Leaf Level Index Pages Data Pages Non-Leaf Level Leaf Level (Key Value) Leaf Level © 2005 Microsoft Data Pages

Microsoft Premier Support Services Index Clustered Index sysindexes id indid = 1 root Clustered Index Akhtar … Martin Page 140 - Root Akhtar lastname, firstname Martin SELECT Ganio Smith FROM member … … WHERE lastname = 'Ota' Page 141 Akhtar 2334. . . Barr 5678. . . Con 2534. . . Funk 1334. . . Funk 1534. . . Page 100 Page 145 Ganio 7678. . . Hall 8078. . . Jones 2434. . . Jones 5978. . . Jones 2634. . . Page 110 © 2005 Microsoft Martin 1234. . . Martin 7778. . . Ota 5878. . . Phua 7878. . . Rudd 6078. . . Page 120 Smith 1434. . . Smith 5778. . . Smith 7978. . . White 2234. . . White 1634. . . Page 130

Microsoft Premier Support Services Index Clustered Index v테이블에 1개 만 존재 가능 v. Data 자체가 물리적으로 Disk 드라이브에 정렬 v. Clustered Index의 leaf노드는 실제 data page 따라서 Pointer jump가 필요없고 디스크상에서 대량 범위 처리시(64 KB이상) sequential I/O 작업으로 처리 v대량 범위 처리에 강점 © 2005 Microsoft

Microsoft Premier Support Services Index non-clustered Index sysindexes id indid = 2 root Akhtar. . . Martin SELECT lastname, firstname Page 37 Page 28 Page 12 - Root FROM member Akhtar Martin Ganio WHERE lastname Smith. . . Page 41 Akhtar 4: 706: 01 Barr 4: 705: 03 Con 4: 704: 01 Funk 4: 706: 02 Funk 4: 704: 02 Non-Leaf Clustered clustered Level Index BETWEEN 'Masters' AND 'Rudd' Page 51 Ganio 4: 709: 01 Hall 4: 709: 04 Jones 4: 709: 02 Jones 4: 708: 03 Jones 4: 707: 03 Page 61 Martin 4: 708: 01 Matey 4: 706: 04 Ota 4: 707: 02 Ota Phua 4: 708: 02 Rudd 4: 705: 01 Page 71 Leaf Level Smith 4: 706: 03 (Key Value) Smith 4: 708: 04 Smith 4: 707: 01 White 4: 704: 03 White 4: 705: 02 Heap Page 704 01. . . Conn 02. . . Funk 03. . . White. . . . File ID #4 Page 705 01. . . Rudd 02. . . White 03. . . Barr. . . . Page 706 01. . . Akhtar 02. . . Funk 03. . . Smith 04. . . Matey. . Page 707 01. . . Smith 02. . . Ota 03. . . Jones. . . . © 2005 Microsoft Page 808 01. . . Martin 02 02. . . Phua 03. . . Jones 04. . . Smith. . Page 709 01. . . Ganio 02. . . Jones 03. . . Hall. . . .

Microsoft Premier Support Services Index non-clustered Index v. Table당 249개 까지 생성 가능 v각각의 row에 대해 Pointer jump가 필요 v이 Pointer jump는 디스크상에서 nonsequential I/O 작업 필요 v선택성이 높은 소수의 row fetch에 유용 © 2005 Microsoft

Microsoft Premier Support Services Index non-clustered Index with clustered Index sysindexes Nonclustered Index on First Name id indid = 2 root Non-Leaf Level Aaron. . . Jose Aaron Jose SELECT lastname, firstname, phone Deanna Nina FROM … member … WHERE firstname = 'Mike' Aaron Adam Amie … Con Barr Baldwin … Jose Judy Mike … Leaf Level Lugo Kaethler (Clustered Key Value) Nash … Barr Kim Nagata O’Melia Clustered Index On Last Name Barr Cox Daum … Deanna Daum Don Hall Doug Hampton … … Adam Arlette Deanna … … … Kim Kobara La. Brie … Shane Linda Ryan … © 2005 Microsoft … … Nagata Nash Nixon … Susanne … Mike … Toby … … …

Microsoft Premier Support Services Index Unique Index USE Northwind CREATE UNIQUE NONCLUSTERED INDEX U_Cust. ID ON customers(Customer. ID) Customers Customer. ID Company. Name Contact. Name QUICK BONAP 12 WJKIM QUICK-Stop 김 도엽 Bon app' 김 좌엽 Walking KCC IT Academy 김 우진 … 새로운 row 추가시 중복 허용 안함 WJKIM KCC IT Academy김 우진 © 2005 Microsoft …

Microsoft Premier Support Services Index 복합키의 순서 결정 Composite Index최적의 Column 순서 결정 v. CREATE INDEX문에 기술된 Key Column들의 순서가 중요 u 예: CREATE INDEX test_ind ON test (A, B) ü WHERE A=‘Value’ : efficient ü WHERE A=‘Value’ and B=‘Value’: efficient ü WHERE B=‘Value’ : less efficient v. Query 형태 분석 v. Selectivity 고려 © 2005 Microsoft

Microsoft Premier Support Services Index Covering Index v. Composite Index에 Query검색에 필요한 모든 Column들이 포함되는 특 별한 Nonclustered Index v. 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 © 2005 Microsoft

Microsoft Premier Support Services 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’ © 2005 Microsoft

Microsoft Premier Support Services Index statistics 정보보기 v. DBCC SHOW_STATISTICS(table, index) v. Statistical Information Includes: u마지막 업데이트 시간 u histogram작성시의 샘플 row 수 u Density 정보 u. Average key length u. Histogram step information © 2005 Microsoft

Microsoft Premier Support Services Index Fillfactor 옵션 v. Leaf-Level Page를 얼마의 비율로 채울 것인가를 결정 v초기값에만 영향 Data Pages Full Con. . . 470401 Funk. . . 470402 White. . . 470403 Rudd. . . 470501 White. . . 470502 Barr. . . 470503 Akhtar. . . 470601 Funk. . . 470602 Smith. . . 470603 Martin. . . 470604 Smith. . . 470701 Ota. . . 470702 Martin. . . 470801 Phua. . . 470802 Jones. . . 470803 Smith. . . 470804 Ganio. . . 470901 Jones. . . 470902 Fillfactor 50 = Leaf Pages 50% Full Con. . . 470401 Funk. . . 470402 White. . . 470403 Rudd. . . 470501 White. . . 470502 Barr. . . 470503 Akhtar. . . 470601 Funk. . . 470402 Smith. . . 470603 Martin. . . 470604 Smith. . . 470701 Ota. . . 470702 © 2005 Microsoft Martin. . . 470801 Phua. . . 470802 Jones. . . 470803 Smith. . . 470804 Ganio. . . 470901 White. . . 470902

Microsoft Premier Support Services Index DBCC Showcontig v테이블의 데이터와 인덱스에 대한 단편화 정보를 표시 v. DEMO v실행시기 utable에 과도한 수정이 발생한 경우 u. Table에 많은 양의 data import시 uquery 성능 저하시 © 2005 Microsoft

Microsoft Premier Support Services Index drop_existing 옵션 v. Rebuilding an Index u. Reorganizes leaf pages u. Removes fragmentation u. Recalculates index statistics v. Changing Index Characteristics u. Type u. Index columns u. Options CREATE UNIQUE NONCLUSTERED INDEX U_Ord. ID_Prod. ID ON [Order Details] (Order. ID, Product. ID) WITH DROP_EXISTING, FILLFACTOR=65 © 2005 Microsoft

Microsoft Premier Support Services Index DBCC DBREINDEX v지정한 데이터베이스의 테이블에 대해 하나 이상의 인덱스를 다시 작성 vindex_name을 지정하지 않거나 ' '로 지정하면 테이블의 모든 인덱스가 다시 작성 v. DBCC DBREINDEX ( [ 'database. owner. table_name' [ , index_name [ , fillfactor ] ] ) © 2005 Microsoft

Microsoft Premier Support Services JOIN Join의 종류와 용법 Join Of Two Tables Types of Joins The table accessed first is called “driving(outer)”, the second table is “driven(inner)” v. Nested Loop u. For each row of the outer table find all matching rows in the inner v. Merge Join u. Process both tables in the order of the join columns v. Hash Join u. Build a hash table (from the outer) and pass all rows of the inner through the hash table identifying the matches © 2005 Microsoft

Microsoft Premier Support Services JOIN Join의 종류와 용법 Join Of Two Tables Types of Joins © 2005 Microsoft

Microsoft Premier Support Services JOIN Loop Join SELECT a. FLD 1, . . . , b. FLD 1, . . . FROM TAB 1 a, TAB 2 b WHERE a. KEY 1 = b. KEY 2 AND a. FLD 1 = 'AB' AND b. FLD 2 = '10' FLD 1=' AB' TABLE ACCESS BY ROWID KEY 2= KEY 1 TABLE ACCESS BY ROWID o NESTED LOOPS TABLE ACCESS BY ROWID TAB 1 INDEX RANGE SCAN FLD 1_IDX TABLE ACCESS BY ROWID TAB 2 INDEX UNIQUE SCAN PK_TAB 2 FLD 2 ='10' check o o 운반 단위 x u 순차적 (부분범위처리 가능) u 종속적 (먼저 처리되는 테이블의 INDEX (FLD 1) TAB 1 INDEX (KEY 2) TAB 2 처리범위에 따라 처리량 결정) u 랜덤(Random) 액세스 위주 u 연결고리 상태에 따라 영향이 큼 u 주로 좁은 범위 처리에 유리 특징 및 활용 지침 - Random Block I/O의 부담이 커 다량의 데이터 연결시 치명타 조인의 연결고리 인덱스 전략이 중요 드라이빙 처리 범위의 일량이 전체일량에 영향을 미친다 온라인 TXN 업무 등 소량 데이터 처리와 부분범위 가능한 서비스 © 2005 Microsoft

Microsoft Premier Support Services JOIN Merge Join SELECT /*+ use_merge(a b) */ a. FLD 1, . . . , b. FLD 2, . . . FROM TAB 1 a, TAB 2 b WHERE a. KEY 1 = b. KEY 2 AND a. FLD 1 = 'AB' AND b. FLD 2 = '10' FLD 1= 'AB' MERGE JOIN SORT JOIN u 독립적 (각 처리범위로 전체일량 결정) u 스캔(Scan) 액세스 위주 u 연결고리 상태에 영향이 없음 u 주로 넓은범위 처리에 유리 TABLE ACCESS BY ROWID a. KEY 1= b. KEY 2 를 조건으로 Merge . . . TABLE ACCESS BY ROWID TAB 1 INDEX RANGE SCAN FLD 1_IDX SORT JOIN TABLE ACCESS BY ROWID TAB 2 INDEX RANGE SCAN FLD 2_IDX u 동시적 (무조건 전체범위처리-Sort) TABLE ACCESS BY ROWID S O R T . . . FLD 2= '10' . . . 운반단위 INDEX (FLD 1) TAB 1 TAB 2 INDEX (FLD 2) 특징 및 활용 지침 - Sort의 부담이 있으므로 대용량의 데이터 연결시 Sort부하에 주의 각각의 집합처리 범위를 독립적으로 줄일 수 있을때 유리 통계조회 및 배치처리 등 다량의 데이터 처리로 전체범위 서비스 각 집합 데이터량 범위가 비슷하게 다량이면서 Throughput위주 © 2005 Microsoft

Microsoft Premier Support Services Optimizer Hints Query Hints v. Query hints should be used for special cases—not as standard operating procedure v. Hint Types: u. Table Hints u. Join Hints u. Query Hints © 2005 Microsoft

Microsoft Premier Support Services Optimizer Hints Classification – Example Table Hint SELECT S_NAME, N_NAME FROM SUPPLIER S WITH (INDEX = S_NATIONKEY_IDX) INNER MERGE JOIN NATION N ON (S_NATIONKEY = N_NATIONKEY) JOIN REGION R ON (N_REGIONKEY = R_REGIONKEY AND R_NAME='AFRICA') OPTION (MERGE JOIN, HASH JOIN) WITH (INDEX = S_NATIONKEY_IDX) MERGE Join Hint Query Hint OPTION (MERGE JOIN, HASH JOIN) © 2005 Microsoft

Microsoft Premier Support Services Optimizer Hints Table Hint v. Select * From authors(index(ix_authors)) v. Select * From authors(index(0)) v. Select * From authors(index(1)) v. Select * From authors(index(2, 3)) © 2005 Microsoft

Microsoft Premier Support Services Optimizer Hints Join Hint v. Select title_id, pub_name, title v. From titles INNER HASH JOIN publishers v. ON titles. pub_id = publishers. pub_id v. INNER LOOP JOIN v. INNER MERGE JOIN © 2005 Microsoft

Microsoft Premier Support Services Optimizer Hints Query Hint v그룹화 힌트 : HASH GROUP, ORDER GROUP v. UNION 힌트 : HASH UNION, MERGE UNION, CONCAT UNION v. JOIN 힌트 : LOOP JOIN, HASH JOIN, MERGE JOIN v. FAST n v. FORCE ORDER v. MAXDOP n v. KEEP PLAN v. ROBUST PLAN © 2005 Microsoft

Microsoft Premier Support Services Optimizer Hints Query Hint HINT를 이용한 JOIN의 비용분석 데모 © 2005 Microsoft

Microsoft Premier Support Services © 2005 Microsoft