SQL Server 2016 Tempdb 경합(contention) 최적화

SQL Server 2016 Tempdb 경합(contention) 최적화

 

·         Version : SQL Server 2017, 2016, 2014

 

SQL Server에서 tempdb는 경합이 자주 발생하는 부분이므로 항상 모니터링의 주요 대상이다. SQL Server 2016에서는 최신 업데이트를 통해 tempdb file 및 trace flag 개선작업이 진행 되었다.

 

[Object Allocation Contention]

SQL Server에서  tempdb 구성시 일반적인 권장사항은 아래와 같다.

1.       여러 데이터 파일 생성 : CPU 코어당 최소 1개 이상으로  데이터 파일을 생성하며 최대8개의 파일로 구성한다.(필요에 따라 더 많이 생성할 수도 있다.) 모든 파일의 크기는 동일 해야한다.

2.       SQL Server 2014 또는 이전 버전의 경우 TF 1117 및 TF1118을 설정한다. SQL Serve 2016에서는 이 추적 플래그가 기본 디자인으로 적용되어 설정할 필요 없으며 TF 3427을 사용한다.

이러한 권장사항은 많은 임시테이블이 동시에 생성될 때 발생할 수 있는 개체 할당(object allocation) 병목 문제를 해결하는데 도움이 된다. PAGELATCH 대기는 2:X:1(PFS), 2:X:3(SGAM) 또는 2:X:<8088의 일부 배수> (PFS) 리소스에서 경합이 발생한 것으로 X는 파일 번호를 나타낸다. 이 페이지는 SQL Serve가 새 개체에 공간을 할당 해야할 때 사용되는 특수 페이지이다. 동일한 크기의 여러 파일을 보유함으로써 각각의 새로운 개체 할당은 라운드로빈 방식으로 다른파일과 다른  PFS페이지를 조회한다. SQL Server에서는 이러한 구성을 기본값으로 설정하는것이 일반적이다.

 

SQL Server는 여러 데이터 파일을 분할하여  완벽한PFS 경합을 해결하지 못하여 파일간 라운드로빈 및 파일내의 PFS 페이지간에 라운드로빈을 사용하여 모든 파일과 파일 자체에서 개체 할당을 분산시킬수 있도록 디자인을 수정하였고 아래 그림과 같이 동작한다.

이 그림을 살펴보면 처음부터 끝까지 파일을 채우지 않고 파일 전체에 걸쳐 오브젝트 할당하여  분산시키는 것을 볼 수 있다. 이렇게 분산 하였을때 단점은 파일 시작 부분으로 부터 데이터를 재배치 해야하므로 축소 작업이 오래 걸릴수 있다. 또한 축소 작업중 지속적으로 임시 테이블이 생성되는 경우 축소가 안될 수도 있다. 그래서 일반적으로 축소 작업을 권장하지 않는다.

이번 업데이트로 변경된 디자인으로 파일 수를 늘리면 PFS 경합에 도움될 뿐만 아니라 파일 크기를 늘리면 파일의 PFS 페이지 수가 늘어난다. 이러한 동작은 PFS 경합을 해결하기 위해 여러개의 파일이 필요한 필수 조건을 대체한다. 하지만 위에서 나열된 파일 수에 대한 권장사항은 여전히 유효하며 실제로 PFS 경합외에도 tempdb 저장장치에 대한 I/O 경합이 있는 경우 여러 파일을 사용하면 여러 파일에 걸쳐 I/O의 균형을 쉽게 맞출수 있다.

 

[Metadata Contention]

개체 할당 경합은 많은 버전의 SQL Server에서 흔히 발생했지만 예전에 비해 처리량이 많아지면서 임시 테이블 생성 빈도 또한 증가하면서 tempdb의 또 다른 유형인 메타데이터 충돌이 발생했다. 이 경합은 임시 테이블을 추적하는데 사용되는 tempdb의 시스템 개체에 대한 경합이다. SQL Server 2000부터 메타데이터 경합이 발생했기 때문에 SQL Server 2005부터 임시테이블 캐싱이  도입 되었다. 저장 프로시저에 의해 작성된 임시 테이블의 메타 데이터를 캐싱하므로 경합을 줄일 수 있었다. 캐시는 무제한 리소스가 아니므로 새 객체를 위한 공간을 만들기 위해 주기적으로 캐시를 정리해야한다. 캐시에서 항목을 제거할 때마다 메타데이터 테이블에서 해당 행을 삭제해야 한다. 이때 캐시 삭제에 대한 경합이 발생한다.  사실 이 문제는 그리 크지 않았지만 SQL Server 2016에서는 새로운 기능과 함께 메타데이터가 증가 했기 때문에 이 문제가 더욱 두드러졌다. 임시 테이블과 Always Encrypted와 같은 기능은 추가 메타데이터가 필요하다. 즉 메모리 부족이나 캐시 항목이 무효화되어 임시 테이블 캐시를 제거해야하는 경우 과거보다 훨씬 많은 메타데이터를 제거해야한다.

 

이 문제를 해결하기 위해 임시 테이블 캐시를 정리하는 방법에 3가지 주요 변경사항을 적용했다.

1.       프로세스를 동기식에서 비동기식으로 변경

저장 프로시저 내에서 임시 테이블이 변경될 때마다(예 : 인덱스 추가, 열 추가, 명시적 드롭 등) 재사용할 수 없다. 이전 동작은 프로시저가 끝날 때 테이블을 동기적으로 삭제하는 것이었다. 이제 테이블은 즉시 삭제되지 않고 삭제된 목록으로 이동되어 나중에 다른 스레드에 의해 삭제 된다.

 

2.       NUMA 노드당 한개의 Helper 스레드 사용 및 각 패스마다 제거되는 테이블 수 증가

이전는 삭제할 목록이 특정 크기에 도달하면 캐시에 항목을 추가해야하는 스레드가 먼저 삭제 목록에서 개체를 제거해야했다. 이제 NUMA 노드당 하나의 스레드만 helper 스레드로 처리되고 스레드는 한번에 1개가 아닌64개의 개체를 제거한다.

 

3.       메타데이터 검색시 래칭 전략을 최적화

페이지에서 레코드를 제거하려면 메모리에서 해당 페이지에 독점(exclusive)으로 액세스 해야한다. 이는 페이지 래치 또는 버퍼 래치, 특히 PGAELATCH_EX이다. 원래 프로세스는 독점(exclusive )래치를 획득하여 삭제할 메타데이터 행을 검색했다. 이제는 공유 래치를 사용하여 먼저 행이 존재하는지 확인한 다음 삭제해야하는 행을 있을때에만 베타적인 래치를 획득한다. 우리가 찾고 있는 대부분의 메타데이터는 임시 테이블에 존재하지 않으므로 래치를 독점적으로 변환하지 않아도 된다. 이로인해 전반적인 프로세스가 차단을 일으킬 가능성이 줄어든다.

 

그러면 이 메타데이터 경합이 발생하는지 어떻게 알 수 있을까? 언뜻 보면 tempdb와 비슷한 모양을 보일 수 있다. PAGELATCH는 다양한 tempdb 페이지 (2:X:Y, X는 파일 번호, Y는 페이지 번호) 를 기다린다. 차이점은 PFS및 SGAM페이지가 아니라 sysobjvalues및 syseobjvalues와 같은 시스템 객체에 속한 페이지이다. 아래 링크는 tempdb의 메타 데이터 경합을 해결하는데 도움된다.

·         Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016  : https://support.microsoft.com/en-us/help/4131193/performance-issues-occur-in-form-of-pagelatch-ex-and-pagelatch-sh-wait

·         FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017  : https://support.microsoft.com/en-us/help/4058174/heavy-tempdb-contention-occurs-in-sql-server-2016-or-2017

 

그외에 경합을 줄이는 몇 가지 모범사례가 있다.

1.       저장프로시저의 끝에 임시 테이블을 명시적으로 삭제하지 말것. 임시 테이블을 만든 세션이 종료되면 자동으로 정리된다.

2.       임시 테이블을 생성 후에 변경하지 말것

3.       임시 테이블을 truncate하지 말것

4.       인덱스 작성문을 SQL Server 2014에 도입된 새로운 인라인 인덱스 작성구문으로 이동

위 방법을 사용하면 저장 프로시저에서 만든 임시테이블을 캐싱 할 수 있다. 임시 테이블에서 변경되거나 명시적으로 삭제된 임시 테이블은 재사용할 수 없으므로 삭제 대상으로 표시된다. 삭제해야하는 임시 테이블이 많을 수록 위에 설명된 충돌 가능성이 커진다. 일부 환경에서는 테이블 변수가 임시 테이블보다 오버헤드가 적으며 더 적합할 수도 있다. 테이블의 행수가 적다면(rule of thumbs is < 100) 테이블 변수가 좋은 대안일 수 있다. 하지만 테이블 변수는 통계가 작성되지 않는다는 점에 유의해야한다. 테이블 변수에 대해 실행중인 쿼리가 카디널리티 변경에 민감한 경우 TF 2453을 활성화하여 테이블 변수에 대한 카디널리티 기반으로 리컴파일을 설정하는 것이 좋다.

 

[Auditing Overhead]

SQL Server 2016에서 tempdb에 많은 부하를 주는 변경사항으로는 C2 감사라도고 하는 CCC (Common Criteria Compliance)와 관련 있다. SQL Server는 CCC에서 트랜잭션 수준 감사를 허용하는 기능을 도입했다. 특히 임시테이블에서 많은 삽입과 업데이트를 하는 작업에서 약간의 오버헤드가 발생할 수 있다. 하지만 이 오버헤드는 CCC를 사용 여부에 관계없이 발생한다. SQL Server 2016 SP1 CU2에서는 TF 3427을 적용하여 오버헤드를 무시하도록 설정 할 수 있다. SQL Server 2017 CU4에서는 CCC가 비활성화 된 경우 자동적으로이 코드를 무시한다.

·         FIX: Workloads that utilize many frequent, short transactions in SQL Server 2016 and 2017 may consume more CPU than in SQL Server 2014 : https://support.microsoft.com/en-us/help/3216543/workloads-that-utilize-many-frequent-short-transactions-in-sql-server

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/

 

 

2018-07-02 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, SQL 2017, tempdb, object allocation,  PGAELATCH, 템프디비, 페이지 래치, 페이지 할당,