sql 2008 메모리 할당

 MS SQL 서버를 사용하다 보면 메모리 부족 현상을 경험하는 경우가 종종 있다. 작업관리자에서 확인해보면 sqlserver.exe 프로세스가 메모리를 모두 점유하고 있어 메모리가 부족해진 것이다. MS SQL 서버 2008은 대량의 데이터를 조회하게 되면 sqlserver.exe 프로세서가 서버의 메모리를 점유하게 된다. 이 점유된 메모리는 조회가 완료되거나 해당 어플리케이션이 완전히 종료되어도 SQL Server가 다시 시작하지 않는 이상 그대로 유지되어 있다. 즉, 메모리를 사용 후 반환하지 않는다.

이러한 상황은 많은 조건의 작업에서 계속 누적되어 결국 서버 메모리 부족으로 시스템이 느려지거나 다운되는 현상이 일어나게 된다. 그러나 메모리가 부족하다고 해서 무작정 시스템 사양을 늘릴 수만도 없는 노릇이다. 그렇다면 어떻게 대처하는 것이 비용과 업무량 면에서 효율적일까?

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

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

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

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

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

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

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

SQL 서버의 메모리 할당량 확인하기

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

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

AWE(Address Windowing Extension)은 32비트 운영체제에서 대량의 메모리에 액세스 할 수 있게 한다. Windows 2008 R2 버전에 설치된 SQL 서버라 선택하지 않았다. 여기서 최소 서버 메모리와 최대 서버 메모리를 조정하여 SQL 서버의 메모리 상태를 관리할 수 있다.

실제 시스템 상에서 SQL 서버가 사용하고 있는 메모리 수준이 적당하지 확인하기

상태를 관리하기 위해서는 Windows의 성능 모니터링을 사용하거나 SQL Server 관리자를 사용하여 확인할 수 있으며 아래의 방법은 Windows의 성능 모니터링을 사용한 방법이다.

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에 지정된 수준보다 더 많은 메모리를 확보하지 않는 다는 것이다.