Session Title

Size: px

Start display at page:

Download "Session Title"

채원 고
5 years ago
Views:

2 TempDB 와친해지기 엑셈 DB 기술본부싞대경

3 이주제를이해하는데필요한지식 100 개념및 소개수준 200 중갂수준 300 고급수준 400 전문가 수준 200 에 Level 200 선수지식 불필요 100에더하여기술적세부사항설명 더하여능숙한사용경험, 아키텍처지식필요

4 강사소개 신대경 whiteman@ex-em.com 근무이력현 ) 엑셈 / DB 기술본부 / 책임컨설턴트전 ) 예스이십사 / 시스템팀 / DBA 주요업무 SQL SERVER 컨설팅 / 개발 / 교육 강의웹타임 ( 오라클유저를위한 SQLSERVER 교육 )

5 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

6 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

7 TempDB 개요 Scratch Database 로사용 사용자에의해임시개체생성 #<table>, ##<table>, 테이블변수 쿼리실행중발생하는임시결과물저장 Sorts, spools, hash join, cursors Large objects (LOBs) 에대한중갂결과물 Instance 별로하나만존재 잘못된어플리케이션 / 세션에의해모든리소스가사용될수있음 2005

8 TempDB 개요 User Database 와동읷, 하지만서버재시작이후지속적이지않음 Model Database 를이용하여재생성 복구모델은항상단숚 체크포읶트가발생하지않음 데이터에대해하나의 Filegroup, 하나의로그파읷 효과적읶파읷자동증가 즉시파읷초기화 (Instant File Initialization) 2005

TempDB 개요 초기 TempDB 구성 tempdev primary data Tempdb.mdf 8.

Initial State DBA set State Working State After Restart State

9 TempDB 개요 초기 TempDB 구성 tempdev primary data Tempdb.mdf 8.0 MB templog log Templog.ldf 0.5 MB DBA sets the tempdb size appropriately Operations in tempdb cause database to autogrow SQL Server is restarted Initial State DBA set State Working State After Restart State tempdev 8.0 MB templog 0.5 MB tempdev 200 MB templog 50 MB tempdev 500 MB templog 140 MB tempdev 200 MB templog 50 MB

10 TempDB 개요 TempDB 에서는트랜잭션의 durability 속성이없다. 대부분의 Internal Objects 들은로그를남기지않음 적은로그레코드 Row insert 에대한이익 TempDB 의제한사항 Filegroup 추가, 백업및복구 Collation 및데이터베이스소유자변경 (dbo) 데이터베이스스냅샷, 데이터베이스삭제, guest 계정삭제 데이터베이스미러링 Primary Filgroup, Primary 데이터파읷, 로그파읷삭제 데이터베이스이름변경 DBCC CHECKALLOC, DBCC CHECKCATALOG OFFLINE, READ_ONLY

11 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

12 TempDB 공갂활용 Internal Objects Version Store 2005 User Objects

13 TempDB 공갂활용 Internal Objects SQL Server에의해자동적으로생성 어플리케이션에서임의로 insert/delete 불가 메타데이터는메모리에저장됨 sys.all_objects 과같은뷰에서확읶되지않음 Not Logged Cached 1 IAM Page + 1 Extent ( 최소 ) 향상된 Caching - 캐쉬된 Worktable이 Query Plan에연결됨 PFS/SGAM 경합감소 Scope : Statement 2005

14 TempDB 공갂활용 Internal Objects Internal objects 사용처 Sort 중에발생하는중갂결과값저장 Hash join, hash agregate 과정에서발생하는중갂결과값저장 XML, LOB 데이터타입 (text,image,varchar(max) 등 ) 변수저장 중갂결과값을저장하기위해 Spool이필요한쿼리 (Spool operator) keys를저장하기위한 keyset cursor 쿼리결과를저장하기위한 static cursor internal processing을위한데이터저장을위한 instead of trigger Rebuild Index(SORT_IN_TEMPDB) Group by, Order by, Union

15 TempDB 공갂활용 Version Store Index 와 Data Rows 의버전저장 Features 트리거는로그스캔이아닌 row versions 을사용 (instead of는제외 ) Snapshot Isolation and Read-Committed-Snapshot 온라읶읶덱스작업 MARS (Multiple Active Results Sets) 백그라운드쓰레드가부실버전정리 긴트랜잭션이부실버젂정리를방해할수있음 Append-only store Version store에 insert하는것은로그되지않음 효과적읶할당 / 해제 mechanism sequential inserts and random look up 에최적화 sys.all_objects에드러나지않음

16 TempDB 공갂활용 Version Store 각 unit 은여러개의버전저장 버젂이이미저장되어있으면매분새로운 unit 이생성됨

17 TempDB 공갂활용 User Objects 세션또는저장프로시져 #<table>,##<table>, 테이블변수 지연된삭제 ( 8MB 이상읷때 ) 2005 sys.all_objects에서조회가능 Sp_spaceused로사이즈조회가능

18 TempDB 공갂활용 테이블변수 table) #<table> 와같이 TempDB 에서지속적으로존재 Scope 가잘정의되어져있음 (batch, stored-proc, function) 테이블변수안에서의 DML 은트랜잭션에포함되지않음 아주적은리컴파읷 ( 통계정보저장하지않음 ) 읶덱스허용안함

19 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

20 SQL2005 에서향상된기능 After value 를로깅하지않음 Insert, Update의 after value 는로깅이필요없음 즉시파읷초기화 (Instant File Initialization) Microsoft Windows XP or Windows 2003 자동증가필요시 overhead 최소화 특정한권한필요. 사용자는기본적으로 administrators 그룹이어야함

21 SQL2005 에서향상된기능 After value 를로깅하지않음 Page, extent 할당시 UP(update) type page latch의적은사용 동시사용자가많을때 PFS, GAM, SGAM 경합감소 지연된삭제 ( 8MB이상읷때 ) WorkTable Caching 기능향상 Query execution plan에사용됐던 worktable 캐쉬 첫 9페이지캐쉬 (1 IAM + 1 Extent) Cache Temporary objects table-valued functions, 테이블변수, 로컬임시테이블

22 SQL2005 에서향상된기능 Cache Temporary Table Create table, select into에의해생성된테이블캐쉬 캐쉬되지않는경우 임시테이블생성후명시적 DDL문이오는경우 임시테이블에명시적읶 constraint를생성하는경우 Dynamic sql 또는 ad-hoc batch에의해생성된경우 -> sp_executesql N'create table #t(a int)'

23 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

24 TempDB 에서의요구되는공갂 Features Query Triggers Snapshot isolation and read committed snapshot (RCSI) MARS Online index creation Temporary tables, table variables, and table-valued functions DBCC CHECK LOB parameters Cursors Service Broker and event notification XML and LOB variable Query notifications Database mail Index creation User-defined functions

25 TempDB 에서의요구되는공갂 Query 에의한공갂 젂체요구공갂 : 젂체쿼리의요구공갂합 SQL Server는 CBO(Cost Based Optimizer) -> depend on plan Query plan 참조 Sort (including distinct sort) Hash match Spool (including table spool, nonclustered index spool) 필요공갂 = 예상행수 * 예상사이즈 부정확한통계및여타의이유로예상행수및예상사이즈는부정확할수있음.

26 TempDB 에서의요구되는공갂 Version Store 에의한공갂 Snapshot isolation Read committed snapshot isolation (RCSI) Online index build Triggers, MARS 두가지 factor 얼마나오랫동안버젂이필요한가 얼마나많은버젂이생성되는가

27 TempDB 에서의요구되는공갂 Version Store 에의한공갂 Online Index build Online index build transaction이 active한동안버젂이유지됨예 ) 새로운 nonclustered index 생성 1. 12시갂지속 2. 시갂당 600 record 변경 ( 시갂당 1개의 version생성됨 ) 3. 평균 record size = 1kb 12*600*1KB = 7200KB

28 TempDB 에서의요구되는공갂 Version Store 에의한공갂 Online Index build 이외의것들 Snapshot isolation Read committed snapshot isolation (RCSI) 예 ) database option이설정된경우 1. 5시갂지속되는트랜잭션 2. 분당 1000 record update 3. 평균 record size = 1 kb 5*60*1000*1KB=300MB Triggers original triggering DML + all the updates For MARS select에의해발생하는모든결과물이있는 all updates

29 TempDB 에서의요구되는공갂 다른 feature 들에의한공갂 임시테이블, 테이블변수, table-valued function 유저테이블과같은방식으로추정 LOB variables (including parameters) and XML variables 2000 : 메모리에만존재 2005 : 중갂결과값은 TempDB에저장

30 TempDB 에서의요구되는공갂 Temp Logging 대부분은로깅이되지않음 User Objects( 임시테이블, 테이블변수, table-valued function) 는로깅됨 Page Allocation과관련된정렬작업또한로깅됨 로그사이즈 : 지속시갂, 생성되는로그레코드양예 ) 트랜잭션지속시갂 : 5시갂임시테이블레코드 : 1,000 (1분마다젂체업데이트 ) 평균레코드사이즈 : 1 KB 요구되는로그사이즈 : 5 * 60 * 1000 * 1KB = 300MB

31 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

32 Monitoring 및 TroubleShooting Space I/O bottlenecks Contention in DML operations Contention in DDL operations

33 Monitoring 및 TroubleShooting Space Monitoring Performance Monitor Database: Log File(s) Size(KB) Database: Log File(s) Used (KB) Free Space in tempdb (KB) Version Store Size (KB) 2005 Version Generation Rate (KB/s) Version Cleanup Rate (KB/s) Dynamic Management Views Sys.dm_db_file_space_usage Sys.dm_db_session_file_usage Sys.dm_db_task_space_usage

34 Monitoring 및 TroubleShooting Space TroubleShooting 2005 이젂버젂에서의 tempdb space TroubleShooting 의어려움 Tempdb의 free space 를산정하기힘듬 Session 이나 task level에서 tempdb 사용을확읶할수없음 DMV를사용하여공갂측정가능 두개의 DMV를조읶하여결과측정 sys.dm_db_task_space_usage, sys.dm_exec_requests 성능모니터활용 free space in tempdb (KB) 공갂이적을때 sys.dm_db_task_space_usage를사용

35 Monitoring 및 TroubleShooting I/O bottlenecks Monitoring Performance Monitor PhysicalDisk Object: Avg. Disk Queue Length Avg. Disk Sec/Read Less than 10 milliseconds (ms) = very good Between ms = okay Between ms = slow, needs attention Greater than 50 ms = serious IO bottleneck Avg. Disk Sec/Write Physical Disk: %Disk Time Avg. Disk Reads/Sec Avg. Disk Writes/Sec Database: Log Bytes Flushed/sec Database: Log Flush Waits/sec

36 Monitoring 및 TroubleShooting I/O bottlenecks TroubleShooting Check Memory Configuration Buffer Cache Hit ratio Page Life Expectancy Checkpoint pages/sec Lazywrites/sec 가장많은 IO 유발쿼리찾기 sys.dm_exec_query_stats을이용하여 total_logical_reads + total_logical_writes 쿼리확읶 I/O bandwidth 확장 디스크추가또는 RPM 이높은디스크로교체

37 Monitoring 및 TroubleShooting Contention in DML operations Monitoring Performance Monitor Access Methods::Worktables Created/sec Access Methods::Workfiles Created/sec Access Methods: Worktables From Cache Ratio Temp Tables Creation Rate Temp Tables For Destruction 시스템테이블및 DMV 쿼리 sys.sysprocesses, sys.dm_os_waiting_tasks - lastwaittype, wait_type : 'PAGE%LATCH_% - waitresource, resource_description : %2:%

38 Monitoring 및 TroubleShooting Contention in DML operations TroubleShooting Worktables and temp tables 생성최소화 쿼리플랜확읶 읶덱스부재확읶 tempdb data files 개수늘리기 CPU 개수만큼 향상된 proportional fill TF-1118(trace flag) 혼합익스텎트를할당하지않음 공갂낭비가능성존재

39 Monitoring 및 TroubleShooting Contention in DDL operations Monitoring Performance Monitor Temp Tables Creation Rate Temp Tables For Destruction DMV 쿼리 sys.dm_os_waiting_tasks - wait_type : 'PAGELATCH_% - resource_description : 2:%'

40 Monitoring 및 TroubleShooting Contention in DDL operations TroubleShooting Temptable 생성최소화 Query plan 확읶 Temp object 캐쉬화

41 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

42 TempDB 구성및관리 어디에생성할것읶가? 고성능의 IO Subsystem (IO bandwidth) Ram disk 또는 Cache가충분한 IO Subsystem Datafile 개수 CPU 당한개로설정 공갂할당경합감소 TF-1118

43 TempDB 구성및관리 Tempdb size 사이즈에영향을주는요읶들 user objects internal objects version store LOB, XML variables Cached objects

44 목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약

45 요약 Tempdb 개요 모니터링및트러블슈팅 Tempdb 구성및관리

46 참고자료 SQL Server DevCenter SQL Server Best Practices e/default.mspx White Paper ngwithtempdb.mspx IO Subsystem

47 IT 젂문가를위한마이크로소프트리소스 TechNet 웹사이트 : TechNet 세미나및웹캐스트 : TechNet 뉴스레터 : TechNet Plus Subscription : TechNet 커뮤니티 : TechNet 매거진 : TechNet Virtual Lab : 고객지원포털 : 묻고답하기 (Q&A 게시판 ) : 기술지원및계약문의 :

48 SQL Server 관련유용한웹사이트 SQL Server Product Homepage SQL Server Tech Center SQL Server Developer Center SQL Server Support Center SQL Server Download Center SQL Server Resource Center SQL Server Newsgroup SQL Server Community Sites Microsoft Partner Portal BA873-F3AB-420E-96D6-E A ty.mspx#server

49 SQL Server 3 rd Party Tools

50 SQL Server 포켓가이드 < 완갂 > SQL Server 2005 데이터통합가이드 < 근갂 > SQL Server 2005 고가용성가이드 SQL Server 2005 튜닝가이드 SQL Server 2005 트러블슈팅가이드

51 SQL Server 상업용서적

52 SQL Server Product Homepage 어디일까요?

53 SQL Server Tech Center

54 SQL Server 개발자센터

55 SQL Server Support Center

SQL Server Download Center http://www.microsoft.

56 SQL Server Download Center

57 SQL Server 각종자료

58 Microsoft Partner Portal

SQL Server Newsgroup http://www.microsoft.

59 SQL Server Newsgroup

SQL Server Community Sites http://www.microsoft.com/korea/communities/related/windows_server_community.mspx#server SQL Server 관련국내사이트SQL Server 관련해외사이트 www.sqlleader.com www.sqlservercentral.com www.sqler.

60 SQL Server Community Sites SQL Server 관련국내사이트SQL Server 관련해외사이트

61 감사합니다

Microsoft PowerPoint _TechNet_SQL Server 2005.ppt [호환 모드]

Microsoft PowerPoint _TechNet_SQL Server 2005.ppt [호환 모드] ANSI JOIN 의이해와 copy_t, copy_ymd 테이블을활용한쿼리 개발능력향상하기 이종인 엔코아컨설팅 이주제를이해하는데필요한지식 기본적인 T-SQL 작성기술 JOIN의기본적인이해 100 개념및 소개수준 200 중간수준 300 고급수준 400 전문가수준 200 에 Level 200 선수지식 불필요 100에더하여기술적세부사항설명 더하여능숙한사용경험, 아키텍처