관리 - 메모리 사용량 관리 방법

 

SQL 서버의 메모리 관리의 필요성

 

 SQL 서버를 사용하다 보면 메모리 부족 현상을 경험하는 경우가 종종 있습니다. 작업 관리자에서 확인해보면 sqlserver.exe 프로세스가 메모리를 모두 점유하고 있어 메모리가 부족해 진 것입니다. SQL 서버 2008은 대량의 데이터를 조회하게 되면 sqlserver.exe 프로세스가 서버의 메모리를 점유하게 됩니다. 이 점유된 메모리는 조회가 완료되거나 해당 어플리케이션이 완전히 종료되어도 SQL 서버가 다시 시작하지 않는 이상 그대로 유지되어 있습니다. 즉 메모리를 사용 후 반환하지 않습니다. 이러한 상황은 많은 조건의 작업에서 계속 누적되어 결국 서버 메모리 부족으로 시스템이 느려지거나 다운되는 현상이 일어나게 됩니다.

 

 

 

MS SQL 서버의 메모리 할당의 특징

 

 SQL 서버 2008 이전의 SQL 서버는 동적 메모리를 지원했으므로 시스템에 여유 메모리가 있을 때 SQL 서버에 자동으로 메모리 사용을 조정할 수 있습니다. 그러나 SQL 서버 2008에서는 사용할 수 있는 메모리의 양의 제한 사항이 없어졌습니다. 여기서 문제가 발생했습니다. 시스템 상에서 메모리 사용이 필요한 경우를 제외하고는 메모리의 양을 계속해서 증가시켜 갈 수 있다는 것입니다.

 SQL 서버는 주기적으로 시스템을 쿼리하여 사용할 수 있는 실제 메모리양을 확인합니다. QueryMemoryResourceNotification을 사용하여 버퍼 풀이 메모리를 할당하고 해제하는 시기를 결정합니다. 서버의 활동에 따라 버퍼 캐시를 늘리거나 줄여 사용 가능한 실제 메모리를 4MB에서 10MB 사이로 유지합니다. 사용 가능한 메모리를 이 수준으로 유지 관리하여 페이징을 방지합니다.

 

 일반적으로 SQL 서버를 설치한 후 기본 구성 그대로 사용할 경우 메모리는 동적으로 구성됩니다. 즉 서버의 물리적인 메모리를 모두 SQL 서버에 할당해 운영하다가 운영체제에서 메모리를 요구하는 경우 메모리를 반환하는 방식입니다. 이 방식은 운영체제가 너무 많은 메모리를 다른 응용 프로그램에게 양도하지 못하도록 합니다. 너무 많은 메모리가 다른 응용 프로그램에게 양도되면 SQL 서버의 사용자에 대한 응답시간이 느려지게 됩니다. 따라서 DB 전용으로 구축해 사용하는 서버는 동적으로 메모리를 구성해 사용하는 것이 좋습니다.

 

 즉, SQL 서버는 운영체제가 사용 가능한 메모리가 작을 경우 메모리를 운영체제에게 반환하고 사용 가능한 메모리가 많은 경우 메모리를 버퍼 풀에 할당합니다. 또한 작업에 메모리가 필요한 경우에만 버퍼풀에 추가하기 때문에 유휴 상태의 서버는 버퍼 풀의 크기가 변하지 않습니다.

 

 결론적으로 한번 할당된 메모리는 OS가 필요한 경우를 제외하고 반환하지 않습니다. SQL 서버는 메모리 사용이 필요할 때 가져다 쓰는 방식이 아니라 점유하고 있다가 사용하는 방식입니다. 왜 SQL 서버는 사용 후 메모리를 반환하지 않을까? 이유는 재사용성 때문입니다. SQL 서버가 프로시저, 파라미터 쿼리 등에 속도가 빠른 이유는 컴파일 후 메모리에 상주해 있다가 바로 실행시키기 때문입니다. 데이터베이스 성능과 메모리 사용과의 관계는 밀접하기 때문에 할당한 메모리를 계속해서 사용하기 위함입니다.

 하지만 데이터베이스 서비스 뿐만 아니라 파일 및 프린터 서버로 사용되는 시스템 환경에서 SQL 서버가 너무 많은 메모리를 차지하고 있다면 설정을 변경할 필요가 있습니다. 하지만 실제 SQL 서버에서 활용하는 메모리가 어느 정도이고 구축된 하드웨어의 자원을 얼마나 사용하고 있을까를 확인하는 것이 시스템 관리자의 첫 번재 임무입니다.

 

 

 

SQLl 서버의 메모리 할당량 확인

 

1. SSMS를 실행하여 [개체 탐색기]에서 연결된 서버에 마우스 우클릭하여 [속성]을 선택합니다.

 

 

  

2. 서버 속성에서 [메모리] 페이지를 선택하여 메모리 할당 상황을 파악합니다.

 

 

 위의 AWE(Address Windowing Extension)은 32비트 운영체제에 대량의 메모리에 액세스 할 수 있게 합니다. 하지만 64비트 운영체제라면 선택할 필요가 없습니다.

 

 

 

윈도우 성능 모니터링을 사용하여 실제 시스템 상 SQL 서버가 사용하고 있는 메모리 양 확인 방법

 

 메모리 양을 확인할 때는 윈도우 성능 모니터링을 사용하거나 SQL 서버 관리자를 사용하여 확인할 수 있으며 아래의 방법은 윈도우 성능 모니터링을 사용한 방법입니다.

 

 

1. [시작-관리도구-성능 모니터]를 선택하거나 실행창에 [perfmon.msc]을 입력합니다. 성능 모니터가 나타나면 [사용자 정의-새로 만들기-데이터 수집기 집합]을 선택합니다.

 

 

2. 새 데이터 수집기 이름을 입력합니다.

 

 

3. 사용할 템플릿을 지정(System Performance를 선택)합니다.

 

 

4. 데이터를 저장할 경로 지정합니다.

 

 

5. 완료

 

 

이제 카운터를 등록합니다.

 

1. 새로 만든 사용자 정의를 마우스 우클릭하여 [새로 만들기-데이터 수집기]를 선택합니다.

 

 

2. 새 데이터 수집기의 이름을 입력과 종류를 선택합니다.

 

 

3. 성능 카우터를 추가하기 위해 [추가]버튼을 클릭합니다.

 

 

4. 필요한 종유의 시스템 카운터를 선택합니다. 이 때 선택해야 하는 종류는 아래의 설명하겠습니다.

 

 

5. 성능 카운터 확인

 

 

이제 성능 카운터를 등록하였습니다. 이제 성능을 확인하는 방법입니다.

 

1. 사용자 정의를 선택한 후 빨간색 네모칸을 클릭하여 분석을 시작합니다.

 

 

2. 분석이 완료되면 보고서에 새로운 내용이 작성되었습니다. 새로운 내용을 더블클릭합니다.

 

 

3. 등록했던 카운터의 성능 정보를 확인할 수 있습니다.

 

 

이 때 SQL 서버의 성능 및 메모리 상태를 확인하기 위한 카운터의 종류와 설명은 다음 표와 같습니다.

 

성능개체	카운터	설명	임계치
Memory	Available Bytes	실제 사용할 수 있는 메모리 양. 즉 남아있는 메모리 양	이 카운터는 항상 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	Free Pages	할당하지 않는 SQL 서버 메모리 버퍼수	5MB이하면 물리적 메모리 부족
SQL Server: Memory Manager	Targer Server Memory	SQL 서버가 사용할 수 있는 전체 메모리 양	이 값을 모니터링해 메모리 설정을 결정
SQL Server: Buffer Manager	Total Server Memory	SQL 서버가 사용하고 있는 전체 메모리 양	이 값을 모니터링해 메모리 설정을 결정

 

• Available Bytes: 현재 프로세스에 사용할 수 있는 메모리의 바이트 수를 나타냅니다. 이 값이 작으면 컴퓨터 전체 메모리가 부족하거나 응용 프로그램이 메모리를 해제하지 않는다는 의미
• Pages/sec: 하드 페이지 폴트 때문에 디스크에서 가져오거나 작업 집합 내의 디스크 여유 공간에 쓴 페이지 수를 나타냄. 이 비율이 높으면 페이징이 과도하다는 의미이다. 페이징의 원인이 디스크 작업인지 확인할려면 memory:page faults/sec 카우터를 모니터링
• Working set: 프로세스에서 사용하는 메모리의 양을 나타냄. 이 숫자가 Min server memory 및 Max server memory 서버 옵션에 설정된 메모리 양보다 작으면 SQL server가 메모리를 너무 많이 사용하도록 구성된 것
• Buffer Cache Hit Ratio: 90%이상의 비율이 알맞다. 이 값이 90%보다 크게 유지될 때까지 메모리를 추가하십시오. 값이 90%보다 크면 데이터 캐시를 통해 모든 데이터 요청의 90% 이상이 충족된 것
• Total Server memory(KB): 컴퓨터의 실제 메모리 양과 비교하여 계속 높게 나타나면 메모리를 추가해야 함

 

 

필수적으로 확인해야 할 값은 위의 값과 아래 값입니다.

• Process: Working set
• SQL Server: Buffer manager: Buffer Cache Hit Ratio
• SQL Server: Buffer Manager: Total Pages
• SQL Server: Memory Manager: Total Server Memory(KB)

 

 이제 SQL 서버가 하드웨어 자원을 사용하는 범위를 확인 할 수 있습니다. 이제 설정을 통해 최적의 SQL Server와 OS 환경을 구축하면 됩니다. SQL Server의 메모리 사용량을 제한하기 위해서는 Min Server Memory 값과 Max Server Memory 값을 수정합니다. Min Server Memory와 Max Server Memory 두 가지 서버 메모리 옵션을 사용하여 SQL Server 인스턴스에서 사용하는 버퍼 풀의 메모리 양를 구성할 수 있습니다. 

 

 

 

Min Server Memory 과 Max Server Memory

 

 MS SQL Server 데이터베이스 엔진의 버퍼 풀에서 사용하는 메모리의 하한선을 설정합니다. 버퍼 풀은 min server memory로 지정한 값에 해당하는 메모리를 즉시 확보하지 않습니다. 버퍼 풀은 초기화하는데 필요한 메모리만으로 시작합니다. 하지만 데이터베이스 엔진은 작업이 증가할 때마다 버퍼 풀에서 필요한 메모리를 계속해서 확보합니다. 버퍼 풀은 Min server memory에 지정된 양에 도달할 때까지 확보한 메모리를 해제하지 않습니다. Min 값에 도달하면 버퍼 풀은 표준 알고리즘을 사용하여 필요할 때 메모리를 확보하고 해제합니다.

 이 두 값의 유일한 차이점은 버퍼 풀이 Min Server Memory에 지정된 수준 아래로 메모리 할당량을 떨어 뜨리지 않고 Max Server Memory에 지정된 수준보다 더 많은 메모리를 확보하지 않는 다는 것입니다.