SQL Server 2000 Internal MCDBA MCT http mssql
SQL Server 2000 Internal 데브피아 세미나 정원혁 / MCDBA, MCT http: //mssql. ce. ro http: //www. gtu. co. kr http: //www. inbrein. com -1 -
Pages v v v Size: 8 K, 96 byte header Max Row Size: 8060 Max Key Size: 900 Max Number of Columns: 1024 Performance Improvements n Rows only compacted when necessary n Slot array used for binary search Torn Page Detection Page header Torn bits: 011011000…. . Row A Row C Row B Offset Slot array 460 200 100 -5 -
페이지 훔쳐보기 v v v v 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, 0 xcd) 옵션 0: 헤더만 1: 행 단위로 2: 페이지 그대로 3: 행 / 그리고 컬럼 값 -6 -
페이지 헤더 PAGE: (1: 205) -------bpage = 0 x 199 CC 000 bhash = 0 x 0000 bpageno = (1: 205) PAGE HEADER: Page @0 x 199 CC 000 --------m_page. Id = (1: 205) m_type. Flag. Bits = 0 x 0 m_obj. Id = 2121058592 m_next. Page = (0: 0) m_free. Cnt = 3984 m_lsn = (21: 133: 17) m_ghost. Rec. Cnt = 0 m_header. Version = 1 m_level = 0 m_index. Id = 0 pminlen = 52 m_free. Data = 6076 m_xact. Reserved = 0 m_torn. Bits = -1918435267 m_type = 1 m_flag. Bits = 0 x 4000 m_prev. Page = (0: 0) m_slot. Cnt = 18 m_reserved. Cnt = 0 m_xdes. Id = (0: 1781) -7 -
페이지 내용 Allocation Status --------GAM (1: 2) = ALLOCATED SGAM (1: 3) = NOT ALLOCATED PFS (1: 1) = 0 x 60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1: 6) = CHANGED ML (1: 7) = NOT MIN_LOGGED DATA: ----Slot 0, Offset 0 x 16 ce ----------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 199 cd 6 ce: 00540030 69737562 7373656 e 2020 199 cd 6 de: 39383331 00 b 71 b 00 0000 02 faf 080 199 cd 6 ee: 0000000 a 00000 fff 0000 199 cd 6 fe: 00008277 69 e 6 ea 0 d 447 ab 809 71 ce 8086 0. T. business 1389. . . . w. . . i. . z. D. . . q -8 -
익스텐트 v v 균일(Uniform) Contain 8 pages from a single object 혼합(Mixed) n Can contain pages from up to 8 objects Extent (8 pages = 64 K) T 1 8 K page T 2 T 1 Mixed Extent T 3 T 4 -10 -
Heap -18 -
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 -19 -
Index 구조 Nonclustered Index Clustered Index Pages Non-Leaf Level (Key Value) Data Pages Leaf Level Data Pages -20 -
Nonclustered on Clsutered Nonclustered Index Clustered Index Non-Leaf Level Index Pages Non-Leaf Level (Key Value) Data Pages Leaf Level -21 -
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 NC: cluster key 값 포함하고 있음을 기억하자 -22 -
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’ -26 -
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 -27 -
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 고려 -28 -
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 -30 -
선택성을 알기 위한 tip v select top 100 key, count(*) from table group by key v DBCC SHOW_STATISTICS (charge, charge 4) -31 -
-32 -
FILLFACTOR & PAD_INDEX v v FILLFACTOR 옵션 PAD_INDEX 옵션 CREATE NONCLUSTERED INDEX zip_ind ON authors (zip) WITH FILLFACTOR = 100 , PAD_INDEX -33 -
인덱스 정보 v sysindexes n 0: data / heap n 1: clustered index n 2 -251: nonclustered index n 255: text /image n v first / root/ IAM sp_helpindex table -35 -
인덱스 조각모음/ 재구성 v v DBCC SHOWCONFIG(table) DBCC SHOWCONFIG(table, index) DBCC DBREINDEX(table, ‘’, 90) DBCC INDEXDEFRAG(0, ‘table’, 1) -36 -
Performance Gains 뭘 튜닝할까? Hardware Windows NT SQL Server Database Application -39 -
튜닝 방법론 v v v Top - down Bottom - Up Where is the Bottleneck? n 교통문제 해결법 -40 -
주요 Parameter들 v Advanced Option sp_configure “show advanced options”, 1 go v v v Max Async I/O (7. 0) n 디폴트 : 32, 최대: 255 Affinity Mask n 낮은 번호의 CPU를 사용 Max Server Memory Min Server Memory 예약 -41 -
주요 Parameter들 v v AWE / PAE Fiber mode (Light Weight Pooling) n CPU usage 100% n context switch / sec : 8000 이상 Query governor cost limit n 초 단위 그러나 근사값 n 실제 측정해 보고… 그 나머지 값들… 특별한 이유가 없다면 건드리 지 말자 -42 -
Tuning Tool 활용 v v SQL Profiler 와 Index Tuning Wizard를 주기 적으로 연동 활용 Query Analyzer n Statistics I/O n Graphical showplan n table scan n index seek n index scan ( clustered, nonclustered) n join (hash, merge, loop) -43 -
Tuning Tool 활용 v SQL Server Performance Monitor 활용 n Bottleneck 감지 n 주요 counter들 n n n (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 v v v v Deadlocking Blocking Normalization Subquery Cursor View Trigger -45 -
모니터링 & 튜닝 도구 v v v 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 -
성능 모니터 v v 할 수 있는 일 n SQL Server I/O n SQL Server memory usage n SQL Server user connections n SQL Server locking n Replication activity 사용법 n MMC 의 일부 n 부하 걸리지 않도록 주의 -49 -
현재동작 in EM v v SQL Server 프로세스 정보 Locks, Blocking, and Deadlocks Managing Locks and Processes 그렇지만 빈번하게 사용하기에는 불편 n sp_who / sp_who 2/ sp_lock/ sp_block 등 으로 대체 -50 -
T-SQL v 1) 시스템 프로시저 n sp_who/ sp_who 2 n sp_lock n sp_block n sp_monitor n sp_spaceused n sp_statistics n sp_helpdb -51 -
T-SQL v 2) 시스템 함수 n @@spid n @@cpu_busy n @@connections n @@error n @@procid -52 -
T-SQL v 3) Transact-SQL 문 n set statistics io n set statistics time n set statistics profile n set showplan_text -53 -
T-SQL v 4) DBCC 문 1 n HELP n TRACEON/ TRACEOFF n SQLPERF(LOGSPACE | IOSTATS | LRUSTATS | NETSTATS) n OPENTRAN n CHECKDB, CHECKFILEGROUP…. n INPUTBUFFER / OUTPUTBUFFER n PROCCACHE -54 -
T-SQL v 4) DBCC 문 2 n SHOWCONTIG n SHOW_STATISTICS n TRACESTATUS n USEROPTIONS n dllname(FREE) / sp_helpextendedproc n INDEXDEFRAG n PINTABLE / UNPINTABLE n CLEANTABLE n DROPCLEANBUFFERS / FREEPROCCACHE -55 -
SQL Profiler v v v 할 수 있는 것 n Choose events to monitor n Choose trace criteria n Choose what data to capture n Group data meaningfully 정말 정말 좋은 도구 응용하여 사용하면 효과 200% -57 -
SQL Query Analyzer v v v Show Query Execution Plan Show Server Trace Show Server-Side Statistics Show Client-Side Statistics Index Tuning Wizard -58 -
How SQL Server Organizes Data in Rows Data Header Fixed Data NB VB Variable Data 4 bytes Null Block Variable Block -66 -
How SQL Server Organizes text, ntext, and image Data Text Pointer Data row Root Structure Intermediate Node block 1 block 2 -67 -
고정 컬럼 테이블 CREATE TABLE Fixed ( Col 1 Char(5) NOT NULL , Col 2 int Not null , Col 3 Char(3) NOT NULL , Col 4 Char(6) NOT NULL , Col 5 Float NOT NULL ) SELECT * FROM sysindexes WHERE id = object_id('fixed') SELECT * FROM syscolumns WHERE id = object_id('fixed') -69 -
가변 컬럼 테이블 CREATE TABLE variable ( Col 1 Char(3) NOT NULL , Col 2 varchar(250) Not null , Col 3 varchar(5) NULL , Col 4 var. Char(20) NOT NULL , Col 5 smallint NOT NULL ) go -71 -
인덱스에서의 행 삭제 v v ghost 레코드 dbcc traceon (2514) – ghost Slot 1, Offset 0 x 75 ---------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Slot 2, Offset 0 x 8 a ---------Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP -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 -
- Slides: 81