TempDB 와친해지기 엑셈 DB 기술본부싞대경
이주제를이해하는데필요한지식 100 개념및 소개수준 200 중갂수준 300 고급수준 400 전문가 수준 200 에 Level 200 선수지식 불필요 100에더하여기술적세부사항설명 더하여능숙한사용경험, 아키텍처지식필요
강사소개 신대경 Email : whiteman@ex-em.com 근무이력현 ) 엑셈 / DB 기술본부 / 책임컨설턴트전 ) 예스이십사 / 시스템팀 / DBA 주요업무 SQL SERVER 컨설팅 / 개발 / 교육 강의웹타임 ( 오라클유저를위한 SQLSERVER 교육 )
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
TempDB 개요 Scratch Database 로사용 사용자에의해임시개체생성 #<table>, ##<table>, 테이블변수 쿼리실행중발생하는임시결과물저장 Sorts, spools, hash join, cursors Large objects (LOBs) 에대한중갂결과물 Instance 별로하나만존재 잘못된어플리케이션 / 세션에의해모든리소스가사용될수있음 2005
TempDB 개요 User Database 와동읷, 하지만서버재시작이후지속적이지않음 Model Database 를이용하여재생성 복구모델은항상단숚 체크포읶트가발생하지않음 데이터에대해하나의 Filegroup, 하나의로그파읷 효과적읶파읷자동증가 즉시파읷초기화 (Instant File Initialization) 2005
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
TempDB 개요 TempDB 에서는트랜잭션의 durability 속성이없다. 대부분의 Internal Objects 들은로그를남기지않음 적은로그레코드 Row insert 에대한이익 TempDB 의제한사항 Filegroup 추가, 백업및복구 Collation 및데이터베이스소유자변경 (dbo) 데이터베이스스냅샷, 데이터베이스삭제, guest 계정삭제 데이터베이스미러링 Primary Filgroup, Primary 데이터파읷, 로그파읷삭제 데이터베이스이름변경 DBCC CHECKALLOC, DBCC CHECKCATALOG OFFLINE, READ_ONLY
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
TempDB 공갂활용 Internal Objects Version Store 2005 User Objects
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
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
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에드러나지않음
TempDB 공갂활용 Version Store 각 unit 은여러개의버전저장 버젂이이미저장되어있으면매분새로운 unit 이생성됨
TempDB 공갂활용 User Objects 세션또는저장프로시져 #<table>,##<table>, 테이블변수 지연된삭제 ( 8MB 이상읷때 ) 2005 sys.all_objects에서조회가능 Sp_spaceused로사이즈조회가능
TempDB 공갂활용 테이블변수 (declare @t table) #<table> 와같이 TempDB 에서지속적으로존재 Scope 가잘정의되어져있음 (batch, stored-proc, function) 테이블변수안에서의 DML 은트랜잭션에포함되지않음 아주적은리컴파읷 ( 통계정보저장하지않음 ) 읶덱스허용안함
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
SQL2005 에서향상된기능 After value 를로깅하지않음 Insert, Update의 after value 는로깅이필요없음 즉시파읷초기화 (Instant File Initialization) Microsoft Windows XP or Windows 2003 자동증가필요시 overhead 최소화 특정한권한필요. 사용자는기본적으로 administrators 그룹이어야함
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, 테이블변수, 로컬임시테이블
SQL2005 에서향상된기능 Cache Temporary Table Create table, select into에의해생성된테이블캐쉬 캐쉬되지않는경우 임시테이블생성후명시적 DDL문이오는경우 임시테이블에명시적읶 constraint를생성하는경우 Dynamic sql 또는 ad-hoc batch에의해생성된경우 -> sp_executesql N'create table #t(a int)'
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
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
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) 필요공갂 = 예상행수 * 예상사이즈 부정확한통계및여타의이유로예상행수및예상사이즈는부정확할수있음.
TempDB 에서의요구되는공갂 Version Store 에의한공갂 Snapshot isolation Read committed snapshot isolation (RCSI) Online index build Triggers, MARS 두가지 factor 얼마나오랫동안버젂이필요한가 얼마나많은버젂이생성되는가
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
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
TempDB 에서의요구되는공갂 다른 feature 들에의한공갂 임시테이블, 테이블변수, table-valued function 유저테이블과같은방식으로추정 LOB variables (including parameters) and XML variables 2000 : 메모리에만존재 2005 : 중갂결과값은 TempDB에저장
TempDB 에서의요구되는공갂 Temp Logging 대부분은로깅이되지않음 User Objects( 임시테이블, 테이블변수, table-valued function) 는로깅됨 Page Allocation과관련된정렬작업또한로깅됨 로그사이즈 : 지속시갂, 생성되는로그레코드양예 ) 트랜잭션지속시갂 : 5시갂임시테이블레코드 : 1,000 (1분마다젂체업데이트 ) 평균레코드사이즈 : 1 KB 요구되는로그사이즈 : 5 * 60 * 1000 * 1KB = 300MB
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
Monitoring 및 TroubleShooting Space I/O bottlenecks Contention in DML operations Contention in DDL operations
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 2005 2005 2005
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를사용
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 10-20 ms = okay Between 20-50 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
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 이높은디스크로교체
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 쿼리 2005 2005 sys.sysprocesses, sys.dm_os_waiting_tasks - lastwaittype, wait_type : 'PAGE%LATCH_% - waitresource, resource_description : %2:%
Monitoring 및 TroubleShooting Contention in DML operations TroubleShooting Worktables and temp tables 생성최소화 쿼리플랜확읶 읶덱스부재확읶 tempdb data files 개수늘리기 CPU 개수만큼 향상된 proportional fill TF-1118(trace flag) 혼합익스텎트를할당하지않음 공갂낭비가능성존재
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:%' 2005 2005
Monitoring 및 TroubleShooting Contention in DDL operations TroubleShooting Temptable 생성최소화 Query plan 확읶 Temp object 캐쉬화
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
TempDB 구성및관리 어디에생성할것읶가? 고성능의 IO Subsystem (IO bandwidth) Ram disk 또는 Cache가충분한 IO Subsystem Datafile 개수 CPU 당한개로설정 공갂할당경합감소 TF-1118
TempDB 구성및관리 Tempdb size 사이즈에영향을주는요읶들 user objects internal objects version store LOB, XML variables Cached objects
목차 TempDB 개요 TempDB 공갂활용 SQL2005에서향상된기능 TempDB에서의요구되는공갂 Monitoring 및 TroubleShooting TempDB 구성및관리 요약
요약 Tempdb 개요 모니터링및트러블슈팅 Tempdb 구성및관리
참고자료 SQL Server DevCenter http://msdn.microsoft.com/sql SQL Server Best Practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractic e/default.mspx White Paper http://www.microsoft.com/technet/prodtechnol/sql/2005/worki ngwithtempdb.mspx IO Subsystem http://support.microsoft.com/kb/917047/en-us
IT 젂문가를위한마이크로소프트리소스 TechNet 웹사이트 : http://www.microsoft.com/korea/technet TechNet 세미나및웹캐스트 : http://www.microsoft.com/korea/technet/events TechNet 뉴스레터 : http://www.microsoft.com/korea/technet/flash TechNet Plus Subscription : http://www.microsoft.com/korea/technet/subscriptions TechNet 커뮤니티 : http://www.microsoft.com/korea/technet/membership TechNet 매거진 : http://www.microsoft.com/technet/technetmag TechNet Virtual Lab : http://www.microsoft.com/technet/virtuallab 고객지원포털 : http://support.microsoft.com/default.aspx/gp/supportportal/ko 묻고답하기 (Q&A 게시판 ) : http://www.microsoft.com/korea/communities/webforum/webforum2.mspx 기술지원및계약문의 : 1577-9700
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 http://www.microsoft.com/korea/sql http://www.microsoft.com/korea/technet/prodtechnol/sql http://www.microsoft.com/korea/msdn/sql http://www.support.microsoft.com/ph/2855 http://www.microsoft.com/downloads/browse.aspx?displaylang=ko&productid= 261BA873-F3AB-420E-96D6-E3004596A551 http://www.microsoft.com/korea/sql/prodinfo/sql2005_resources.mspx http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx http://www.microsoft.com/korea/communities/related/windows_server_communi ty.mspx#server http://partner.microsoft.co.kr/pds/mp_pds.asp
SQL Server 3 rd Party Tools
SQL Server 포켓가이드 < 완갂 > SQL Server 2005 데이터통합가이드 < 근갂 > SQL Server 2005 고가용성가이드 SQL Server 2005 튜닝가이드 SQL Server 2005 트러블슈팅가이드
SQL Server 상업용서적
SQL Server Product Homepage 어디일까요? http://www.microsoft.com/korea/sql
SQL Server Tech Center http://www.microsoft.com/korea/technet/prodtechnol/sql
SQL Server 개발자센터 http://www.microsoft.com/korea/msdn/sql
SQL Server Support Center http://www.support.microsoft.com/ph/2855
SQL Server Download Center http://www.microsoft.com/downloads/browse.aspx?displaylang=ko&productid=261ba873-f3ab-420e-96d6-e3004596a551
SQL Server 각종자료 http://www.microsoft.com/korea/sql/prodinfo/sql2005_resources.mspx
Microsoft Partner Portal http://partner.microsoft.co.kr/pds/mp_pds.asp
SQL Server Newsgroup http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
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.pe.kr www.sqljunkies.com www.mssql.org www.sql-server-performance.com www.sqlworld.pe.kr www.microsoft-oracle.com www.olapforum.com www.devpia.com www.mcpworld.com www.dbguide.net www.databaser.net www.analysisservice.net
감사합니다