[MSSQL] TempDB 관련

시스템데이터베이스 중에 유독 사람들에게 관심을 받지 못하는 데이터베이스가 tempDB가 아닌가 생각해본다.하지만, tempDB는 관심을 많이 가져야 할 시스템데이터베이스 중에 하나 이다. 그럼 이 tempDB가 무엇인지 알아보자.

 

tempDB란 말 그대로 임시로 사용되고 있는 데이터베이스라는 말이다. tempDB 시스템 데이터베이스는 SQL Server 인스턴스에 연결된 모든 사용자가 사용할 수 있는 전역 리소스 이고, 사용자 개체, 내부 개체, 버전 저장소를 저장하는 데 사용한다.

 

사용자 개체(User Objects)

사용자 개체는 사용자에 의해 명시적으로 생성되는 것을 말한다. 이러한 개체들은 사용자의 세션 범위나 해당 개체를 만든 루틴 범위에서만 존재한다. 사용자 개체는 아래의 나열된 것에 하나일수 있다.

 

l  사용자 정의 테이블 및 인덱스

l  시스템 테이블 및 인덱스

l  전역 임시 테이블(##<table>) 및 인덱스

l  로컬 임시 테이블(#<table)) 및 인덱스

l  테이블 변수(create table @<table>)

l  테이블 값 함수에서 반환된 테이블

 

내부 개체(Internal Objects)

내부 개체는 SQL Server 엔진에서 T-SQL문을 처리 하기 위해 필요에 따라 자동적으로 생성 및 삭제가 되며, sys.all_objects와 같은 view에서 확인이 되지 않는다. 내부 개체는 아래의 나열된 것에 하나일수 있다.

 

l  Sort 중에 발생하는 중간 결과 값

l  Hash join, hash aggregate 과정에서 발생하는 중간 결과 값

l  XML, LOB(text, image, varchar(MAX)등) 변수 저장

l  중간 결과 값을 저장하기 위해 Spool이 필요한 query

l  Keys을 저장하기 위한 keyset cursor

l  Query 결과를 저장하기 위한 static cursor

l  인덱스 생성 또는 다시 작성시 SORT_IN_TEMPDB가 지정된 경우

l  Group by, order by ,union

 

내부 개체는 IAM 페이지 하나와 8페이지 익스텐트 하나를 포함하여 최소 9페이지를 사용한다.

 

버전 저장소(Version Store)

버전 저장소는 행 버전 관리를 사용하는 기능을 지원하는 데 필요한 데이터 행을 보관하는 데이터 페이지 모음이다. SQL Server 2005에는 일반 저장소와 온라인 인덱스 작성 버전 저장소가 있습니다.

 

l  행 버전 관리 격리 수준을 사용하여 커밋된 읽기 또는 스냅숏을 사용하는 데이터베이스의 데이터 수정 트랜잭션에서 생성된 행 버전

l  온라인 인덱스 작업, MARS(Multiple Active Result Sets) 및 AFTER 트리거 같은 기능에 대한 데이터 수정 트랜잭션으로 생성된 행 버전

 

앞서 이야기 했듯이 tempDB는 SQL Server 인스턴스에 연결된 모든 사용자가 사용하는 전역 리소스 이기 때문에 자칫 잘못하면 tempDB의 공간이 부족한 문제를 유발할 수 있으며, tempDB의 공간이 부족하면 장애가 발생할 수 있으며, 실행중인 응용프로그램이 작업을 완료하지 못할 수도 있다.

 

다음 표를 통해 tempDB이 디스크 공간 부족이 발생했을 때 나타나는 오류 메시지에 대하여 살펴보자.

 

오류	발생 조건
1101 또는 1105	세션에서 tempDB에 공간을 할당해야 하는 경우
3959	버전 저장소가 꽉 찬 경우, 이 오류는 일반적으로 로그에서 1105 또는 1101 오류 다음에 나타난다.
3967	tempDB 가 꽉 차서 버전 저장소를 줄여야 하는 경우
3958 또는 3966	트랜잭션이 tempDB에서 필요한 버전 레코드를 찾을 수 없는 경우

 

tempDB 디스크 공간 모니터링

위와 같은 문제점이 발생하기 전에 우리는 tempDB의 공간을 주기적으로 체크를 해야 할 것이다.

 

tempDB의 공간 확인

다음 쿼리는 tempDB의 모든 파일에서 사용 가능한 전체 빈 페이지 수와 빈 공간(MB)와 tempDB의 모든 파일에 의해 사용되는 전체 디스크 공간을 반환 한다.

SELECT * FROM (              SELECT  SUM(unallocated_extent_page_count) AS [free pages]              ,            (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]              FROM tempdb.sys.dm_db_file_space_usage ) AS A cross JOIN (              SELECT SUM(size)*1.0/128 AS [size in MB]              FROM tempdb.sys.database_files ) AS B

 

내부 개체에 의해 사용되는 공간 확인

다음 쿼리는 tempDB에서 내부 개체에 의해 사용되는 전체 페이지 수와 공간(MB)을 반환 한다.

SELECT  SUM(internal_object_reserved_page_count) AS [internal object pages used] ,            (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB] FROM sys.dm_db_file_space_usage;

 

사용자 개체에 의해 사용되는 공간 확인

다음 쿼리는 tempDB에서 사용자 개체에 의해 사용되는 전체 페이지 수와 공간(MB)을 반환 한다.

SELECT  SUM(user_object_reserved_page_count) AS [user object pages used] ,            (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] FROM sys.dm_db_file_space_usage;

 

그럼, 우리는 tempDB의 최적화를 위해 어떻게 tempDB을 구성 하여야 하는 것 일까? tempDB을 작성 또는 재구성을 할 때 다음과 같은 부분을 참고하여 만들자.

 

l  고성능의 IO Subsystem

l  Ram Disk 또는 Cache가 충분한 IO Subsystem

l  DataFile을 CPU당 한 개로 설정 (듀얼CPU는 CPU 2개로)

 

tempDB 이동

다음 쿼리는 tempDB을 다른 물리적 디스크로 이동하는 쿼리이다.

-- 먼저현재tempDB의위치와이름을확인한다. SELECT * FROM  tempdb.sys.database_files   -- filename에이동할디스크경로를적어준다. ALTER DATABASE tempdb modify FILE (                            name = 'tempdev'              ,            filename = '이동경로'              ,            size = 500MB              ,            filegrowth = 10% ); ALTER DATABASE tempdb modify FILE (                            name = 'templog'              ,            filename = '이동경로'              ,            size = 500MB              ,            filegrowth = 10% );

 

tempDB 파일 추가

다음 쿼리는 tempDB에 파일을 추가하는 것이다. tempDB의 개수는 CPU개수와 동일하게 맞추는 것을Microsoft에서 권장하고 있다. 즉, CPU가 4개이면 tempDB의 데이터베이스 파일의 개수는 mdf 1개와 ndf 3개가 되는 것이다.

 

ALTER DATABASE tempdb ADD FILE (                            name = 'tempdev2'              ,            filename = '생성경로\tempdb2.ndf'              ,            size = 500MB              ,            filegrowth = 10% ); ALTER DATABASE tempdb ADD FILE (                            name = 'tempdev3'              ,            filename = '생성경로\tempdb3.ndf'              ,            size = 500MB              ,            filegrowth = 10% ); ALTER DATABASE tempdb ADD FILE (                            name = 'tempdev4'              ,            filename = '생성경로\tempdb4.ndf'              ,            size = 500MB              ,            filegrowth = 10% );

 

이때 파일들의 size도 동일하게 하여야 스트라이프 효과도 볼 수 있다고 한다.

여기서 복사해옴 http://gdbt.tistory.com/18