Tempdb 데이터베이스에 대 한 동시성 향상

# 버그: 362099 (SHILOH_BUGS)

증상

Tempdb 데이터베이스를 많이 사용 하면 페이지를 할당 하려고 하면 SQL Server 경합이 발생할 수 있습니다.

Sysprocesses 시스템 테이블 출력에서은 대기 나타날 (PFS 페이지) "2:1:1" 또는 "2:1:3" (SGAM 페이지). 경합 정도 따라 또한 짧은 기간 동안 응답 하지 않는 표시 되는 SQL Server 발생할 수 있습니다.


이러한 작업 tempdb를 많이 사용합니다.
  • 만들고 놓기 임시 테이블 (로컬 또는 전역)을 반복 합니다.
  • 저장을 위해 tempdb 를 사용 하는 테이블 변수
  • 커서와 연결 된 테이블을 작동 합니다.
  • ORDER BY 절을 사용 하 여 연결 된 테이블을 작동 합니다.
  • 테이블에는 GROUP BY 절과 관련 된 작업입니다.
  • 해시 계획와 관련 된 파일을 작동 합니다.
이러한 활동이 상당히 많아지면 경합 문제가 발생할 수 있습니다.

원인

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

페이지에서 혼합된 익스텐트 할당의 일부로 SQL Server 혼합된 페이지 할당 수 없는지 확인 하려면 페이지 여유 공간 (PFS) 페이지를 스캔 해야 합니다. PFS 페이지는 모든 페이지에서 사용 가능한 공간 및 추적 각 PFS 페이지는 약 8000 페이지를 추적 합니다. PFS 및 SGAM 페이지를 변경 하려면 적절 한 동기화가 유지 되며 하 고 짧은 기간 동안 다른 한정자 칸 수입니다.

SQL Server 혼합된 페이지 할당에 대 한 검색, 동일한 파일 및 SGAM 페이지에서 항상 검색을 시작 합니다. 따라서 여러 혼합 페이지 할당이 진행 되는이 문서의 "현상" 절에서 설명 하는 문제가 발생할 수 있습니다 하는 경우 SGAM 페이지에서 상당한 경합이 결과.

참고: 할당 취소 작업 증가 된 경쟁에 기여할 수 있는 페이지를 수정 해야 합니다.

다른 할당 메커니즘 (SGAM, GAM, PFS, IAM) SQL Server 사용 하는 방법에 대 한 자세한 내용은이 문서의 "참조" 절을 참조 하십시오.

해결 방법

Microsoft SQL Server 2000


사용량이 많이 발생 하는 tempdb 에 대 한 할당 리소스 경합을 줄이려면 다음과이 같이 하십시오.
  1. Microsoft SQL Server 2000 서비스 팩 4를 적용 합니다. SQL Server 2000 서비스 팩 4 (SP4)는 다음 Microsoft 웹 사이트에서 제공 됩니다.

    http://www.microsoft.com/downloads/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5

    자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료에서 해당 문서를 확인하십시오.
    SQL Server 2000의 최신 서비스 팩을 구하는 290211 방법
  2. 추적 플래그를 구현 -T1118.
  3. 디스크 대역폭을 최대화 하 고 할당 구조에서 경합을 줄이기 위해 tempdb 데이터 파일 수를 늘립니다. 논리 프로세서 수가 8 개 미만 또는 8로 이면 일반적으로 논리 프로세서로 같은 수의 데이터 파일을 사용 합니다. 논리 프로세서의 수 보다 8 일 8 데이터 파일 사용 하 고 충돌 계속 증가 데이터 파일의 논리 프로세서 수) (최대 4의 배수까지 경합 적용 가능한 수준으로 감소 하거나 작업/코드를 변경 합니다.
참고: Microsoft SQL Server 7.0 다음이 단계도 적용 됩니다. 유일한 예외는 SQL Server 7.0;에 대 한 핫픽스가 없습니다 것입니다. 1 단계 적용 되지 않습니다.

2 단계와 관련 하 여 사용 하 여 추적 플래그를-T1118 Microsoft SQL Server 7.0의 경우 추적 플래그를 사용 하기 전에 Microsoft 기술 자료의 다음 문서를 참조 하십시오.
813492 수정: 인덱스를 만들지 못한다 SQL Server 7.0에서 추적 플래그 1118을 사용 하는 경우

Microsoft SQL Server 2005 및 이후 버전

사용량이 많이 발생 하는 tempdb 에 대 한 할당 리소스 경합을 줄이려면 다음과이 같이 하십시오.
  1. 구현할 추적 플래그-T1118.
  2. 디스크 대역폭을 최대화 하 고 할당 구조에서 경합을 줄이기 위해 tempdb 데이터 파일 수를 늘립니다. 논리 프로세서 수가 8 이하인 경우 일반적으로 논리적 프로세서로 같은 수의 데이터 파일을 사용 합니다. 논리 프로세서의 수 보다 8 일 8 데이터 파일 사용 하 고 충돌 계속 증가 데이터 파일의 논리 프로세서 수) (최대 4의 배수까지 경합 적용 가능한 수준으로 감소 하거나 작업/코드를 변경 합니다.

자세한 내용

SQL 2000 s p 4 및 이후 버전의 수정 프로그램은 경합을 줄이는 방법

SQL Server 2000 s p 4 및 이후 버전 혼합된 페이지 할당에 대 한 라운드 로빈 알고리즘을 소개 하는 수정 프로그램을가지고 있습니다. 수정 프로그램을 시작 하는 파일이 이제 됩니다 각 연속 된 혼합된 페이지 할당에 대 한 다른 파일이 여러 개 있는 경우. 이 같은 순서로 SGAMs 매번 동일한 시작 지점으로 진행을 끊어 경합 문제를 방지 합니다. SGAM의 새 할당 알고리즘은 순수한 라운드 로빈 하 고 속도 유지 하기 위해 비례 채우기를 적용 하지 않습니다. 만드는 tempdb 데이터 파일 크기를 사용 하 여 것이 좋습니다.

어떻게 구현 하는 추적 플래그-T1118 경합을 줄이는

방법 목록은 다음과 같습니다 활용 -T1118 경합이 줄어듭니다.
  • -T1118 서버 전체의 설정입니다.
  • 포함 된 -T1118 추적 플래그 SQL Server 재생 되기 후에 적용 되지 않도록 SQL Server 추적 플래그 시작 매개 변수에서.
  • -T1118 서버에서 단일 페이지 할당을 거의 모두 제거 합니다.
  • 대부분의 단일 페이지 할당을 해제 하 여 SGAM 페이지에서 경합을 줄입니다.
  • 사용 하 여 -T1118 ON으로 설정, GAM 페이지에서 거의 모든 새 할당이 수행 됩니다 (예: 2:1: 추적 플래그 없이 개체의 처음 8 개의 페이지의 범위에서 단일 페이지 할당과 반대로 개체에 한 번에 8 페이지 (1 익스텐트) 2)를 할당 합니다.
  • IAM 페이지 에서도 여전히 SGAM 페이지에서 단일 페이지 할당을 사용 -T1118 ON으로 설정 합니다. 그러나 핫픽스 8.00.0702와 tempdb 데이터 파일을 함께 사용 하면 결과 SGAM 페이지에서 경합 감소. 공간 문제에 대해서 본 문서의 "단점" 절을 참조 하십시오.

동일한 크기로 tempdb 데이터 파일 수를 증가

단일 데이터 파일 (500MB 동일한 크기로 유지 하기 위해 각)에 10을 높이기 위해 좋습니다 tempdb 의 데이터 파일 크기가 5GB이 고 로그 파일 크기가 5GB, 로그 파일을 그대로 둡니다. 다른 데이터 파일을 별도 디스크에 있으면 좋은 것입니다. 그러나 이것이 필요 하 고 동일한 디스크에 공존할 수 있습니다.

최적의 tempdb 데이터 파일 수 tempdb에서 경합 정도에 따라 달라 집니다. 시작 지점으로 tempdb SQL Server 할당 된 프로세서의 수에 같은 것으로 구성할 수 있습니다. 하이 엔드 시스템 (예: 16 또는 32 프로세서)의 경우 시작점은 10이 될 수 있습니다. 경합 줄어들지 자세한 데이터 파일의 수를 늘릴 수도 있습니다.

참고: 듀얼 코어 프로세서는 두 개의 프로세서로 간주 됩니다.

데이터 파일의 크기를 동일은 비례 채우기 알고리즘은 파일의 크기를 기반으로 하므로 중요 합니다. 동일 하지 않은 크기로 데이터 파일이 만들어집니다 비례 채우기 알고리즘을 여러 데이터 파일을 만드는 무 찌 여 모든 파일에 할당을 유포 하는 대신 GAM 할당에 가장 큰 파일을 사용 하려고 합니다.

또한 tempdb 데이터 파일의 자동 증가 비례 채우기 알고리즘을 방해할 수 있습니다. 따라서 tempdb 데이터 파일의 자동 증가 기능을 해제 하는 것이 좋습니다 수도 있습니다. 자동 증가 옵션이 해제 되어 있으면 서버에서 tempdb를 사용 하 여 디스크 공간이 부족 해지지 않도록 충분히 큰 그 데이터 파일을 만들 수 있는지 확인 해야.

동일한 크기로 tempdb 데이터 개수를 늘리면 파일 어떻게 경합을 줄이는

경합을 줄이는 동일한 크기로 tempdb 데이터 파일 수를 늘리는 방법 목록은 다음과 같습니다.
  • Tempdb데이터 파일을 사용 하기만 하면 GAM 페이지와 SGAM 페이지 각각 4GB의 공간에 대 한.
  • 동일한 크기의 데이터 파일 수를 늘리면
    tempdb 는 각 데이터 파일에 대해 GAM 및 SGAM 페이지가 하나 이상 효율적으로 만듭니다.
  • GAM의 할당 알고리즘 제공 한 익스텐트 (연속 8 페이지) 한 번에 라운드 로빈 방식에서 파일의 수와에서 비례 채우기를 적용 하면서. 따라서 10 개의 동일한 크기의 파일이 있으면 첫 번째 할당은 file1, File2에서 두 번째 세 번째 파일은 File3 등 및 기타 등등.
  • 8 페이지 FULL로 표시 되므로 한 번에 GAM이 페이지를 할당 하기 때문에 PFS 페이지의 리소스 경합이 줄어듭니다.

단점

앞에서 언급 한 권장 구성의 유일한 단점은 다음 조건이 충족 될 때 증가 하는 데이터베이스의 크기를 볼 수 있습니다.
  • 사용자 데이터베이스에서에 새 개체가 만들어집니다.
  • 각각의 새 개체는 64KB 미만 저장 차지 합니다.
64KB 할당 하면 이러한 조건에 해당 하는 경우 (8 페이지 * 8 = 64 KB)만 8KB의 공간만 필요한 개체에 대 한 하므로 56KB의 공간이 낭비 됩니다. 그러나 새 개체는 64KB 보다 큰 사용 되는 경우 (8 페이지)에 수명, 추적 플래그를 사용 하 여 단점은 없습니다. 따라서 최악의 시나리오에서는 SQL Server 끝날 수도 있습니다 1 페이지 이상 커지지 않는 새 개체만 위해 첫 번째 할당 동안 7 (7) 추가 페이지를 할당 합니다.


참조

GAM, SGAM, PFS, IAM에 대 한 자세한 내용은 다음 SQL Server 2000 온라인 설명서 항목을 참조 하십시오.
  • "개체에서 사용 하는 공간 관리"
  • "익스텐트 할당 및 빈 공간 관리"
  • "테이블 및 인덱스 아키텍처"
  • "힙 구조"
추가 참조

SQL Server 2005의 tempdb 데이터베이스에 대 한 자세한 내용은 다음 MSDN 웹 사이트를 방문 하십시오.

http://technet.microsoft.com/en-us/library/cc966545.aspx

Tempdb 데이터베이스 파일 및 Tlag 1118 추적 하는 방법에 대 한 자세한 내용은 다음 MSDN 웹 사이트를 방문 하십시오.

http://blogs.msdn.com/b/psssql/archive/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth.aspx

SQL Server 2008 및 SQL Server 2005에서 추적 플래그 1118을 사용 하는 방법에 대 한 자세한 내용은 다음 MSDN 웹 사이트를 방문 하십시오.

http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

모니터링 하 고 tempdb 데이터베이스에 할당 병목 현상을 해결 하는 방법에 대 한 자세한 내용은 다음 MSDN 웹 사이트를 방문 하십시오.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx


속성

문서 ID: 328551 - 마지막 검토: 2017. 2. 7. - 수정: 2

Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Workgroup Edition

피드백