Microsoft .Net Regional director 운영시스템 튜닝 방법론 김 우 진 ㈜ 데브피아 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

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

Memory 원인분석 Application 처리 로직 DB설계 Index SQL처리문 과도한 재 컴파일 과도한 단편화 등등…. 분석된 원인 처리후에도 병목이라면… 메모리추가

DISK % Disk Read Time % Disk Write Time % Disk Time Avg. Disk Queue Length

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

RAID

Configuration 상의 ISSUE 고정 vs 동적 메모리 할당 DB전용 머신 vs 혼용 서버 파일 그룹 그냥 디폴트로 사용한다? RAID 와 파일 그룹 파일 증가 속성 Log 백업

물리적 저장구조 Database Data (file) .mdf or .ndf Log (file) .ldf Tables, Indexes Data Extent (8개의 연결된 8KB page들) Page (8KB) 한 row의 최대 길이는 8092 bytes

User-defined Filegroup 파일 그룹 의 종류 Northwnd.ldf E:\ User-defined Filegroup Primary Filegroup Transaction Log OrdHist1.ndf OrdHist2.ndf D:\ Northwnd.mdf C:\ sys… sysusers sysobjects … Orders Customers Products OrdHistYear2 OrdHistYear1

RAID와 파일 그룹 MYFILEGROUP DB LOG FileA.mdf FileB.ndf All Tables 18GB drives FileA.mdf FileB.ndf FileC. ndf FileD. ndf FileE. ndf FileF. ndf FileG. ndf FileH. ndf MYFILEGROUP All Tables and all indexes On single filegroup 50% read 50% write DB LOG

Database 운영상의 ISSUE 서비스 팩 적용 유무 단편화 DEAD LOCK BLOCKING ERROR

서비스 팩 적용 유무 ‘hind_’ 로 시작하는 인덱스는 MSSQL 서버의 버그로서 불필요한 리소스를 낭비하게 되고 옵티마이저가 잘못된 판단을 내리게 되는 원인이 될 수 있습니다. -> SP1에서 해결 서비스 팩은 제품 출시 이후 발견된 특수한 문제들에 대한 해결책들을 담고 있으며, 실제로 기술지원 결과 많은 문제들이 서비스 팩 설치만으로 해결되기도 합니다.

단편화 DBCC SHOWCONTIG로 체크 테이블 이름 단편화 율 AASTMST 40% ABALANCE 21.88% ABILLHST 25% ABUDGET 24.32% ABUDGETTEMP ACOSTMST 37.14% ACOSTRATE 33.33% ACUSTBAL 14.53% ADATE AGLBOE 17.07% AGLDTL 18.04% AGLDTLT 41.52% AGLHDR 22.39% AGLHDRH 29.63% DBCC SHOWCONTIG로 체크

단편화 단편화의 영향 과도한 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 use pubs begin tran update employee set lname = 'smith1' where emp_id ='PSA89086M' waitfor delay '00:00:10' update authors set au_lname = 'jones1' where au_id = '172-32-1176‘ commit tran --연결2 update authors set au_lname = 'jones2' where au_id = '172-32-1176' update employee set lname = 'smith2' where emp_id ='PSA89086M' 명령이 성공적으로 완료되었습니다. 서버: 메시지 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 서버의 데이터 액세스 방법 Index 검색 Table Scan Data Pages … Index Pages

Index 장단점 목적 & 장점 단점 Trade-off 검색 속도 증가 공간 점유 유일성 강화 경우에 따라 갱신 속도 향상 (update,delete) 단점 공간 점유 갱신 속도 저하 ( insert ) Trade-off (정답이 없음 - insert도 hot spot시 clustered index로 성능 향상)

B-Tree 구조  $ Insert A B C E F G I J L M D H K O P R T U V X Y Z Q S W

Index 구조 Clustered Index Nonclustered Index Index Pages Non-Leaf Level Leaf Level (Key Value) Data Pages Leaf Level Data Pages

클러스터와 인덱스 비교 클러스터는 인덱스 개념을 데이터 페이지에 적용한 개념 INDEX CLUSTER INDEX TABLE CLUSTERD INDEX TABLE Index Rowid column 999 . . . 111 3 . . . . 123 1 123 10 123 12 . . . . . 123 99 Rowid Columns 1 AB 123 . . . . . . . . . . . 4 CA 354 . . 12 BS 123 . . 3 BB 217 . . 10 BD 123 . . 9 CS 5 . . . . . . . . . . . 99 DD 123 . . 111 1 . . . . 999 . . Cluster Cluster Key Header 123 10 1 AB . . . . . 12 BS . . . . . 10 BD . . . . . . . . . . . . . . 99 DD . . . . . 3 BB . . . . . . . . . . . . . . 10 Cluster Header . . . . . . . . . Rowid Columns

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

Clustered Index 테이블에 1개 만 존재 가능 Data 자체가 물리적으로 Disk 드라이브에 정렬 Clustered Index의 leaf노드는 실제 data page 따라서 Pointer jump가 필요없고 디스크상에서 대량 범위 처리시(64KB이상) sequential I/O 작업으로 처리 대량 범위 처리에 강점 PK는 Clustered ? Index 장점 + scan 장점 = 슈퍼 스타?

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

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 id indid = 2 root Clustered Index On Last Name Nonclustered Index on First Name Non-Leaf Level Leaf Level (Clustered Key Value) Aaron Deanna … ... Jose Nina Don Doug Daum Hall Hampton Adam Amie Con Barr Baldwin Judy Mike Lugo Kaethler Nash Cox Arlette Kim Kobara LaBrie Shane Linda Ryan Nagata Nixon Susanne Toby O’Melia Clustered Index On Last Name Nonclustered Index on First Name Non-Leaf Level Leaf Level (Clustered Key Value) Aaron Deanna … ... Jose Nina Don Doug Daum Hall Hampton Adam Amie Con Barr Baldwin Judy Mike Lugo Kaethler Nash Cox Arlette Kim Kobara LaBrie Shane Linda Ryan Nagata Nixon Susanne Toby O’Melia SELECT lastname, firstname, phone FROM member WHERE firstname = 'Mike' Mike Nash Nagata Nash Mike …

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)

동일한 범위 검색 결과에 대한 처리 경로에 따른 비교용 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 , 이익 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(들) 존재 여부 확인

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 SELECT Cost: 0% Bookmark Lookup Cost: 8% Hash Match Root… Cost 28% Member.corp_no Cost 9% Member.fname Cost: 10% Filter Sequence of Steps Index Seek Scanning a particular range of rows from a 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 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

기타 ISSUE Partitioning Database 설계 Application 처리 로직 예) 웹 로직 예) 공장 등의 Polling 로직등