tempdb의 용량 계획
이 항목에서는 tempdb에 필요한 적절한 디스크 공간을 확인하기 위한 지침을 제공합니다. 또한 이 항목에는 프로덕션 환경에서 성능을 최적화하기 위한 tempdb 권장 구성과 tempdb 공간 사용을 모니터링하는 방법이 포함되어 있습니다.
tempdb 사용 방법
tempdb 시스템 데이터베이스는 SQL Server 인스턴스에 연결된 모든 사용자가 사용할 수 있는 전역 리소스입니다. tempdb 데이터베이스는 사용자 개체, 내부 개체 및 버전 저장소를 저장하는 데 사용합니다.
사용자 개체
사용자 개체는 사용자에 의해 명시적으로 생성됩니다. 이러한 개체는 사용자 세션 범위나 해당 개체를 만든 루틴 범위에 있을 수 있습니다. 루틴은 저장 프로시저, 트리거 또는 사용자 정의 함수입니다. 사용자 개체는 다음 중 하나일 수 있습니다.
사용자 정의 테이블 및 인덱스
시스템 테이블 및 인덱스
전역 임시 테이블 및 인덱스
로컬 임시 테이블 및 인덱스
테이블 변수
테이블 반환 함수에서 반환된 테이블
내부 개체
내부 개체는 SQL Server 데이터베이스 엔진에서 SQL Server 문을 처리하기 위해 필요에 따라 생성되며 문 범위 내에서 생성 및 삭제됩니다. 내부 개체는 다음 중 하나일 수 있습니다.
커서 또는 스풀 작업에 대한 작업 테이블 및 임시 LOB(Large Object) 저장소
해시 조인 또는 해시 집계 작업에 대한 작업 파일
인덱스 생성 또는 다시 작성(SORT_IN_TEMPDB가 지정된 경우), 특정 GROUP BY, ORDER BY 또는 UNION 쿼리 같은 작업의 중간 정렬 결과
각 내부 개체는 IAM 페이지 하나와 8페이지 익스텐트 하나를 포함하여 최소 9페이지를 사용합니다. 페이지 및 익스텐트에 대한 자세한 내용은 페이지 및 익스텐트 이해를 참조하십시오.
버전 저장소
버전 저장소는 행 버전 관리를 사용하는 기능을 지원하는 데 필요한 데이터 행을 보관하는 데이터 페이지 모음입니다. 버전 저장소에는 일반 버전 저장소와 온라인 인덱스 작성 버전 저장소가 있습니다. 버전 저장소에는 다음이 포함되어 있습니다.
행 버전 관리 격리 수준을 사용하여 커밋된 읽기 또는 스냅숏을 사용하는 데이터베이스의 데이터 수정 트랜잭션에서 생성된 행 버전
온라인 인덱스 작업, MARS(Multiple Active Result Sets) 및 AFTER 트리거 같은 기능에 대한 데이터 수정 트랜잭션으로 생성된 행 버전
다음 표에서는 tempdb에 사용자 개체, 내부 개체 또는 행 버전을 만드는 SQL Server의 기능을 나열합니다. 가능하면 디스크 공간 사용을 예측하는 방법이 제공됩니다.
기능 |
tempdb 사용 |
추가 정보 |
|---|---|---|
트리거가 설정된 경우의 대량 로드 작업 |
대량 가져오기 최적화는 트리거가 설정된 경우 사용할 수 있습니다. SQL Server는 트랜잭션을 업데이트하거나 삭제하는 트리거에 대해 행 버전 관리를 사용합니다. 삭제 또는 업데이트된 각 행의 복사본이 버전 저장소에 추가됩니다. 이 표의 뒷부분에 나오는 "트리거"를 참조하십시오. |
|
CTE(공통 테이블 식) 쿼리 |
CTE(공통 테이블 식)는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있습니다. CTE(공통 테이블 식) 쿼리에 대한 쿼리 계획에서 스풀 연산자를 사용하여 중간 쿼리 결과를 저장할 때 데이터베이스 엔진은 tempdb에 작업 테이블을 만들어 이 작업을 지원합니다. |
|
커서 |
키 집합 커서와 정적 커서는 tempdb에 내장된 작업 테이블을 사용합니다. 키 집합 커서는 작업 테이블을 사용하여 커서에서 행을 식별하는 키 집합을 저장합니다. 정적 커서는 작업 테이블을 사용하여 커서의 전체 결과 집합을 저장합니다. 커서의 디스크 공간 사용은 선택한 쿼리 계획에 따라 달라질 수 있습니다. 쿼리 계획이 이전 버전의 SQL Server와 같으면 디스크 공간 사용량도 거의 같습니다. |
|
데이터베이스 메일 |
이 표의 뒷부분에 나오는 "Service Broker"를 참조하십시오. |
|
DBCC CHECKDB |
DBCC CHECKDB는 tempdb 작업 테이블을 사용하여 중간 정렬 작업 결과를 저장합니다. 작업의 tempdb 디스크 공간 요구 사항을 확인하려면 DBCC CHECKDB WITH ESTIMATEONLY를 실행하십시오. |
|
이벤트 알림 |
이 표의 뒷부분에 나오는 "Service Broker"를 참조하십시오. |
|
인덱스 |
인덱스를 만들거나 다시 작성할 때(오프라인 또는 온라인) SORT_IN_TEMPDB 옵션을 ON으로 설정하면 데이터베이스 엔진에서 tempdb를 사용하여 인덱스를 작성하는 데 사용되는 중간 정렬 결과를 저장하도록 지시할 수 있습니다. SORT_IN_TEMPDB가 지정되어 있으며 정렬이 필요한 경우 tempdb에 가장 큰 인덱스를 저장할 수 있는 디스크 공간 및 index create memory 옵션 값과 같은 디스크 공간이 있어야 합니다. 자세한 내용은 인덱스 디스크 공간 예를 참조하십시오. 테이블 및 인덱스는 분할할 수 있습니다. 분할 인덱스에서 SORT_IN_TEMPDB 인덱스 옵션이 지정되어 있으며 인덱스가 기본 테이블에 맞게 정렬된 경우 tempdb에 가장 큰 파티션의 중간 정렬 실행을 저장할 수 있는 공간이 있어야 합니다. 인덱스가 정렬되지 않은 경우에는 tempdb에 모든 파티션의 중간 정렬 실행을 저장할 수 있는 공간이 있어야 합니다. 자세한 내용은 분할 인덱스에 대한 특수 지침을 참조하십시오. 온라인 인덱스 작업은 행 버전 관리를 사용하여 인덱스 작업이 다른 트랜잭션에서 수정하는 내용의 영향을 받지 않도록 합니다. 따라서 행 버전 관리를 사용하면 이미 읽은 행에 대한 공유 잠금을 요청할 필요가 없습니다. 온라인 인덱스 작업 중 여러 사용자가 동시에 업데이트 및 삭제 작업을 수행하려면 tempdb의 버전 레코드를 위한 공간이 필요합니다. 온라인 인덱스 작업에 SORT_IN_TEMPDB가 사용되며 정렬이 필요한 경우 tempdb에 앞에서 중간 정렬 결과에 대해 설명한 추가 디스크 공간도 있어야 합니다. 클러스터형 인덱스를 만들거나, 삭제하거나, 다시 작성하는 온라인 인덱스 작업에는 임시 매핑 인덱스를 작성 및 유지 관리하기 위한 추가 디스크 공간도 필요합니다. CREATE 및 UPDATE STATISTIC 작업에서는 통계를 작성하기 위해 tempdb를 사용하여 행 샘플을 정렬할 수 있습니다. 자세한 내용은 인덱스 DDL 작업의 디스크 공간 요구 사항을 참조하십시오. |
|
LOB(Large Object) 데이터 형식 변수 및 매개 변수 |
LOB(Large Object) 데이터 형식은 varchar(max), nvarchar(max), varbinary(max)text, ntext, image 및 xml입니다. 이러한 데이터 형식은 크기가 최대 2GB이며 저장 프로시저, 사용자 정의 함수, 일괄 처리 또는 쿼리에서 변수나 매개 변수로 사용될 수 있습니다. LOB 데이터 형식으로 정의된 매개 변수와 변수는 값이 작을 경우 주 메모리를 저장소로 사용합니다. 그러나 큰 값은 tempdb에 저장됩니다. LOB 변수와 매개 변수는 tempdb에 저장될 때 내부 개체로 간주됩니다. sys.dm_db_session_space_usage 동적 관리 뷰를 쿼리하여 지정된 세션 동안 내부 개체에 할당된 페이지를 보고할 수 있습니다. SUBSTRING 또는 REPLICATE 같은 일부 내장 문자열 함수는 LOB 값에 적용될 때 tempdb에 중간 임시 저장소가 필요할 수 있습니다. 마찬가지로 데이터베이스에서 행 버전 관리 기반의 트랜잭션 격리 수준을 사용하고 큰 개체를 수정하면 LOB의 변경된 조각이 tempdb의 버전 저장소로 복사됩니다. |
|
MARS(Multiple Active Result Sets) |
일반적으로 MARS라고 하는 다중 활성 결과 집합이 단일 연결에서 발생할 수 있습니다. 활성 결과 집합이 있을 때 MARS 세션에서 INSERT, UPDATE 또는 DELETE 같은 데이터 수정 문을 실행하면 이 수정 문의 영향을 받는 행이 tempdb의 버전 저장소에 저장됩니다. 이 표의 뒷부분에 나오는 "행 버전 관리"를 참조하십시오. |
|
쿼리 알림 |
이 표의 뒷부분에 나오는 "Service Broker"를 참조하십시오. |
|
쿼리 |
SELECT, INSERT, UPDATE 및 DELETE 문이 포함된 쿼리는 내부 개체를 사용하여 해시 조인, 해시 집계 또는 정렬의 중간 결과를 저장할 수 있습니다. 쿼리 실행 계획을 캐시하면 해당 계획에 필요한 작업 테이블이 캐시됩니다. 작업 테이블은 캐시될 때 잘려 9페이지만 다시 사용할 수 있도록 캐시에 보관됩니다. 그 결과 다음 쿼리 실행 시 성능이 향상됩니다. 시스템 메모리가 부족하면 데이터베이스 엔진은 실행 계획을 제거하여 관련 작업 테이블을 삭제할 수 있습니다. |
|
행 버전 관리 |
행 버전 관리는 다음 기능을 지원하기 위해 사용되는 일반적인 프레임워크입니다.
행 버전은 활성 트랜잭션에서 행 버전에 액세스해야 하는 동안 tempdb 버전 저장소에 저장되어 있습니다. 현재 버전 저장소의 내용은 sys.dm_tran_version_store에 반환됩니다. 버전 저장소 페이지는 전역 리소스이기 때문에 파일 수준에서 추적됩니다. sys.dm_db_file_space_usage의 version_store_reserved_page_count 열을 사용하여 버전 저장소의 현재 크기를 볼 수 있습니다. 버전 저장소를 정리할 때는 특정 버전에 대한 액세스가 필요한 가장 오래 실행 중인 트랜잭션을 고려해야 합니다. 버전 저장소 정리와 관련하여 가장 오래 실행 중인 트랜잭션을 검색할 때는 sys.dm_tran_active_snapshot_database_transactions의 elapsed_time_seconds 열을 확인하십시오. Transaction 개체의 Free Space in Tempdb (KB) 카운터와 Version Store Size (KB) 카운터는 tempdb에 있는 행 버전 저장소의 크기와 증가율을 모니터링하는 데 사용할 수 있습니다. 자세한 내용은 SQL Server, Transactions 개체를 참조하십시오. tempdb에서 행 버전 관리에 필요한 공간을 예측하려면 먼저 활성 트랜잭션의 모든 변경 내용을 버전 저장소에 유지해야 한다는 것을 고려해야 합니다. 그러면 나중에 시작된 스냅숏 트랜잭션이 이전 버전에 액세스할 수 있습니다. 또한 활성 스냅숏 트랜잭션이 있는 경우 스냅숏 시작 시 활성 상태인 트랜잭션에 의해 생성된 버전 저장소 데이터도 모두 유지해야 합니다. 기본 수식은 다음과 같습니다. [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
|
Service Broker |
Service Broker를 통해 개발자는 독립적인 여러 구성 요소가 함께 작동하여 하나의 태스크를 완수할 수 있도록 느슨한 방식으로 연결된 비동기 응용 프로그램을 쉽게 구축할 수 있습니다. 이러한 응용 프로그램 구성 요소는 태스크를 완료하는 데 필요한 정보가 포함된 메시지를 교환합니다. Service Broker는 메모리에 보관할 수 없는 기존 대화 컨텍스트를 유지하기 위해 tempdb를 명시적으로 사용합니다. 크기는 대화당 약 1KB입니다. 또한 Service Broker는 타이머 이벤트 및 백그라운드에서 전달된 대화에 사용되는 작업 테이블 같이 개체를 쿼리 실행 컨텍스트에서 캐싱하여 tempdb를 암시적으로 사용합니다. |
|
저장 프로시저 |
저장 프로시저는 전역 또는 로컬 임시 테이블과 해당 인덱스, 변수 또는 매개 변수 같은 사용자 개체를 만들 수 있습니다. 저장 프로시저의 임시 개체를 캐시하여 이러한 개체를 삭제하고 만드는 작업을 최적화할 수 있습니다. 이 동작으로 인해 tempdb 디스크 공간 요구 사항이 늘어날 수 있습니다. 다시 사용할 수 있도록 임시 개체당 최대 9페이지가 저장됩니다. 뒷부분에 나오는 "임시 테이블 및 table 변수"를 참조하십시오. |
|
임시 테이블 및 table 변수
|
임시 테이블과 table 변수는 tempdb에 저장됩니다. 임시 테이블 개체의 디스크 공간 요구 사항은 이전 버전의 SQL Server와 같습니다. 임시 테이블 크기를 예측하는 방법은 표준 테이블 크기를 예측하는 방법과 같습니다. 자세한 내용은 테이블 크기 추정을 참조하십시오. table 변수는 지역 변수처럼 작동합니다. table 변수는 table 형식으로, 주로 테이블 반환 함수의 결과 집합으로 반환되는 행 집합을 임시로 저장하는 데 사용됩니다. table 변수를 저장하는 데 필요한 디스크 공간은 선언된 변수의 크기와 해당 변수에 저장된 값에 따라 달라집니다. 다음 조건을 만족할 때 로컬 임시 테이블과 변수가 캐시됩니다.
임시 테이블이나 table 변수를 캐시하면 임시 개체가 더 이상 사용되지 않아도 삭제되지 않으며 대신 잘립니다. 최대 9페이지가 저장되어 호출하는 개체를 다음에 실행할 때 다시 사용됩니다. 캐싱을 사용하면 개체를 삭제하고 만드는 작업이 매우 신속하게 실행되며 페이지 할당 경합이 줄어듭니다. 성능을 최적화하려면 다음 수식을 사용하여 tempdb에서 캐시된 로컬 임시 테이블이나 table 변수에 필요한 디스크 공간을 계산해야 합니다. 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
|
트리거 |
AFTER 트리거에 사용되는 inserted 테이블과 deleted 테이블이 tempdb에 생성됩니다. 즉, 트리거에 의해 업데이트되거나 삭제된 행의 버전이 관리됩니다. 여기에는 트리거를 실행한 문에 의해 수정된 모든 행도 포함됩니다. 트리거에 의해 삽입된 행의 버전은 관리되지 않습니다. INSTEAD OF 트리거는 쿼리와 유사한 방식으로 tempdb를 사용합니다. INSTEAD OF 트리거의 디스크 공간 사용량은 이전 버전의 SQL Server와 같습니다. 이 표의 앞에 나오는 "쿼리"를 참조하십시오. 트리거가 설정된 경우 데이터를 대량 로드하면 삭제 또는 업데이트된 각 행의 복사본이 버전 저장소에 추가됩니다. |
|
사용자 정의 함수 |
사용자 정의 함수는 전역 또는 로컬 테이블과 해당 인덱스, 변수 또는 매개 변수 같은 임시 사용자 개체를 만들 수 있습니다. 예를 들어 테이블 반환 함수의 반환 테이블은 tempdb에 저장됩니다. 스칼라 함수 및 테이블 반환 함수의 매개 변수와 반환 값에 허용되는 데이터 형식은 대부분의 LOB 데이터 형식을 포함합니다. 예를 들어 반환 값은 xml 또는 varchar(max) 데이터 형식일 수 있습니다. 이 표의 앞에 나오는 "LOB(Large Object) 데이터 형식 변수 및 매개 변수"를 참조하십시오. 테이블 반환 사용자 정의 함수의 임시 개체를 캐시하여 이러한 개체를 삭제하고 만드는 작업을 최적화할 수 있습니다. 이 표의 앞에 나오는 "임시 테이블 및 table 변수"를 참조하십시오. |
|
XML |
xml 데이터 형식의 변수와 매개 변수는 최대 2GB가 될 수 있으며 값이 작으면 주 메모리를 저장소로 사용합니다. 그러나 큰 값은 tempdb에 저장됩니다. 이 표의 앞에 나오는 "LOB(Large Object) 데이터 형식 변수 및 매개 변수"를 참조하십시오. sp_xml_preparedocument 시스템 저장 프로시저는 tempdb에 작업 테이블을 만듭니다. MSXML 파서는 이 작업 테이블을 사용하여 구문 분석된 XML 문서를 저장합니다. tempdb의 디스크 공간 요구 사항은 저장 프로시저를 실행할 때 지정된 XML 문서의 크기에 거의 비례합니다. |
SQL Server로의 업그레이드에 대한 용량 계획
프로덕션 환경에서 tempdb의 적절한 크기는 많은 요인에 따라 결정됩니다. 이 항목의 앞부분에서 설명한 것처럼 기존 작업, 사용된 SQL Server 기능 등이 이러한 요인에 포함됩니다. SQL Server 테스트 환경에서 다음 태스크를 수행하여 기존 작업을 분석하는 것이 좋습니다.
tempdb에 대해 자동 증가를 설정합니다.
개별 쿼리 또는 작업 추적 파일을 실행하고 tempdb 공간 사용을 모니터링합니다.
인덱스 다시 작성 및 tempdb 공간 모니터링 같은 인덱스 유지 관리 작업을 실행합니다.
이전 단계의 공간 사용 값을 사용하여 전체 작업 사용량을 예측하고 예상 동시 작업에 대해 이 값을 조정한 다음 tempdb의 크기를 알맞게 설정합니다.
tempdb 공간을 모니터링하는 방법은 tempdb의 디스크 공간 부족 문제 해결을 참조하십시오. 인덱스 작업 중에 tempdb 사용량을 예측하는 방법은 인덱스 디스크 공간 예를 참조하십시오.
프로덕션 환경에 대한 tempdb 구성
tempdb 성능을 최적화하려면 tempdb 성능 최적화에서 제공하는 지침과 권장 구성을 따르십시오.
tempdb 사용 모니터링 방법
tempdb에 디스크 공간이 부족하면 SQL Server 프로덕션 환경에 심각한 장애가 발생할 수 있으며 실행 중인 응용 프로그램이 작업을 완료하지 못할 수 있습니다. sys.dm_db_file_space_usage 동적 관리 뷰를 사용하여 tempdb 파일에서 이러한 기능에 의해 사용되는 디스크 공간을 모니터링할 수 있습니다. 또한 sys.dm_db_session_space_usage 및 sys.dm_db_task_space_usage 동적 관리 뷰를 사용하면 세션이나 태스크 수준에서 tempdb의 페이지 할당 또는 할당 취소 작업을 모니터링할 수 있습니다. 이러한 뷰를 사용하면 tempdb 디스크 공간을 많이 사용하는 큰 쿼리, 임시 테이블 또는 테이블 변수를 식별할 수 있습니다. 또한 tempdb에서 사용 가능한 여유 공간과 tempdb를 사용 중인 리소스를 모니터링하는 데 사용할 수 있는 여러 가지 카운터가 있습니다. 자세한 내용은 tempdb의 디스크 공간 부족 문제 해결을 참조하십시오.