[Database] [MSSQL] tempdb 공간 관리하기.

* 데이터베이스에서 연산을 할때 tempdb를 사용한다. 때로는 tempdb의 자원이 너무 부족하여 성능저하가 발생할 수도 있다. 이때에는 공간을 늘리는 등의 작업을 통해 해결할 수 있다.

#1. tempdb의 정보를 확인해보자.
 참조1 : https://technet.microsoft.com/ko-kr/library/ms175527(v=sql.105).aspx
 참조2 : https://msdn.microsoft.com/ko-kr/library/ms174397(v=sql.120).aspx

-- FileName : tempdb의 이름
-- PhysicalName : tempdb의 논리적 파일 이름
-- state_desc : tempdb의 현재 상태
-- FileSizeinMB : tempdb에 할당된 공간(MB)
-- AutoGrowth : tempdb에 할당된 공간을 모두 사용 후, 자동 증가 여부
-- GrowthIncrement : tempdb의 자동 증가 방식
SELECT 
    name AS FileName,
 physical_name AS PhysicalName,
 state_desc,
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'OFF'
        WHEN -1 THEN 'ON'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END AutoGrowth,
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'fixed / will not grow'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'in 8-KB pages'
            ELSE 'percentage'
        END
FROM tempdb.sys.database_files;

#2. tempdb의 사용 내역에 대해 확인해보자.
 참조 : https://technet.microsoft.com/ko-kr/library/ms176029(v=sql.105).aspx

-- free pages : tempdb의 모든 파일에서 사용 가능한 전체 빈 페이지 수
-- free space in MB : tempdb의 모든 파일에서 사용 가능한 전체 빈 공간(MB)
-- version store pages used : tempdb에서 버전 저장소에 의해 사용되는 전체 페이지 수
-- version store space in MB : tempdb에서 버전 저장소에 의해 사용되는 전체 공간(MB)
-- internal object pages used : tempdb에서 내부 개체에 의해 사용되는 전체 페이지 수와 공간(MB)
-- internal object space in MB : tempdb에서 내부 개체에 의해 사용되는 전체 공간(MB)
-- user object pages used : tempdb에서 사용자 개체에 의해 사용되는 전체 페이지 수와 공간(MB)
-- user object space in MB : tempdb에서 사용자 개체에 의해 사용되는 전체 공간(MB)
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],
SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB],
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],
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에서 많은 공간을 사용 중인 경우 가장 오랫동안 실행되는 트랜잭션을 확인해야 한다.
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

#3. tempdb의 공간을 늘려보자.
 참조 : https://msdn.microsoft.com/ko-kr/library/bb522469(v=sql.120).aspx

-- tempdb의 dev 공간을 200MB로 늘리고, 자동 증가를 10MB단위로 증가
ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', SIZE = 200MB, FILEGROWTH = 10MB)

#4. tempdb의 물리적 공간을 변경하거나 추가해보자.
 참조 : https://codeclassic.wordpress.com/tag/tempdb/

-- tempdb의 dev 파일을 d:\mssql\tempdb.mdf 로 변경
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')

-- tempdev2라는 새로운 파일을 추가
ALTER DATABASE tempdb ADD FILE (NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf', SIZE = 200MB, FILEGROWTH = 10MB)