tempdb 데이터베이스

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 SQL 서버, Azure SQL 데이터베이스 또는 Azure SQL Managed Instance의 인스턴스에 연결된 모든 사용자가 사용할 수 있는 전역 리소스인 tempdb 시스템 데이터베이스에 대해 설명합니다.

개요

tempdb 시스템 데이터베이스는 다음을 보유하는 전역 리소스입니다.

• 명시적으로 생성되는 임시 사용자 개체 여기에는 전역 또는 로컬 임시 테이블 및 인덱스, 임시 저장 프로시저, 테이블 변수, 테이블 반환 함수에 반환된 테이블 및 커서가 포함됩니다.

• 데이터베이스 엔진에서 만든 내부 개체 다음이 포함됩니다.

  • 스풀, 커서, 정렬 및 임시 LOB(대규모 개체) 스토리지에 대한 중간 결과를 저장하는 작업 테이블입니다.
  • 해시 조인 또는 해시 집계 작업에 대한 작업 파일
  • 인덱스 만들기 또는 다시 작성하기(SORT_IN_TEMPDB이 지정된 경우), 또는 특정 GROUP BY, ORDER BY, UNION 쿼리와 같은 작업에 대한 중간 정렬 결과

  각 내부 개체는 최소 9페이지(IAM 페이지 및 8페이지 익스텐트)를 사용합니다. 페이지 및 익스텐트에 대한 자세한 내용은 페이지 및 익스텐트를 참조하세요.

• 버전 저장소는 행 버전 관리 기능을 지원하는 데이터 행을 포함하는 데이터 페이지의 컬렉션입니다. 일반 버전 저장소와 온라인 인덱스 작성 버전 저장소의 두 가지 유형이 있습니다. 버전 저장소에는 다음이 포함됩니다.

  • 행 버전 관리 격리 또는 스냅샷 격리 트랜잭션을 통해 READ COMMITTED을(를) 사용하는 데이터베이스의 데이터 수정 트랜잭션에 의해 생성되는 행 버전입니다.
  • 온라인 인덱스 작업, MARS(Multiple Active Result Sets) 및 AFTER 트리거 같은 기능에 대한 데이터 수정 트랜잭션에서 생성된 행 버전

트랜잭션을 롤백할 수 있도록 tempdb 내 작업은 최소 로깅됩니다. 시스템이 항상 깨끗한 데이터베이스 복사본으로 시작되도록 SQL Server를 시작할 때마다 tempdb이(가) 다시 생성됩니다. 임시 테이블 및 저장 프로시저는 연결이 끊어지면 자동으로 삭제되고 시스템이 종료될 때 연결이 활성화되지 않습니다.

tempdb에 있는 어떠한 내용도 SQL Server의 한 세션에서 다른 세션으로 저장되지 않습니다. tempdb에서는 백업 및 복원 작업이 허용되지 않습니다.

SQL Server에서 tempdb의 물리적 속성

다음 표 목록에서는 SQL Server 및 SQL Server의 tempdb 데이터와 로그 파일의 초기 구성 값을 나열합니다. 값은 model 데이터베이스의 기본값을 기반으로 합니다. 이러한 파일의 크기는 SQL Server 버전에 따라 조금씩 다를 수 있습니다.

보조 데이터 파일의 수는 컴퓨터의 (논리적) 프로세서 수에 따라 달라집니다. 일반적으로 논리 프로세서 수가 8보다 작거나 같으면 논리 프로세서와 동일한 개수의 데이터 파일을 사용합니다. 논리 프로세서 수가 8보다 크면 8개의 데이터 파일을 사용합니다. 경합이 지속될 경우, 경합이 허용 가능한 수준으로 감소할 때까지 데이터 파일 수를 4의 배수로 늘리거나, 워크로드/코드를 변경합니다.

데이터 파일 수의 기본값은 KB 2154845 일반적인 지침을 기반으로 합니다.

현재 tempdb의 크기 및 성장 매개 변수를 확인하려면 tempdb.sys.database_files 쿼리를 사용하세요.

SQL Server에서 tempdb 데이터 및 로그 파일 이동

tempdb 데이터 및 로그 파일을 이동하려면 시스템 데이터베이스 이동을 참조하세요.

SQL Server에서 tempdb에 대한 데이터베이스 옵션

다음 표에서는 tempdb 데이터베이스의 각 데이터베이스 옵션에 대한 기본값과 수정 가능 여부를 나열합니다. 이러한 옵션에 대한 현재 설정을 확인하려면 sys.databases 카탈로그 뷰를 사용하세요.

이러한 데이터베이스 옵션에 대한 설명은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

Azure SQL의 tempdb

Azure SQL Database의 tempdb의 동작은 SQL Server, Azure SQL Managed Instance 및 Azure VM의 SQL Server 동작과 다릅니다.

SQL Database의 tempdb

Azure SQL Database의 단일 데이터베이스 및 풀링된 데이터베이스는 데이터베이스 수준을 범위로 하고 tempdb에 저장되는 전역 임시 테이블 및 전역 임시 저장 프로시저를 지원합니다. 전역 임시 테이블 및 전역 임시 저장 프로시저는 동일한 데이터베이스 내의 모든 사용자 세션에 대해 공유됩니다. 다른 데이터베이스의 사용자 세션은 전역 임시 테이블에 액세스할 수 없습니다. 자세한 내용은 데이터베이스 범위 전역 임시 테이블(Azure SQL Database)을 참조하세요.

단일 데이터베이스의 경우 논리 서버의 각 단일 데이터베이스에는 고유한 tempdb이(가) 있습니다. 탄력적 풀에서, tempdb은 같은 풀 내의 모든 데이터베이스를 위한 공유된 리소스이지만, 하나의 데이터베이스에서 생성된 임시 개체는 풀 내의 다른 데이터베이스에서는 보이지 않습니다.

Azure SQL Database의 단일 데이터베이스 및 풀링된 데이터베이스의 경우 모든 시스템 데이터베이스 중에서 master 데이터베이스 및 tempdb 데이터베이스에만 액세스할 수 있습니다. 자세한 내용은 Azure의 논리 서버란?을 참조하세요.

Azure SQL Database의 tempdb 크기에 대해 자세히 알아보려면 다음을 검토하세요.

• vCore 구매 모델: 단일 데이터베이스, 풀링된 데이터베이스
• DTU 구매 모델: 단일 데이터베이스, 풀링된 데이터베이스

SQL Managed Instance의 tempdb

Azure SQL Managed Instance는 동일한 관리되는 인스턴스 내의 모든 사용자 세션에서 모든 전역 임시 테이블 및 전역 임시 저장 프로시저에 액세스할 수 있는 SQL Server와 동일한 방식으로 임시 개체를 지원합니다. 마찬가지로 모든 시스템 데이터베이스에 액세스할 수 있습니다.

tempdb 파일 수, 증가 증분 및 최대 크기를 구성할 수 있습니다. Azure SQL Managed Instance에서 tempdb 설정을 구성하는 방법에 대한 자세한 내용은 Azure SQL Managed Instance에 대한 tempdb 설정 구성을 참조하세요.

Azure SQL Managed Instance의 tempdb 크기에 대해 자세히 알아보려면 리소스 제한을 검토하세요.

제한 사항

다음 작업은 tempdb 데이터베이스에서 수행할 수 없습니다.

• 파일 그룹 추가
• 데이터베이스 백업 또는 복원
• 데이터 정렬 변경 기본 정렬은 서버 정렬입니다.
• 데이터베이스의 소유자 변경 tempdb은(는) sa가 소유합니다.
• 데이터베이스 스냅샷 만들기
• 데이터베이스 삭제
• 데이터베이스에서 guest 사용자 삭제
• 변경 데이터 캡처 활성화
• 데이터베이스 미러링 참여
• 기본 파일 그룹, 기본 데이터 파일 또는 로그 파일을 제거합니다.
• 데이터베이스 또는 주 파일 그룹의 이름을 변경합니다.
• DBCC CHECKALLOC 실행 중.
• DBCC CHECKCATALOG 실행 중.
• 데이터베이스를 OFFLINE으로 설정합니다.
• 데이터베이스 또는 기본 파일 그룹을 READ_ONLY(으)로 설정.

사용 권한

모든 사용자가 tempdb에 임시 개체를 만들 수 있습니다. 사용자가 추가 사용 권한을 받는 경우를 제외하고 자신의 고유 개체에만 액세스할 수 있습니다. 사용자가 tempdb을(를) 사용하지 못하도록 tempdb 연결 권한을 취소할 수 있습니다. 일부 루틴 작업은 tempdb를 사용해야 하기 때문에 권장하지 않습니다.

SQL Server에서 tempdb 성능 최적화

tempdb 데이터베이스의 크기와 물리적인 배치는 시스템 성능에 영향을 줄 수 있습니다. 예를 들어 tempdb에 대해 정의된 크기가 너무 작으면 사용자가 SQL Server 인스턴스를 다시 시작할 때마다 시스템의 처리 로드 중 일부가 작업을 지원하는 데 필요한 크기로 tempdb을(를) 자동 증가시키기 위해 소모될 수 있습니다.

가능하면 인스턴트 파일 초기화를 사용하여 데이터 파일에 대한 증가 작업의 성능을 향상시키세요.

환경의 일반적인 작업량을 수용할 수 있는 값으로 파일 크기를 설정하여 모든 tempdb 파일에 충분한 공간을 미리 할당합니다. 사전 할당은 tempdb이(가) 너무 자주 확장되지 않도록 방지하는데, 이는 성능에 영향을 미칩니다. 예기치 않은 예외 발생 시 디스크 공간을 늘리기 위해 tempdb 데이터베이스가 자동 증가되도록 설정해야 합니다.

SQL Server에서는 여유 공간이 더 많은 파일에서 할당을 선호하는 비례 채우기 알고리즘을 사용하므로, 데이터 파일은 각 filegroup 내에서 동일한 크기여야 합니다. tempdb을(를) 동일한 크기의 여러 데이터 파일로 분할하면 tempdb을(를) 사용하는 작업에서 높은 수준의 병렬 효율성을 얻게 됩니다.

tempdb 데이터베이스 파일이 너무 작은 값으로 증가하지 않도록 파일 증가 증분을 적절한 크기로 설정하고 모든 데이터 파일에 동일한 증분을 설정하세요. tempdb에 기록되는 데이터양에 비해 파일 증가가 너무 작은 경우, tempdb은(는) 자동 증가 이벤트를 통해 자주 확장되어야 할 수도 있습니다. 자동 증가 이벤트는 성능에 부정적인 영향을 줍니다.

tempdb의 현재 크기 및 성장 매개 변수를 확인하려면 다음 쿼리를 사용합니다.

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

tempdb 데이터베이스를 고속 I/O 하위 시스템에 배치합니다. 직접 연결된 디스크가 많은 경우 디스크 스트라이프를 사용합니다. I/O 병목 현상이 발생하지 않는 한 개별 또는 tempdb 데이터 파일 그룹이 반드시 다른 디스크 또는 스핀들 위에 있을 필요는 없습니다.

사용자 데이터베이스에 사용되는 디스크와는 다른 디스크에 tempdb 데이터베이스를 배치합니다.

SQL Server tempdb의 성능 향상

SQL Server 2016 (13.x)에서 도입되었습니다.

• 임시 테이블 및 테이블 변수가 캐시됩니다. 캐싱을 사용하면 임시 개체를 삭제하고 만드는 작업이 매우 빠르게 실행되도록 할 수 있습니다. 캐싱은 페이지 할당 및 메타데이터 경합도 줄입니다.
• 사용되는 UP (업데이트) 래치 수를 줄이기 위해 할당 페이지 래치 프로토콜이 향상되었습니다.
• tempdb 로그 파일의 디스크 I/O 대역폭 사용량을 줄일 수 있도록 tempdb의 로깅 오버헤드가 감소했습니다.
• 설치 프로그램은 새 인스턴스를 설치하는 동안 여러 tempdb 데이터 파일을 추가합니다. 데이터베이스 엔진 Configuration 섹션의 새 UI 입력 컨트롤과 명령줄 매개 변수 /SQLTEMPDBFILECOUNT을(를) 사용하여 이 작업을 수행할 수 있습니다. 기본적으로 설치 프로그램은 논리 프로세서 수 또는 8개 중 더 낮은 tempdb 데이터 파일을 추가합니다.
• 여러 tempdb 데이터 파일이 있는 경우 모든 파일은 증가 설정에 따라 동시에 동일한 양만큼 자동 증가합니다. 추적 플래그 1117은 더 이상 필요하지 않습니다. 자세한 내용은 사용자 데이터베이스에 대한 -T1117 및 -T1118 변경 내용을 참조하세요.
• tempdb의 모든 할당에는 단일 익스텐트가 사용됩니다. 추적 플래그 1118은 더 이상 필요하지 않습니다. tempdb 성능 향상에 대한 자세한 내용은 블로그 문서 TEMPDB - 파일 및 추적 플래그 및 업데이트, Oh My!를 참조하세요.
• 기본 파일 그룹의 경우 AUTOGROW_ALL_FILES 속성이 켜져 있으며 속성을 수정할 수 없습니다.

SQL Server 2017 (14.x)에서 도입되었습니다.

• SQL 설치 환경은 초기 tempdb 파일 할당에 대한 지침을 향상시킵니다. SQL 설치 프로그램은 초기 파일 크기가 1GB보다 큰 값으로 설정되고 인스턴트 파일 초기화가 활성화되지 않은 경우 인스턴스 시작이 지연되지 않도록 고객에게 경고합니다.
• 새로운 sys.dm_tran_version_store_space_usage DMV가 데이터베이스당 버전 저장소 사용량을 추적하기 위해 SQL Server 2017에 도입되었습니다. 이 새로운 DMV는 데이터베이스당 버전 저장소 사용량 요구 사항에 따라 tempdb 크기 조정을 사전에 계획할 수 있는 DBA의 버전 저장소 사용량에 대한 tempdb을(를) 모니터링하는 데 유용합니다.
• 적응 조인 및 메모리 부여 피드백과 같은 새로운 지능형 쿼리 처리 기능은 쿼리의 연속 실행에 대한 메모리 유출을 줄여 불필요한 tempdb 사용률을 줄입니다.

SQL Server 2019 (15.x)에서 도입되었습니다.

• SQL Server 2019(15.x)부터 SQL Server는 최대 디스크 처리량을 허용하기 위해 tempdb용 파일을 열 때 FILE_FLAG_WRITE_THROUGH 옵션을 사용하지 않습니다. SQL Server를 시작할 때 tempdb이(가) 다시 만들어지므로 데이터 일관성을 위해 다른 시스템 데이터베이스 및 사용자 데이터베이스에 대해 이러한 옵션이 필요하지 않습니다. FILE_FLAG_WRITE_THROUGH에 대한 자세한 내용은 SQL Server에서 데이터 안정성을 확장하는 로깅 및 데이터 스토리지 알고리즘을 참조하세요.
• 메모리 최적화 TempDB 메타데이터는 tempdb 내 PAGELATCH 대기에서 병목 상태를 제거하고 새로운 수준의 확장성을 잠금 해제합니다. 자세한 내용은 방법(및 시기: 메모리 최적화 TempDB 메타데이터)에 대한 이 비디오 데모를 시청하세요. 자세한 내용은 메모리 최적화 tempdb 메타데이터 모니터링 및 문제 해결을 참조하세요.
• PFS(동시 페이지 사용 가능한 공간) 페이지 업데이트는 모든 데이터베이스에서 패치 래치 경합을 줄여 주는데, 이는 tempdb에서 가장 일반적으로 볼 수 있는 문제입니다. 이러한 개선은 PFS 업데이트를 사용하여 동시성을 관리하는 방식을 변경하여 배타적 래치가 아닌 공유 래치로 업데이트할 수 있도록 합니다. 이 동작은 SQL Server 2019(15.x)부터 모든 데이터베이스(TempDB 포함)에서 기본적으로 설정되어 있습니다. PFS 페이지에 대한 자세한 내용은 언더커버: GAM, SGAM 및 PFS 페이지를 참조하세요.
• 기본적으로 Linux에 SQL Server를 새로 설치하면 논리적 코어 수(최대 8개 데이터 파일 포함)에 따라 여러 tempdb 데이터 파일이 생성됩니다. 이 위치에서 부 버전 또는 주 버전 업그레이드에는 적용되지 않습니다. 각 tempdb 파일은 자동 증가 속도가 64MB인 8MB입니다. 이 동작은 Windows의 기본 SQL Server 설치와 유사합니다.

SQL Server 2022 (16.x)에서 도입되었습니다.

• SQL Server 2022(16.x)는 시스템 페이지 래치 동시성 향상을 통해 향상된 확장성을 도입했습니다. GAM(전역 할당 맵) 페이지 및 SGAM(공유 전역 할당 맵) 페이지에 대한 동시 업데이트는 데이터 페이지 및 범위를 할당/할당 해제하는 동안 페이지 래치 경합을 줄입니다. 이러한 향상된 기능은 모든 사용자 데이터베이스에 적용되며, 특히 tempdb가 많은 워크로드에 도움이 됩니다. GAM 및 SGAM 페이지에 대한 자세한 내용은 언더커버: GAM, SGAM 및 PFS 페이지를 참조하세요. 자세한 내용은 시스템 페이지 래치 동시성 향상(Ep. 6) | 노출된 데이터를 시청하세요.

메모리 최적화 tempdb 메타데이터

지금까지 tempdb 내 메타데이터 경합은 SQL Server에서 실행되는 많은 워크로드의 확장성의 병목 현상이었습니다. SQL Server 2019 (15.x)에는 메모리 내 데이터베이스 기능 제품군인 메모리 최적화 TempDB 메타데이터의 일부인 새로운 기능이 도입되었습니다.

이 기능은 이 병목 상태를 효과적으로 제거하고 tempdb 사용량이 많은 워크로드에 대한 새로운 수준의 확장성을 제공합니다. SQL Server 2019 (15.x)에서 임시 테이블 메타데이터 관리에 필요한 시스템 테이블은 래치가 없는 비내구성 메모리 최적화 테이블로 이동할 수 있습니다.

메모리 최적화 TempDB 메타데이터를 사용하는 방법과 시기에 대한 개요는 7분 분량의 다음 동영상을 시청하세요.

메모리 최적화 tempdb 메타데이터 구성 및 사용

이 새 기능을 옵트인하려면 다음 스크립트를 사용하세요.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

이 구성 변경은 효력 발생을 위해 서비스 다시 시작을 요구합니다.

다음 T-SQL 명령을 사용하여 tempdb의 메모리 최적화 여부를 확인할 수 있습니다.

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

메모리 최적화 TempDB 메타데이터를 사용하도록 설정한 후 어떤 이유로든 서버를 시작할 수 없는 경우, -f 시작 옵션을 통해 최소 구성으로 SQL Server 인스턴스를 시작하여 기능을 무시할 수 있습니다. 그런 다음, 기능을 사용하지 않도록 설정하고 정상 모드에서 SQL Server를 다시 시작할 수 있습니다.

서버 메모리 부족을 방지하기 위해 tempdb를 리소스 풀에 바인딩할 수 있습니다. 이 작업은 리소스 풀을 데이터베이스에 바인딩하기 위해 일반적으로 수행하는 단계가 아니라 ALTER SERVER 명령을 통해 수행됩니다.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');

또한 메모리 최적화 TempDB 메타데이터를 이미 사용하도록 설정한 경우에도 이 변경을 적용하려면 다시 시작해야 합니다.

메모리 최적화 tempdb 제한 사항

• 기능을 켜고 끄는 것은 동적이 아닙니다. tempdb의 구조를 변경해야 하는 고유한 사항 때문에 이 기능을 설정하거나 해제하려면 다시 시작해야 합니다.

• 단일 트랜잭션이 둘 이상의 데이터베이스에서 메모리 최적화 테이블에 액세스할 수 없습니다. 사용자 데이터베이스의 메모리 최적화 테이블을 포함하는 트랜잭션은 동일한 트랜잭션의 tempdb 시스템 뷰에 액세스할 수 없습니다. 사용자 데이터베이스의 메모리 최적화 테이블과 동일한 트랜잭션에서 tempdb 시스템 보기에 액세스를 시도하면 다음과 같은 오류가 발생합니다.

  A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
  

  예시:

  BEGIN TRAN;
  
  SELECT *
  FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb
  
  INSERT INTO <user database>.<schema>.<mem-optimized table>
  VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
  
  COMMIT TRAN;
  

• 메모리 최적화 테이블에 대한 쿼리는 잠금 및 분리 힌트를 지원하지 않으므로 메모리 최적화 tempdb 카탈로그 보기에 대한 쿼리는 잠금 및 분리 힌트를 유지하지 않습니다. SQL Server의 다른 시스템 카탈로그 보기와 마찬가지로 시스템 보기에 대한 모든 트랜잭션은 READ COMMITTED (또는 이 경우 READ COMMITTED SNAPSHOT) 분리 내에 있습니다.

• 메모리 최적화 TempDB 메타데이터가 사용하도록 설정된 경우에는 임시 테이블에 Columnstore 인덱스를 만들 수 없습니다.

• columnstore 인덱스에 대한 제한으로 인해, 메모리 최적화 메타데이터가 사용하도록 설정된 경우 COLUMNSTORE 또는 COLUMNSTORE_ARCHIVE 데이터 압축 매개 변수와 함께 sp_estimate_data_compression_savings 시스템 저장 프로시저를 사용할 수 없습니다.

• 시스템 저장 프로시저를 사용하면 메모리 내 엔진이 가비지 수집에 적합한 메모리 내 데이터의 삭제된 행과 관련된 메모리를 수동으로 해제할 수 있습니다. 이는 메모리 부족 오류의 특정 메모리 최적화 tempdb 메타데이터(HkTempDB)를 해결하는 데 도움이 될 수 있습니다. 자세한 내용은 sys.sp_xtp_force_gc (Transact-SQL)를 참조하세요.

SQL Server의 tempdb 용량 계획

SQL Server 프로덕션 환경에서 tempdb의 적절한 크기는 많은 요인에 따라 결정됩니다. 앞에서 설명한 대로 이러한 요인에는 기존 워크로드 및 사용되는 SQL Server 기능이 포함됩니다.

SQL Server 테스트 환경에서 다음 작업을 수행하여 기존 워크로드를 분석하는 것이 좋습니다.

• tempdb에 대해 자동 증가 설정
• 개별 쿼리 또는 워크로드 추적 파일을 실행하고 tempdb 공간 사용을 모니터링합니다.
• 인덱스 다시 작성 및 tempdb 공간 모니터링 같은 인덱스 유지 관리 작업을 실행합니다.
• 이전 단계의 공간 사용 값을 사용하여 총 워크로드 사용량을 예측합니다. 예상 동시 작업에 대해 이 값을 조정한 다음 그에 따라 tempdb 크기를 설정합니다.

tempdb 사용 모니터링

tempdb 내 디스크 공간이 부족하면 SQL Server 프로덕션 환경에서 상당한 중단이 발생할 수 있습니다. 실행 중인 애플리케이션이 작업을 완료하지 못하도록 방해할 수도 있습니다. sys.dm_db_file_space_usage 동적 관리 뷰를 사용하여 tempdb 파일에서 사용되는 디스크 공간을 모니터링할 수 있습니다.

예를 들어 다음 네 개의 샘플 스크립트는 tempdb 내 사용 가능한 공간의 양, 버전 저장소에서 사용하는 공간의 양, 내부 개체에서 사용되는 공간의 양 및 사용자 개체에서 사용하는 공간의 양을 찾습니다.

 -- Determining the amount of free space in tempdb
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;

-- Determining the amount of space used by the version store
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 tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
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 tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
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 tempdb.sys.dm_db_file_space_usage;

또한 sys.dm_db_session_space_usage 및 sys.dm_db_task_space_usage 동적 관리 뷰를 사용하면 세션이나 태스크 수준에서 tempdb의 페이지 할당 또는 할당 취소 작업을 모니터링할 수 있습니다. 이러한 보기를 사용하면 tempdb 디스크 공간을 많이 사용하는 큰 쿼리, 임시 테이블 또는 테이블 변수를 식별할 수 있습니다. 또한 tempdb 내 사용 가능한 여유 공간과 tempdb을(를) 사용 중인 리소스를 모니터링하는 데 사용할 수 있는 여러 가지 카운터가 있습니다.

예를 들어 다음 스크립트를 사용하여 각 세션에서 현재 실행 중인 모든 태스크에서 내부 개체가 사용하는 tempdb 공간을 가져옵니다.

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
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;

다음 스크립트를 사용하여 실행 중 또는 완료한 태스크의 현재 세션에서 내부 개체가 사용하는 tempdb 공간을 찾습니다.

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

관련 콘텐츠

• 인덱스에 대한 SORT_IN_TEMPDB 옵션
• 시스템 데이터베이스
• sys.databases
• sys.master_files
• 데이터베이스 파일 이동