tempdb 데이터베이스

적용 대상: Microsoft Fabric의 SQL ServerAzure SQL Database Azure SQL Managed InstanceSQL 데이터베이스

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

개요

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

• 명시적으로 만들어진 사용자 개체입니다. 다음이 포함됩니다.

  • 글로벌 또는 로컬 임시 테이블 및 해당 테이블에 대한 인덱스
  • 임시 저장 프로시저
  • 테이블 변수를 포함한 큰 변수
  • 테이블 반환 함수에서 반환된 테이블
  • 커서

  사용자 데이터베이스에서 만들 수 있는 사용자 객체는 tempdb에서도 만들 수 있지만, 내구성이 보장되지 않으며 데이터베이스 엔진 인스턴스가 다시 시작되면 삭제됩니다.

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

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

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

• 버전 저장소- 행 버전 관리가 지원되는 데이터 행을 포함하는 데이터 페이지의 컬렉션입니다. 버전 저장소에는 다음이 포함됩니다.

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

  SQL Server 2025(17.x) 미리 보기부터 ADR(가속 데이터베이스 복구)을 사용하도록 설정한 tempdbtempdb 경우 두 개의 서로 다른 독립 버전 저장소가 포함됩니다.

  • ADR을 사용하도록 설정하지 않은 사용자 데이터베이스의 트랜잭션에 의해 생성된 행 버전에 사용되는 기존 버전 저장소입니다.
  • tempdb에서 트랜잭션에 의해 생성된 행 버전에 사용되는 영구 버전 저장소(PVS)입니다.

  ADR이 tempdb에서 사용하도록 설정된 경우, 데이터 파일에 두 버전 저장소를 모두 포함할 수 있는 충분한 디스크 공간을 tempdb 할당해야 합니다. 워크로드에 따라 PVS 데이터를 포함하려면 데이터 파일의 tempdb 크기를 늘려야 할 수 있습니다.

  기존 버전 저장소의 공간 사용량에 대한 tempdb 자세한 내용은 tempdb에서 사용되는 공간을 참조하세요. PVS에서 사용하는 공간에 대한 자세한 내용은 PVS (영구 버전 저장소)에서 사용하는 공간을 참조하세요.

내 tempdb 작업은 최소 로깅됩니다. tempdb 는 데이터베이스 엔진이 시작될 때마다 다시 만들어지므로 시스템이 항상 빈 tempdb 데이터베이스로 시작됩니다. 임시 저장 프로시저 및 로컬 임시 테이블은 만든 세션의 연결이 끊어지면 자동으로 삭제됩니다.

tempdb 데이터베이스 엔진의 한 작동 시간에서 다른 가동 시간으로 저장할 항목이 없습니다. tempdb에서는 백업 및 복원 작업이 허용되지 않습니다.

SQL Server에서 tempdb의 물리적 속성

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

모든 tempdb 데이터 파일에는 항상 동일한 초기 크기 및 증가 매개 변수가 있어야 합니다.

tempdb 데이터 파일 수

데이터베이스 엔진의 버전, 해당 구성 및 워크로드 tempdb 에 따라 할당 경합을 완화하기 위해 여러 데이터 파일이 필요할 수 있습니다.

권장되는 총 데이터 파일 수는 컴퓨터의 논리 프로세서 수에 따라 달라집니다. 일반 지침:

• 논리 프로세서 수가 8보다 작거나 같은 경우 동일한 수의 데이터 파일을 사용합니다.
• 논리 프로세서 수가 8보다 크면 8개의 데이터 파일을 사용합니다.
• 할당 경합이 계속 관찰되는 경우 tempdb 경합이 허용 가능한 수준으로 감소할 때까지 데이터 파일 수를 4의 배수로 늘리거나 워크로드를 변경합니다.

자세한 내용은 SQL Server tempdb 데이터베이스의 할당 경합을 줄이기 위한 권장 사항을 참조하세요.

현재 크기 및 증가 매개 변수를 tempdb확인하려면 sys.database_files 카탈로그 뷰를 tempdb사용합니다.

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

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

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

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

¹ 설정을 ACCELERATED_DATABASE_RECOVERYON으로 tempdb에서 SQL Server 2025(17.x) 미리 보기부터 지원합니다. 이전 버전의 SQL Server에서는 데이터베이스에 ACCELERATED_DATABASE_RECOVERY 대한 tempdb 옵션을 수정할 수 없습니다.

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

Azure SQL Database의 tempdb

Azure SQL Database에서 동작 및 구성의 tempdb 일부 측면은 SQL Server와 다릅니다.

단일 데이터베이스의 경우 논리 서버의 각 데이터베이스에는 고유한 tempdb데이터베이스가 있습니다. 탄력적 풀에서 동일한 풀 tempdb 의 모든 데이터베이스에 대한 공유 리소스이지만 한 데이터베이스에서 만든 임시 개체는 동일한 탄력적 풀의 다른 데이터베이스에 표시되지 않습니다.

tempdb카탈로그 뷰 및 DMV(동적 관리 뷰)를 포함한 개체는 데이터베이스에 대한 데이터베이스 간 참조를 tempdb 통해 액세스할 수 있습니다. 예를 들어 sys.database_files 보기를 쿼리할 수 있습니다.

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Azure SQL Database의 전역 임시 테이블은 데이터베이스에 한정됩니다. 자세한 내용은 Azure SQL Database의 데이터베이스 범위 전역 임시 테이블을 참조하세요.

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

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

SQL Managed Instance의 tempdb

Azure SQL Managed Instance에서 동작 및 기본 구성의 tempdb 일부 측면은 SQL Server와 다릅니다.

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

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

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

Fabric의 SQL 데이터베이스에 있는 tempdb

Microsoft Fabric의 SQL 데이터베이스 크기에 대해 tempdb 자세히 알아보려면 기능 비교의 리소스 제한 섹션인 Microsoft Fabric의 Azure SQL Database 및 SQL Database를 검토하세요.

Azure SQL Database와 마찬가지로 Microsoft Fabric의 SQL 데이터베이스에 있는 전역 임시 테이블은 데이터베이스 범위가 지정됩니다. 자세한 내용은 Azure SQL Database의 데이터베이스 범위 전역 임시 테이블을 참조하세요.

제한 사항

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

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

사용 권한

모든 사용자가 tempdb에 임시 개체를 만들 수 있습니다.

사용자는 추가 권한을 받지 않는 한 자신의 비 임시 개체에 tempdb만 액세스할 수 있습니다.

데이터베이스 사용자 또는 역할의 사용을 방지하기 위해 사용 권한을 CONNECTtempdb 할 수 있습니다tempdb. 많은 작업에서 .를 사용해야 tempdb하므로 권장되지 않습니다.

SQL Server에서 tempdb 성능 최적화

파일의 크기 및 물리적 배치는 tempdb 성능에 영향을 줄 수 있습니다. 예를 들어, 초기 크기 tempdb이 너무 작은 경우, 데이터베이스 엔진 인스턴스를 다시 시작할 때마다 워크로드를 지원하는 데 필요한 크기로 tempdb이 자동 확장되도록 시간과 리소스가 소모될 수 있습니다.

• 가능하면 인스턴트 파일 초기화를 사용하여 데이터 파일에 대한 증가 작업의 성능을 향상시킵니다.
  • SQL Server 2022(16.x)부터 최대 64MB의 트랜잭션 로그 파일 증가 이벤트도 즉시 파일 초기화의 이점을 얻을 수 있습니다. 자세한 내용은 인스턴트 파일 초기화 및 트랜잭션 로그를 참조하세요.
• 환경의 일반적인 작업량을 수용할 수 있는 값으로 파일 크기를 설정하여 모든 tempdb 파일에 충분한 공간을 미리 할당합니다. 사전 할당은 자동 증가가 tempdb 너무 자주 발생하지 않도록 방지하여 성능에 부정적인 영향을 줄 수 있습니다.
• 계획되지 않은 증가 이벤트 중에 공간을 제공하려면 데이터베이스의 tempdb 파일을 자동 증가로 설정해야 합니다.
• 크기가 같은 여러 데이터 파일로 분할하면 tempdb 사용하는 tempdb작업의 효율성이 향상될 수 있습니다.
  • 데이터 할당 불균형을 방지하려면 데이터베이스 엔진이 사용 가능한 공간이 더 많은 파일에서 할당을 선호하는 비례 채우기 알고리즘을 사용하기 때문에 데이터 파일의 초기 크기 및 증가 매개 변수가 같아야 합니다.
  • 파일 증가 증분을 적절한 크기(예: 64MB)로 설정하고 증가 불균형을 방지하기 위해 모든 데이터 파일에 대해 증가 증분을 동일하게 만듭니다.
• SQL Server 2025(17.x) 미리 보기부터 빠른 데이터베이스 복구 를 사용하도록 설정하여 트랜잭션에 tempdb 대한 빠른 트랜잭션 롤백 및 적극적인 로그 잘림의 이점을 얻을 수 있습니다 tempdb. 자세한 내용은 tempdb의 ADR을 참조하세요.
  • ADR tempdb 을 사용하도록 설정하거나 사용하지 않도록 설정하려면 데이터베이스 엔진을 다시 시작해야 합니다.

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

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

tempdb 데이터베이스를 고속 I/O 하위 시스템에 배치합니다. 디스크 수준 I/O 병목 현상이 발생하지 않는 한 개별 데이터 파일 또는 데이터 파일 그룹이 tempdb 반드시 다른 디스크에 있을 필요는 없습니다.

사용자 데이터베이스와 tempdb 간에 I/O 경합이 있는 경우, tempdb 파일을 사용자 데이터베이스가 사용하는 디스크와 다른 디스크에 배치합니다.

SQL Server용 tempdb의 향상된 기능

SQL Server 2025(17.x) 미리 보기에 도입

• Tempdb 공간 리소스 거버넌스는 애플리케이션 또는 사용자 워크로드에서 사용하는 총 공간 양 tempdb 에 제한을 적용합니다. 이렇게 하면 안정성이 향상되고 런어웨이 쿼리 또는 워크로드가 많은 공간을 소비하지 못하게 하여 중단을 방지할 수 있습니다 tempdb. 자세한 내용은 Tempdb 공간 리소스 거버넌스를 참조하세요.
• 가속 데이터베이스 복구는 tempdb에서 지원되며, tempdb의 트랜잭션에 대해 즉각적인 트랜잭션 롤백과 적극적인 로그 잘림을 제공합니다.
• 최적화된 할로윈 보호 는 DML(데이터 수정 언어) 문에 tempdb 할로윈 보호 스풀을 사용하지 않음으로써 공간 사용량을 줄입니다.

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

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

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

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

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

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

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

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

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

임시 개체 메타데이터 경합은 지금까지 많은 SQL Server 워크로드의 확장성에 병목 현상이 있었습니다. 이 문제를 해결하기 위해 SQL Server 2019(15.x)에는 메모리 내 데이터베이스 기능 제품군인 메모리 최적화 TempDB 메타데이터의 일부인 기능이 도입되었습니다.

메모리 최적화 TempDB 메타데이터 기능을 사용하도록 설정하면 이전에 내부의 임시 개체 메타데이터 경합 tempdb으로 제한되었던 워크로드에 대해 이 병목 현상이 제거됩니다. SQL Server 2019(15.x)부터 임시 개체 메타데이터 관리에 관련된 시스템 테이블은 래치 프리, 비영구성 메모리 최적화 테이블이 될 수 있습니다.

다음 진단 쿼리는 임시 개체 메타데이터 경합이 발생하는 경우 하나 이상의 행을 반환합니다. 각 행은 시스템 테이블을 나타내며 이 진단 쿼리가 실행될 때 해당 테이블에 액세스하기 위해 경합하는 세션 수를 반환합니다.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

메모리 최적화 TempDB 메타데이터 기능을 사용하는 방법과 시기에 대한 개요는 이 7분 분량의 비디오를 시청하세요.

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

다음 섹션에서는 메모리 최적화 TempDB 메타데이터 기능을 사용하도록 설정, 구성, 확인 및 사용하지 않도록 설정하는 단계를 포함합니다.

활성화하다

이 기능을 사용하도록 설정하려면 다음 스크립트를 사용합니다.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

자세한 내용은 ALTER SERVER를 참조하세요. 이 구성 변경은 효력 발생을 위해 서비스 다시 시작을 요구합니다.

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

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

반환된 값이 1이고 기능을 사용하도록 설정한 후 다시 시작되면 기능이 활성화됩니다.

메모리 최적화 TempDB 메타데이터를 사용하도록 설정한 후 어떤 이유로든 서버가 시작되지 않는 경우 시작 옵션을 사용하여 -f으로 데이터베이스 엔진 인스턴스를 시작하여 이 기능을 무시할 수 있습니다. 그런 다음, 기능을 사용하지 않도록 설정하고 데이터베이스 엔진을 정상 모드로 다시 시작하는 옵션을 제거할 -f 수 있습니다.

리소스 풀에 바인딩하여 메모리 사용 제한

잠재적인 메모리 부족 조건으로부터 서버를 보호하려면 메모리 최적화 TempDB 메타데이터에서 사용하는 메모리를 제한하는 리소스 관리자 tempdb에 바인딩 하는 것이 좋습니다. 다음 샘플 스크립트는 리소스 풀을 만들고 최대 메모리를 20%설정하고 리소스 관리자를 사용하도록 설정하고 리소스 풀에 바인딩합니다 tempdb .

이 예제에서는 데모용으로 메모리 제한으로 20% 사용합니다. 사용자 환경의 최적 값은 워크로드에 따라 크거나 작을 수 있으며 워크로드가 변경될 경우 시간이 지남에 따라 변경될 수 있습니다.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

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

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

리소스 풀 바인딩 확인 및 메모리 사용 모니터링

리소스 풀에 바인딩되어 있는지 확인하고 tempdb 풀에 대한 메모리 사용 통계를 모니터링하려면 다음 쿼리를 사용합니다.

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

리소스 풀 바인딩 제거

메모리 최적화 TempDB 메타데이터를 사용하도록 설정하는 동안 리소스 풀 바인딩을 제거하려면 다음 명령을 실행하고 서비스를 다시 시작합니다.

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

비활성화

메모리 최적화 TempDB 메타데이터를 사용하지 않도록 설정하려면 다음 명령을 실행하고 서비스를 다시 시작합니다.

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

메모리 최적화 TempDB 메타데이터의 제한 사항

• 메모리 최적화 TempDB 메타데이터 기능을 사용하거나 사용하지 않도록 설정하려면 다시 시작해야 합니다.

• 경우에 따라 MEMORYCLERK_XTP 메모리 클럭에서 메모리 사용량이 높아 워크로드에서 메모리 부족 오류가 발생할 수 있는 것을 관찰할 수 있습니다.

  다른 모든 메모리 클럭과 목표 서버 메모리에 대해 MEMORYCLERK_XTP 클럭의 메모리 사용량을 확인하려면 다음 쿼리를 실행합니다.

  SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
         SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
         SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
  FROM sys.dm_os_memory_clerks
  CROSS JOIN sys.dm_os_sys_info;
  

  메모리가 높은 경우 MEMORYCLERK_XTP 다음과 같이 문제를 완화할 수 있습니다.

  • tempdb 메모리 최적화 TempDB 메타데이터로 메모리 사용량을 제한하는 리소스 풀에 데이터베이스를 바인딩합니다. 자세한 내용은 메모리 최적화 tempdb 메타데이터 구성 및 사용을 참조하세요.
  • 시스템 저장 프로시저를 주기적으로 실행하여 더 이상 필요하지 않은 메모리를 해제 MEMORYCLERK_XTP 할 수 있습니다. 자세한 내용은 sys.sp_xtp_force_gc (Transact-SQL)를 참조하세요.

  자세한 내용은 메모리 최적화 tempdb 메타데이터(HkTempDB)의 메모리 부족 오류를 참조하세요.

• In-Memory OLTP를 사용하는 경우 단일 트랜잭션은 둘 이상의 데이터베이스에서 메모리 최적화 테이블에 액세스할 수 없습니다. 따라서 사용자 데이터베이스의 메모리 최적화 테이블을 포함하는 읽기 또는 쓰기 트랜잭션은 동일한 트랜잭션의 시스템 뷰에도 액세스할 tempdb 수 없습니다. 이 경우 오류 41317이 표시됩니다.

  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.
  

  이 제한은 단일 트랜잭션이 둘 이상의 데이터베이스에서 메모리 최적화 테이블에 액세스하려고 시도하는 다른 시나리오에도 적용됩니다.

  예를 들어 메모리 최적화 테이블을 포함하는 사용자 데이터베이스 에서 sys.stats 카탈로그 뷰를 쿼리하면 오류 41317이 발생할 수 있습니다. 이 문제는 쿼리가 사용자 데이터베이스의 메모리 최적화 테이블과 메모리 최적화 메타데이터의 tempdb 데이터에 액세스하려고 하기 때문에 발생합니다.

  다음 예제 스크립트는 메모리 최적화 TempDB 메타데이터를 사용할 때 이 오류를 생성합니다.

  BEGIN TRAN;
  
  -- Create an In-memory OLTP transaction that accesses a system view in tempdb
  SELECT name
  FROM tempdb.sys.tables;
  
  -- An attempt to create an In-memory OLTP transaction in the user database fails
  INSERT INTO <user database>.<schema>.<memory-optimized table>
  VALUES (1);
  
  COMMIT TRAN;
  

  참고 항목

  이 제한은 임시 테이블에는 적용되지 않습니다. 사용자 데이터베이스의 메모리 최적화 테이블에 액세스하는 동일한 트랜잭션에 임시 테이블을 만들 수 있습니다.

• 시스템 카탈로그 뷰에 대한 쿼리는 항상 격리 수준을 사용합니다 READ COMMITTED . 메모리 최적화 TempDB 메타데이터가 활성화되면, 시스템 카탈로그 뷰에 대한 쿼리는 tempdb 격리 수준을 사용하여 SNAPSHOT에서 실행됩니다. 두 경우 모두 잠금 힌트는 적용되지 않습니다.

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

  • 따라서 메모리 최적화 TempDB 메타데이터가 활성화되면 sp_estimate_data_compression_savings 시스템 저장 프로시저를 COLUMNSTORE 또는 COLUMNSTORE_ARCHIVE 데이터 압축 매개 변수와 함께 사용할 수 없습니다.

SQL Server의 tempdb 용량 계획

적절한 크기를 tempdb 결정하는 것은 여러 요인에 따라 달라집니다. 이러한 요인에는 사용되는 워크로드 및 데이터베이스 엔진 기능이 포함됩니다.

일반적인 워크로드를 재현할 수 있는 테스트 환경에서 다음 작업을 수행하여 공간 소비를 분석 tempdb 하는 것이 좋습니다.

• 파일에 대해 tempdb 사용하도록 설정합니다. 모든 tempdb 데이터 파일은 동일한 초기 크기 및 자동 증가 구성을 가져야 합니다.
• 워크로드를 재현하고 tempdb의 공간 사용을 모니터링합니다.
• 정기적인 인덱스 유지 관리를 사용하는 경우 유지 관리 작업을 실행하고 공간을 모니터링 tempdb 합니다.
• 이전 단계에서 사용한 최대 공간 값을 사용하여 총 워크로드 사용량을 예측합니다. 예상 동시 작업에 대해 이 값을 조정한 다음 그에 따라 tempdb 크기를 설정합니다.

tempdb 사용 모니터링

디스크 공간이 tempdb 부족하면 상당한 중단과 애플리케이션 가동 중지 시간이 발생할 수 있습니다. sys.dm_db_file_space_usage 동적 관리 뷰를 사용하여 파일에 사용되는 tempdb 공간을 모니터링할 수 있습니다.

예를 들어 다음 예제 스크립트는 다음을 찾습니다.

• tempdb의 여유 공간 (성장에 사용할 수 있는 tempdb의 여유 디스크 공간은 고려하지 않음).
• 기존 버전 저장소에서 사용하는 공간입니다.
  • ADR(가속 데이터베이스 복구)이 사용하도록 설정된tempdb경우 PVS(영구 버전 저장소) 의 크기를 모니터링하려면 PVS 크기 검사를 참조하세요.
• 내부 개체가 사용하는 공간.
• 사용자 개체에서 사용하는 공간입니다.

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

세션 또는 작업 수준에서 tempdb의 페이지 할당 또는 할당 취소 작업을 모니터링하려면, sys.dm_db_session_space_usage 및 sys.dm_db_task_space_usage 동적 관리 뷰를 사용할 수 있습니다. 이러한 뷰는 많은 공간을 사용하는 쿼리, 임시 테이블 또는 테이블 변수를 식별하는 데 도움이 될 수 있습니다 tempdb .

예를 들어 각 세션에서 현재 실행 중인 태스크의 내부 개체에 의해 할당되고 할당 취소된 공간을 가져오려면 다음 예제 스크립트를 사용하세요 tempdb.

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 습니다.

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

관련 콘텐츠

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