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

다음 예에서는 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

다음 예에서는 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페이지를 할당했으며 이후 할당을 취소한 페이지가 없습니다. 이는 잠재적 문제를 나타냅니다.

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

SQL Server 프로파일러를 사용하는 대신 다음 예에서와 같이 모든 세션에 대해 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;

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;

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