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



출처: https://sqlmvp.tistory.com/1349 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.