Transcript Clustered Index

운영시스템 튜닝 방법론
김우진
㈜ 데브피아
DB사업부 / 차장
Microsoft .Net Regional director
MCT & MCDBA
http://www.devpia.com
차례







튜닝?
Hardware 상의 ISSUE
Configuration 상의 ISSUE
Database 운영상의 ISSUE
Index 상의 ISSUE
SQL문 사용의 ISSUE
기타 ( Design ) ISSUE
튜닝?

튜닝 정의
 병목점 처리 -> 튜닝 방법론?

협의의 튜닝 – DB , HW , Programming
광의의 튜닝 – 운영 시스템


현 운영시스템 성능의 장애요소
 지식 인정(X) -> 지식은 부가가치가 아니다?
 열심히 그냥 열심히 …..
튜닝 방법론

바뀌어야 할 3요소 – 시야 , 시각 , 마인드
 튜닝은 기술이 아니라 예술이다.


운영시스템 튜닝을 위한 시야
운영시스템 튜닝을 위한 시각
운영시스템 튜닝을 위한 마인드

Project 구축 시에도…

운영시스템 튜닝을 위한 시야






Hardware
Network
Operating System
Database
Application (Programming)
설계 및 구현 로직
운영시스템 튜닝을 위한 시각

튜너는 엔지니어? 아티스트?

기술 vs 비즈니스
 튜닝이란 효율의 철학이다.
 검은 고양이와 흰 고양이
운영시스템 튜닝을 위한 마인드

튜닝은 전략가를 요한다.

병목자원을 튜닝하기 위한 가장 좋은 방법?

성능을 위해선…
 전략 ( 처리 로직 설계 및 DB 설계 )
 전술 ( SQL 문 & Programming 처리 )
 병력 ( Hardware Resource )

Hint , Tip , Trick -> 기본으로 돌아가자.
튜닝 시 사용할 툴




NT Performance 모니터
SQL Server Profiler
SQL Server Index Tunning Wizard
SQL Server Query Analyzer
Hardware 상의 ISSUE

NT Performance 모니터 사용

CHECKPOINT
 CPU
 Memory
 DISK
 NETWORK

RAID?
CPU

프로세서 모니터링
 Processor
 % Processor Time
 % Privileged Time
 % User Time
 Processor Queue Length
카운터
최대
평균
기준 값
비교
% Processor Time
95.5286
88.8596
80% 이하
비정상
% Privileged Time
12.1748
3.8481
80% 이하
정상
% User Time
99.3538
85.0115
80% 이하
비정상
Processor Queue Length
18.00
8.40
2.0000
CPU 병목현상
CPU

원인분석
 Application 처리 로직
 DB설계
 Index
 SQL처리문
 과도한 Hash 조인
 과도한 재 컴파일 등등….

분석된 원인 처리후에도 병목이라면…
 프로세서 추가
 더 빠른 프로세서로 교체
 더 많은 캐쉬를 갖고 있는 프로세서로 교체
Memory

메모리 모니터링
 Available Bytes
 Pages/sec (paging in/out)
 Page Reads/sec
 Page Writes/sec
 SQLServer:Buffer Manager->Buffer Cache
Hit Ratio
Memory
카운터
Available Bytes
최대
평균
기준 값
비교
최소 4MB 이상
비정상
26,456,064
3,615,300
167.2521
36.0780
0~20
페이징 과다 추정
Page Reads/sec
59.3582
9.2691
5.0000
읽기 작업이 많음.
Page Writes/sec
0.5976
0.0799
5.0000
정상
Cache Manager : Cache Hit Ratio
53.5569
53.5537
90 이상
병목 현상
Pages/sec
Memory

원인분석
 Application 처리 로직
 DB설계
 Index
 SQL처리문
 과도한 재 컴파일
 과도한 단편화 등등….

분석된 원인 처리후에도 병목이라면…
 메모리추가
DISK




% Disk Read Time
% Disk Write Time
% Disk Time
Avg. Disk Queue Length
카운터
최대
평균
기준 값
비교
% Disk Read Time
11.4405
1.2310
40% 이하
정상
% Disk Write Time
7.5544
1.1944
40% 이하
정상
% Disk Time
13.5231
2.4254
80% 이하
정상
Avg.Disk Queue Length
12.4331
1.5254
2
정상
DISK

원인분석
 Application 처리 로직
 DB설계
 Index
 SQL처리문
 파일그룹 및 파일 위치
 과도한 단편화 등등….

분석된 원인 처리후에도 병목이라면…
 디스크 추가
 RAID 고려
Network

Network이 병목 상태이고 이 운영시스템 서버가
주요 원인이라면..

원인분석
 Application 처리 로직
 DB설계
 Index
 SQL처리문 등등….

분석된 원인 처리 후에도 병목이라면…
 NI 추가
 Subneting
 Network 업그레이드 고려
RAID



RAID는 RAID5?
Workload 분석
DATA와 LOG가 동일?
 Sequential vs Non Sequential
Workload 형태
17%
40%
17%
Select(현재)
Insert(현재)
26%
Update(현재)
Delete(현재)
RAID
Configuration 상의 ISSUE

고정 vs 동적 메모리 할당
 DB전용 머신 vs 혼용 서버

파일 그룹
 그냥 디폴트로 사용한다?


RAID 와 파일 그룹
파일 증가 속성

Log 백업
물리적 저장구조
Database
Data (file)
.mdf or .ndf
Tables, Indexes
Log (file)
.ldf
Extent
(8개의 연결된 8KB page들)
Data
Page (8KB)
한 row의 최대 길이는 8092 bytes
파일 그룹 의 종류
sys…
sys…
sysusers
sysobjects
C:\
…
Orders
Customers
Products
OrdHistYear2
OrdHistYear1
D:\
Northwnd.mdf
OrdHist1.ndf OrdHist2.ndf
Primary Filegroup
User-defined Filegroup
E:\
Northwnd.ldf
Transaction Log
RAID와 파일 그룹
18GB drives
FileA.mdf
FileB.ndf
FileC. ndf
FileD. ndf
50% read
50% write
FileE. ndf
FileF. ndf
FileG. ndf
FileH. ndf
DB LOG
M
Y
F
I
L
E
G
R
O
U
P
All Tables
and all indexes
On single
filegroup
Database 운영상의 ISSUE





서비스 팩 적용 유무
단편화
DEAD LOCK
BLOCKING
ERROR
서비스 팩 적용 유무


‘hind_’ 로 시작하는 인덱스는 MSSQL 서버의
버그로서 불필요한 리소스를 낭비하게 되고
옵티마이저가 잘못된 판단을 내리게 되는 원인이
될 수 있습니다. -> SP1에서 해결
서비스 팩은 제품 출시 이후 발견된 특수한
문제들에 대한 해결책들을 담고 있으며, 실제로
기술지원 결과 많은 문제들이 서비스 팩
설치만으로 해결되기도 합니다.
단편화
DBCC SHOWCONTIG로
체크
테이블 이름
단편화 율
AASTMST
40%
ABALANCE
21.88%
ABILLHST
25%
ABUDGET
24.32%
ABUDGETTEMP
25%
ACOSTMST
37.14%
ACOSTRATE
33.33%
ACUSTBAL
14.53%
ADATE
33.33%
AGLBOE
17.07%
AGLDTL
18.04%
AGLDTLT
41.52%
AGLHDR
22.39%
AGLHDRH
29.63%
단편화

단편화의 영향
 과도한 IO 발생
 CACHE 적중률 저하
 메모리 병목
 성능에 심각한 영향

단편화 처리 - Index Defragmenting vs. Index Rebuilding
 인덱스 재생성 (DBCC DBREINDEX)
 DBCC INDEXDEFRAG

DBCC INDEXDEFRAG이 작업은 잠금을
오래 보유하지 않으므로 실행 중인 쿼리나 업데이트를
차단하지 않고 진행 도중에 언제든지 종료할 수
있으며 완료된 작업은 모두 그대로 보존된다.
DB 성능 모니터링 도구



SQL Profiler
Index Tuning Wizard
Query Analyzer
SQL Profiler

성능이 좋지않은 쿼리를 찾는다.

교착 상태를 발견한다.

저장 프로시저 성능을 모니터링한다.

Microsoft® SQL Server™ 동작 감사

사용자 당 Transact-SQL 동작을 모니터링한다.
DEAD LOCK
시스템에 심각한 부하
빈번한 발생은 반듯이 원인 규명 및 해결
DEAD LOCK
--연결1
--연결2
use pubs
use pubs
begin tran
begin tran
update employee set lname = 'smith1' where
emp_id ='PSA89086M'
update authors set au_lname = 'jones2' where
au_id = '172-32-1176'
waitfor delay '00:00:10'
waitfor delay '00:00:10'
update authors set au_lname = 'jones1' where
au_id = '172-32-1176‘
update employee set lname = 'smith2' where
emp_id ='PSA89086M'
commit tran
commit tran
명령이 성공적으로 완료되었습니다.
서버: 메시지 1205, 수준 13, 상태 50, 줄 1
트랜잭션(프로세스 ID 52)이 (잠금) 리소스에서
다른 프로세스와의 교착 상태가 발생하여 실행이
중지되었습니다.
트랜잭션을 다시 실행하십시오.
DEAD LOCK
BLOCKING



운영중의 시스템에서 체크가 쉽지 않음.
DEAD LOCK 과는 달리 정상적인 LOCK
프로필러에서 실행 기간이 긴 Query들 분석하여
원인 파악.
오류처리



Application에서 발생시키는 오류를 체크
시스템 성능 저하 요소
데이터 무결성에도 악영향
오류처리
Index 상의 ISSUE






Index ?
Clustered vs. NonClustered
PK는 Clustered?
Composite Index
Indexed View
Index Turning Wizard
Index ?






옵티마이저가 최적의 처리 경로를 결정하기
위한 요소
포인터로 직접 엑세스 할 수 없는 RDB의 단점
해소
다수의 애플리케이션을 커버할 수 있도록 고려
Seek, Scan의 의미
B-tree의 구조
인덱스와 클러스터
SQL 서버의 데이터 액세스 방법
Table Scan
Data
DataPages
Pages
…
Index 검색
Index Pages
Data Pages
…
Index 장단점

목적 & 장점
검색 속도 증가
유일성 강화
경우에 따라 갱신 속도 향상 (update,delete)

단점
공간 점유
갱신 속도 저하 ( insert )

Trade-off
(정답이 없음 - insert도 hot spot시 clustered index로
성능 향상)
B-Tree 구조
N
D H K
A B C
E F
G
I
Q S
J
L
 $ Insert
A
C
E F
O P
H
B D
$
M
G
R
T
J
L
U V
X Y
Z
N
K
I
W
Q S
M
O P
R
W
T
U V
X Y
Z
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
클러스터와 인덱스 비교
클러스터는 인덱스 개념을 데이터
페이지에 적용한 개념
INDEX
CLUSTER
CLUSTERD INDEX
INDEX
TABLE
Index Rowid
column
Rowid Columns
111
...
...
..
3
.
.
123
123
123
...
1
10
12
..
123
99
...
...
.
.
999
...
•
•
. .
...
..
1
AB 123
..
4
..
CA 354
. . ...
..
..
12
BS
123
..
3
BB
217
..
10
9
..
BD
CS
. .
123
5
...
..
..
..
99
..
DD 123
. . ...
..
..
Cluster Cluster
Key
Header
111
...
1
.
123
10
...
999
.
..
TABLE
Rowid Columns
3
..
BB
. .
...
...
..
..
10 Cluster Header
1 AB . . . .
. 12 BS . . .
. . 10 BD . .
. ..
..
..
... .
.
DD
.99
.
. .
.. .
. .
... ..
...
. . ..
..
Clustered Index
sysindexes
id
indid = 1
root
Clustered Index
Akhtar
…
Martin
Page 140 - Root
Akhtar
SELECT
lastname, firstname Martin
Ganio
Smith
FROM
member
…
…
WHERE
lastname
=
'Ota'
Page 141
Page 145
Akhtar 2334
Barr
5678
Con
2534
Funk 1334
Funk 1534
...
...
Page 100
...
...
...
...
...
...
Ganio 7678
Hall
8078
Jones 2434
Jones 5978
Jones 2634
...
...
Page 110
...
...
...
...
...
...
Martin 1234
Martin 7778
Ota
5878
Phua 7878
Rudd 6078
...
...
Page 120
...
...
...
...
...
...
Smith 1434
Smith 5778
Smith 7978
White 2234
White 1634
...
...
Page 130
...
...
...
...
...
...
Clustered Index




테이블에 1개 만 존재 가능
Data 자체가 물리적으로 Disk 드라이브에 정렬
Clustered Index의 leaf노드는 실제 data page
따라서 Pointer jump가 필요없고 디스크상에서
대량 범위 처리시(64KB이상) sequential I/O
작업으로 처리
대량 범위 처리에 강점
 PK는 Clustered ?
Index 장점 + scan 장점 = 슈퍼 스타?
Nonclustered Index
sysindexes
id
indid = 2
root
Akhtar
...
Martin
SELECT lastname,
firstname
Page 37
Page
28
Page 12 - Root
AkhtarFROM member
Martin
Ganio
Smith
WHERE
lastname
...
...
Page 41
Akhtar 4:706:01
Barr 4:705:03
Con 4:704:01
Funk 4:706:02
Funk 4:704:02
Non
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
Matey
Ota 4:707:02
Ota
Phua
Phua 4:708:02
Rudd
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 ...
02
... Ota
Ota
03 ... Jones
... ... ...
... ... ...
Page 808
01 ... Martin
02
02 ... Phua
03 ... Jones
04 ... Smith
... ... ...
Page 709
01 ... Ganio
02 ... Jones
03 ... Hall
... ... ...
... ... ...
Nonclustered Index




Table당 249개 까지 생성 가능
각각의 row에 대해 Pointer jump가 필요
이 Pointer jump는 디스크상에서
nonsequential I/O 작업 필요
선택성이 높은 소수의 row fetch에 유용
Logical Read 페이지수 측정



인덱스 없을시 : Logical Read 수 = 테이블의
페이지 수 (sysindexes.dpages)
Clustered 인덱스
 인덱스에 있는 수준들의 수
 스캔 될 Data pages 수
Non-clustered 인덱스
 인덱스에 있는 수준들의 수
 Leaf 페이지의 수
 각 행 검색당 1 logical read
Nonclustered Index with
a 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
…
Daum
Hall
Hampton
…
Jose
Judy
Mike
…
Leaf Level
Lugo
(Clustered
Kaethler
Key Value)
Nash
…
Barr
Kim
Nagata
O’Melia
Clustered Index
On Last Name
Barr
Cox
Daum
…
Deanna
Don
Doug
…
Adam
Arlette
Deanna
…
…
…
…
…
Kim
Kobara
LaBrie
…
Shane
Linda
Ryan
…
…
…
…
…
Nagata
Nash
Nixon
…
Susanne
Mike
Toby
…
…
…
…
…
Composite Index최적의 Column 순서 결정

CREATE INDEX문에 기술된 Key Column들의
순서가 중요
 예: CREATE INDEX test_ind ON test (A, B)





WHERE A=‘Value’ : efficient
WHERE A=‘Value’ and B=‘Value’: efficient
WHERE B=‘Value’ : less efficient
Query 형태 분석
Selectivity 고려
Covering Indexes


Composite Index에 Query검색에 필요한 모든
Column들이 포함되는 특별한 Nonclustered
Index
Covering index의 예:
select col1, col3 from table1
where col2 = ‘value’
go
create index indexname1 on table1
(col2, col1, col3)
go
동일한 범위 검색 결과에 대한
처리 경로에 따른 비교용
SELECT columnA
FROM TableA
WHERE chno BETWEEN 20 AND 30
Access method
Table scan
Clustered index on the chno column
Nonclustered index on the chno column
Composite index on chno , columnA
columns
Page I/O
10,417
1042
100,273
273
SQL 서버 2000의 Index 확장기능
활용



계산된 컬럼에 인덱스 생성
뷰에 인덱스 생성
Indexes with ASC & DESC
계산된 열의 인덱스 예
CREATE TABLE 매출 (
productID int primary key
원가 int
, 판매가 int
, 이익 as (판매가-원가)
)
insert into 매출 values (500,700)
insert into 매출 values (600,850)
insert into 매출 values (400,750)
go
create index ind_매출 on 매출 (이익)
인덱스된 뷰의 활용



인덱스 된 뷰는 데이터베이스 안에 결과 집합을
저장한다.
인덱스의 단점(가상 테이블)을 극복할 수 있는
방안
인덱스 된 뷰의 생성 가이드
 관리 비용을 증가시키므로 신중하게 사용한다.
 기반 데이터가 자주 변경이 되지 않은 테이블에 사용
 많은 조인문들과 집계 쿼리에 유용
Indexed Views Example
--Stmt 1: Create populate table
create table sales (storeID int, qty integer not null,
other_data varchar(20))
--Stmt 2:Create view
CREATE VIEW Store_Sales WITH SCHEMABINDING AS
SELECT StoreId, SUM(qty) Total, COUNT_BIG(*) count
FROM dbo.Sales
GROUP BY StoreId
--Stmt 3: Create index on view
CREATE UNIQUE CLUSTERED INDEX iView
ON Store_Sales(StoreId)
--Stmt 4: Select from base table (will use indexed
view)
SELECT TOP 5 storeId, SUM(Qty) FROM Sales group by
storeId
Indexing 고려사항




Order by
Group by
Distinct
집계 값 자주 접근 등…
-> 인덱스 설계 단에서 반영하면?
-> Workload 고려
Indexing 고려사항






WHERE 절에서 참조 되는 컬럼
Clustered 인덱스 주의 깊게 선택
non-clustered 인덱스의 선택성이 높게
인덱스를 중요한 트랜잭션에 맞추어라
컬럼 순서에 주의
조인에서 사용되는 인덱스 컬럼
Index Tuning Wizard




개별 쿼리 분석 및 index 제시
수집된 workload 분석 및 index 제시
저장된 SQL Scripts 분석 및 index 제시
View에 대한 index 제시
SQL문 ISSUE








Query Optimizer
인덱스 힌트
검색인수
커서
Temp Table
Order by , Group by , Distinct ,
Corelated Subquery
Stored Procedure
Query Analyzer
Query Optimizer의 기능


가장 효율적인 Query Plan을 결정
 Index들이 존재하는지 그리고 유용한지를
확인
 어떤 index나 column이 사용될 수 있는지
확인
 Cost에 근거한 평가방식을 사용
Query Plan을 작성
Query Optimization 단계


Query 분석
 검색 인수 확인
Index 선택
 Index(들) 존재 여부 확인
 유용한 Index(들) 수집
SARG (Search Arguments)
검색인수

검색을 제한할 수 있으며 INDEX를 사용할 수
있는 경우 - Optimizable operators 사용
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’
인덱스 힌트

Index hint ?
권장 안함 – 마지막 방법으로만 사용
커서



Set Oriented vs. Row Oriented
RDBMS 호출비용
한번 더 생각하고…
한번 더 생각 후 사용할 것들..


Temp Table
Order by , Group by , Distinct ,
Corelated Subquery
-> Application 처리 로직 변경
-> DB (Index) 설계에 반영 고려
실행 계획의 캐쉬 이용
 메모리에 실행 계획을 저장한다.
 모든 연속적인 실행을 위해 하나의 계획을 복사하다.
 모든 병렬적 실행을 위해 다른 계획을 복사한다.
 실행 문을 사용한다.
 만약 존재하면, 기존의 실행 계획이 재사용된다.
 만약 존재하지 않으면 새로운 실행 계획이 생성된다.
 실행 계획은 재 컴파일한다.
 데이터베이스의 변경으로 실행 계획이 비효율적이거나 또는
유용하지 않다면 재 컴파일한다.
Stored Procedures







성능 향상 (재 컴파일 비용 감소)
네트워크 트래픽을 감소
2-Tire 방식의 최적화 방안
SQL를 활용한 비즈니스 로직 구현
애플리케이션 로직을 공유
데이터베이스 스키마를 감출 수 있다.
보안 정책을 단순화 한다.
SQL Server Query Analyzer

Query Plan 보기 - 개별 쿼리 분석
 Showplan_all (estimated) , 실제 수행 안함
 Statistics Profile (actual) , 실제 수행
 statistics io
 Set statistics time – 구문 분석 및 컴파일
시간, 실행 시간
 Graphical Showplan
Example Query Plan
Query Plan
Sequence of Steps
Member.corp_no
Cost 9%
SELECT Bookmark Lookup
Cost: 0%
Cost: 8%
Hash Match Root…
Cost 28%
Index Seek
Scanning a particular range of rows from a Filter
Cost: 0%
non-clustered index.
Physical operation:
Logical operation:
Row count:
Estimated row sizes:
I/O cost:
CPU cost:
Number of executes:
Cost:
Subtree cost:
Index Seek
Index Seek
414
24
0.00706
0.000605
1.0
0.007675(6%)
0.00767
Argument:
OBJECT:
([credit].[dbo].[member].[fname]), SEEK:
([member],[firstname] >=‘Rb’ AND
[member],[firstname] <‘T’) ORDERED
Member.fname
Cost: 10%
기타 ISSUE



Partitioning
Database 설계
Application 처리 로직
예) 웹 로직
예) 공장 등의 Polling 로직등

