SQL Server tempdb 데이터베이스에서 할당 경합을 줄이기 위한 권장 사항

오리지날 제품 버전: SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022
원래 KB 번호: 2154845

요약

이 문서에서는 SQL Server tempdb 데이터베이스에서 할당 경합을 진단하고 줄이는 방법을 설명합니다. PFS(페이지 여유 공간), GAM(전역 할당 맵) 및 SGAM(공유 전역 할당 맵) 페이지의 PAGELATCH_UP 대기를 다룹니다. 동일한 크기의 여러 tempdb 데이터 파일을 사용하고 이전 버전에서는 플래그 1118 및 1117을 추적하여 동시 워크로드가 많은 상태에서 SQL Server 성능을 향상시키는 방법을 설명합니다.

증상

Microsoft SQL Server 실행하는 서버에서 서버가 부하가 많이 발생하는 경우 심각한 차단을 확인할 수 있습니다. 동적 관리 뷰 sys.dm_exec_requests 및 sys.dm_os_waiting_tasks는 요청이나 태스크가 tempdb 리소스를 기다리고 있음을 보여줍니다. 대기 유형은 PAGELATCH_UP이고, 대기 리소스는 tempdb의 페이지를 가리킵니다. 이러한 페이지는 2:1:1, 2:1:3 등의 형식일 수 있습니다(PFS 및 SGAM 페이지 tempdb).

다음 작업에서는 tempdb가 광범위하게 사용됩니다.

• 임시 테이블(로컬 또는 전역)에 대한 반복적인 만들기 및 삭제 작업입니다.
• 스토리지에 사용하는 tempdb 테이블 변수입니다.
• 커서와 연결된 작업 테이블입니다.
• ORDER BY절과 관련된 작업 테이블.
• GROUP BY 절과 연관된 작업 테이블입니다.
• 해시 계획과 연결된 작업 파일입니다.

이러한 활동은 경합을 일으킬 수 있습니다.

원인

tempdb 많이 사용되는 경우 SQL Server 페이지를 할당하는 동안 경합이 발생할 수 있습니다. 경합 정도에 따라 사용하는 tempdb 쿼리 및 요청이 잠시 응답하지 않을 수 있습니다.

이전 버전의 SQL Server 개체를 만드는 동안 시스템은 혼합 익스텐트에서 두 페이지를 할당하고 새 개체에 할당합니다. 한 페이지는 IAM( 인덱스 할당 맵 ) 페이지이고 두 번째 페이지는 개체의 첫 번째 페이지입니다. SQL Server는 SGAM(공유 전역 할당 맵) 페이지를 사용하여 혼합 익스텐트를 추적합니다. 각 SGAM 페이지는 약 4GB의 데이터를 추적합니다.

혼합 익스텐트에서 페이지를 할당하려면 SQL Server PFS(페이지 사용 가능한 공간) 페이지를 검색하여 무료 혼합 페이지를 찾습니다. PFS 페이지는 모든 페이지에서 사용 가능한 공간을 추적하고 각 PFS 페이지는 약 8,000페이지를 추적합니다. 동기화는 SQL Server PFS 및 SGAM 페이지를 변경하는 경우 필요하며, 동기화는 짧은 기간 동안 다른 한정자를 중단시킬 수 있습니다.

SQL Server는 할당할 혼합 페이지를 검색할 때 항상 동일한 파일 및 SGAM 페이지에서 검사를 시작합니다. 이 동작은 여러 혼합 페이지 할당이 동시에 실행될 때 SGAM 페이지에서 격렬한 경합을 일으키며, 이는 앞에서 설명한 증상을 생성합니다.

SQL Server 사용하는 할당 메커니즘(SGAM, GAM, PFS 및 IAM)에 대한 자세한 내용은 관계 콘텐츠 섹션을 참조하세요.

해결 방법

2014년 SQL Server 2024년 7월 9일에 추가 지원이 종료되었으므로 대부분의 독자는 SQL Server 2016 이상 버전 지침을 따라야 합니다.

SQL Server 2016 이상 버전

1. tempdb 데이터베이스에서 현재 성능 지침을 검토합니다.
2. tempdb에 여러 개의 동일한 크기의 데이터 파일이 있는지 확인하십시오. 시작점으로 논리 프로세서당 최대 8개의 파일당 하나의 데이터 파일을 사용합니다. 경합이 계속되면 논리 프로세서 수까지 4개의 그룹으로 파일을 추가합니다.
3. 추가 할당 향상을 위해 최신 누적 업데이트로 SQL Server 인스턴스를 최신 상태로 유지합니다. 예를 들어 KB 4099472: SQL Server 2014, 2016 및 2017 PFS 페이지 라운드 로빈 알고리즘 향상을 참조하세요.
4. SQL Server 2019 이상 버전에서 경합이 지속되는 경우 동시 PFS 업데이트 및 메모리 최적화 메타데이터에 대한 tempdb 지침을 검토합니다.

SQL Server 2014 및 이전 버전

동시성을 tempdb향상시키려면 다음 방법을 시도해 보세요.

• 디스크 대역폭을 최대화하고 할당 구조에 tempdb 대한 경합을 줄이기 위해 데이터 파일 수를 늘립니다. 일반적으로 논리 프로세서 수가 8개 이하인 경우 논리 프로세서와 동일한 수의 데이터 파일을 사용합니다. 논리 프로세서 수가 8보다 큰 경우 8개의 데이터 파일로 시작합니다. 경합이 계속되면 논리 프로세서 수까지 4개의 그룹으로 데이터 파일을 추가하거나 워크로드 또는 코드를 변경합니다.

• SQL Server 2005의 tempdb 작업의 모범 사례 지침을 검토하세요.

• 이전 단계로도 할당 경합이 크게 줄어들지 않고 경합이 SGAM 페이지에서 발생하는 경우 추적 플래그 -T1118를 사용하도록 설정합니다. 이 추적 플래그에서 SQL Server 각 데이터베이스 개체에 전체 익스텐트 할당을 수행하여 SGAM 페이지의 경합을 크게 줄입니다.

  참고

  • 이 추적 플래그는 SQL Server 인스턴스의 모든 데이터베이스에 영향을 줍니다. 할당 경합이 SGAM 페이지에 있는지 확인하려면 DML 작업으로 인한 모니터링 경합을 참조하세요.
  • SQL Server 2014의 경우 서비스 팩 3을 적용하여 KB 4099472 수정을 수행하면 동일한 데이터 파일의 여러 PFS 페이지에서 라운드 로빈 PFS 페이지 할당을 수행하여 경합이 더욱 줄어듭니다.

tempdb 할당에 대한 자세한 정보

SQL Server 2016 이상 버전의 기본 tempdb 동작

SQL Server 2016부터는 기본적으로 tempdb 할당 경합이 감소하므로 일반적으로 추적 플래그 1118 또는 1117을 사용하도록 설정할 필요가 없습니다.

• tempdb에 대한 혼합 익스텐트 할당은 기본적으로 비활성화됩니다 (이전에 추적 플래그 1118로 활성화되던 동작). 모든 새 할당은 균일한 익스텐트에서 제공됩니다.
• 모든 tempdb 데이터 파일이 함께 자동 증가 합니다(이전에 추적 플래그 1117에서 사용하도록 설정된 동작).
• SQL Server 설치 프로그램은 논리 프로세서 수에 따라 최대 8개까지 동일한 크기의 tempdb 데이터 파일을 권장합니다.
• 동시 PFS 업데이트 SQL Server 2019 이상 버전에서 PFS 페이지 래치 경합을 추가로 줄입니다.

SQL Server 2016 이상 버전을 실행하는 경우 크기 조정 및 tempdb 파일 수에 집중합니다. SQL Server 2014 이전 버전에서만 추적 플래그를 사용합니다.

tempdb 데이터 파일 크기를 동일하게 설정한 예

예를 들어 단일 tempdb 데이터 파일이 8GB이고 로그 파일이 2GB인 경우 데이터 파일 수를 8개(동일한 크기 조정을 유지하기 위해 각각 1GB)로 늘리고 로그 파일을 변경하지 않은 상태로 둡니다. 별도의 디스크에 데이터 파일을 배치하면 추가적인 성능 이점을 제공할 수 있지만 필수는 아닙니다. 파일은 동일한 디스크 볼륨에 공존할 수 있습니다.

최적의 tempdb 데이터 파일 수는 관찰되는 경합 수준에 따라 달라집니다. 시작점으로 파일 수를 SQL Server 할당된 논리 프로세서 수와 동일하게 설정합니다. 고급 시스템의 경우 8개의 파일로 시작합니다. 경합이 줄어들지 않으면 데이터 파일을 더 추가하세요.

모든 데이터 파일에 동일한 크기 조정을 사용합니다. SQL Server 2000 SP4(서비스 팩 4)는 혼합 페이지 할당에 라운드 로빈 알고리즘을 사용하는 수정 사항을 도입했습니다. 이 향상된 기능으로 두 개 이상의 파일이 있는 경우 각 연속 혼합 페이지 할당에 대한 시작 파일이 변경됩니다. SGAM 할당 알고리즘은 순수 라운드 로빈이며 비례 채우기를 적용하지 않으므로 모든 tempdb 데이터 파일을 동일한 크기로 만듭니다.

tempdb 데이터 파일이 더 많을수록 경합이 감소하는 방법

동일한 크기의 tempdb 데이터 파일을 추가하면 다음과 같은 방법으로 경합이 줄어듭니다.

• 데이터 tempdb파일이 하나 있는 경우 각 4GB 공간에 대해 하나의 GAM 페이지와 하나의 SGAM 페이지가 있습니다.
• 크기가 같은 더 많은 데이터 파일은 각 데이터 파일에 대해 하나 이상의 GAM 및 SGAM 페이지를 효과적으로 만듭니다.
• GAM 할당 알고리즘은 비례 채우기 원칙을 유지하면서 파일들로부터 라운드 로빈 방식으로 한 번에 하나의 익스텐트(연속된 8개 페이지)를 할당합니다. 예를 들어 크기가 같은 파일이 10개인 경우 첫 번째 할당은 File1, 두 번째 할당은 File2, 세 번째는 File3 등입니다.
• GAM이 페이지를 할당할 때 한 번에 8개 페이지가 FULL 상태로 표시되므로 PFS 페이지 경합이 줄어듭니다.

추적 플래그 -T1118이 경합을 줄이는 방법

추적 플래그 -T1118 는 다음과 같은 방법으로 경합을 줄입니다.

• -T1118 는 서버 전체 설정입니다.
• SQL Server 다시 시작한 후에도 추적 플래그가 계속 적용되도록 SQL Server 시작 매개 변수에 -T1118 추가합니다.
• -T1118 는 서버에서 거의 모든 단일 페이지 할당을 제거합니다.
• 대부분의 단일 페이지 할당을 사용하지 않도록 설정하면 SGAM 페이지 경합이 줄어듭니다.
• -T1118이 켜져 있으면 거의 모든 새 할당은 개체의 처음 8페이지에 대해서는 익스텐트에서 단일 페이지를 할당하는 대신, 한 번에 8페이지(익스텐트 1개)를 개체에 할당하는 GAM 페이지(예: 2:1:2)에서 이루어집니다.
• IAM 페이지는 -T1118이 설정되어 있어도 SGAM 페이지로부터 단일 페이지 할당을 계속 사용합니다. 그러나 핫픽스 8.00.0702 및 더 많은 tempdb 데이터 파일과 결합하면 순 효과는 SGAM 페이지 경합을 줄입니다. 공간 문제는 다음 섹션을 참조하세요.

최신 SQL Server의 기본 동작과 비교한 추적 플래그 1118

추적 플래그 -T1118 단점

사용 -T1118 의 단점은 다음 조건이 모두 충족되면 데이터베이스 크기가 증가할 수 있다는 것입니다.

• 사용자 데이터베이스에 새 개체를 만듭니다.
• 각 새 개체는 64KB 미만의 스토리지를 차지합니다.

이러한 조건에서 SQL Server 56KB의 스토리지를 낭비하는 8KB만 필요한 개체에 대해 64KB(8페이지 * 8KB)를 할당할 수 있습니다. 새 개체가 수명 동안 64KB(8페이지) 이상을 사용하는 경우 추적 플래그에는 단점이 없습니다. 최악의 경우 SQL Server 첫 번째 할당 중에 7개의 추가 페이지를 할당하지만 한 페이지 이상으로 증가하지 않는 새 개체에 대해서만 할당합니다.

관련 콘텐츠

• tempdb 데이터베이스
• DBCC TRACEON: 추적 플래그(Transact-SQL)