증상
tempdb 데이터베이스를 많이 사용하는 경우 SQL Server 페이지를 할당하려고 할 때 경합이 발생할 수 있습니다.
sysprocesses 시스템 테이블 출력에서 waitresource는 "2:1:1"(PFS 페이지) 또는 "2:1:3"(SGAM 페이지)로 표시할 수 있습니다. 경합 정도에 따라 짧은 기간 동안 응답하지 않는 SQL Server 나타날 수도 있습니다.
이러한 작업은 tempdb를 많이 사용합니다.
- 임시 테이블(로컬 또는 전역)을 반복적으로 만들고 삭제합니다.
- 스토리지 목적으로 tempdb를 사용하는 테이블 변수입니다.
- CURSORS와 연결된 작업 테이블입니다.
- ORDER BY 절과 연결된 작업 테이블입니다.
- GROUP BY 절과 연결된 작업 테이블입니다.
- HASH PLANS와 연결된 작업 파일입니다.
이러한 활동을 많이 사용하고 상당히 많이 사용하면 경합 문제가 발생할 수 있습니다.
원인
개체를 만드는 동안 혼합 익스텐트에서 두 페이지(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)에서 사용하는 다양한 할당 메커니즘에 대한 자세한 내용은 이 문서의 "참조" 섹션을 참조하세요.
해결 방법
Microsoft SQL Server 2000
사용량이 많은 tempdb에 대한 할당 리소스 경합을 줄이려면 다음 모든 단계를 수행합니다.
Microsoft SQL Server 2000용 서비스 팩 4를 적용합니다. SQL Server 2000 SP4(서비스 팩 4)는 다음 Microsoft 웹 사이트에서 사용할 수 있습니다.
http://www.microsoft.com/download/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5
자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료 문서를 참조하세요.
290211 최신 SQL Server 2000 서비스 팩을 가져오는 방법
추적 플래그 -T1118을 구현합니다.
tempdb의 데이터 파일 수를 늘려 디스크 대역폭을 최대화하고 할당 구조에서 경합을 줄입니다. 일반적으로 논리 프로세서 수가 8보다 작거나 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에 대한 하나의 데이터 파일을 사용하면 GAM 페이지가 하나만 있고 각 4GB 공간에 대해 하나의 SGAM 페이지만 있습니다.
- 의 크기가 같은 데이터 파일 수 늘리기
tempdb는 각 데이터 파일에 대해 하나 이상의 GAM 및 SGAM 페이지를 효과적으로 만듭니다. - GAM에 대한 할당 알고리즘은 비례 채우기를 적용하면서 라운드 로빈 방식으로 파일 수에서 한 번에 한 익스텐트(8개의 연속 페이지)를 제공합니다. 따라서 같은 크기의 파일이 10개 있는 경우 첫 번째 할당은 File1, 두 번째 할당은 File2, File3의 세 번째 할당 등입니다.
- GAM이 페이지를 할당하기 때문에 한 번에 8개 페이지가 FULL로 표시되므로 PFS 페이지의 리소스 경합이 줄어듭니다.
단점
앞에서 언급한 권장 사항의 유일한 단점은 다음 조건이 충족될 때 데이터베이스 크기가 증가하는 것을 볼 수 있다는 것입니다.
- 사용자 데이터베이스에 새 개체가 만들어집니다.
- 각 새 개체는 64KB 미만의 스토리지를 차지합니다.
이러한 조건이 맞으면 8KB의 공간만 필요하므로 56KB의 스토리지를 낭비하는 개체에 대해 64KB(8페이지 * 8KB = 64KB)를 할당할 수 있습니다. 그러나 새 개체가 수명 동안 64KB(8페이지) 이상을 사용하는 경우 추적 플래그가 있는 단점은 없습니다. 따라서 최악의 경우 SQL Server 첫 번째 할당 중에 1페이지를 넘지 않는 새 개체에 대해서만 7개의 추가 페이지를 할당하게 될 수 있습니다.
참조 자료
GAM, SGAM, PFS 및 IAM에 대한 자세한 내용은 다음 SQL Server 2000 온라인 설명서 topics 참조하세요.
- "개체에서 사용하는 공간 관리"
- "익스텐트 할당 및 사용 가능한 공간 관리"
- "테이블 및 인덱스 아키텍처"
- "힙 구조"
추가 참조
SQL Server 2005의 tempdb 데이터베이스에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하세요.
http://technet.microsoft.com/en-us/library/cc966545.aspx
tempdb 데이터베이스 파일 및 추적 Tlag 1118에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하세요.
SQL Server 2005 및 SQL Server 2008에서 추적 플래그 1118을 사용하는 방법에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하세요.
tempdb 데이터베이스에서 할당 병목 상태를 모니터링하고 문제를 해결하는 방법에 대한 자세한 내용은 다음 MSDN 웹 사이트를 참조하세요.