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

  • 아티클

이 문서는 서버에서 부하가 많이 발생할 때 심각한 차단이 발생하는 문제를 resolve 데 도움이 됩니다.

원래 제품 버전: SQL Server
원본 KB 번호: 2154845

증상

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

참고

페이지가 8088까지 균등하게 나눌 수 있는 경우 PFS 페이지입니다. 예를 들어 2:3:905856 페이지는 tempdb의 file_id=3에 있는 PFS입니다.

다음 작업은 tempdb 를 광범위하게 사용합니다.

  • 임시 테이블(로컬 또는 전역)의 반복적인 만들기 및 삭제 작업입니다.
  • 스토리지에 tempdb 를 사용하는 테이블 변수입니다.
  • CURSORS와 연결된 작업 테이블입니다.
  • ORDER BY 절과 연결된 작업 테이블입니다.
  • GROUP BY 절과 연결된 작업 테이블입니다.
  • HASH PLANS와 연결된 작업 파일입니다.

이러한 활동으로 인해 경합 문제가 발생할 수 있습니다.

원인

tempdb 데이터베이스를 많이 사용하는 경우 SQL Server 페이지를 할당하려고 할 때 경합이 발생할 수 있습니다. 경합 정도에 따라 tempdb 와 관련된 쿼리 및 요청이 잠시 응답하지 않을 수 있습니다.

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

혼합 범위에서 페이지를 할당하려면 SQL Server PFS(페이지 사용 가능한 공간) 페이지를 검사하여 할당할 수 있는 혼합 페이지를 확인해야 합니다. PFS 페이지는 모든 페이지에서 사용 가능한 공간을 추적하고 각 PFS 페이지는 약 8,000페이지를 추적합니다. PFS 및 SGAM 페이지를 변경하기 위해 적절한 동기화가 유지됩니다. 짧은 기간 동안 다른 한정자를 중단시킬 수 있습니다.

SQL Server 할당할 혼합 페이지를 검색할 때 항상 동일한 파일 및 SGAM 페이지에서 검사를 시작합니다. 이로 인해 여러 혼합 페이지 할당이 진행 중인 경우 SGAM 페이지에서 경합이 심합니다. 이로 인해 증상 섹션에 설명된 문제가 발생할 수 있습니다 .

참고

할당 해제 작업도 페이지를 수정해야 합니다. 이로 인해 경합이 증가할 수 있습니다.

SQL Server(SGAM, GAM, PFS, IAM)에서 사용되는 다양한 할당 메커니즘에 대한 자세한 내용은 참조 섹션을 참조하세요.

해결 방법

크기가 같은 tempdb 데이터 파일 수 늘리기

예를 들어 tempdb 의 단일 데이터 파일 크기가 8GB이고 로그 파일 크기가 2GB인 경우 데이터 파일 수를 8(8)(동일한 크기 조정을 유지하기 위해 각각 1GB)으로 늘리고 로그 파일을 그대로 두는 것이 좋습니다. 서로 다른 데이터 파일을 별도의 디스크에 두면 추가 성능 이점이 제공됩니다. 그러나 이 작업은 필요하지 않습니다. 파일은 동일한 디스크 볼륨에 공존할 수 있습니다.

tempdb 데이터 파일의 최적 수는 tempdb에 표시되는 경합 정도에 따라 달라집니다. 시작점으로 tempdb가 SQL Server 할당된 논리 프로세서 수와 같도록 구성할 수 있습니다. 고급 시스템의 경우 시작 번호는 8(8)일 수 있습니다. 경합이 줄어들지 않으면 데이터 파일 수를 늘려야 할 수 있습니다.

데이터 파일의 크기 조정을 동일하게 사용하는 것이 좋습니다. SQL Server 2000 SP4(서비스 팩 4)는 혼합 페이지 할당에 라운드 로빈 알고리즘을 사용하는 수정 사항을 도입했습니다. 이러한 개선으로 인해 시작 파일은 연속된 혼합 페이지 할당마다 다릅니다(둘 이상의 파일이 있는 경우). SGAM에 대한 새 할당 알고리즘은 순수한 라운드 로빈이며 속도를 유지하기 위해 비례 채우기를 존중하지 않습니다. 모든 tempdb 데이터 파일을 동일한 크기로 만드는 것이 좋습니다.

tempdb 데이터 파일 수를 늘리면 경합이 감소하는 방법

다음 목록에서는 크기가 같은 tempdb 데이터 파일 수를 늘리면 경합이 감소하는 방법을 설명합니다.

  • tempdb에 대한 데이터 파일이 하나 있는 경우 4GB 간격마다 하나의 GAM 페이지와 하나의 SGAM 페이지만 있습니다.

  • tempdb의 크기가 같은 데이터 파일 수를 늘리면 각 데이터 파일에 대해 하나 이상의 GAM 및 SGAM 페이지가 효과적으로 만들어집니다.

  • GAM의 할당 알고리즘은 비례 채우기를 준수하면서 라운드 로빈 방식으로 파일 수에서 한 번에 1개 범위(연속 페이지 8개)를 할당합니다. 따라서 동일한 크기의 파일이 10개 있는 경우 첫 번째 할당은 File1, 두 번째 할당은 File2, File3의 세 번째 할당 등입니다.

  • GAM이 페이지를 할당하기 때문에 한 번에 8페이지가 FULL로 표시되므로 PFS 페이지의 리소스 경합이 줄어듭니다.

추적 플래그 -T1118을 구현하여 경합을 줄이는 방법

참고

이 섹션은 SQL Server 2014 및 이전 버전에만 적용됩니다.

다음 목록에서는 추적 플래그 -T1118 을 사용하여 경합을 줄이는 방법을 설명합니다.

  • -T1118 은 서버 전체 설정입니다.
  • SQL Server 재활용된 후에도 추적 플래그가 계속 적용되도록 SQL Server 대한 시작 매개 변수에 -T1118 추적 플래그를 포함합니다.
  • -T1118 은 서버의 거의 모든 단일 페이지 할당을 제거합니다.
  • 대부분의 단일 페이지 할당을 사용하지 않도록 설정하면 SGAM 페이지에서 경합을 줄일 수 있습니다.
  • -T1118이 켜진 경우 거의 모든 새 할당은 추적 플래그 없이 개체의 처음 8페이지에 대한 익스텐트에서 단일 페이지가 아닌 한 번에 8페이지(1익스텐트)를 개체에 할당하는 GAM 페이지(예: 2:1:2)에서 수행됩니다.
  • -T1118이 켜진 경우에도 IAM 페이지는 SGAM 페이지의 단일 페이지 할당을 계속 사용합니다. 그러나 핫픽스 8.00.0702 및 증가된 tempdb 데이터 파일과 결합하면 SGAM 페이지에서 경합이 감소합니다. 공간 문제는 다음 섹션을 참조하세요.

단점

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

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

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

참조