본문 바로가기

MSSQL/Setting

tempDB 에 관하여

tempDB

 

Gravity DBA 이승연

 

시스템데이터베이스 중에 유독 사람들에게 관심을 받지 못하는 데이터베이스가 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도 동일하게 하여야 스트라이프 효과도 볼 수 있다고 한다.

 

 


'MSSQL > Setting' 카테고리의 다른 글

SQL 2005 SSIS FTP을 이용한 백업 파일 전송  (2) 2009.05.08
배포 데이터베이스 이동 시키기  (0) 2009.04.21