SQL Server 2000 Internal 데브피아 세미나 정원혁 / MCDBA, MCT http://mssql.ce.ro http://www.gtu.co.kr http://www.inbrein.com -1-
차례 페이지 아키텍쳐 인덱스 아키텍쳐 튜닝의 방법론/ 과정/ 중요성 모니터링/ 튜닝 도구 살펴보기 데이터 가져오기/ 수정하기 데이터 가져오기/ 수정하기 Session1 : 모니터링/ 튜닝 도구 – 프로필러/ 쿼리 분석기/ EM/ 성능 모니터 모니터렁 도구 에 대해 공부합니다. 우선 도구 그 자체를 좀 배워 봅시다. 이미 알고 잇는 것이라면 복습할 시간이 될겁니다. 쿼리 분석기의 사용법을 공부하는 것이 아니라 쿼리 분석기의 튜닝 도구 사용법을 공부합니다. Session2 : 아키텍쳐 – 페이지 / 익스텐트/ 가변컬럼 / 고정컬럼 sql 서버 내부를 탐험해 봅니다. 아키텍쳐를 살펴보고, 실제 컬럼이 어떻게 저장되는지 해부해 봅니다. sql 서버의 해부학 시간입니다. Session3 : 튜닝의 방법론/ 과정/ 중요성 밥먹구 졸리죠? 좀 쉽시다… 이런 저런 튜닝 과 모니터링에 대한 방법론적, 엔지니어의 자세에 대한 이야기를 해봅니다. 그러나 그간 간과 했다면 다시 한번 되돌아, 추스려 봅시다. Session4 : 인덱스 아키텍쳐 인덱스.. 그 것의 아키텍쳐를 살펴봅니다. 세션 2가 기초에 있어야 합니다. 인덱스가 저장되는 원리, 작동원리, 특성등을 정확하게 살펴봅니다. Session5 : 데이터 가져오기/ 수정하기 – 실제 일어나는 일 실제 select/ insert/ update/ delete 문장이 발생할 때 어떤 일이 일어나는지 확인해 봅니다. Forward recored, ghost record 에 대해 알아봅니다. 이런 것들을 어떻게 해야 빠른 성능을 내게 할 수 있는지 알아봅니다. -2-
Questions: How many of you... 인덱스 구조 / 인덱스 사용 - 클러스터/ 넌 클러스터 기초 과정/ 중급 과정/ 고급 과정 인덱스 튜닝 마법사/ 프로필러 Inside SQL 2000 -3-
페이지 아키텍쳐 -4-
Pages Size: 8K, 96 byte header Max Row Size: 8060 Max Key Size: 900 Page header Size: 8K, 96 byte header Max Row Size: 8060 Max Key Size: 900 Max Number of Columns: 1024 Performance Improvements Rows only compacted when necessary Slot array used for binary search Torn Page Detection Torn bits: 011011000….. Row A Row C Row B dbcc page Offset Slot array 460 200 100 -5-
페이지 훔쳐보기 DBCC TRACEON (3604) DBCC PAGE (dbname, file번호, page번호, 옵션) DBCC TRACEOFF (3604) 예) dbcc page (pubs, 1, 205, 1) SELECT first FROM SYSINDEXES WHERE ID = object_id('titles') SELECT convert(int, 0xcd) 옵션 0: 헤더만 1: 행 단위로 2: 페이지 그대로 3: 행 / 그리고 컬럼 값 -6-
페이지 헤더 PAGE: (1:205) --------------- bpage = 0x199CC000 bhash = 0x00000000 bpageno = (1:205) PAGE HEADER: Page @0x199CC000 ---------------- m_pageId = (1:205) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4000 m_objId = 2121058592 m_indexId = 0 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 52 m_slotCnt = 18 m_freeCnt = 3984 m_freeData = 6076 m_reservedCnt = 0 m_lsn = (21:133:17) m_xactReserved = 0 m_xdesId = (0:1781) m_ghostRecCnt = 0 m_tornBits = -1918435267 m_objId = 2121058592/ m_indexId = 0 / m_prevPage = (0:0)/ m_nextPage = (0:0) pminlen = 52 byte 최소 즉 고정 컬럼 / m_slotCnt = 18 행/ m_freeCnt = 3984 사용가능 m_freeData = 6076 byte / m_lsn = (21:133:17) m_ghostRecCnt = 0 m_tornBits = -1918435267 -7-
페이지 내용 Allocation Status ----------------- GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED DATA: ----- Slot 0, Offset 0x16ce --------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 199cd6ce: 00540030 69737562 7373656e 20202020 0.T.business 199cd6de: 39383331 00b71b00 00000000 02faf080 1389............ 199cd6ee: 00000000 0000000a 00000fff 00000000 ................ 199cd6fe: 00008277 69e6ea0d 447ab809 71ce8086 w......i..zD...q -8-
페이지 페이지 종류 데이터 인덱스 텍스트/이미지 전역 할당 맵, 보조 전역 할당 맵 페이지 빈 공간 인덱스 할당 맵 대량 변경 맵 차등 변경 맵 -9-
익스텐트 균일(Uniform) Contain 8 pages from a single object 혼합(Mixed) Can contain pages from up to 8 objects 8K page Extent (8 pages = 64K) T1 T2 Mixed Extent T3 T4 실제 예제 만들것 -10-
익스텐트 할당 및 빈 공간관리 전역 할당 맵(GAM) 공유 전역 할당 맵(SGAM) 64,000 extents에 대한 정보 현재 익스텐트의 사용 GAM SGAM 비어 있음, 사용 중이지 않음 1 균일 익스텐트 또는 완전 혼합 익스텐트 빈 페이지가 있는 혼합 익스텐트 -11-
익스텐트 할당 및 빈 공간관리 페이지 여유 공간(PFS) 페이지 ntext, text 또는 image 열의 개별 페이지가 할당되었는지 여부 각 페이지의 빈 공간 크기 1-50% / 51-80% / 81-95% / 96-100% 이후 8000페이지에 대한 빈공간 정보 1-50% / 51-80% / 81-95% / 96-100% 에 대한 추가 정보 필요 -12-
개체에서 사용하는 공간 관리 IAM (Index Allocation Map) 힙 또는 인덱스가 사용하는 데이터베이스 파일의 익스텐트를 매핑 개체 당, 파일 당 적어도 하나 이상의 IAM -13-
개체에서 사용하는 공간 관리 bitmap 구조 모자랄 때는 다른 IAM으로 링크 -14-
DCM, BCM 차등 변경 맵(DCM) 마지막 BACKUP DATABASE 문 이후에 변경된 익스텐트 마지막 BACKUP LOG 문 이후에 대량 기록 작업에 의해 수정된 익스텐트 대량 로그 복구 모델일 때만 사용 -15-
인덱스 아키텍쳐 -16-
데이터 액세스 방식 … Table Scan : 모든 Page를 순차적으로 액세스하는 방식 Index에 근거한 검색 : Index Page들을 검색하여 조건에 맞는 Key를 찾아 내는 방식 Table Scan : 모든 Page를 순차적으로 액세스하는 방식 Data Pages … Index Pages -17-
Heap -18-
Index 분류 SQL Server Index Type Clustered Index Non-clustered Index Uniqueness Unique Index Non-unique Index Column 개수 Single-Column Index Composite Index -19-
Index 구조 Clustered Index Nonclustered Index Index Pages Non-Leaf Level Leaf Level (Key Value) Index Pages Non-Leaf Level Data Pages Leaf Level Data Pages -20-
Nonclustered on Clsutered Clustered Index Nonclustered Index Non-Leaf Level Leaf Level (Key Value) Index Pages Non-Leaf Level Data Pages Leaf Level -21-
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) NC: cluster key 값 포함하고 있음을 기억하자 -22-
Indexing 기본 원칙 데이터에 대한 이해 검색 제한 Selectivity (선택성) 확인 Table에 대한 Query 형태 분석 Query들의 우선 순위 확인 Composite Index-최적의 Column 순서 결정 -23-
데이터에 대한 이해 Logical Design과 Physical Design 데이터 특성 어떻게 데이터가 저장되는지 수행되는 query들의 형태 전형적으로 수행되는 query들의 수행주기 -24-
검색 제한 Search Arguments 사용 최적의 Search Arguments 작성 Query에서 WHERE 절을 지정 WHERE절이 row의 개수를 제한하는지 확인 Query에서 참조되는 모든 Table에 대한 구문을 검증 Leading wildcard의 사용을 자제 -25-
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’ -26-
Selectivity (선택성) 확인 High selectivity Low selectivity member_no 1 2 . last_name first_name Randall Flood Joshua Kathie 10000 Anderson Bill SELECT * FROM member WHERE member_no > 8999 1000 10000 = 10% Number of rows meeting criteria Total number of rows in table = Low selectivity SELECT * FROM member WHERE member_no < 9001 9000 10000 = 90% -27-
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 고려 -28-
Index를 생성하면 효과적인 경우 특정 값과 일치하는 소수의 Row를 검색하는데 사용되는 Column들 자주 Join Key로 사용되는 Column들 특정 순서로 조회되는 Column들 (C.I) *** Good Selectivity *** -29-
Good Selectivity Selectivity 와 Density Index의 Selectivity 확인 DBCC SHOW_STATISTICS (table_name, index_name) Statistics 갱신 디폴트 : auto update statistics 수작업 : UPDATE STATISTICS table_name -30-
선택성을 알기 위한 tip select top 100 key, count(*) from table group by key DBCC SHOW_STATISTICS (charge, charge4) -31-
-32-
FILLFACTOR & PAD_INDEX CREATE NONCLUSTERED INDEX zip_ind ON authors (zip) WITH FILLFACTOR = 100 , PAD_INDEX -33-
DESC 인덱스 필요한 경우 SELECT 판매금액, 사원 FROM 판매테이블 ORDER BY 판매금액 DESC, 사원 CREATE INDEX idx ON 판매테이블 (판매금액 DESC, 사원) -34-
인덱스 정보 sysindexes 0: data / heap 1: clustered index 2-251: nonclustered index 255: text /image first / root/ IAM sp_helpindex table -35-
인덱스 조각모음/ 재구성 DBCC SHOWCONFIG(table) DBCC SHOWCONFIG(table, index) DBCC DBREINDEX(table, ‘’, 90) DBCC INDEXDEFRAG(0, ‘table’, 1) -36-
DBCC SHOWCONFIG DBCC SHOWCONTIG이(가) 'Orders' 테이블을 스캔하는 중... 테이블: 'Orders' (21575115); 인덱스 ID: 1, 데이터베이스 ID: 6 TABLE 수준 스캔이 수행되었습니다. - 스캔한 페이지................................: 20 - 스캔한 익스텐트..............................: 5 - 전환된 익스텐트..............................: 4 - 익스텐트 당 평균 페이지 수........................: 4.0 - 스캔 밀도[최적:실제].......: 60.00% [3:5] - 논리 스캔 조각화 상태 ..................: 0.00% - 익스텐트 스캔 조각화 상태 ...................: 40.00% - 페이지 당 사용 가능한 평균 바이트 수............: 146.5 - 평균 페이지 밀도(전체).....................: 98.19% DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오. -37-
튜닝의 방법론 -38-
뭘 튜닝할까? Performance Gains Hardware Windows NT SQL Server Database Application -39-
튜닝 방법론 Top - down Bottom - Up Where is the Bottleneck? 교통문제 해결법 -40-
주요 Parameter들 Advanced Option Max Async I/O (7.0) 디폴트 : 32, 최대:255 sp_configure “show advanced options”, 1 go Max Async I/O (7.0) 디폴트 : 32, 최대:255 Affinity Mask 낮은 번호의 CPU를 사용 Max Server Memory Min Server Memory Memory 예약 -41-
주요 Parameter들 AWE / PAE Fiber mode (Light Weight Pooling) CPU usage 100% context switch / sec : 8000 이상 Query governor cost limit 초 단위 그러나 근사값 실제 측정해 보고… 그 나머지 값들… 특별한 이유가 없다면 건드리지 말자 -42-
Tuning Tool 활용 SQL Profiler 와 Index Tuning Wizard를 주기적으로 연동 활용 Query Analyzer Statistics I/O Graphical showplan table scan index seek index scan ( clustered, nonclustered) join (hash, merge, loop) -43-
Tuning Tool 활용 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 -44-
기타 Issue Deadlocking Blocking Normalization Subquery Cursor View Trigger -45-
모니터링, 튜닝 도구 -46-
모니터링 & 튜닝 도구 Windows 2000 Event Viewer Windows System Monitor with SQL Server Current Activity Window in SQL Server Enterprise Manager Transact-SQL Tools SQL Profiler SQL Query Analyzer -47-
이벤트 표시기 제일 먼저 확인해 보자!! 가득 차지 않도록 주의 설정 변경 주기적 백업 MSDN/ KB 등에서 해당 번호 찾기 -48-
성능 모니터 할 수 있는 일 SQL Server I/O SQL Server memory usage SQL Server user connections SQL Server locking Replication activity 사용법 MMC 의 일부 부하 걸리지 않도록 주의 -49-
현재동작 in EM SQL Server 프로세스 정보 Locks, Blocking, and Deadlocks Managing Locks and Processes 그렇지만 빈번하게 사용하기에는 불편 sp_who / sp_who2/ sp_lock/ sp_block 등으로 대체 -50-
T-SQL 1) 시스템 프로시저 sp_who/ sp_who2 sp_lock sp_block sp_monitor sp_spaceused sp_statistics sp_helpdb -51-
T-SQL 2) 시스템 함수 @@spid @@cpu_busy @@connections @@error @@procid -52-
T-SQL 3) Transact-SQL 문 set statistics io set statistics time set statistics profile set showplan_text -53-
T-SQL 4) DBCC 문 1 HELP TRACEON/ TRACEOFF SQLPERF(LOGSPACE | IOSTATS | LRUSTATS | NETSTATS) OPENTRAN CHECKDB, CHECKFILEGROUP…. INPUTBUFFER / OUTPUTBUFFER PROCCACHE -54-
T-SQL 4) DBCC 문 2 SHOWCONTIG SHOW_STATISTICS TRACESTATUS USEROPTIONS dllname(FREE) / sp_helpextendedproc INDEXDEFRAG PINTABLE / UNPINTABLE CLEANTABLE DROPCLEANBUFFERS / FREEPROCCACHE -55-
T-SQL 5) 추적 플래그 3604/ 3605 1204 2528 3205 주의 : 기술지원 범위 벗어난다. -56-
SQL Profiler 할 수 있는 것 Choose events to monitor Choose trace criteria Choose what data to capture Group data meaningfully 정말 정말 좋은 도구 응용하여 사용하면 효과 200% -57-
SQL Query Analyzer Show Query Execution Plan Show Server Trace Show Server-Side Statistics Show Client-Side Statistics Index Tuning Wizard -58-
프로필러 깊이 알기 -59-
할 수 있는 것 성능이 나쁜 쿼리 – 범인 잡아내기 데드 락 잡아내기 동작 감사 – 특정 로그인에 대해 몰래 카메라 달기 동작 감사 – 특정 로그인에 대해 몰래 카메라 달기 저장 프로시저 성능 모니터 SQL 서버 동작 감사 -60-
주의 할 일 캡쳐가 너무 커지고 복잡하지 않도록 필터 필터의 기법 spid text 이벤트 duration / CPU time 로그인/ user db -61-
팁 테이블로 저장 장: 쿼리 문을 사용 다양한 분석 가능 단: SQL 서버에 부하 > 파일로 먼저 저장한 후 다시 테이블로 저장 id를 저장하면 이름으로 보인다 이벤트 별로 그룹핑 : 특정 이벤트를 쉽게 찾을 수 있다 -62-
추적 재생 replay 가능 모든 문맥이 다 캡쳐 되어야 한다. 실제로 수행된다 !!! 문제 해결에 대단히 도움 -63-
프로파일러의 꽃 – 인덱스 튜닝 마법사 의미 있는 input = 의미 있는 튜닝 결과 추천하는 것을 그대로 믿어서는 안 된다 프로파일러의 꽃 – 인덱스 튜닝 마법사 의미 있는 input = 의미 있는 튜닝 결과 추천하는 것을 그대로 믿어서는 안 된다 좋은 권장 도구로 이를 근거로 튜닝의 출발점을 삼는다 또는 튜닝 한 결과의 검증을 삼는다 -64-
데이터 수정과 실제 -65-
How SQL Server Organizes Data in Rows Header Fixed Data NB VB Variable Data 4 bytes Null Block Variable Block -66-
How SQL Server Organizes text, ntext, and image Data Pointer Data row Root Structure Intermediate Node Intermediate Node block 1 block 2 block 1 block 2 -67-
테이블의 행 구조 Status Bit A / B (1 + 1 byte) 고정 컬럼 길이 (2) 고정길이 데이터(n) 컬럼 수(2) null bitmap (컬럼마다 1bit) 가변 컬럼 수(2) 컬럼 오프셋(2 * 가변길이) 가변 컬럼 데이터 (n) -68-
고정 컬럼 테이블 CREATE TABLE Fixed ( Col1 Char(5) NOT NULL , Col2 int Not null , Col3 Char(3) NOT NULL , Col4 Char(6) NOT NULL , Col5 Float NOT NULL ) SELECT * FROM sysindexes WHERE id = object_id('fixed') SELECT * FROM syscolumns -69-
고정 컬럼 테이블 2 페이지 내용 읽기 : 역순으로 NULL 일 때는 어떻게 저장되나? 주의 깊게 보기 -70-
가변 컬럼 테이블 CREATE TABLE variable ( Col1 Char(3) NOT NULL , Col2 varchar(250) Not null , Col3 varchar(5) NULL , Col4 varChar(20) NOT NULL , Col5 smallint NOT NULL ) go -71-
가변컬럼 테이블2 가변 컬럼 길이 (2 byte) : 3개(0003) 첫 가변 컬럼 끝 위치(2) (010e) 두 번째 가변 컬럼 끝 위치(2) (010e) -- NULL이기 때문에 없음 세번째 가변컬럼 끝 위치(2) (1100) 19af6060: 00090030 7b636261 04000500 010e0003 0...abc{........ 19af6070: 0111010e 78787878 78787878 78787878 ....xxxxxxxxxxxx 19af6060: 00090030 7b636261 04000500 010e0003 0...abc{........ 19af6070: 0111010e 78787878 78787878 78787878 ....xxxxxxxxx -72-
고정 컬럼 vs 가변 컬럼 가변 컬럼은 오버헤드를 지닌다 고정 컬럼에서 NULL은 모두 자리를 차지한다. 부서코드 varchar(2) 주소1 varchar(20) 이름 varchar(10) 사번 char(5) -73-
데이터삽입 / 페이지분할 절반이 새 페이지로 이동 새 페이지는 같은 extent 에서 먼저 찾고 없으면 새 extent 할당 받는다 (GAM, SGAM 정보 이용) clustered index 페이지만 분할 heap 의 경우는 PFS 페이지 정보를 가지고 빈 페이지에 삽입 demo -74-
힙 에서 행 삭제 자동으로 공간을 당겨 오지 않는다. (새 행 삽입을 위해 공간 필요 시까지) 그 슬롯은 비워둔다 자동으로 공간을 당겨 오지 않는다. (새 행 삽입을 위해 공간 필요 시까지) 그 슬롯은 비워둔다 OFFSET TABLE: ------------- Row - Offset 4 (0x4) - 180 (0xb4) 3 (0x3) - 159 (0x9f) 2 (0x2) - 0 (0x0) 1 (0x1) - 117 (0x75) 0 (0x0) - 96 (0x60) 데모 -75-
인덱스에서의 행 삭제 ghost 레코드 dbcc traceon (2514) – ghost Slot 1, Offset 0x75 ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Slot 2, Offset 0x8a Record Type = GHOST_DATA_RECORD -76-
고스트 레코드 begin tran DELETE FROM smallrows WHERE a = 3 go DBCC PAGE(pubs, 1, 95,1,1) DBCC TRACEON(2514) DBCC CHECKTABLE(smallrows) commit 'smallrows'의 DBCC결과입니다. 1 페이지에 'smallrows' 개체에 대한 행이 4개 있습니다. Ghost Record count = 1 -77-
행의 이동 가변 컬럼에서 현재 값보다 더 큰 크기(용량)의 값을 입력 => 가변 컬럼의 오버헤드 발생 가변 컬럼에서 현재 값보다 더 큰 크기(용량)의 값을 입력 => 가변 컬럼의 오버헤드 발생 클러스터 인덱스의 키 값의 변화 연속적인 넌 클러스터 색인의 키 값 변화를 피하기 위해 Forward record Slot 2, Offset 0x1feb --------------------- Record Type = FORWARDING_STUB Record Attributes = 19553feb: 00005904 00000100 00 .Y....... -78-
행의 이동 Unforwarding 컬럼 크기가 원래 크기에 맞게 축소될 때 데이터베이스가 줄어들 때 포워드 행 수 알아내기 DBCC TRACEON(2509) DBCC CHECKTABLE(table) 2 페이지에 'bigrows' 개체에 대한 행이 5개 있습니다. Forwarded Record count = 1 Ghost Record count = 0 -79-
In-place / Non in-place update 같은 페이지, 같은 위치 안에서의 업데이트 non in-place : 삭제 후 삽입 방식 클러스터 인덱스의 키 업데이트 update trigger 복제에서의 게시 -80-
정리 적절한 도구를 사용 기초 자료를 확보 다 믿지는 말자 의미 있는 것을 모니터/ 캡쳐 하자 계속 부단히 공부: 온라인 설명서 -81-