증상
tempdb 데이터베이스가 많이 사용되는 경우 SQL Server에서 페이지를 할당하려고 할 때 경합이 발생할 수 있습니다.
sysprocesses 시스템 테이블 출력에서 waitresource는 "2:1:1"(PFS 페이지) 또는 "2:1:3"(SGAM 페이지)로 표시될 수 있습니다. 경합 정도에 따라 SQL Server가 짧은 시간 동안 응답하지 않는 것으로 나타날 수도 있습니다.
이러한 작업은 tempdb를 많이 사용합니다.
- 임시 테이블(로컬 또는 글로벌)의 반복적인 생성 및 삭제
- 저장소 목적으로 tempdb를 사용하는 테이블 변수입니다.
- 커서와 연관된 작업 테이블.
- ORDER BY 절과 연관된 작업 테이블입니다.
- GROUP BY 절과 연결된 작업 테이블입니다.
- HASH PLANS와 연결된 작업 파일입니다.
이러한 활동을 과도하게 많이 사용하면 경합 문제가 발생할 수 있습니다.
원인
개체를 만드는 동안 혼합 범위에서 두(2)개의 페이지를 할당하고 새 개체에 할당해야 합니다. 한 페이지는 IAM(인덱스 할당 맵)용이고 두 번째 페이지는 개체의 첫 번째 페이지용입니다. SQL Server는 SGAM(공유 글로벌 할당 맵) 페이지를 사용하여 혼합 범위를 추적합니다. 각 SGAM 페이지는 약 4GB의 데이터를 추적합니다.
혼합 범위에서 페이지를 할당하는 과정의 일환으로 SQL Server는 PFS(페이지 사용 가능 공간) 페이지를 검사하여 자유롭게 할당할 수 있는 혼합 페이지를 찾아야 합니다. PFS 페이지는 모든 페이지에서 사용 가능한 공간을 추적하며 각 PFS 페이지는 약 8000페이지를 추적합니다. PFS 및 SGAM 페이지를 변경하기 위해 적절한 동기화가 유지됩니다. 다른 한정자가 잠시 동안 지연될 수 있습니다.
SQL Server는 할당할 혼합 페이지를 검색할 때 항상 동일한 파일 및 SGAM 페이지에서 검색을 시작합니다. 이로 인해 여러 혼합 페이지 할당이 진행 중일 때 SGAM 페이지에서 격렬한 경합이 발생하여 이 문서의 "증상" 섹션에 설명된 문제가 발생할 수 있습니다.
참고: 할당 취소 활동은 또한 페이지를 수정해야 하므로 경합 증가에 기여할 수 있습니다.
SQL Server에서 사용하는 다양한 할당 메커니즘(SGAM, GAM, PFS, IAM)에 대한 자세한 내용은 이 문서의 "참조" 섹션을 참조하세요.
해결 방법
Microsoft SQL Server 2000
사용량이 많은 tempdb에 대한 할당 리소스 경합을 줄이려면 다음 모든 단계를 수행합니다.
Microsoft SQL Server 2000용 서비스 팩 4를 적용합니다. SQL Server 2000 서비스 팩 4(SP4)는 다음 Microsoft 웹 사이트에서 다운로드할 수 있습니다.
https://www.microsoft.com/download/details.aspx?familyid=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5
자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료 문서를 참조하세요.
290211 최신 SQL Server 2000 서비스 팩을 구하는 방법
추적 플래그 -T1118을 구현합니다.
tempdb의 데이터 파일 수를 늘려 디스크 대역폭을 최대화하고 할당 구조에서의 경합을 줄입니다. 일반적으로 논리 프로세서 수가 8보다 작거나 같으면 논리 프로세서와 동일한 수의 데이터 파일을 사용합니다. 논리 프로세서 수가 8보다 큰 경우 8개의 데이터 파일을 사용한 다음, 경합이 계속되면 경합이 허용 가능한 수준으로 줄어들 때까지 데이터 파일 수를 4의 배수(논리 프로세서 수까지)로 늘리거나 워크로드/코드를 변경합니다.
참고 이러한 단계는 Microsoft SQL Server 7.0에도 적용됩니다. 유일한 예외는 SQL Server 7.0용 핫픽스가 없다는 점이므로 1단계가 적용되지 않는다는 것입니다.
2단계인 Microsoft SQL Server 7.0용 추적 플래그 -T1118 사용과 관련하여 추적 플래그를 사용하기 전에 Microsoft 기술 자료의 다음 문서를 참조하십시오.
813492 해결: 추적 플래그 1118을 사용하면 SQL Server 7.0에서 인덱스를 만들 수 없습니다.
Microsoft SQL Server 2005 이상 버전
사용량이 많은 tempdb에 대한 할당 리소스 경합을 줄이려면 다음 단계를 수행합니다.
- 추적 플래그 -T1118을 구현합니다.
- tempdb의 데이터 파일 수를 늘려 디스크 대역폭을 최대화하고 할당 구조에서의 경합을 줄입니다. 일반적으로 논리 프로세서 수가 8보다 작거나 같으면 논리 프로세서와 동일한 수의 데이터 파일을 사용합니다. 논리 프로세서 수가 8보다 큰 경우 8개의 데이터 파일을 사용한 다음, 경합이 계속되면 경합이 허용 가능한 수준으로 줄어들 때까지 데이터 파일 수를 4의 배수(논리 프로세서 수까지)로 늘리거나 워크로드/코드를 변경합니다.
추가 정보
SQL 2000 SP4 이상 버전에서 픽스를 통해 경합을 줄이는 방법
SQL Server 2000 SP4 이상 버전에는 혼합 페이지 할당을 위한 라운드 로빈 알고리즘을 도입하는 수정 사항이 있습니다. 수정을 통해 시작 파일은 각 연속 혼합 페이지 할당에 대해 다릅니다(둘 이상의 파일이 있는 경우). 이렇게 하면 동일한 출발점에서 매번 동일한 순서로 SGAM을 통과하는 열차를 분할하여 경합 문제를 방지할 수 있습니다. SGAM에 대한 새로운 할당 알고리즘은 순수 라운드 로빈이며 속도를 유지하기 위해 비례 채우기를 따르지 않습니다. 같은 크기의 tempdb 데이터 파일을 만드는 것이 좋습니다.
추적 플래그 -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 페이지에서 경합이 줄어드는 순효과가 있습니다. 공간 문제의 경우 이 문서의 "단점" 섹션을 참조하세요.
동일한 크기의 tempdb 데이터 파일 수 늘리기
tempdb의 데이터 파일 크기가 5GB이고 로그 파일 크기가 5GB이면 단일 데이터 파일을 10개(동일한 크기를 유지하기 위해 각각 500MB)로 늘리고 로그 파일은 그대로 두는 것이 좋습니다. 다른 데이터 파일을 별도의 디스크에 보관하는 것이 좋습니다. 그러나 필수는 아니며 동일한 디스크에 공존할 수 있습니다.
tempdb 데이터 파일의 최적 개수는 tempdb에 나타나는 경합 정도에 따라 달라집니다. 시작점으로 tempdb를 최소한 SQL Server에 할당된 프로세서 수와 동일하게 구성할 수 있습니다. 고급 시스템(예: 16 또는 32 proc)의 경우 시작 번호는 10이 될 수 있습니다. 경합이 줄어들지 않으면 데이터 파일 수를 더 늘려야 할 수 있습니다.
참고: 듀얼 코어 프로세서는 두 개의 프로세서로 간주됩니다.
비례 채우기 알고리즘은 파일 크기를 기반으로 하므로 데이터 파일의 크기를 동일하게 조정하는 것이 중요합니다. 데이터 파일이 같지 않은 크기로 만들어지는 경우 비례 채우기 알고리즘은 모든 파일 간에 할당을 분산하는 대신 GAM 할당에 가장 큰 파일을 더 많이 사용하려고 시도하므로 여러 데이터 파일을 만드는 목적이 무효화됩니다.
tempdb 데이터 파일의 자동 증가는 또한 비례 채우기 알고리즘을 방해할 수 있습니다. 따라서 tempdb 데이터 파일에 대한 자동 증가 기능을 해제하는 것이 좋습니다. 자동 증가 옵션이 꺼져 있는 경우 tempdb를 사용하여 서버에 디스크 공간 부족 현상이 발생하지 않도록 데이터 파일을 만들어야 합니다.
동일한 크기의 tempdb 데이터 파일 수를 늘려 경합을 줄이는 방법
다음은 동일한 크기의 tempdb 데이터 파일 수를 늘려 경합을 줄이는 방법에 대한 목록입니다.
- tempdb에 대해 하나의 데이터 파일을 사용하면 각 4GB의 공간에 대해 하나의 GAM 페이지와 하나의 SGAM 페이지만 있습니다.
- 다음에 대해 동일한 크기의 데이터 파일 수를 늘립니다.
tempdb는 각 데이터 파일에 대해 하나 이상의 GAM 및 SGAM 페이지를 효율적으로 만듭니다. - GAM에 대한 할당 알고리즘은 비례 채우기를 적용하면서 라운드 로빈 방식으로 파일 수에서 한 번에 하나의 범위(연속 8페이지)를 제공합니다. 따라서 동일한 크기의 파일이 10개 있는 경우 첫 번째 할당은 File1에서, 두 번째는 File2에서, 세 번째는 File3에서 할당됩니다.
- GAM이 페이지를 할당하기 때문에 한 번에 8페이지가 FULL로 표시되므로 PFS 페이지의 리소스 경합이 줄어듭니다.
단점
앞에서 언급한 권장 사항의 유일한 단점은 다음 조건이 true일 때 데이터베이스 크기가 증가할 수 있다는 것입니다.
- 사용자 데이터베이스에 새 개체가 만들어집니다.
- 각 새 개체는 64KB 미만의 스토리지를 차지합니다.
이러한 조건이 참이면 8KB의 공간만 필요한 개체에 64KB(8페이지 * 8KB = 64KB)를 할당하여 56KB의 스토리지를 낭비할 수 있습니다. 그러나 새 개체가 수명 동안 64KB(8페이지) 이상을 사용하는 경우 추적 플래그에 단점이 없습니다. 따라서 최악의 경우 SQL Server는 1페이지를 초과하지 않는 새 개체에 대해서만 첫 번째 할당 중에 7페이지를 추가로 할당하게 될 수 있습니다.
참조 자료
GAM, SGAM, PFS 및 IAM에 대한 자세한 내용은 다음 SQL Server 2000 온라인 설명서 topics를 참조하십시오.
- "개체가 사용하는 공간 관리"
- "익스텐트 할당 및 사용 가능한 공간 관리"
- "테이블 및 인덱스 아키텍처"
- "힙 구조"
추가 참조
SQL Server 2005의 tempdb 데이터베이스에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하십시오.
https://technet.microsoft.com/library/cc966545.aspx
tempdb 데이터베이스 파일 및 Trace Tlag 1118에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하세요.
SQL Server 2005 및 SQL Server 2008에서 추적 플래그 1118을 사용하는 방법에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하세요.
tempdb 데이터베이스의 할당 병목 상태를 모니터링하고 해결하는 방법에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하십시오.