SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소

·       Version : SQL Server 2019

SQL Server 2019에는 응용프로그램 코드에 필요한 변경을 최소화 하면서 성능을 향상시키는 몇 가지 성능 최적화가 도입 되었다. 이번 포스트에서는 SQL Server 2019 성능 개선 사항 중 하나인 temp 테이블을 사용한 작업 부하에 대해 리컴파일 감소로 인한 성능 향상을 설명한다.

·       Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

이 개선 사항을 이해하기 위해 먼저 SQL Server 2017 및 그 이전의 동작을 살펴본다. DML 문 (SELECT, INSERT, UPDATE, DELETE)을 사용하여 임시 테이블을 참조할 때 임시 테이블이 외부 범위 배치에 의해 생성된 경우에는 실행 될 때 마다 DML 문을 다시 컴파일 힌다.

아래 스크립트는 실습를 사용하여 재현할 수 있다. OuterProc 프로시저에서는 아래와 같은 기능을 한다.

1. 임시테이블 만들기

2. InnerProc 저장 프로시저 호출

InnerProc 저장 프로시저의 경우 OuterProc에서 생성된 임시 테이블을 참조하는 두 개의 DML 문이 있다.

3. 임시 테이블에 행을 삽입

4. 임시 테이블에서 행을 리턴

우리는 임시 테이블을 DML문과 다른 범위로 만들었으며 기존 구현(SQL Server 2019 이전)에 대해 이 임시 테이블 스키마가 실질적으로 변경되지 않음을 “신뢰”하지 않으므로 실행될 때마다 연관된 DML문이 추가 리컴파일 활동을 하여 CPU 사용률을 높이고 전체 워크로드 성능 및 처리량을 줄여 성능 저하로 이어질 수 있다.

SQL Server 2019 부터는 불필요한 리컴파일을 피하기 위해 추가 검사를 수행한다.

·       컴파일 타임에 임시 테이블을 생성하는데 사용된 외부 범위 모듈이 연속 실행에 사용된 것과 동일한지 확인한다.

·       초기 컴파일시 변경 한 DDL (Data Definition Language) 변경 사항을 추적하고 이를 연속 시행을 위한 DDL작업과 비교한다.

결과적으로 보증하지 않은 리컴파일 및 관련  CPU 오버헤드가 줄어든다.

아래 그림은 각 “OuterProc“ 저장 프로시저를 1,000번 (in a loop) 실행하는 16개의 동시 스레드의 테스트 결과를 보여준다. Y축은 발생횟수를 나타내며 파란색 선은 Batch Requests/sec 를 나타내고 녹색 선은 SQL Re-Compilations/sec를 나타낸다.

이 예제에서 기능이 활성화 되면 다음과 같은 결과가 나타난다.

·       Batch Requests/sec (파란색 선, 두 번째 혹)로 표시되는 처리량 개선

·       전체 작업 시간 단축

·       SQL Re-Compilations/sec (녹색선)로 표시되는 리컴파일 감소. (두 번째 테스트 시작시 약간의 증가가 발생하였음)

이 기능은 모든 데이터베이스 호환성 수준에서 SQL Server 2019에서 기본적으로 사용된다. 이 글을 쓰는 시점에서 이 기능은 데이터베이스 호환성 수준 150에서 Azure SQL Database에서도 사용할 수 있지만, 향후 다른 호환성 수준에서도 적용될 수 있다.

2019-09-24/ Sungwook Kang / http://sungwookkang.com

Azure SQL, SQL Server 2019, temp table, SQL Re-compilations, Batch Requests, Intelligent query processing in SQL databases