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]
No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34942
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17247
2194 Azure purview - data governance, compliance 솔루션에 대해 file 코난(김대우) 2022.10.16 129
2193 Azure Synapse - COPY INTO로 대용량 데이터 분산 로드 코난(김대우) 2021.10.21 352
2192 Azure SQL Database로 CSV 파일 BULK INSERT - Python 코난(김대우) 2021.09.27 432
2191 Azure Synapse - Spark와 SQL Data warehouse 서비스 file 코난(김대우) 2021.09.16 328
2190 Azure에서 제공하는 데이터베이스 서비스 종류, AWS 및 GCP와 제품 비교 코난(김대우) 2020.12.25 965
2189 SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 jevida(강성욱) 2020.02.28 1631
» SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 jevida(강성욱) 2019.09.24 1840
2187 Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 jevida(강성욱) 2019.09.24 1360
2186 SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 jevida(강성욱) 2019.09.21 1374
2185 SQL Server 2019 Log Writer Workers jevida(강성욱) 2019.09.21 1537
2184 SQL Server Login Timeout 디버깅 jevida(강성욱) 2019.09.19 2076
2183 SQL Server Worker Thread 기본 계산 jevida(강성욱) 2019.09.18 1506
2182 SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) jevida(강성욱) 2019.09.17 1325
2181 SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 jevida(강성욱) 2019.09.14 1354
2180 BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 jevida(강성욱) 2019.09.13 1436
2179 삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) jevida(강성욱) 2019.09.12 1234
2178 QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 jevida(강성욱) 2019.09.11 1135
2177 ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행 jevida(강성욱) 2019.09.10 1205
2176 Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링 jevida(강성욱) 2019.09.09 1279
2175 SQL Server Enterprise Edition 에서 CPU를 40 Core 이상 사용하지 못하는 현상 jevida(강성욱) 2019.09.04 1359





XE Login