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 [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 109
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 24
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
2203 Microsoft SQL Server 2022 공식발표 file 코난(김대우) 2022.11.24 24
2202 Azure Synapse Analytics 빅데이터 분석 코난(김대우) 2022.11.05 19
2201 Azure Synapse Analytics를 활용한 게임사 유저 이탈분석과 ML Modeling 코난(김대우) 2022.11.02 15
2200 ETL의 끝판왕, 애저 데이터 팩토리 | ep3. 클로징 | 애저 듣고보는 잡학지식 코난(김대우) 2022.11.01 11
2199 ETL의 끝판왕, 애저 데이터 팩토리 | ep2. 데모 | 애저 듣고보는 잡학지식 코난(김대우) 2022.10.31 8
2198 ETL의 끝판왕, 애저 데이터 팩토리 | ep1. 소개 | 애저 듣고보는 잡학지식 코난(김대우) 2022.10.30 19
2197 ETL의 끝판왕, 애저 데이터 팩토리 | ep0. 인트로 | 애저 듣고보는 잡학지식 코난(김대우) 2022.10.28 35
2196 Azure Cosmos DB의 PostgreSQL file 코난(김대우) 2022.10.19 45
2195 Citus PostgreSQL extension - 분산데이터베이스 file 코난(김대우) 2022.10.18 31
2194 Azure purview - data governance, compliance 솔루션에 대해 file 코난(김대우) 2022.10.16 34
2193 Azure Synapse - COPY INTO로 대용량 데이터 분산 로드 코난(김대우) 2021.10.21 224
2192 Azure SQL Database로 CSV 파일 BULK INSERT - Python 코난(김대우) 2021.09.27 260
2191 Azure Synapse - Spark와 SQL Data warehouse 서비스 file 코난(김대우) 2021.09.16 187
2190 Azure에서 제공하는 데이터베이스 서비스 종류, AWS 및 GCP와 제품 비교 코난(김대우) 2020.12.25 694
2189 SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 jevida(강성욱) 2020.02.28 1488
» SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 jevida(강성욱) 2019.09.24 1745
2187 Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 jevida(강성욱) 2019.09.24 1293
2186 SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 jevida(강성욱) 2019.09.21 1289
2185 SQL Server 2019 Log Writer Workers jevida(강성욱) 2019.09.21 1480
2184 SQL Server Login Timeout 디버깅 jevida(강성욱) 2019.09.19 1951





XE Login

테스트 팝업
Close