전용 SQL 풀의 tempdb 오류 문제 해결

아티클
04/13/2024

적용 대상: Azure Synapse 분석

전용 SQL 풀에서 tempdb 데이터베이스는 임시 테이블 및 데이터 이동을 위한 중간 공간(예: 순서 섞기 이동, 자르기 이동), 정렬, 로드, 메모리 유출 및 기타 작업에 사용됩니다. 또한 tempdb 데이터베이스와 상호 작용하는 한 세션에서 커밋되지 않은 트랜잭션은 로그가 다른 모든 세션을 플러시하지 못하게 하여 로그 파일이 채워지는 것을 방지합니다. tempdb 데이터베이스는 공유 리소스이므로 tempdb 공간을 많이 사용하면 다른 사용자의 쿼리가 실패하고 에스컬레이션되어 새 연결이 설정되지 않을 수 있습니다.

전용 SQL 풀에 연결할 수 없는 경우 어떻게 해야 하나요?

문제가 있는 연결 또는 쿼리를 식별하는 기존 연결이 없는 경우 새 연결을 만들지 못하는 문제를 해결하는 유일한 방법은 일시 중지 및 다시 시작 또는 전용 SQL 풀 크기 조정 입니다. 이 작업은 이 문제를 일으킨 사용자 트랜잭션을 종료하고 서비스가 다시 시작될 때 tempdb 데이터베이스를 다시 만듭니다.

참고: 일시 중지 및 크기 조정 작업이 이 시나리오에서 완료하는 데 평소보다 오래 걸릴 수 있으므로 서비스에서 실행 중인 모든 트랜잭션을 실행 취소할 수 있는 추가 시간을 제공해야 합니다.

전체 tempdb 데이터 파일 문제 해결

1단계: tempdb 데이터베이스를 채우는 쿼리 식별

ETL 프레임워크에 로깅 구성 요소를 구현하거나 전용 SQL 풀 문을 감사하지 않는 한 쿼리가 실행되는 동안 tempdb 데이터베이스를 채우는 쿼리를 식별해야 합니다. 대부분의 경우 항상 실행 중인 쿼리가 문제가 발생한 시간 프레임 동안 실행되는 쿼리가 tempdb의 공간 부족 오류의 원인입니다. 다음 쿼리를 실행하여 장기 실행 쿼리 목록을 가져옵니다.

SELECT TOP 5 *
FROM sys.dm_pdw_exec_requests
WHERE status = 'running'
ORDER BY total_elapsed_time desc;

의심스러운 쿼리가 있으면 다음 옵션 중 하나를 시도해 보세요.

문을 죽입니다.
장기 실행기가 완료될 수 있도록 다른 워크로드가 tempdb 공간을 더 이상 소비하지 않도록 합니다.

2단계: 되풀이 방지

책임 있는 쿼리를 식별하고 조치를 취한 후에는 문제가 되풀이되지 않도록 완화를 구현하는 것이 좋습니다. 다음 표에서는 tempdb 전체 오류의 가장 일반적인 원인에 대한 완화를 보여 줍니다.

원인	설명	완화
분산 플랜이 잘못되었습니다.	지정된 쿼리에 대해 생성된 분산 계획은 제대로 유지 관리되지 않는 테이블 통계의 결과로 실수로 높은 빈도의 데이터 이동을 도입할 수 있습니다.	관련 테이블에 대한 통계를 업데이트하고 일정에 따라 유지 관리되는지 확인합니다.
CCI(클러스터형 columnstore 인덱스) 상태가 좋지 않음	메모리 유출로 인해 tempdb 공간을 사용합니다.	CCI를 다시 빌드하고 정기적인 일정에 따라 유지 관리되도록 합니다.
대규모 트랜잭션	많은 양의 `CREATE TABLE AS SELECT (CTAS)` 또는 `INSERT SELECT` 문이 데이터 이동 작업 중에 tempdb를 채웁니다.	`CTAS` 또는 `INSERT SELECT` 문을 여러 개의 작은 트랜잭션으로 분할합니다.
메모리 할당 부족	할당된 메모리가 부족한 쿼리(리소스 클래스 또는 워크로드 그룹을 통해)는 로 유출할 `tempdb`수 있습니다.	더 큰 리소스 클래스 또는 더 많은 리소스가 있는 워크로드 그룹을 사용하여 쿼리를 실행합니다.
최종 사용자 외부 테이블 쿼리	외부 테이블에 대한 쿼리는 엔진이 데이터를 처리하기 전에 전체 파일을 읽어야 하기 때문에 최종 사용자 쿼리에 `tempdb` 적합하지 않습니다.	영구 테이블에 데이터를 로드 한 다음, 사용자 쿼리를 직접 만듭니다.
전체 리소스가 부족합니다.	높은 작업 중에 전용 SQL 풀이 최대 tempdb 용량에 가까운 것을 확인할 수 있습니다.	위의 완화와 함께 전용 SQL 풀을 확장하는 것이 좋습니다.

전체 tempdb 트랜잭션 로그 파일 문제 해결

tempdb 트랜잭션 로그는 일반적으로 클라이언트/사용자가 다음 중 하나일 때만 채워지게 됩니다.

명시적 트랜잭션을 열지만 또는 ROLLBACK을 COMMIT 발급하지 않습니다.
설정 IMPLICIT_TRANSACTION = ON (특히 자동 커밋 기능을 사용하는 JDBC 클라이언트 및 도구의 경우).

1단계: 열린 트랜잭션 식별

문제가 있는 연결은 열린 트랜잭션이 있지만 "유휴" 상태 있는 클라이언트에서 온 것일 수 있습니다. 다음 쿼리를 실행하여 이 시나리오를 식별합니다.

SELECT *
FROM sys.dm_pdw_exec_sessions
WHERE is_transactional = 1
AND status = 'Idle';

참고: 이 쿼리의 결과로 반환되는 모든 연결이 반드시 문제가 되는 것은 아닙니다. 실행 사이에 15분 이상 쿼리를 두 번 이상 실행하고 이 상태에서 유지되는 연결을 확인합니다.

2단계: 문제 완화 및 방지

열려 있는 트랜잭션을 보유하는 클라이언트를 식별한 후 사용자와 협력하여 다음 중 하나 또는 둘 다를 변경합니다.

드라이버 구성(예: JDBC AutoCommit 설정을 로 설정 off하여 을 설정합니다 IMPLICIT_TRANSACTIONS = ON.)
임시 쿼리 동작(예: 없이 COMMIT/ROLLBACK잘못 실행)BEGIN TRAN

또는 이 시나리오를 주기적으로 검색하고 잠재적으로 문제가 있는 세션을 종료 하는 자동화된 프로세스를 만드는 것이 좋습니다.

리소스

DMV sys.dm_pdw_errors 쿼리하여 오류를 확인합니다.