나른한 오후~

IT 관리자를 위한 필수 응급처치법

MS SQL 서버의 메모리가 부족해요!

박상옥|호스트웨이코리아 시스템 팀

MS SQL 서버의 메모리가 부족해요! MS SQL을 꾸준히 다뤄 온 시스템 관리자라면 한번쯤은 메모리가 부족하다는 에러 메시지를 경험해봤을 것이다. 그러나 무작정 시스템 사양을 늘릴 수만도 없는 노릇. 그렇다면 어떻게 대처하는 것이 비용과 업무량 면에서 효율적일까. 이번 호에는 SQL 관련 메모리 문제에 부딪혔을 때 이를 해결하는 방법을 살펴보고 그 방지책으로 SQL 서버 부하를 모니터링하는 방법을 알아본다.

A사는 게임 사이트의 DB 서버를 물리 메모리 2GB로 새로 구축했다. 그러나 얼마 지나지 않아 MS SQL 서비스가 메모리를 모두 사용해 여유 메모리가 남지 않는 상황에 몰리게 됐다. 결국 시스템 관리자는 메모리 추가를 요청했지만 메모리 업그레이드는 불가능했다. 해당 서버는 윈도우 2000 서버 운영체제에 MS SQL 서버 2000을 사용했기 때문에 물리적으로는 더 이상 메모리를 증설할 수 없었기 때문이다.

이처럼 MS SQL 서버를 사용하는 기업 가운데 메모리 부족 현상을 경험하는 경우가 종종 있다. 작업관리자에서 확인해보면 Sqlservr.exe 프로세스가 메모리를 모두 점유하고 있어 메모리가 부족해진 것이다. 그렇다면 이런 경우 시스템 관리자는 어떻게 대처해야 할까.

일반적으로 MS SQL 서버 2000을 설치한 후 기본 구성 그대로 사용할 경우 메모리는 동적으로 구성된다. 즉 서버의 물리적인 메모리를 모두 SQL 서버에 할당해 운영하다가, 운영체제에서 메모리를 요구할 경우 메모리를 반환하는 방식이다(따라서 DB 전용으로 구축해 사용하는 서버는 동적으로 메모리를 구성해 사용하는 것이 좋다). 하지만 실제 SQL 서버에서 활용하는 메모리는 어느 정도이고 구축된 하드웨어의 자원을 얼마나 사용하고 있을까. 시스템 관리자의 첫 번째 임무는 이것을 확인하는 것이다.

운영체제별 최대 지원 메모리는 <표 1>과 같다(앞서 언급한 A사의 경우 더 이상 메모리를 증설할 수 없는 이유도 Standard Edition의 경우 최대 2GB 메모리까지 지원하기 때문이다). 운영체제가 윈도우 2003이고 Enterprise Edition이라면 사용가능한 메모리는 더 늘어나겠지만 이 경우에도 SQL이 Standard Edition이면 2GB 이상 사용할 수 없다.

SQL 모니터링은 이렇게

만약 SQL 서버가 다른 애플리케이션과 함께 구동된다면 SQL이 메모리를 독점하는 것을 막기 위해 고정 메모리를 구성하는 것이 좋다. SQL 서버가 사용할 수 있는 메모리의 한계를 시스템적으로 정하는 것이다. 이처럼 SQL 서버가 고정 메모리를 할당하도록 하려면 <화면 1>과 같이 동적 메모리의 최소, 최대치를 같게 한 후 ‘고정된 메모리 크기 사용’ 옵션을 선택하면 된다. 저장 프로시저를 이용해 고정 메모리를 구성할 수도 있는데 다음과 같이 설정하면 된다(이 때 RECONFIGURE WITH OVERRIDE를 반드시 실행해야 한다).

sp_configure “show advanced options”, 1
go
sp_configure “max server memory”, 512
go
RECONFIGURE WITH OVERRIDE
go

이처럼 메모리를 어떻게 할당해 활용할 것인가에 대한 판단이 끝났다면 현재 시스템의 사용량을 측정해 향후 시스템 확장 계획의 기본 자료로 삼아야 한다. 먼저 시스템의 성능을 모니터링하려면 <화면 2>와 같이 『시작 → 프로그램 → 관리도구 → 성능』을 이용한다. 시간의 추이에 따라 성능의 변화를 통해 이상 징후를 발견하면 적절한 조치를 내리면 된다. 여기에 새로 모니터링하고자 하는 성능 카운터를 추가하려면 <화면 3>과 같이 선택하면 된다.

메모리 모니터링시 관심을 가져야 할 성능 개체는 <표 2>에 정리했다. 각 성능 개체별로 그 의미와 임계치 등을 확인해 그 값에 따라 적절한 조치를 해주면 된다. 이밖에도 CPU를 모니터링할 때는 Processor와 System 성능 개체에 관심을 가질 필요가 있다. 이 가운데 Processor 개체는 CPU 사용률을 나타내는 것으로 24시간을 주기로 80% 이상 10분 동안 유지된다면 CPU에 병목현상이 발생하는 것이므로 이에 대한 보완책을 세워두는 것이 필요하다. System 개체는 프로세서를 얻기 위해 프로세서 큐에서 대기한 쓰레드 수를 의미한다. CPU 1개당 값이 2보다 크면 병목현상이 나타나는 것이므로 주의깊게 살펴봐야 한다.

디스크 모니터링은 크게 두 가지로 나눠 살펴볼 수 있다. 먼저 일반 디스크의 경우 디스크 사용률을 나타내는 PhysicalDisk 개체의 %Disk Time는 90보다 낮고, Avg.Disk Queue Length 개체는 2보다 낮은 것이 정상이다. 단, 어레이(Array) 디스크인 경우에는 다음을 참고한다.

[RAID 0]
Physical Disk: Disk Reads/sec + Disk Write/sec / Disk count < 100
[RAID 1]
Physical Disk: Disk Reads/sec + (2 * Disk Write/sec) / 2 < 100
[RAID 10]
Physical Disk: Disk Reads/sec + (2 * Disk Write/sec) / Disk count < 100
[RAID 5]
Physical Disk: Disk Reads/sec + (4 * Disk Write/sec) / Disk count < 100

이처럼 다양한 성능모니터를 통해 시스템 자원을 관찰하는데 해당 수치는 최소 하루 이상 모니터링한 결과에 10분간 지속되는 값을 대입해야 정확한 값을 얻을 수 있다. 또한 이것은 SQL의 모니터링이므로 다른 응용 프로그램의 작업이 일어날 경우는 이를 배제해야 한다. 지금까지 살펴본 성능 모니터 값을 카운터 로그로 남겨 놓으면 이를 이용해 <화면 4>와 같이 시스템 상태 그래프를 추출할 수 있다.

<화면 1> 고정된 메모리 크기 사용

<화면 2> 윈도우 성능 모니터

<화면 3> 새로운 카운터 추가

<화면 4> 실제 카운터 로그로 추출한 시스템 상태 그래프

<화면 5> 프로필러를 이용한 모니터링

<화면 6> SQL 서버 Best Practices Analyzer

<화면 7> SQL Best Practices Analyzer Reports

<표 2> 메모리 모니터링