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> 새로운 카운터 추가
<표 1> SQL 버전별 지원되는 메모리 한계
| 운영체제 | Enterprise | Standard | Personal | Developer |
| 윈도우 2000 데이터센터 | 64GB | 2GB | 2GB | 64GB |
| 윈도우 2000 어드밴스드 서버 | 8GB | 2GB | 2GB | 8GB |
| 윈도우 2000 서버 | 4GB | 2GB | 2GB | 4GB |
| 윈도우 2000 프로페셔널 | N/A | N/A | 2GB | 2GB |
<화면 4> 실제 카운터 로그로 추출한 시스템 상태 그래프
<화면 5> 프로필러를 이용한 모니터링
<화면 6> SQL 서버 Best Practices Analyzer
<화면 7> SQL Best Practices Analyzer Reports
<표 2> 메모리 모니터링
| 성능 개체 | 카운터 | 설명 | 임계치 |
| Memory | Availavle MBytes | 실제 사용할 수 있는 메모리 양. 즉 남아있는 메모리 양 | 이 카운터는 항상 5MB보다 커야 한다. |
| Memory | Pages/sec | 메모리에서 디스크로 Page Out 되거나 디스크에서 메모리로 Page in 된 초당 페이지수 | 값이 0이어야 함. SQL 전용 서버의 경우 평균 0~20, 20을 초과할 경우 물리적인 메모리가 부족한 것임 |
| Process | Page Faults/Sec SQL Server Instance | 초당 페이지 없음. 오류수 | 이 값 또한 0이어야 함. 20을 초과할 경우 메모리가 부족함 |
| Process | Working Set SQL Server Instance | SQL 서버가 사용하는 인스턴트별 메모리 양 | 5MB 보다 높아야 한다. 이보다 낮다면 메모리 부족 |
| SQL Server: Buffer Manager | Buffer Cache hit Ratio | 캐시 적중율 | 90%보다 높아야 한다. |
| SQL Server: Buffer Manager | Free Pages | 할당하지 않은 SQL 서버 메모리 버퍼 수 | 5MB 이하면 물리적 메모리가 부족하다. |
| SQL Server: Memory Manager | Target Server Memory | SQL 서버가 사용할 수 있는 전체 메모리 양 | 이 값을 모니터링해 메모리 설정을 결정 |
| SQL Server: Buffer Manager | Total Server Memory | SQL 서버가 사용하고 있는 전체 메모리 양 | 이 값을 모니터링해 메모리 설정을 결정 |
실행 중인 SQL문을 모니터링하자
지금까지 전반적인 시스템 모니터링 방법을 살펴봤다. 지금부터는 실제로 운영되는 SQL문을 모니터링하는 방법을 살펴보자. SQL 서버를 설치하면 ‘SQL 프로필러(Profiler)’라는 툴이 같이 설치되는데 이를 이용해 <화면 5>와 같이 쿼리문의 Duration 항목을 보면 실행시간을 확인할 수 있다. 여기서 실행시간이 비정상적으로 긴 쿼리가 발견되면 문제가 발생한 것일 가능성이 크다. 이 밖에도 프로필러를 이용하면 SQL 서버의 보안감사 등 다양한 작업을 할 수 있다.
요근래 마이크로소프트는 기타 유용한 툴들과 함께 각 서버별로 BPA(Best Practices Analyzer) 툴을 제공한다. BPA 툴이란 일반적으로 알려진 최적의 사례를 현재 사용하고 있는 데이터베이스에 적용시켜주는 데이터베이스 관리 툴로, 주로 SQL의 사용률과 관리 측면에 초점을 맞췄다. 이 툴은 MS 웹사이트(www.microsoft.com/ downloads)에서 ‘SQL Best Practices Analyzer’로 검색하면 다운로드할 수 있다.
SQL BPA를 설치하면 SQL 서버에 sqlbpa라는 데이터베이스를 생성해 검색한 결과를 저장하며 <화면 7>과 같은 리포트를 출력하므로 이를 토대로 SQL의 상태와 관리에 관련해서 참고할 수 있다. 이 때 한 가지 주의할 사항은 SQL BPA를 실행하면 검사대상 데이터베이스를 직접 액세스한다는 점이다. 이 과정에서 일정 정도의 SQL의 부하가 발생하므로 사용량이 적은 시간에 실행하는 것이 바람직하다.
출처명: 마이크로소프트웨어 [2005년 10월]'
