소개
#으로 시작하는 임시 테이블, @로 시작하는 테이블 변수, 그 외 cursor, xml, row versioning 등 다양한 용도의 임시 데이터를 저장하는 tempdb에는 몇 가지 유형의 병목 문제가 있어왔고 그 문제를 해결 또는 완화하기 위해 SQL Server 2005 버전부터 매번 다양한 개선 작업이 적용되어 왔습니다 (아래 첫 번째 링크 문서 참조).
SQL Server 2022에서는 병목 유형 중에 하나인 GAM/SGAM같은 system page에 동시 대량 접근으로 인한 병목 문제를 완화하도록 엔진이 개선되었습니다. 이에 대해서 간단히 살펴보겠습니다.
- 버전별 SQL Server tempdb의 성능 향상 이력 소개 (문서 중간 쯤에 있음)
- System Page Latch Concurrncy Enhancements (Youtube 영상)
- Under the covers: GAM, SGAM and PFS pages (GAM, SGAM 페이지가 무엇인지 모르신다면 참조)
GAM/SGAM, system page의 할당 병목 문제 개선
테이블, 인덱스와 같은 개체 및 기타 데이터를 저장하기 위해서는 Page(8KB)라는 공간을 할당하는데 이 공간에 대한 정보를 관리하는 (시스템) 페이지가 바로 GAM, SGAM 또는 PFS입니다. 간단히 설명하면 GAM(Global Allocation Map)은 8개의 연속되는 Page(일명 Extent) 단위로 할당되는 공간을 추적 관리하고 SGAM은 개별 Page 단위로 할당되는 공간을 관리하기 위한 용도입니다. (보다 자세한 설명은 위에 세 번째로 소개한 링크의 문서를 참조하세요)
임시 테이블이나 테이블 변수 사용 쿼리나 프로시저가 동시에 대량으로 호출되면 이러한 Systemp Page에 동시 접근이 그 만큼 많아지면서 병목 지점이 되고 성능에 영향을 미치게 됩니다. 그 중에서 PFS 페이지에 대한 병목은 2016, 2019 등을 거치면서 상당히 완하가 되었고 SGAM 페이지 병목은 SQL Server 2016부터 GAM Page 사용이 디폴트 방식이 되어서(이전 버전에서는 추적 플래그 1118 사용) 상대적으로 덜 이슈가 되었습니다.
단일 Thread 접근에서 다중 Thread 접근 허용으로 개선
SQL Server 2019까지 GAM/SGAM 페이지의 접근은 단일 Thread만을 허용함으로 인해 줄서기가 발생하고 이로 인해 병목의 주 원인이 되었다면 SQL Server 2022에서는 다중 Thread의 동시 업데이트를 허용함으로써 해당 병목을 완하하는데 도움이 되도록 엔진 동작을 개선했다고 소개합니다 (더 자세한 설명은 위에 두 번째 링크 영상을 참조하세요)
그럼 간단한 재현 목적의 데모를 수행하고 결과를 비교해 보겠습니다.
A. tempdb 구성
[그림-1] SQL Server 2019와 2022에서 tempdb의 구성을 아래와 같이 동일하게 맞추었습니다.
B. SQL 부하 테스트 by SQLBigEyes Hammer
[그림-2] SQLBigEyes Hammer (제가 만든 SW이며 무료입니다^^)를 이용해서 아래와 같이 # 임시 테이블을 랜덤하게 생성하는 쿼리를 동시에 50개 30번 반복 옵션으로 수행하면서 아래 모니터링 스크립트를 이용, GAM 페이지 대기 발생 여부를 확인합니다.
C. 모니터링용 쿼리
page_id가 2번이 GAM 페이지, 3번이 SGAM 페이지에 해당하므로 이를 확인하는 SQL 스크립트입니다. 위의 # 생성 쿼리가 수행되는 동안 SSMS에서 수동으로 반복 실행하면서 발생 여부를 확인했습니다.
SELECT r.wait_type, r.wait_resource, r.wait_time
, pi.page_id, pi.page_type_desc, pi.gam_page_id, pi.sgam_page_id, pi.gam_status_desc
, pi.file_id, pi.index_id, r.session_id, st.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS p
CROSS APPLY sys.dm_db_page_info(p.db_id, p.file_id, p.page_id, 'DETAILED') AS pi
WHERE r.wait_type LIKE '%page%'
AND pi.page_id IN (2, 3)
D. SQL Server 2019의 결과
[그림-3] 2번 페이지의 PAGELATCH 대기가 대량으로 발생하는 것을 볼 수 있습니다.
E. SQL Server 2022의 결과
2022에서는 위와 같은 대기가 발생하지 않았습니다.
정리
실제 고객들의 SQL Server를 진단분석해 보면 기간계 시스템이건 온라인 서비스이건 tempdb 사용량은 대체로 엄청납니다. 따라서 여러가지 유형의 성능 이슈들이 발생합니다. 그 중에서도 임시 개체의 동시 대량 할당 및 사용 시 발생하는 병목 이슈 중의 하나인 GAM/SGAM 이슈와 그 완화책으로 적용된 SQL Server 2022의 엔진 개선이 최종 사용자들에게 도움이 되기를 기대해 봅니다. 실제 SQL Server 2022로 업그레이드한 고객들에게서 그러한 결과물이 나오면 좋겠습니다 (저도 경험하거나 정보를 얻으면 공유하겠습니다)
모두 건강하세요.
김정선 드림
