아티클
09/27/2010

tempdb의 디스크 공간 부족 문제 해결

이 항목에서는 tempdb 데이터베이스에 디스크 공간이 부족하여 발생한 문제를 진단 및 해결하기 위한 절차와 권장 사항에 대해 설명합니다. tempdb에 디스크 공간이 부족하면 SQL Server 프로덕션 환경에 중요한 장애가 발생할 수 있으며 실행 중인 응용 프로그램이 작업을 완료하지 못할 수 있습니다.

tempdb 공간 요구 사항

tempdb 시스템 데이터베이스는 SQL Server 인스턴스에 연결된 모든 사용자가 사용할 수 있는 전역 리소스입니다. tempdb 데이터베이스는 사용자 개체, 내부 개체 및 버전 저장소를 저장하는 데 사용합니다.

sys.dm_db_file_space_usage 동적 관리 뷰를 사용하여 tempdb 파일에서 사용자 개체, 내부 개체 및 버전 저장소에 사용되는 디스크 공간을 모니터링할 수 있습니다. 또한 sys.dm_db_session_space_usage 및 sys.dm_db_task_space_usage 동적 관리 뷰를 사용하면 세션이나 태스크 수준에서 tempdb의 페이지 할당 또는 할당 취소 작업을 모니터링할 수 있습니다. 이러한 뷰를 사용하면 tempdb 디스크 공간을 많이 사용하는 큰 쿼리, 임시 테이블 또는 테이블 변수를 식별할 수 있습니다.

tempdb 디스크 공간 문제 진단

다음 표에는 tempdb 테이블의 디스크 공간 부족을 나타내는 오류 메시지가 나열됩니다. 이러한 오류는 SQL Server 오류 로그에서 확인할 수 있으며 실행 중인 응용 프로그램에 반환될 수도 있습니다.

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

tempdb 디스크 공간 문제는 또한 자동 증가로 설정된 데이터베이스에서 데이터베이스 크기가 빠르게 증가할 때에도 나타납니다.

tempdb 디스크 공간 모니터링

다음 예에서는 tempdb에서 사용 가능한 공간과 버전 저장소 및 내부 개체와 사용자 개체에서 사용되는 공간을 확인합니다.

tempdb의 빈 공간 확인

다음 쿼리는 tempdb의 모든 파일에서 사용 가능한 전체 빈 페이지 수와 빈 공간(MB)을 반환합니다.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

버전 저장소에 의해 사용되는 공간 확인

다음 쿼리는 tempdb에서 버전 저장소에 의해 사용되는 전체 페이지 수와 공간(MB)을 반환합니다.

SELECT 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]
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;

온라인 인덱스 작업과 관련되지 않은 오랫동안 실행되는 트랜잭션에는 큰 버전 저장소가 필요합니다. 이 버전 저장소는 트랜잭션이 시작된 이후부터 생성된 모든 버전 정보를 유지합니다. 온라인 인덱스 작성 트랜잭션은 완료하는 데 오랜 시간이 걸릴 수 있지만 온라인 인덱스 작업 전용의 개별적인 버전 저장소가 사용됩니다. 따라서 이러한 작업은 다른 트랜잭션의 버전이 제거되지 않도록 방지하지 않습니다. 자세한 내용은 행 버전 관리 리소스 사용을 참조하십시오.

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

다음 쿼리는 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의 모든 파일에 의해 사용되는 전체 디스크 공간을 반환합니다.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

쿼리에 의해 사용되는 공간 모니터링

tempdb 공간 사용에 대한 가장 일반적인 문제 중 하나는 많은 공간을 사용하는 큰 쿼리와 관련된 것입니다. 일반적으로 이러한 공간은 작업 테이블 또는 작업 파일과 같은 내부 개체에 사용됩니다. 내부 개체에 의해 사용되는 공간을 모니터링하면 얼마나 많은 공간이 사용되는지 확인할 수 있지만 해당 공간을 사용 중인 쿼리를 직접적으로 식별할 수는 없습니다.

다음과 같은 방법을 사용하면 tempdb에서 가장 많은 공간을 사용 중인 쿼리를 식별할 수 있습니다. 첫 번째 방법은 일괄 처리 수준의 데이터를 검사하며 두 번째 방법보다 데이터가 덜 사용됩니다. 두 번째 방법을 사용하면 디스크 공간을 사용 중인 특정 쿼리, 임시 테이블 또는 테이블 변수를 식별할 수 있지만 이를 위해 더 많은 데이터를 수집해야 합니다.

방법 1: 일괄 처리 수준 정보

일괄 처리 요청에 단지 일부 쿼리만 포함되어 있고 이들 중 하나만 복잡한 쿼리인 경우 일반적으로 이러한 정보만 있으면 특정 쿼리 대신 어떤 일괄 처리에서 공간을 사용 중인지 확인할 수 있습니다.

이 방법을 사용하려면 SQL Server 에이전트 작업이 몇 분 정도의 폴링 간격을 사용하여 sys.dm_db_session_space_usage 및 sys.dm_db_task_space_usage 동적 관리 뷰로부터 폴링을 수행하도록 설정되어야 합니다. 다음 예에서는 3분 간격의 폴링이 사용됩니다. sys.dm_db_session_space_usage에는 현재 활성 태스크의 할당 작업이 포함되지 않기 때문에 두 개의 뷰에서 폴링을 모두 수행해야 합니다. 두 가지 시간 간격에 할당된 페이지 간 차이를 비교하면 간격 사이에 얼마나 많은 페이지가 할당되었는지 계산할 수 있습니다.

다음 예에서는 SQL Server 에이전트 작업에 필요한 쿼리를 제공합니다.

1. 각 세션에서 현재 실행 중인 모든 태스크의 내부 개체에 의해 사용되는 공간 확인

다음 예에서는 all_task_usage 뷰를 만듭니다. 쿼리를 수행하면 뷰가 tempdb에서 현재 실행 중인 모든 태스크의 내부 개체에 의해 사용되는 전체 공간을 반환합니다.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

2. 현재 세션에서 실행 중인 태스크와 완료된 태스크에 대한 내부 개체에 의해 사용되는 공간 확인

다음 예에서는 all_session_usage 뷰를 만듭니다. 쿼리를 수행하면 뷰가 tempdb에서 현재 실행 중인 태스크 및 완료된 태스크의 모든 내부 개체에 의해 사용되는 공간을 반환합니다.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

이러한 뷰를 3분 간격으로 쿼리할 경우 결과 집합은 다음 정보를 제공합니다.

세션 시작 후 오후 5:00에 세션 71에 100페이지가 할당되었으며 100페이지가 할당 취소되었습니다.
세션 시작 후 오후 5:03에 세션 71에 20100페이지가 할당되었으며 100페이지가 할당 취소되었습니다.

이러한 정보를 분석하면 두 측정 값 사이의 차이점을 확인할 수 있습니다. 세션이 내부 개체에 대해 20,000페이지를 할당했으며 이후 할당을 취소한 페이지가 없습니다. 이는 잠재적 문제를 나타냅니다.

[!참고]

데이터베이스 관리자는 3분 이하의 간격으로 좀더 자주 폴링을 수행할지 결정할 수 있습니다. 하지만 3분 이하로 실행되는 쿼리의 경우에는 tempdb에서 많은 공간을 사용하지 않을 것입니다.

해당 시간 동안 실행되는 일괄 처리를 확인하려면 SQL Server 프로파일러를 사용하여 RPC:Completed 및 SQL:BatchCompleted 이벤트 클래스를 캡처합니다.

SQL Server Profiler를 사용하는 대신 다음 예에서와 같이 모든 세션에 대해 3분마다 한 번씩 DBCC INPUTBUFFER를 실행할 수 있습니다.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

방법 2: 쿼리 수준 정보

일부 경우에는 입력 버퍼나 SQL Server ProfilerSQL:BatchCompleted 이벤트를 검토해도 tempdb에서 가장 많은 디스크 공간을 사용 중인 쿼리를 확인할 수 없습니다. 다음 방법을 사용하면 이러한 문제에 대한 해답을 확인할 수 있지만 이를 위해서는 방법 1에서 정의된 프로시저보다 더 많은 데이터를 수집해야 합니다.

이 방법을 사용하려면 sys.dm_db_task_space_usage 동적 관리 뷰로부터 폴링을 수행하는 SQL Server 에이전트 작업을 설정해야 합니다. 방법 1과 비교하여 폴링 간격은 1분으로 짧게 설정해야 합니다. 이렇게 짧은 간격으로 설정하는 이유는 쿼리(태스크)가 현재 실행 중이 아닌 경우 sys.dm_db_task_space_usage가 데이터를 반환하지 않기 때문입니다.

폴링 쿼리에서 sys.dm_db_task_space_usage 동적 관리 뷰에 정의된 뷰는 sys.dm_exec_requests와 연결되어 sql_handle, statement_start_offset, statement_end_offset 및 plan_handle 열을 반환합니다.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

쿼리 계획이 캐시에 있는 경우 쿼리의 Transact-SQL 텍스트와 XML 실행 계획 형식으로 된 쿼리 실행 계획을 언제라도 검색할 수 있습니다. 실행된 쿼리의 Transact-SQL 텍스트를 가져오려면 sql_handle 값과 sys.dm_exec_sql_text 동적 관리 함수를 사용합니다. 쿼리 실행 계획을 가져오려면 plan_handle 값과 sys.dm_exec_query_plan 동적 관리 함수를 사용합니다.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

쿼리 계획이 캐시에 없는 경우 다음 방법 중 하나를 사용하여 쿼리의 Transact-SQL 텍스트와 쿼리 실행 계획을 가져올 수 있습니다.

1. 폴링 방법 사용

all_query_usage 뷰로부터 폴링을 수행하고 다음 쿼리를 실행하여 쿼리 텍스트를 가져옵니다.

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

sql_handle은 각 고유 일괄 처리에 대해 고유해야 하기 때문에 중복된 sql_handle 항목을 저장할 필요가 없습니다.

계획 핸들 및 XML 계획을 저장하려면 다음 쿼리를 실행합니다.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

2. SQL Server 프로파일러 이벤트 사용

sys.dm_exec_sql_text 및 sys.dm_exec_query_plan 함수를 폴링하는 대신 SQL Server Profiler 이벤트를 사용할 수 있습니다. 프로파일러 이벤트를 사용하여 쿼리 계획과 생성된 쿼리 텍스트를 캡처할 수 있습니다. 예를 들어 이벤트 165는 추적, SQL 텍스트, 쿼리 계획 및 쿼리 통계에 대한 성능 통계를 반환합니다.

임시 테이블 및 테이블 변수에 의해 사용된 공간 모니터링

폴링 쿼리와 비슷한 방식을 사용하여 임시 테이블 및 임시 변수에 의해 사용된 공간을 모니터링할 수 있습니다. 임시 테이블이나 임시 변수 내에서 많은 양의 사용자 데이터를 가져오는 응용 프로그램은 tempdb에 공간 사용 문제를 일으킬 수 있습니다. 이러한 테이블 또는 변수는 사용자 개체에 속합니다. sys.dm_db_session_space_usage 동적 관리 뷰에서 user_objects_alloc_page_count 및 user_objects_dealloc_page_count 열을 사용하고 위에 설명된 방법을 사용할 수 있습니다.

세션별 페이지 할당 및 할당 취소 모니터링

다음 표에서는 지정된 세션에 대해 sys.dm_db_file_space_usage, sys.dm_db_session_space_usage 및 sys.dm_db_task_space_usage 동적 관리 뷰에 의해 반환되는 결과를 보여 줍니다. 각 행은 지정된 세션에 대해 tempdb에 있는 할당 또는 할당 취소 작업을 나타냅니다. 이러한 작업은 Event 열에 나열됩니다. 남은 열은 동적 관리 뷰의 열로 반환되는 값을 나타냅니다.

이 시나리오에서는 tempdb 데이터베이스가 할당되지 않은 익스텐트에 있는 872개의 페이지와 사용자 개체에 예약된 익스텐트에 있는 100개의 페이지로 시작한다고 가정합니다. 세션은 사용자 테이블에 대해 10개의 페이지를 할당한 다음 이를 모두 할당 취소합니다. 처음 8개 페이지는 혼합 익스텐트에 있습니다. 남은 2개의 페이지는 단일 익스텐트에 있습니다.

이벤트	dm_db_file_space_usage unallocated_extent_page_count 열	dm_db_file_space_usage user_object_reserved_page_count 열	dm_db_session_space_usage 및 dm_db_task_space_usage user_object_alloc_page_count 열	dm_db_session_space_usage 및 dm_db_task_space_usage user_object_dealloc_page_count 열
시작	872	100	0	0
기존 혼합 익스텐트에서 페이지 1 할당	872	100	1	0
페이지 2-8을 할당하여 새로운 혼합 익스텐트 하나 사용	864	80	8	0
페이지 9를 할당하여 새로운 단일 익스텐트 하나 사용	856	108	16	0
기존 단일 익스텐트에서 페이지 10 할당	856	108	16	0
기존 단일 익스텐트에서 페이지 10 할당 취소	856	108	16	0
페이지 9 할당 취소 및 단일 익스텐트 할당 취소	864	100	16	8
페이지 8 할당 취소	864	100	16	9
페이지 7-1 할당 취소 및 혼합 익스텐트 할당 취소	872	100	16	16

참고 항목

개념

tempdb 성능 최적화

tempdb 데이터베이스

tempdb의 용량 계획

다음을 통해 공유