VM 환경의 SQL Server에서 할당된 CPU 모두 사용하지 못하는 현상

 

·       Version : SQL Server

 

Virtual Machine (VM) 성능이 향상됨에 따라 많은 시스템들이 Physical 서버에서 VM 머신으로 마이그레이션을 진행하고 있다. 이번 포스트는 Physical머신에서 VM으로 마이그레이션 SQL Server에서 할당된 CPU 모두 사용하지 못하는 성능 문제에 대해서 다룬다.

 

Host Server

OS

Windows Server 2019 Standard

CPU

2 socket (64 Core)

RAM

128GB

 

VM Server

OS

Windows Server 2019 Standard

SQL Server

SQL Server 2016 Standard

CPU

8 Core

RAM

32GB

 

아래 그림을 보면 가상머신에 할당된 CPU 8 Core 에서 4 Core 사용하는것을 확인할 있다.

 

아래 쿼리를 사용하여 실제 SQL Server 사용중인 CPU 확인할 있다. 역시 할당된 8개의 코어중에 4개만 사용하고 있음이 표시되었다.

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers

 

 

원인을 확인결과 VM 머신의 세팅에 문제가 있다는 것을 확인하였다. 필자가 구성한 VM환경을 보면, 8 소켓에 8Core 설정되어 있는것을 확인할 있다. 뜻은 1소켓 1 코어가 8 할당되었다는 의미이다. SQL 공식 다큐먼트를 보면 Standard 경우 4소켓 또는 24코어까지만 지원된다고 되어 있다. 소켓의 개수가 8개로 세팅되어, 실제 4 소켓만 동작을 하기 때문에 4Core 동작을 하게 된것이다.

VM 환경을 변경하여, 1소켓 8 Core 설정을 변경한 정상적으로 모든 CPU 사용하는 것을 확인하였다.

 

VM 환경을 변경하는것은 시스템 운영에 문제가 발생할 있으므로 반드시 시스템 담당자와 상의해서 진행할 있도록 한다.

 

아래 스크립트는 현재 구성된 VM 환경에서 SQL Server 할당된 CPU 모두 사용가능한지 쉽게 확인한다.

----------------------------------------------------------------------------------------------------------------

-- CPU VISIABLE ONLINE CHECK

----------------------------------------------------------------------------------------------------------------

DECLARE @OnlineCpuCount int

DECLARE @LogicalCpuCount int

 

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

 

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',

   CASE

     WHEN @OnlineCpuCount < @LogicalCpuCount

     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'

     ELSE 'You are using all CPUs assigned to O/S. GOOD!'

   END as 'CPU Usage Desc'

----------------------------------------------------------------------------------------------------------------

GO

 

 

 

2020-03-03/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, VM, Virtual Machine, CPU 할당, 가상머신 SQL, SQL on VM, dm_os_schedulers, dm_os_sys_info

SQL Server 복원 성능 최적화

 

·       Version : SQL Server

 

SQL Server에서 백업 파일을 복원할때 빠르게 복원하기 위한 최적화 방법을 소개한다. 방법을 사용한다고 해서 무조건 빠르게 복원되지는 않으며, 사용할 있는 시스템 리소스에 따라 최적화된 옵션을 제공함으로써 빠르게 복원할 있게 유도 하는 것이다.

 

데이터베이스 백업 복원에 대한 통계를 확인하기 위해 추적 플래그 3213, 3605 설정한다.

DBCC TRACEON (3213, -1)

DBCC TRACEON (3605, -1)

 

데이터베이스를 복원하면, SQL 이벤트 로그에서 아래와 같은 내용을 확인할 있다.

 

기본 설정을 사용하면  최대 전송크기는 1024K 이고 버퍼수는 6인것을 확인할 있다. 이때 사용되는 버퍼 공간은 6MB이다.

버퍼공간 = 최대 전송크기 X 버퍼

 

여기서 주목해야 부분은 메모리 제한(Memory limit) 이다. 현재 필자의 메모리 제한은 4095 MB (사용자 마다 다름)이지만 사용되는 버퍼 공간은 6MB 이다. 따라서 버퍼 공간을 늘려 복원 속도를 높일 있다. 최대 전송 크기 버퍼수 변경은 데이터베이스 복원시 추가 매개변수로 사용할 있다.

아래 예시는 최대 메모리 제한까지 사용하기 위해서 MAXTRANSFERSIZE 4096K 설정하고, BUFFERCOUNT 1000으로 설정하였다.

RESTORE DATABASE [DB] FROM DISL = N'D:\DB\BACKUP\DB.BAK' WITH REPLACE, STATS = 5, MAXTRANSFERSIZE = 4194302, BUFFERCOUNT = 1000

 

추가 매개변수를 사용하여 데이터베이스를 복원할 경우, 복원의 속도는 빨라지겠지만 동일 서버에서 운영되는 다른 서비스에 영향을 미칠 수도 있다. 해당 옵션을 사용하기 전에 시스템의 리소스 가용능력, 사용량등을 확인할 있도록 한다.

중요한것은 실제 운영 환경에 반영하기전에 테스트 환경에서 먼저 검증을 있도록 한다.

 

[참고자료]

·       Optimizing Backup and Restore Performance in SQL Server : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190954(v=sql.105)?redirectedfrom=MSDN

 

 

2020-02-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, SQL Server Recovery Step, 데이터베이스 복원, Database Recovery Process, BUFFERCOUNT, MAXTRANSFERSIZE

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상

 

·       Version : SQL Server 2016 Enterprise Edition

 

SQL Server에서 트랜잭션 로그를 사용하여 데이터를 복원시, 평소와 다르게 매우 오래 걸리는 현상이 발생하였다. 처음에는 I/O 서브 시스템을 의심하고 물리장비 까지 교체하였으나, 증상을 동일하였다. 여러가지 가설을 세웠고, 원인 분석 결과, Slow query 트랜잭션 로그의 복원시간과 관련이 있다는 것을 발견할 있었다.

 

필자의 운영 환경은 10 마다 트랜잭션 로그 백업을 진행하고, 백업된 로그는 다른 서버에서 Read Only(STAND BY) 복원하여 부서에서 사용할 있도록 로그 쉬핑을 구성하였다. 10 마다 발생하는 로그의 양은 10MB~ 20MB 정도로 평상시 복원하는데 1 정도 소요되었다. 그런데 어느날 부터 복원시간이 증가하여 30분정도 걸렸다. 그러다 보니, 연관된 여러 ETL 시스템 리포트 시스템에 영향을 주게 되었었다.

아래 그래프는 트랜잭션로그 복원시 디스크의 Idle Time 캡처한 것으로 파란색이 평상시의 상태이고 빨간색과 노란색이 문제가 발생했던 때의 상태이다. 트랜잭션 로그 복원이 진행되는  동안 엄청난 I/O 발생하는 것을 확인할 있다.

 

트랜잭션 복원이 진행되는 동안 단계에 대한 작업시간을 확인하기 위해서 DBCC TRACE 명령을 사용하여 Inter log SQL Server이벤트 로그에 기록하도록 하였다.

DBCC TRACEON(3004, -1)

DBCC TRACEON(3605, -1)

DBCC TRACEON(3213, -1)

 

트레이스 적용 트랜잭션로그 복원을 진행하고, 아래와 같은 이벤트 로그를 확인할 있었다.

 

이벤트 로그를 확인해보면 redo, undo 관한 시간이 표시되어 있는데, Slow 쿼리가 발생할 경우 트랜잭션 커밋이나 롤백이 발생하지 않은 상태(더티페이지)에서 백업이 진행되고, 다음에 쿼리 실행이 완료되어 다름 트랜잭션 로그에 반영되었을때, 해당 쿼리의 결과에 따라, 데이터가 롤백되거나 커밋을 해야해서 (특히 롤백이 문제다) 데이터 페이지, 인덱스 페이지등에 반영하느라 디스크 I/O 오버헤드가 발생하고 전체적인 복원 시간이 느려졌다. 아래와 같은 단계로 진행된다.

 

1 단계 : 분석. 트랜잭션 로그의 마지막 체크 포인트에서 시작한다. 단계는 SQL Server 중지 더티 페이지 테이블 (DPT) 확인하고 구성한다. 활성 트랜잭션 테이블은 SQL Server 중지 커밋되지 않은 트랜잭션으로 구성된다.

 

2 단계 : 다시 실행. 단계는 데이터베이스를 SQL 서비스가 중지 시점의 상태로 되돌린다. 가장 오래된 커밋되지 않은 트랜잭션이 롤백의 시작점 이다. DPT 최소 로그 시퀀스 이름 ( 로그 레코드에 LSN으로 레이블이 지정됨) SQL Server 페이지에서 작업을 다시 실행해야하는 번째 시간이며 가장 오래된 열린 트랜잭션에서 다시 시작하여 기록 작업을 다시 실행해야 한다. 필요한 잠금 장치를 확보 한다.

 

3 단계 : 실행 취소. 여기에서 1 단계에서 식별 활성 트랜잭션 (SQL Server 중단 커밋되지 않은) 목록이 개별적으로 롤백된다. SQL Server 트랜잭션에 대한 트랜잭션 로그 항목 간의 링크를 따른다. SQL Server 중지 커밋되지 않은 트랜잭션은 모두 취소된다.

 

 

정리하면, Slow 쿼리 증가로 인해 활성 트랜잭션이 많아졌고, 트랜잭선 로그 백업이 진행될때 더티페이지가 증가함에 따라 해당 로그 파일로 복원시 롤백 커밋 작업이 진행되어 이때 I/O 오버헤드가 발생하여 전체적인 복원이 느려졌다.

 

[참고자료]

·       Understanding How Restore and Recovery of Backups Work in SQL Server  : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191455(v=sql.105)?redirectedfrom=MSDN

·       SQL Server Mysteries: The Case of the Not 100% RESTORE : https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-mysteries-the-case-of-the-not-100-restore

·       SQLskills SQL101: Why is restore slower than backup : https://www.sqlskills.com/blogs/paul/sqlskills-sql101-why-is-restore-slower-than-backup/

·       SQL Server Database Recovery Process Internals – database STARTUP Command : https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

 

 

2020-02-27/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, Log shipping, 트랜잭션 로그 복원, SQL Server Recovery Step, 데이터베이스 복원, 로그 전달, 로그쉬핑, Database Recovery Process

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

+ Recent posts