Data Science Lab

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

MySQL/MariaDB Temp table and Temp file 모니터링

·      Version : MySQL 5.7, Docker

MySQL/MariaDB에서 Temp table 및 Temp file 모니터링하는 방법에 대해서 알아본다. 데이터베이스 서브 시스템에서 상대적으로 느린 부분이 디스크이다. 임시 테이블 및 임시 파일이 디스크에 기록될때 데이터베이스의 성능 저하가 발생할 수 있기기 때문에 지속적인 모니터링을 통해서 디스크에 생성되는 임시 테이블 및 파일이 높지 않은지 살펴보아야 한다.

아래 스크립트는 temp table 및 temp file의 지표를 나타낸다.

위 지표에서 성능 저하가 발생하는 부분은 Created_tmp_disk_tables 항목이다. 이 항목의 수치가 높다면 현재 쿼리를 처리하기 위해 디스크를 많이 사용하고 있다는 뜻이다. 일반적으로 아래와 같은 쿼리는 임시 테이블을 생성한다.

·       OEDER BY 및 GROUP BY에 명시된 컬림이 다른 쿼리

·       한 개의 쿼리에 DISTINCT와 ORDER BY가 동시에 존재하는 경우

·       DISTINCT가 인데스러 처리되지 못하는 쿼리

·       UNION ALL이 사용된 쿼리

·       실행계획에 select_type이 UNION RESILT가 나타나는 경우

·       실행계획에 select_type이 DERIVDE 인 쿼리

이 외에도 임시 테이블에 저장되는 레코드의 크기가 tmp_table_size 또는 max_heap_table_size보다 큰 경우 디스크에 임시 테이블을 생성하여 저장한다. 디스크 사용을 줄이기 위해 tmp_table_size 및 max_heap_table_size의 시스템 변수 값을 증가시키면 MySQL 서버가 사용할 수 있는 메모리 공간이 줄어들기 때문에 주의해야 한다. 아래 스크립트는 tmp_table_size 및 max_heap_table_size를 확인하는 스크립트 이다.

SELECT 절에서 사용되는 컬럼에 따라서도 디스크에 임시 테이블이 생성되는데, TEXT, BLOB 형식의 컬럼이 포함되는 경우 MEMORY 스토리지 엔진이 이 데이터 타입을 지원하지 않아 디스크에 임시 테이블을 생성한다.

[참고자료]

·       https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Created_tmp_disk_tables

2018-05-02 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

MySQL, temp table, temp file 모니터링, 임시 테이블, 임시 파일 모니터링, tmp_table_size, max_heap_table_size, created_tmp_disk_tables, created_tmp_files, created_tmp_tables

SQL Server 임시 테이블 특성

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

임시 테이블은 tempdb 데이터베이스에 저장되었다가 더 이상 사용되지 않을 때 자동으로 삭제 된다. 그 외 기능은 일반 테이블과 비슷하다. 임시 테이블에는 전역과 로컬 두 유형이 있으며 로컬은 # 기호로 시작하며 전역은 ## 기호로 시작한다.

로컬 임시테이블은 현재 사용자의 연결에만 표시되고 사용자가 SQL Server 인스턴스와의 연결을 종료하면 삭제 된다.

전역은 테이블 생성 후 모든 사용자에게 표시되고 테이블을 참조하는 모든 사용자가 SQL Server인스턴스와 연결을 종료하면 삭제 된다. 즉 테이블을 생성한 사용자가 SQL Server와 연결을 끊어도 다른 사용자가 해당 테이블을 사용하고 있으면 다른 사용자의 사용이 종료될 때까지 기다렸다가 삭제된다.

실습을 통해서 전역 임시테이블의 특성에 대해서 살펴보자. 전역 임시 테이블은 테이블 이름 앞에 ## 기호로 식별되며 tempdb 데이터베이스에 저장된다.

테이블 생성 후 전역 임시 테이블은 모든 사용자와 모든 연결에서 볼 수 있다. 전역 임시테이블들은 수동으로 drop table 명령으로도 삭제 할 수 있다. 작성자 세션이 종료되어도 다른 세션에서 해당 테이블이 활성세션에 참조 되어 있는 경우 SQL Server 기준에서 마지막 T-SQL 문이 완료 될 때까지 삭제되지 않으며 참조 세션 문을 실행하는 동안 테이블은 새 연결에 사용할 수 없다.

SSMS에서 새쿼리 창을 실행하여 아래 스크립트(쿼리1)를 실행한다. 스크립트는 임시 테이블을 생성하고 데이터를 삽입한다.

새 쿼리 창을 열고 아래 스크립트(쿼리2)를 실행 한다. 정상적으로 값이 조회되는 것을 확인할 수 있다.

새 쿼리창을 실행하여 아래 스크립트(쿼리3)를 실행 한다. 그리고 쿼리1의 세션을 종료 한다.

쿼리3이 실행되는 동안 쿼리2를 조회해 보면 정상적으로 값이 조회되는 것을 확인할 수 있다. 하지만 쿼리3의 작업이 완료되면 전역 임시테이블은 삭제되어 쿼리2에서 오류가 발생하는 것을 확인할 수 있다.

반복문의 경우 어떻게 작동하는지 알아본다. 쿼리 1을 실행하고 아래 스크립트(쿼리4)를 실행한다. 쿼리4가 진행 되는 동안 쿼리1의 세션을 종료 한다.

쿼리4의 경우 하나의 T-SQL 명령문을 사용하지만 While 구문을 실행하면서 반복해서 테이블을 참조하는데 임시테이블이 삭제 후 참조하려고 했기 때문에 오류가 발생하였다.

이처럼 로컬 임시 테이블과 전역 임시 테이블은 비슷하지만 다른 특성이 있어 잘 파악하고 사용하여야 하며 쿼리를 개발 할 때 While 같은 경우 한 세션에서 실행되지만 임시 테이블이 삭제된 경우 반복 과정에서 오류가 날 수 있다는 점을 주의해야 한다.

[참고 자료]

• 테이블 속성

  https://technet.microsoft.com/ko-kr/library/ms177399(v=sql.105).aspx

• Temp table 객체 생성시 세션간 충돌하지 않는 이유 : http://sqlmvp.kr/220400045796

2015-09-30 / 강성욱 / http://sqlmvp.kr

MS SQL, SQL Server, 임시 테이블, Temp table, 전역 임시 테이블, 로컬 임시 테이블, temp table, Global temporary table

Temp table 객체 생성시 세션간 충돌하지 않는 이유

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

데이터베이스를 사용할 때 temp table(임시 테이블)을 많이 사용한다. 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 어떻게 충돌을 방지할 수 있을까? 다음 간단한 테스트를 통해서 임시테이블 생성과 충돌 방지에 대해서 알아본다.

아래 스크립트는 임시 테이블을 생성한다.

테이블을 만든 후에 메타데이터에서 tempdb 데이터베이스 내부에 생성된 객체를 확인한다.

생성된 임시 테이블은 부정적인 개체ID와 이름에 긴 밑줄과 함께 번호가 있는것으로 확인할 수 있다. SQL Server에서는 임시테이블에 번호를 부여하여 전체 인스턴스에 대한 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 충돌을 방지할 수 있다. 동일한 이름으로 두 번째 임시 테이블을 생성한다.

메타데이터를 확인해 보면 다른 번호를 가지고 있는 객체가 생성된 것을 확인할 수 있다.

이렇게 생성된 임시테이블은 다른 세션에서 동일한 이름을 사용해도 충돌을 방지 할 수 있으며 세션이 종료될 때 자동으로 제거된다. 임시 테이블인 세션이 종료될 때 삭제되는 것을 제외하면 일반 테이블과 유사하다.

[참고자료]

http://blog.sqlauthority.com/2014/11/27/sql-server-inside-temp-table-object-creation/

2015-06-24 / 강성욱 / http://sqlmvp.kr

SQL Server, TempDB, Temp table, 임시테이블, mssql, 데이터베이스, sys.sysobjects