[MSSQL] temp DB설정

Temp DB는 MSSQL에서 많은일을 수행합니다. 

MSDN에서 보면 

아래와 같은 역할을 합니다. 

tempdb 시스템 데이터베이스는 SQL Server 인스턴스에 연결된 모든 사용자가 사용할 수 있는 전역 리소스로서 다음 항목을 보관하는 데 사용됩니다

• 전역 또는 로컬 임시 테이블, 임시 저장 프로시저, 테이블 변수, 커서 등 명시적으로 생성된 임시 사용자 개체
• 스풀 또는 정렬의 중간 결과를 저장하기 위한 작업 테이블 등 SQL Server 데이터베이스 엔진에서 만든 내부 개체
• 행 버전 관리 격리를 사용하여 커밋된 읽기 또는 스냅숏 격리 트랜잭션을 사용하는 데이터베이스의 데이터 수정 트랜잭션에서 생성된 행 버전
• 온라인 인덱스 작업, MARS(Multiple Active Result Sets) 및 AFTER 트리거 같은 기능에 대한 데이터 수정 트랜잭션에서 생성된 행 버전

<출처 : MSDN>

음.. 먼가 어렵습니다. 그쵸?

하지만 우리가 중요하게 봐야할 부분은 첫번째 항목과 두번째 항목입니다. 

정리하면 "사용자와 SQL Server가 만든 중간단계의 데이터를 저장하는 곳"이라는 것이지요 

이처럼 중요한 역할을 하는 DB이기 때문에 당연히 관리를 해주거나 모니터를 해줘야합니다. 

아래는 Temp Database의 관리 방법을 정리 한 글입니다. 

--

-- Temp DB

--

           

   Temp DB는 기본 8MB에 자동증가 10%로 최대크기가 정해지지 않는다.

 인스턴스가 시작될때 마다 초기화 되며, 모니터링을 통해서 크기를 정해주는게 자동증가로 인한 포퍼먼스 영향을 회피할 수 있다.

(* temp DB는 축소하지 않는다. 최적의 크기가 최대의 크기기 때문에 줄어들면 또다시 자동증가로 퍼포먼스에 영향을 줄 수 있다.)

TempDB는 코어개수에 맞게 생성해두는게 좋다.

일반적으로 cpu 코어 개수의 1/2만큼으로 생성하고 최적의 개수를 찾아야한다.

             

-- tempdb의 공간 확인

사용가능한 전체 빈 페이지 수와 빈공간(MB)과 사용되는 전체 디스크 공간을 반환

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
GO

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

-- 내부 개체에 의해 사용되는 전체 페이지 수와 공간(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

GO

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

-- 사용자 개체에 의해 사용되는 전체 페이지 수와 공간(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
GO

-- cpu 코어 개수의 1/2 만큼 나누어보자

-- TempDB는 Default로 MSSQL의 DB Dir에 생성됩니다. 하지만 이렇게 생성되면 IO에 문제가 되므로 위치를 다른 HDD로 지정합시다.

ALTER DATABASE tempdb ADD FILE
(
               name = 'tempdev2' ,
                filename = 'D\Temp\tempdb2.ndf',
               size = 2048MB,
               filegrowth = 1024MB
);
ALTER DATABASE tempdb ADD FILE
(
               name = 'tempdev3' ,
                filename = 'D\Temp\tempdb3.ndf',
               size = 2048MB,
               filegrowth = 1024MB
);
ALTER DATABASE tempdb ADD FILE
(
               name = 'tempdev4' ,
                filename = 'D\Temp\tempdb4.ndf',
               size = 2048MB,
               filegrowth = 1024MB
);

-- 원래 있던 tempdb의 위치를 바꿔주자

select * from tempdb .sys. database_files
 
ALTER DATABASE tempdb modify FILE
(
               name = 'tempdev' ,
                filename = 'D\Temp\tempdb.mdf',
               size = 2048MB,
               filegrowth = 1024MB
);
ALTER DATABASE tempdb modify FILE
(
               name = 'tempdev'
                filename = 'D\Tempdb.ldf',
               size = 2048MB,
               filegrowth = 1024MB
);

-- SQL Server 2008 이후버전 부터는 temp의 CHECKSUM 옵션이 자동으로 ON으로 되어있다.

-- Temp DB의 안정성과 데이터 보호를 위해서는 CHECKSUM버전을 ON하는게 좋다. (이전버전에서는 설정을 하자!!)

-- Temp DB의 설정은 재시작해야 적용됩니다.