Transcript SQL2000_Internal.ppt

SQL Server 2000
Internal
데브피아 세미나
정원혁 / MCDBA, MCT
http://mssql.ce.ro
http://www.gtu.co.kr
http://www.inbrein.com
-1-
차례
 페이지
아키텍쳐
 인덱스 아키텍쳐
 튜닝의 방법론/ 과정/ 중요성
 모니터링/ 튜닝 도구 살펴보기
 데이터 가져오기/ 수정하기
-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
Max Number of Columns: 1024
Performance Improvements
 Rows only compacted
when necessary
 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-
페이지 훔쳐보기







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_typeFlagBits = 0x0
m_objId = 2121058592
m_nextPage = (0:0)
m_freeCnt = 3984
m_lsn = (21:133:17)
m_ghostRecCnt = 0
m_headerVersion = 1
m_level = 0
m_indexId = 0
pminlen = 52
m_freeData = 6076
m_xactReserved = 0
m_tornBits = -1918435267
m_type = 1
m_flagBits = 0x4000
m_prevPage = (0:0)
m_slotCnt = 18
m_reservedCnt = 0
m_xdesId = (0:1781)
-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
199cd6de: 39383331 00b71b00 00000000 02faf080
199cd6ee: 00000000 0000000a 00000fff 00000000
199cd6fe: 00008277 69e6ea0d 447ab809 71ce8086
0.T.business
1389............
................
w......i..zD...q
-8-
페이지

페이지 종류
 데이터
 인덱스
 텍스트/이미지
 전역 할당 맵, 보조 전역 할당 맵
 페이지 빈 공간
 인덱스 할당 맵
 대량 변경 맵
 차등 변경 맵
-9-
익스텐트


균일(Uniform)
Contain 8 pages from a single object
혼합(Mixed)
 Can contain pages from up to 8
objects
Extent
(8 pages = 64K)
T1
8K page
T2
T1
Mixed Extent
T3 T4
-10-
익스텐트 할당 및 빈 공간관리



전역 할당 맵(GAM)
공유 전역 할당 맵(SGAM)
64,000 extents에 대한 정보
현재 익스텐트의 사용
GAM
SGAM
비어 있음, 사용 중이지 않음
1
0
균일 익스텐트 또는 완전 혼합 익스텐트
0
0
빈 페이지가 있는 혼합 익스텐트
0
1
-11-
익스텐트 할당 및 빈 공간관리

페이지 여유 공간(PFS) 페이지
 ntext, text 또는 image 열의 개별 페이지가
할당되었는지 여부
 각 페이지의 빈 공간 크기
 1-50% / 51-80% / 81-95% / 96-100%
 이후 8000페이지에 대한 빈공간 정보
-12-
개체에서 사용하는 공간 관리



IAM (Index Allocation Map)
힙 또는 인덱스가 사용하는 데이터베이스 파일의
익스텐트를 매핑
개체 당, 파일 당 적어도 하나 이상의 IAM
-13-
개체에서 사용하는 공간 관리


bitmap 구조
모자랄 때는 다른 IAM으로 링크
-14-
DCM, BCM


차등 변경 맵(DCM)
 마지막 BACKUP DATABASE 문 이후에
변경된 익스텐트
대량 복사 변경 맵(BCM)
 마지막 BACKUP LOG 문 이후에 대량 기록
작업에 의해 수정된 익스텐트
 대량 로그 복구 모델일 때만 사용
-15-
인덱스 아키텍쳐
-16-
데이터 액세스 방식
Table Scan : 모든 Page를 순차적으로 액세스하는 방식
Data
DataPages
Pages
…
Index에 근거한 검색 : Index Page들을 검색하여 조건에 맞는
Key를 찾아 내는 방식 Index Pages
Data 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 구조
Nonclustered Index
Clustered Index
Index Pages
Non-Leaf
Level
Index Pages
Non-Leaf
Level
Leaf Level
(Key Value)
Data Pages
Leaf
Level
Data Pages
-20-
Nonclustered on Clsutered
Clustered Index
Nonclustered Index
Non-Leaf
Level
Index Pages
Non-Leaf
Level
Leaf Level
(Key Value)
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)
go

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
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
9000
= 10000 = 90%
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
-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들
범위에 해당하는 소수의 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


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
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 = 의미 있는 튜닝 결과
추천하는 것을 그대로 믿어서는 안 된다
 좋은 권장 도구로
 이를 근거로 튜닝의 출발점을 삼는다
 또는 튜닝 한 결과의 검증을 삼는다
-64-
데이터 수정과 실제
-65-
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
Intermediate Node
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
WHERE id = object_id('fixed')
-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 ....xxxxxxxxx
-72-
고정 컬럼 vs 가변 컬럼






가변 컬럼은 오버헤드를 지닌다
고정 컬럼에서 NULL은 모두 자리를 차지한다.
부서코드 varchar(2)
주소1 varchar(20)
이름 varchar(10)
사번 char(5)
-73-
데이터삽입 / 페이지분할




절반이 새 페이지로 이동
새 페이지는 같은 extent 에서 먼저 찾고 없으면
새 extent 할당 받는다 (GAM, SGAM 정보 이용)
clustered index 페이지만 분할
heap 의 경우는 PFS 페이지 정보를 가지고 빈
페이지에 삽입
-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
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-
행의 이동

가변 컬럼에서
현재 값보다 더 큰 크기(용량)의 값을 입력
=> 가변 컬럼의 오버헤드 발생

클러스터 인덱스의 키 값의 변화
연속적인 넌 클러스터 색인의 키 값 변화를
피하기 위해

Forward record
Slot 2, Offset 0x1feb
--------------------Record Type = FORWARDING_STUB
19553feb: 00005904 00000100
00
Record Attributes =
.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


in-place :
 같은 페이지, 같은 위치 안에서의 업데이트
non in-place :
 삭제 후 삽입 방식
 클러스터 인덱스의 키 업데이트
 update trigger
 복제에서의 게시
-80-
정리





적절한 도구를 사용
기초 자료를 확보
다 믿지는 말자
의미 있는 것을 모니터/ 캡쳐 하자
계속 부단히 공부: 온라인 설명서
-81-