tempdb 튜닝

tempdb 데이터베이스를 많이 사용하면 SQL Server에서 페이지를 할당하려고 할 때 경합이 일어날 수 있음

tempdb를 사용하게 되는 경우

1. 임시 테이블(로컬 또는 전역)을 반복해서 만들고 삭제

2. 저장 목적으로 tempdb를 사용하는 테이블 변수

3. CURSORS와 관련된 작업 테이블

4. ORDER BY 절과 관련된 작업 테이블

5. GROUP BY 절과 관련된 작업 테이블

6. HASH PLANS와 관련된 작업 파일

현상확인

SELECT * FROM sys.sysprocesses 시스템 테이블 출력에서

waitresource가 "2:1:1"(PFS 페이지) 또는 "2:1:3"(SGAM 페이지)으로 표시될 수 있음

경합 정도에 따라 잠시 동안 SQL Server가 응답하지 않는 것처럼 나타날 수도 있음

원인

mixed extent에서 페이지 할당의 일환으로 SQL Server는 PFS(Page Free Space) 페이지를 검색하여

어떤 혼합 페이지를 할당할 수 있는지 확인해야 한다. PFS 페이지는 모든 페이지에서 사용할 수 있는

여유 공간을 추적하고 각 PFS 페이지는 약 8000 페이지를 추적한다.

PFS 및 SGAM 페이지를 변경하기 위해 적절한 동기화가 유지되며

이를 통해 잠시 동안 다른 수정 작업을 막을 수 있다.

키워드 : 혼합 익스텐트, 서로 다른 임시 오브젝트, 여유공간 추적, 동기화 대기

해결방법

1. 최신 핫픽스 유지

2. 추적플래그 -T1118 사용 (mixed extent가 아닌 uniform extent로 운영)

3. 프로세서 수와 동일하게 tempdb 데이터 파일 수를 늘리고 동일한 크기로 파일 생성

(동일한 크기의 파일이 여러개 있는 경우 첫 번째 할당은 File1, 두 번째 할당은 File2, 세 번째 파일은 File3, ...)

4. tempdb의 위치를 데이터파일과 다른 디스크로 분리

단점

사용자 데이터베이스에 새 개체가 만들어질 때 각각의 새 개체는 64KB 미만의 공간을 차지함.

FIX: tempdb 데이터베이스에 대한 동시성 강화

mixed extent 와 uniform extent