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, 템프디비페이지 래치페이지 할당,



출처: https://sqlmvp.tistory.com/1260?category=618825 [Database Lab]
No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38528
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20769
2154 SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 jevida(강성욱) 2019.03.26 713
2153 SQL Server MySQL PostgreSQL 비교 jevida(강성욱) 2019.03.26 1206
2152 SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout jevida(강성욱) 2019.03.26 794
2151 SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환 jevida(강성욱) 2019.03.26 601
2150 SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상) jevida(강성욱) 2019.03.26 623
2149 SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인 jevida(강성욱) 2019.03.26 927
2148 SQL Server 2019에서 향상된 Rowstore batch mode jevida(강성욱) 2019.03.26 521
2147 SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화 jevida(강성욱) 2019.03.26 551
2146 In-memory optimized table에 사용되는 Hash Index jevida(강성욱) 2019.03.26 483
2145 VM환경에서 AG를 구성하였을때VSS 백업 동작 변경 jevida(강성욱) 2019.03.25 468
2144 SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치 jevida(강성욱) 2019.03.25 499
2143 SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size jevida(강성욱) 2019.03.25 445
2142 SQL Server 설치시 발생하는 1638 오류 jevida(강성욱) 2019.03.25 694
2141 SQL Server 666코드의 고유 식별자 오류 jevida(강성욱) 2019.03.25 388
2140 SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅 jevida(강성욱) 2019.03.25 480
2139 SQL Server Scheduling and Yielding 트러블슈팅 jevida(강성욱) 2019.03.25 442
» SQL Server 2016 Tempdb 경합(contention) 최적화 jevida(강성욱) 2019.03.25 563
2137 XEvent를 사용하여 Auto tuning 작업 모니터링 jevida(강성욱) 2019.03.25 596
2136 SQL Server In-Memory OLTP에 ASP.NET 세션 상태 저장하기 jevida(강성욱) 2019.03.25 458
2135 SQL Server에서 JSON 데이터 저장하기 jevida(강성욱) 2019.03.25 631





XE Login