노하우 공유 시리즈 1: 좁은 길로 접근하는 SQL Server 성능 관리(3회)

SQL Server CPU 성능과 트러블슈팅

김민제

운 좋게도 FOREVER21에서 SQL Server DBA로 사회에 첫발을 내디뎠다. 업무가 적성과 잘 맞아서 오프라인 스터디 및 세미나에 적극 참여하고 있다. 1인출판과 전자책에도 관심이 많아서 SQL Server 및 데이터 전문가 자격증 분야의 전자책도 몇 권 출판했다.
‘좁은 길로 접근하는 SQL Server 성능 관리’ 연재는 관련 전문서를 찾아보던 중 SQL Server 성능과 관련한 실무 내용을 찾기가 어려워 지식공유 차원에서 시작하게 됐다. 이 연재가 비슷한 일을 하는 이들과 지식을 나누고 함께 실력을 키워가는 바탕이 됐으면 좋겠다고 한다. okwow123@naver.com

본격적으로 CPU 모니터링 방법에 대해 말해보고자 한다. 넓은 의미의 개념부터 좁은 의미의 개념까지 톱다운 방식으로 내려가고자 한다. CPU에 대해 잘 모르는 사람도 글을 읽는 데 큰 무리가 없을 것이다. 이번 장에서 언급할 내용은 다음과 같이 크게 3가지다.


1. SQL Server에서 CPU와 스레드의 관계

2. SQL Server에서 실제로 CPU를 모니터링 하는 방법

3. 실제 사례 분석

1. SQL Server에서 CPU와 스레드의 관계

첫 번째로 SQL Server에서 CPU와 스레드의 관계다. 우선 SQL Server에서 CPU를 모니터링 하기 위해 지금까지 배운 기본 개념을 다시 한 번 짚고 심화해 살펴보자.


1) 프로세서, 스레드, 프로세스의 차이점

프로세서, 스레드, 프로세스의 차이점에 대해 명확하게 설명이 가능한가 분명히 많이 들어본 문장임에도 정확한 개념을 설명하는 것은 쉽지 않다. 일단 프로세서는 말 그대로 처리기라고 생각하면 된다. 이 처리기는 명령어들을 해석하고 처리하는 역할을 한다. 보통 프로세서의 의미 안에 CPU가 포함돼 있다. 프로세스는 실행중인 프로그램이라는 의미로, 디스크에 있던 프로그램이 메모리에 적재된 상태라는 뜻이다. 스레드는 프로세스 내에서 실제로 작업을 실행하는 최소 단위다. 하나의 프로세스에 한 개의 스레드가 있을 수도 있고 여러 개의 스레드가 있을 수도 있다. 그래서 스레드를 경량 프로세스라고 부르기도 한다.


이와 같은 개념을 바탕으로 SQL Server와 연관을 시켜보자. 윈도우 운영체제에서 SQL Server라는 프로그램을 실행하면 인스턴스가 올라온다. 인스턴스의 내부에는 수많은 스레드가 존재한다.


작업관리자 → 세부정보 → sqlserver.exe를 찾는다.





[그림 1]과 같은 화면이 나타나고, [그림 2]와 같이 ‘열 선택’ → ‘스레드’를 선택해 추가한다. 그러면 실제로 [그림 3]처럼 실제 sqlserver.exe라는 프로그램이 몇 개의 스레드를 사용중인지 알 수 있다.




SQL Server 인스턴스가 생성한 스레드들은 부하에 따라 CPU에 고르게 분산한다. 참고로 CPU 하나당 스레드 하나를 실행할 수 있다. 예를 들어 CPU가 8개인 옥타코어 같은 경우에는 동시에 8개의 스레드를 실행시킬 수 있다. CPU의 연산수행 방식은 크게 4가지 단계로 구성돼 있다.


2) CPU의 연산수행 방식





FETCH: 프로그램 메모리에서 어드레스를 지정하고 실제 명령을 명령 레지스터에 넣는다.

DECODE: 명령 디코더에 의해 명령 레지스터에 있는 명령을 해석하고 제어부로 전달한다.

EXECUTE: 제어부는 연산기를 통해 명령을 실행한다.

WRITE(STORE): 처리된 결과를 레지스터에 쓴다.


3) SQL Server에서의 스레드 작동 방식

그렇다면 SQL Server에서 스레드는 어떤 방식으로 실행될까 다시 넓은 의미에서 보면 운영체제 차원에서 CPU라는 자원을 직접 통제하고, 수많은 프로그램 중 하나인 SQL Server라는 프로그램을 실행한다. SQL Server 프로그램은 운영체제 차원에서 CPU에 할당된 스레드 중 하나를 할당해 실행한다. 즉 여러 개의 스레드를 통해 SQL Server를 실행하는 것이다. 이때 SQL Server는 효율적인 스레드 관리를 위해 SQLOS라는 작은 운영체제를 사용하게 된다. SQLOS는 메모리 관리자, I/O 관리자, 락(Lock) 관리자, 버퍼풀, 스레드 스케줄러 등으로 구성된다. SQLOS 내의 스레드 스케줄러에 의해 실제 운영체제에서 할당 받은 스레드들을 사용할 수 있다. 그렇다면 SQL Server에서 실제 스레드를 볼 수 있는 방법이 있을까 DMV를 통해 가능하다.
CPU와 스레드 관련 DMV는 크게 3가지로 구성돼 있다.


각각에 대한 설명은 [SQL Server에서 실제로 CPU를 모니터링 하는 방법]에서 조금 더 자세히 설명하고자 한다.





4) 스레드의 상태 변화

SQL Server에서 실제 스레드의 존재를 확인했으니 스레드의 상태 변화에 대해 알아보자.





RUNNING: 프로세서 코어에서 실제 스레드가 돌아가고 있는 상태를 의미한다. 오직 하나의 스레드만이 각 코어(프로세서)에서 실행이 가능하다.

SUSPENDED: 스레드가 아직 실행이 가능하지 않은 상태를 의미한다. SUSPENDED 상태 스레드들은 대기 리스트에 존재하게 된다.

RUNNABLE: 스레드가 사용 가능한 상태다. RUNNABLE QUEUE에 들어가 있는 상태로, 아직 실제 실행중인 상태는 아니다.

2. SQL Server에서 실제로 CPU를 모니터링하는 방법

실제 SQL Server에서 CPU 사용량과 관련된 리소스들을 모니터링하는 방법으로는 DMV, XEVENT, Agent Job+Alert+WMI가 있다.


1) CPU 리소스 모니터링 방법 1: DMV

이 방식에 대해 간략하게 설명하면 일단 CPU와 관련된 DMV들을 분석한다. 분석한 DMV의 RAW데이터를 C# 프로그래밍으로 수집하도록 만들 수도 있고, Agent job을 이용해 데이터를 저장할 수도 있다. 관련 DMV를 먼저 나열하면 다음과 같이 3개가 있다.





sys.dm_os_schedulers DMV는 CPU 프로세서와 관련이 있고, sys.dm_os_workers와 sys.dm_os_threads DMV는 스레드와 관련이 있다. SQL Server는 thread와 workers라는 두 개의 DMV가 존재하는데 비슷한 DMV로 생각하면 된다.
결국은 아래와 같은 DMV 쿼리를 통해 CPU_TIME이 높은 것을 찾아낼 수 있다.




정확하게는 CPU 사용률을 백분율로 나타낸다. 예를 들어, 현재 내가 사용할 수 있는 CPU 리소스가 1000이라고 가정하자. 만약 현재 시스템이 500이라는 리소스를 사용하고 있다면 현재 시간에 대한 CPU 사용률은 50%다. 이처럼 특정 시간 동안의 CPU 사용률은 전체 리소스 에 대한 실제 사용률이다. 그렇기 때문에 보통 CPU_TIME이 높은 쿼리가 많은 CPU 리소스를 사용했다고 가정할 수 있다. 그래서 결국은 위 DMV를 통해 CPU_TIME이 높은 순서로 쿼리를 찾아보면 문제가 되는 쿼리를 발견하게 된다.


2) CPU 리소스 모니터링 방법 2: XEVENT

XEVENT의 기본적인 지식을 알고 있다는 가정 하에 설명하고자 한다. XEVENT를 통한 분석 방법은 DMV와 비슷하다. XEVENT는 Profiler처럼 지속적으로 데이터를 저장하는 기능이 있다. 분석 방은 XEVENT 생성 → RAW데이터 저장 → 실제 활용한 데이터 추출이다. 실제 활용할 수 있는 데이터는 CPU_TIME과 같은 컬럼이지만, 단순히 CPU_TIME만을 보고 부하를 예측하기는 불가능하다. 그래서 전체 쿼리에 대한 정보 또한 같이 추가했다.
관련 내용은 아래 URL에서 더 자세히 살펴볼 수 있다.


https://www.sqlrx.com/sql-server-20122014-extended-events-for-developers-part-1/


① XEVENT를 생성

수집하는 정보는 Duration이 1초 이상인 모든 쿼리다.





② 데이터 보는 방법





③ 전체 데이터 분석 방법





여기서 핵심은 모든 구문을 다 자세히 알 필요는 없다는 것이다. 방법은 방법일 뿐이므로 단순하게 도구로만 사용하고 필요한 데이터만을 추출하면 된다.

3) CPU 리소스 모니터링 방법 3: Agent job과 Alert과 WMI 이용

이 방법은 MSDN Wiki에 쓴 deadlock을 잡는 방법을 조금 변형한 것이다. 관련 내용은 https://goo.gl/KSLBDa에서 살펴볼 수 있다.


실행 과정은 다음과 같다.


Agent Job 생성 → 실행하는 SP는 현재 Query 기록으로 남김 → WMI를 이용한 Alert 생성 → CPU 사용률이 일정 퍼센티지 이상일 경우 Job실행


① AgentJob을 생성하고 실행하는 SP는 만들어 둔 DMV를 활용한다.





이런 식으로 TABLE_XXX에 DMV 실행 결과를 저장하도록 SP를 만든다.


② WMI를 이용한 Alert를 생성한다. 그 후에 유형을 WMI 이벤트 경고로 선택한다.





[그림 6]의 ①처럼 ‘WMI 이벤트 경고’를 선택하고 ②네임스페이스에 경로를 써준다. 네임스페이스는 윈도우 OS에서 제공하는 기능으로 관련 리소스들을 볼 수 있는 공간으로 생각하면 된다.


□ 네임스페이스





아래 쿼리는 CPU 토털 사용률이 60% 이상일 경우를 말한다. PercentIdleTime이 60미만일 경우라는 조건이 있고, 이는 현재 사용할 수 있는 리소스가 60%가 남지 않은 경우를 의미한다. 즉 현재 CPU 사용률이 60퍼센트를 넘어가고 있다는 것이다.


□ 쿼리




이런 식으로 작성하고 다음과 같이 첫 번째로 만들었던 Job을 선택하면 된다.





이렇게 DMV, XEVENT, WMI를 이용한 Alert 방식을 사용해 실제 CPU 사용률을 모니터링할 수 있다. 마지막으로 간단한 실제 사례를 통해 설명하고자 한다.

3. 실제 사례 분석

CPU 사용량이 높아지는 경우를 살펴보자. 일단 실제 사례를 분석하기 위해 테스트 데이터를 만들어야 한다.


1) 테스트 데이터 생성




2) HASH 조인을 통한 CPU 사용량 확인





위와 같은 식으로 HASH조인을 쓸 경우, 실제 실행계획은 아래와 같다.





Parallelism을 사용할 경우 CPU 코어를 동시에 여러 개 사용하기 때문에 CPU에 부담이 간다. 실제로 CPU 사용량을 확인해 보면 순간적으로 CPU 사용량이 튀는 것을 보인다. 이 때 앞에서 설명한 [SQL Server에서 실제로 CPU를 모니터링 하는 방법]을 참고해서 History를 남기고 분석을 할 수 있다.


3) 해결방법

이럴 경우, 가장 적절한 해결방법은 HASH조인을 타지 않도록 인덱스를 만들어주는 것이다. 그래서 Nested Loop조인을 통해 CPU 부하를 줄일 수 있다.





실제 CPU를 얼마나 썼는지에 대한 분석법은 작업관리자를 통해 볼 수도 있지만, SET STATISTICS TIME ON이라는 명령어를 통해 CPU_TIME을 확인할 수도 있다.


그래서 아래처럼 인덱스를 만들기 전에는 CPU를 16ms를 썼지만, 인덱스를 만든 후에는 CPU TIME보다 Elapsed time이 늘어난 것을 볼 수 있다.





결론적으로 위와 같은 사례를 통해 적절한 인덱스 생성이 얼마나 CPU 사용량에 영향을 끼치는지 알 수 있다.